在 SQL Server 2008 的 NVarChar 中存储 UTF-8 时遇到问题

问题描述

我正在使用 System.Net.WebClient 从网站中提取数据，当数据返回时，除了带重音的字母外，所有内容都会解析并看起来不错.例如，当它返回一个 é 时，SQL Server 2008 将其保存为 ??.

I'm pulling data using System.Net.WebClient from a web site, and when the data comes back everything parses and looks good except letters with accents. For example, when it returns an é, SQL Server 2008 saves it as ??.

只需要弄清楚如何将这些 UTF-8 字符转换为 SQL Server 可以读取的内容.我将它存储在 NVARCHAR(MAX) 数据类型中.

Just need to figure out how to convert these UTF-8 characters into something SQL Server can read. I'm storing it in an NVARCHAR(MAX) datatype.

如果您好奇，我正在使用 Linq-to-SQL 插入数据库.

I'm using Linq-to-SQL to insert into the database if you were curious.

有什么想法可以将其转换为正确的格式吗?

Any thoughts on what I could do to convert it to the proper format?

推荐答案

想通了！使用 WebClient 类时，我将数据下载为字符串.

Figured it out! When using the WebClient class, I was downloading the data as a string.

我的原始配置...

System.Net.WebClient wc = new WebClient();
string htmlData = wc.DownloadString(myUri);

我尝试将此数据转换为 UTF-16...从当前字符串开始，但由于 Microsoft 使用 UTF-16，因此它自己处理了转换.

I tried to convert this data into a UTF-16...from it's current string, but since Microsoft operates in UTF-16, it had handled the conversion on its own.

相反，我将方法改为从数据中读取实际的 byte[] 数组，就像这样......

Instead, I switched my approach to reading the actual byte[] array from the data like so...

System.Net.WebClient wc = new WebClient();
string htmlData = UTFConvert(wc.DownloadData(myUri));

private string UTFConvert(byte[] utfBytes)
{
    byte[] isoBytes = Encoding.Convert(Encoding.UTF8, Encoding.Unicode, utfBytes);
    return Encoding.Unicode.GetString(isoBytes);
}

这解决了问题，SQL 现在可以正确地看到所有内容中的重音.伊皮.

This fixed the problem, and SQL correctly see's the accents in everything now. Yippee.

干杯，感谢您的帮助！