c＃将SQL表（带有特殊字符）读入DataSet [英] c# read SQL Table (with special characters) into DataSet

问题描述

我从SQL Datatable读取特殊字符并将其加载到C＃中的数据表中时遇到问题。我正在使用SqlDataAdapter填充我的表：

  using（oSQLConnection = new SqlConnection（oDBConnection.ConnectionString））
 {
 oSQLConnection.Open（）; 
 using（oSQLDataAdapter = new SqlDataAdapter（sQuery，oSQLConnection））
 {
 oSQLDataAdapter.SelectCommand.CommandTimeout = 0; 
 oSQLDataAdapter.Fill（DataSource，sTableName）; 
} 
} 
  

但是，对于特殊字符 Sam的注意到报价不是正常的报价。此外，像休息一样的字符 - 现在不是一个正常的破折号（ - 这是一个破折号），在这个例子中，该字符将显示为 rest¿ 在数据集。其中一位成员说尝试使用SqlDataReader，但是DataSet没有加载功能（只有DataTable） - 你们有其他建议吗？

我发现这些结果是这样的，如果我在调用该方法后在代码中放置一个断点。在数据集中，它将显示为休息现在，如果我将这个数据集导出到一个csv文件，它会与休息一起出现。我的猜测是数据集中不支持该字符，因此它将转换为数据集支持的另一个字符。

SQL列dataType = nvarchar

谢谢。

EDITED：
好​​吧，所以我想出了为什么这些角色不匹配。这是因为我们使用em dash或特殊的引号将数据加载到Oracle中。但是，Oracle不支持这些字符，因此它们可以将字符替换为支持上升问号的另一个字符。我相信这是因为Oracle是在UTF8中，我们检索SQL中的数据（即在SQL中进行字符串匹配）由于SQL有变量作为倒置的问号进入它们不匹配。

所以我有一个问题给你们。当我们加载这些特殊字符在Oracle - EM Dash和特别报价。是否可以保持角色相同？所以当我们在SQL中检索它时，它会相应地出现

谢谢。

解决方案>

这是一个可能的解决方案，但它是未经测试的，所以请记住！

我在一段时间以前遇到过类似的问题，在Oracle中有特殊字符。我们的字符设置为UTF-16。有一些像普通的32-127范围以外的变音器和其他ascii字符。您可能尝试的一件事是首先对该范围之外的任何内容进行HTML编码，然后将其加载到c＃中的数据表中，然后在datatable正在填充（或之后）已经填写...），在这些字段上执行html解码。

我们有一个html编码功能，为我们这样做，像这样：

  FUNCTION HTML_Encode_Special_Characters 
（p_string IN VARCHAR2，
 p_do_encode IN VARCHAR2 DEFAULT'Y'）
返回VARCHAR2 IS 
 first_location number：= REGEXP_INSTR（p_string，'（[^'|| CHR（1）||' - '|| CHR（127）||']）'）; 
 v_string VARCHAR2（32000）; 
 v_char VARCHAR2（1个字符）; 
 v_ascii_number number; 
 BEGIN 
如果p_do_encode ='Y'AND first_location> 0 THEN 
 v_string：= p_string; 
 
 FOR i IN first_location .. length（v_string）LOOP 
 v_char：= substr（v_string，i，1）; 
 v_ascii_number：= ascii（v_char）; 
 IF v_ascii_number> 128 THEN 
  - 只是替换所有的
 v_string：= REPLACE（v_string，v_char，'&＃'|| v_ascii_number ||';'）; 
 END IF; 
 END LOOP; 
 
返回（v_string）; 
 ELSE 
返回p_string; 
 END IF; 
 
 END; 
  

所以这应该将字符串数据加载到你的表中，任何你有特殊字符的情况下将出现像'&＃189'或某种事情。

然后在C＃方面你可以用html解码做一些事情：

 使用System.Web; 
 
 HttpUtility.HtmlDecode（mystring）; 
  

再次未经测试。但是它应该很容易实现和/或调整它，以使其工作。

