C＃数据表批量插入 - 不在SQL Server 2008中正确存储重音字符 [英] C# Data table bulk insert - Does not store accented characters properly in SQL server 2008

问题描述

我在SQL Server 2008中将欧洲语言文本插入nvarchar列。重音字符未正确存储在SQL DB中。

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 = 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 );
	}
}

法语/欧洲语言字符未正确存储在SQL Server数据库中。

我正常插入查询时工作正常。插入MYTABLEvalues（1，'Accented chars-Lescaractèresaccentuésfrançais'）



请让我知道为什么它不适用于SQL Bulk复制类。需要更改任何设置或需要修改C＃代码以正确存储非英文字符。



谢谢

Ashok

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

推荐答案

除了Peter提到的sendStringParametersAsUnicode参数外，还要检查你的表定义：如果它是VARCHAR那么这可能是问题。请尝试使用NVARCHAR，因为它支持Unicode。

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.

感谢您的回复。在批量插入之前将csv文件读入数据表时似乎会出现此问题。我在读取csv文件时包含了编码参数。 （Encoding.Default）并且它正确加载法语文本并且它存储在SQL DB中没有任何问题。



旧代码：List lstData = File.ReadAllLines（stFile ）.ToList（）;



工作代码：List lstData = File.ReadAllLines（stFile，Encoding.Default）.ToList（）;



谢谢

Ashok

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

这篇关于C＃数据表批量插入 - 不在SQL Server 2008中正确存储重音字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！