SqlBulkCopy does not store accented characters properly

Question

SqlBulkCopy does not store accented characters properly

datatable sqlbulkcopy sql-server-2008

Question

I am inserting French language text into nvarchar column in SQL server 2008. The French accented characters are not stored properly in the SQL DB.

string strData = "Accented chars- Les caractÃ¨res accentuÃ©s franÃ§ais ";

DataTable dtTemp = new DataTable();
dtTemp.Columns.Add("ID", typeof(string));
dtTemp.Columns.Add("Value", typeof(string));

DataRow dr = dtTemp.NewRow();
dr["ID"] = "100";
dr["Value"] = strData;
dtTemp.Rows.Add(dr);


strSQLCon = GetSQLConnectionString();
using (SqlConnection cn = new SqlConnection(strSQLCon))
{
    cn.Open();
    using (SqlBulkCopy copy = new SqlBulkCopy(cn))
    {
        copy.ColumnMappings.Add("ID", "ID");
        copy.ColumnMappings.Add("Value", "Value");

        copy.DestinationTableName = "MYTABLE";
        copy.WriteToServer(dtTemp);
    }
}

The French characters are not stored properly in SQL server data base. It works fine when i do a normal insert query. insert into MYTABLEvalues(1 , 'Accented chars- Les caractÃ¨res accentuÃ©s franÃ§ais')

Please let me know why it does not work with SQL Bulk copy class. Any settings need to be changed or C# code needs to be modified to store the non-English characters properly.

user3219354

Popular Answer

I tested your code on the following table and it worked fine, at least on SQL Server 2012.

CREATE TABLE [dbo].[MYTABLE](
    [ID] [varchar](20) NOT NULL,
    [Value] [nvarchar](255) NOT NULL)

Svein FidjestÃ¸l

View more on Stack Overflow

andrei.ciprian · Accepted Answer · 11/17/2014 3:33:13 PM

I am designing this table, the collation for every column is set to French_CI_AS, French culture, accent sensitive. Every sql string type considered. french collation

I am building a typed dataset for this table (not the purpose of this question).

Sql bulk copy:

  var ds = new FrenchCharacters.FrenchDataSet();
  using (var destinationConnection = new SqlConnection(StackOverflow.Properties.Settings.Default.StackOverflowConnectionString))
      {
        destinationConnection.Open();
        //all French characters http://en.wikipedia.org/wiki/French_orthography
        string[] sArray = new string[] { 
            "Ã€Ã , Ã‚Ã¢, Ã†Ã¦, Ã„Ã¤"
            , "Ã‡Ã§"
            , "ÃŽÃ®, ÃÃ¯"
            , "Ã”Ã´, Å’Å“, Ã–Ã¶"
            , "Ã™Ã¹, Ã›Ã», ÃœÃ¼"
            , "Å¸Ã¿"
          };
        // open the connection
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
        {
          bulkCopy.BatchSize = 500;
          bulkCopy.NotifyAfter = 10000;
          bulkCopy.DestinationTableName = "French";
          //
          // build data table to be written to the server
          // data table is now strongly-typed ds.French
          //
          for (int i = 0; i < 100; i++)
          {
            foreach (string s in sArray)
              ds.French.AddFrenchRow(s, s, s, s, s, s);                 
          }
          //
          bulkCopy.WriteToServer(ds.French);
        }
      }

result: enter image description here

Notice no invalid entries whatever the sql char type!.

SqlBulkCopy does not store accented characters properly

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator