Data table bulk insert - Does not store accented characters properly

[ashok.k]

Hi,

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

string strData ="Accented chars- Les caract?res accentu?s fran?ais ";

DataTable dtTemp =newDataTable();
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 =newSqlConnection(strSQLCon))
{
cn.Open();
using(SqlBulkCopy copy =newSqlBulkCopy(cn))
{
copy.ColumnMappings.Add("ID","ID");
copy.ColumnMappings.Add("Value","Value");

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

The French/european language characters are not stored properly in SQL server data base.
It works fine when i do a normal insert query. insert into MYTABLE values(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.


Thanks
Ashok

 

[wim sturkenboom]

I'm definitely not the specialist on encodings, but I tried your code and it works fine. Using SQL server 2008R2, VS Ultimate 2012 and framework 4.5.

Some points that I can think of

• collation; what is the default collation of your database?
• datatype of the column in your database (varchar, nvarchar)? SORRY, see you mentioned nvarchar
• string encoding; the only reference on the web that I can find and might relate to this problem is the encoding of the string Accented chars- Les charactres accentu?s fran?ais (interpretation of source: utf 8 - How to write UTF-8 characters using bulk insert in SQL Server? - Stack Overflow)

You can maybe use the SQL profiler to see what happens when you insert the data (source: c# - SqlBulkCopy Unicode/UTF8 error - Stack Overflow)

It works fine when i do a normal insert query. insert into MYTABLE values(1 , 'Accented chars- Les caract?res accentu?s fran?ais')

Do you do this using SSMS or from C# code? I assume SSMS, have you tried a simple insert query (instead of the bulkcopy) from your c# code?

 

[ashok.k]

thanks for your reply. The issue seems to occur while reading the csv file into data table before bulk insert. I included the encoding parameter while reading the csv file. (Encoding.Default) and it loads the french text properly and it gets stored in SQL DB without any issues.
old code: List lstData = File.ReadAllLines(stFile).ToList();
Working code: List lstData = File.ReadAllLines(stFile, Encoding.Default).ToList();
Thanks
Ashok