如何将阿拉伯语内容从Excel文件添加到SQL Server数据库中

Question

我有一个关于SQL Server如何识别阿拉伯语的问题，即使我将字段作为nvarchar(MAX)，即使我在SQL查询中的值之前添加N'。

这是我在数据库中的表：

Id_Test : int
Arabic  : nvarchar(MAX)

在导入包含阿拉伯语的 Excel 文件后，我得到了：

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

这是我的代码：

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();
        }
    }

这是我之前更改的，但它不起作用：

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

谢谢。

Answer 1

如果列中始终包含阿拉伯语，则需要将列排序规则调整为

Arabic_CI_AI_KS_WS