Data table bulk insert - Does not store accented characters properly

ashok.k

Member
Joined
Nov 17, 2014
Messages
14
Programming Experience
5-10
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

Well-known member
Joined
Aug 6, 2014
Messages
85
Location
Roodepoort, South Africa
Programming Experience
10+
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

Member
Joined
Nov 17, 2014
Messages
14
Programming Experience
5-10
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
 
Top Bottom