How can I add Arabic content from Excel file into SQL Server database?

Question

I have an issue about how SQL Server doesn't recognize Arabic language even if I have field as nvarchar(MAX) and even if I add N' before value in my SQL query.

This is my table in database:

Id_Test : int
Arabic  : nvarchar(MAX)

and after importing my Excel file containing Arabic, this what I have get:

Id_Test | Arabic
--------+-----------------------
1       | ?????????????????????

Here is my code:

private void Button_Click(object sender, RoutedEventArgs e)
{
        OpenFileDialog openfile = new OpenFileDialog();
        openfile.DefaultExt = ".xlsx";
        openfile.Filter = "(.xlsx)|*.xlsx";
        //openfile.ShowDialog();

        var browsefile = openfile.ShowDialog();

        if (browsefile == true)
        {
            txtFilePath2.Text = openfile.FileName;

            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            //Static File From Base Path...........
            //Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory + "TestExcel.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            //Dynamic File Using Uploader...........
            Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(txtFilePath2.Text.ToString(), 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.get_Item(1); ;
            Microsoft.Office.Interop.Excel.Range excelRange = excelSheet.UsedRange;

            string strCellData = "";
            double douCellData;
            int rowCnt = 0;
            int colCnt = 0;

            DataTable dt = new DataTable();

            for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
            {
                string strColumn = "";
                strColumn = (string)(excelRange.Cells[1, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
                dt.Columns.Add(strColumn, typeof(string));
            }

            for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)
            {
                string strData = "";

                for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)
                {
                    try
                    {
                        strCellData = (string)(excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
                        strData += strCellData + "|";
                    }
                    catch (Exception ex)
                    {
                        douCellData = (excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
                        strData += douCellData.ToString() + "|";
                    }
                }

                strData = strData.Remove(strData.Length - 1, 1);
                dt.Rows.Add(strData.Split('|'));
            }

            dtGrid2.ItemsSource = dt.DefaultView;
            string sql = "";

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sql = sql + "insert into Test (Arabic) values('"
                      + dt.Rows[i]["Arabic"].ToString().Replace("'", "''") + "')";
            }

            using (SqlConnection connection = new SqlConnection(_ConnectionString))
            {
                connection.Open();
                //if (_serv.ServeurID == 0) throw new Exception("record does not exist in dataserveur table.");
                SqlCommand cmd = new SqlCommand(sql, connection);

                cmd.ExecuteNonQuery();

                connection.Close();
            }

            excelBook.Close(true, null, null);
            excelApp.Quit();
        }
    }

This is what I have changed before but it's not working:

+ dt.Rows[i]["Arabic"].ToString().Replace("N'", "''") + "')";

Thank you.

use a typed parameter. this solves two issues at once: 1) no need for the N prefix, there will no longer be a string literal; 2) no SQL injection vulnerability — Cee McSharpface, Nov 01 '17 at 10:04
@dlatikay It's not the case because I have tried to add N' and it has not worked for me. and concerning typed parameter, can you explain more? thank you sir — Z4N4TI, Nov 01 '17 at 10:09
I see. just to clarify this for me, what you wrote last in your answer looks like you had replaced N-something *inside* the strings instead of (correctly) writing `values(N'" + dt.Rows[i]["Arabic"].ToString() + "'")`? The escaping (doubling) of quotes *inside the string* is a different story which by the way is discouraged because it leaves so many possibilities for errors and vulnerabilities. [use a parameter](http://bobby-tables.com/csharp) — Cee McSharpface, Nov 01 '17 at 10:11

score 1 · Answer 1 · answered Nov 01 '17 at 10:02

1

if the column is to always have arabic in it, then you need to adjust the columns collation to

Arabic_CI_AI_KS_WS

answered Nov 01 '17 at 10:02

bilpor

3,467
6
32
77

No , I can have french and arabic in the same time, that's why – Z4N4TI Nov 01 '17 at 10:08

How can I add Arabic content from Excel file into SQL Server database?

1 Answers1