使用ADO.Net写UTF-8到SQL Server文本字段和维护UTF-8字节 [英] Write utf-8 to a sql server Text field using ADO.Net and maintain the UTF-8 bytes
问题描述
我有一些XML编码为UTF-8，我想写这在SQL Server中的文本字段。 UTF-8是处理文本的字节兼容，所以它应该能够做到这一点，然后读出XML后来还是编码为UTF-8。
I have some xml encoded as UTF-8 and I want to write this to a Text field in SQL Server. UTF-8 is byte compatible with Text so it should be able to do this and then read out the xml later still encoded as utf-8.
不过特殊字符，如。AAO，这是多字节的UTF-8得到改变的方式。
However special characters such as ÄÅÖ, which are multi-byte in UTF-8 get changed on the way.
我有这样的代码：
byte[] myXML = ...
SqlCommand _MyCommand = new SqlCommand(storeProcedureName, pmiDB.GetADOConnection());
_MyCommand.CommandType = CommandType.StoredProcedure;
_MyCommand.Parameters.Add("xmlText", SqlDbType.Text);
_MyCommand.Parameters["xmlText"].Value = Encoding.UTF8.GetString(myXML);
_MyCommand.ExecuteNonQuery();
我的猜测是，改变XML的字节数组到字符串转换特殊字符为UTF-16字符，然后再改为Latin1的。而Latin1的高龄津贴是不一样的UTF-8 OAA。
My guess is that changing the xml byte array to string changes the special characters to UTF-16 characters which are then changed again to the Latin1. And Latin1 ÖÄÅ are not the same as UTF-8 ÖÄÅ.
我怎么能写UTF-8 XML字节的文本字段没有他们得到改变？
How can I write the UTF-8 xml bytes to the Text field without them getting changed?
推荐答案
这是我工作的解决方案是更改存储过程，以使与myXML参数为VARBINARY（最大），这让我通过字节数组中为止。然后在SP我投了VARBINARY（最大值）为varchar（最大值）。这将保留字节需要UTF-8
The solution that I got to work was to change the Stored Procedure so that the myXml parameter was Varbinary(Max), which allowed me to pass in the byte array. Then in the SP I Cast the Varbinary(max) to Varchar(max). This preserves the bytes as required for UTF-8
SET myXMLText = CAST(myXMLBinary as VARCHAR(MAX))
这篇关于使用ADO.Net写UTF-8到SQL Server文本字段和维护UTF-8字节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
