C# Data table bulk insert - Does not store accented characters properly in SQL server 2008

Question

0.00/5 (No votes)

See more:

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.

C#

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_Code" ] = 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 = "MTABLE";
		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 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.

Thanks
Ashok

Posted 17-Nov-14 3:54am

Ashokkuma

Updated 17-Nov-14 3:56am

Kornfeld Eliyahu Peter

v2

Add a Solution

Comments

Kornfeld Eliyahu Peter 17-Nov-14 9:58am

http://msdn.microsoft.com/en-us/library/ms378988(v=sql.100).aspx
Look for the sendStringParametersAsUnicode parameter, you may changed it from the default true to false?

What driver do you use to connect?

Ashokkuma 18-Nov-14 5:05am

Thanks for your reply. it works fine after i included encoding parameter while reading the csv file into Datatable

Kornfeld Eliyahu Peter 18-Nov-14 5:14am

So accent went home while reading and not while writing... :-)
Glad you solved it!

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-11-17T05:22:00

Solution 1

As well as the sendStringParametersAsUnicode parameter Peter mentioned, also check your table definition: if it is VARCHAR then that may be the problem. Try NVARCHAR instead, as it supports Unicode.

Posted 17-Nov-14 5:22am

OriginalGriff

Ashokkuma · Accepted Answer · 2014-11-17T23:05:00

thanks for your replies. 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