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.
Accepted Answer
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. 
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:
Notice no invalid entries whatever the sql char type!.
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)