一个替代解决方案也将是简单地执行一个数据文件在你的数据库将所有这些特殊字符更改为常规表示（即，对正常短划线的破折号）。您可以使用一些正则表达式，使用CHR（）函数，并使用DUMP（）函数查看奇数数据。

I am having an issue reading special characters from SQL Datatable and loading into a datatable in C#. I'm currently using SqlDataAdapter to fill my table:

using (oSQLConnection = new SqlConnection(oDBConnection.ConnectionString))
{
    oSQLConnection.Open();
    using (oSQLDataAdapter = new SqlDataAdapter(sQuery, oSQLConnection))
    {
        oSQLDataAdapter.SelectCommand.CommandTimeout = 0;
        oSQLDataAdapter.Fill(DataSource, sTableName);
    }
}

But this does not work well for special characters such as Sam’s notice the quote it's not a normal quote. Also characters like rest – now is not a normal en dash (- it's an em dash) for this example the character will show up as rest ¿ now in the dataset. One of the member said try using SqlDataReader but DataSet doesn't have a load function (only DataTable) - Do you guys have any other suggestions?

I found that these results are coming out like this if i put a breakpoint in the code after this method is called. In the dataset it'll show up as rest now and if i were to export this dataset into a csv file it comes off with the rest ¿ now. My guess is that the character is not supported in the dataset so it converts into another character that is supported by dataset.

SQL Column dataType = nvarchar

Thanks.

EDITED: Okay guys so i figured out why these characters are NOT matching. It is because we load the DATA into Oracle with em dash or the special quotation mark. However Oracle does NOT support these characters so they switch the character with another character that is supported the upsidedown question mark. I believe it's because Oracle is in UTF8 and we retrieve the data in SQL (ie. do the string matching in SQL) Since SQL has variable coming in as upside down question mark They do NOT match.

So i have a question for you guys. When we load these special characters in Oracle - EM Dash and special quotation . is it possible to keep the character the same? so when we retrieve it in SQL it comes up as accordingly

Thanks.

解决方案

Here is a possible solution, but it's untested so bear that in mind!

I came across a similar issue some time ago with special characters in Oracle. We have our character set to UTF-16. Had wierd things like umlauts and other ascii characters outside the normal 32-127 range.

One thing you might try is first doing an HTML encode to anything outside that range, loading that into your datatable in c#, then as the datatable is filling (or after it's been filled...), perform an html decode on those fields.

We have an html encode function that does this for us, like so:

  FUNCTION HTML_Encode_Special_Characters
   (p_string IN VARCHAR2,
    p_do_encode IN VARCHAR2 DEFAULT 'Y')
  RETURN VARCHAR2 IS
    first_location number := REGEXP_INSTR(p_string, '([^' || CHR(1) || '-' || CHR(127) || '])');
    v_string VARCHAR2(32000);
    v_char VARCHAR2(1 char);
    v_ascii_number number;
  BEGIN
    IF p_do_encode = 'Y' AND first_location > 0 THEN
      v_string := p_string;

      FOR i IN first_location .. length(v_string) LOOP
        v_char := substr(v_string,i,1);
        v_ascii_number := ascii(v_char);
        IF v_ascii_number > 128 THEN
          -- just replace all of them
          v_string := REPLACE (v_string, v_char,'&#' || v_ascii_number || ';');
        END IF;
      END LOOP;

      RETURN (v_string);
    ELSE
      RETURN p_string;
    END IF;

  END;

So this should load "string" data into your table and any case where you have special characters, it would show up like '&#189' or something to that effect.

Then on the C# side you could do something with html decode:

using System.Web;

HttpUtility.HtmlDecode(mystring);

Again... untested. But it should be very easy to implement and test out and/or tweak it slightly to get it working.

An alternate solution would also be to simply perform a datafix on your database to change all those special characters to "regular" representations (ie, an em-dash to a normal dash). You can do that with some regular expressions, using the CHR() function, and to view odd data using the DUMP() function.

这篇关于c＃将SQL表（带有特殊字符）读入DataSet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！