Insert Arabic Words into Database

Often at forums.asp.net we come across the problem that occurs while inserting foreign language to the database. Supopose I wanted to insert 'اتصالات' to a table named 'table_test'. When I look at the entry at all I see is ?????.

Why did this happen? Its because the field we enter the data needs to be having COLLATE Arabic.

To understand that we must first understand what collation is.

What is collation?

Collation refers to a set of rules that determine how data is sorted and compared.
Character data is sorted using rules that define the correct character sequence,
with options for specifying
1) case-sensitivity,
2) accent marks,
3) kana character types and
4)character width.

Case sensitivity

If A and a, B and b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent sensitivity

If a and á, o and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and áis 225. The ASCII value of o is 111 and ó is 243.

Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity

When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

Now we have identified the problem. Lets try to solve it using an example.
In this example I am saving a textbox value to database on the click of a button.

Suppose I have a table named table_test with two columns
1. [myid] int auto
2. [mytext] nvarchar(50)

I want to enter the arabic word to the field mytext.

Step1
At Database provide collate to the field on which we want to insert the arabic word

ALTER TABLE test_table
ALTER COLUMN mytext VARCHAR(50) COLLATE Arabic_CI_AI

Step 2

protected void myButton_Click(object sender, EventArgs e)
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["forums_ConnectionString"].ConnectionString))
    {
        conn.Open();
        string sql = String.Format("INSERT INTO test_table (mytext) VALUES (N'{0}')",
            mytext.Text.Trim());
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            cmd.ExecuteNonQuery();
        }
    }
}

Please note that I have put N' before the value.
Read the article Why do some SQL strings have an 'N' prefix? if you want to know its significance.

Related Posts :