C# 如何将unicode数据保存到oracle？

How to save unicode data to oracle?

提问by Hemant

I am trying to save unicode data (greek) in oracle database (10 g). I have created a simple table:

我正在尝试将 unicode 数据（希腊语）保存在 oracle 数据库 (10 g) 中。我创建了一个简单的表：

I understand that NVARCHAR2 always uses UTF-16 encoding so it must be fine for all (human) languages.

我知道 NVARCHAR2 总是使用 UTF-16 编码，所以它必须适用于所有（人类）语言。

Then I am trying to insert a string in database. I have hardcoded the string ("How are you?" in Greek) in code. Then I try to get it back from database and show it.

然后我试图在数据库中插入一个字符串。我在代码中硬编码了字符串（希腊语中的“你好吗？”）。然后我尝试从数据库中取回它并显示它。

class Program
{
    static string connectionString = "<my connection string>";

    static void Main (string[] args) {
        string textBefore = "Τι κ?νει?;";

        DeleteAll ();
        SaveToDatabase (textBefore);
        string textAfter = GetFromDatabase ();

        string beforeData = String.Format ("Before: {0}, ({1})", textBefore, ToHex (textBefore));
        string afterData = String.Format ("After: {0}, ({1})", textAfter, ToHex (textAfter));

        Console.WriteLine (beforeData);
        Console.WriteLine (afterData);

        MessageBox.Show (beforeData);
        MessageBox.Show (afterData);

        Console.ReadLine ();
    }

    static void DeleteAll () {
        using (var oraConnection = new OracleConnection (connectionString)) {
            oraConnection.Open ();
            var command = oraConnection.CreateCommand ();

            command.CommandText = "delete from UNICODEDATA";
            command.ExecuteNonQuery ();
        }            
    }

    static void SaveToDatabase (string stringToSave) {
        using (var oraConnection = new OracleConnection (connectionString)) {
            oraConnection.Open ();
            var command = oraConnection.CreateCommand ();

            command.CommandText = "INSERT into UNICODEDATA (ID, UNICODESTRING) Values (11, :UnicodeString)";
            command.Parameters.Add (":UnicodeString", stringToSave);
            command.ExecuteNonQuery ();
        }
    }

    static string GetFromDatabase () {
        using (var oraConnection = new OracleConnection (connectionString)) {
            oraConnection.Open ();

            var command = oraConnection.CreateCommand ();
            command.CommandText = "Select * from UNICODEDATA";
            var erpReader = command.ExecuteReader ();

            string s = String.Empty;
            while (erpReader.Read ()) {
                string text = erpReader.GetString (1);
                s += text + ", ";
            }

            return s;
        }
    }

    static string ToHex (string input) {
        string bytes = String.Empty;
        foreach (var c in input)
            bytes += ((int)c).ToString ("X4") + " ";

        return bytes;
    }
}

Here are different outputs:

以下是不同的输出：

Text before sending to database in a message box:

在消息框中发送到数据库之前的文本：

Text after getting from database in a message box:

在消息框中从数据库获取后的文本：

Console Output:

控制台输出：

Please can you suggest what I might be doing wrong here?

请你能建议我在这里可能做错了什么吗？

采纳答案by Jon Skeet

I can see five potential areas for problems:

我可以看到五个潜在的问题领域：

1. How are you actually getting the text into your .NET application? If it's hardcoded in a string literal, are you sure that the compiler is assuming the right encoding for your source file?

2. There could be a problem in how you're sending it to the database.

3. There could be a problem with how it's being stored in the database.

4. There could be a problem with how you're fetching it in the database.

5. There could be a problem with how you're displaying it again afterwards.

1. 您实际上是如何将文本输入到您的 .NET 应用程序中的？如果它是硬编码在字符串文字中，您确定编译器为您的源文件假设正确的编码吗？

2. 您将其发送到数据库的方式可能存在问题。

3. 它在数据库中的存储方式可能存在问题。

4. 您在数据库中获取它的方式可能存在问题。

5. 之后您再次显示它的方式可能存在问题。

Now areas 2-4 sound like they're less likely to be an issue than 1 and 5. How are you displaying the text afterwards? Are you actually fetching it out of the database in .NET, or are you using Toad or something similar to try to see it?

现在，区域 2-4 听起来不像 1 和 5 那样有问题。之后您如何显示文本？您实际上是从 .NET 中的数据库中获取它，还是使用 Toad 或类似的东西来尝试查看它？

If you're writing it out again from .NET, I suggest you skip the database entirely - if you just display the string itself, what do you see?

如果您从 .NET 再次写出它，我建议您完全跳过数据库 - 如果您只显示字符串本身，您会看到什么？

I have an article you might find useful on debugging Unicode problems. In particular, concentrate on every place where the encoding couldbe going wrong, and make sure that whenever you "display" a string you dump out the exact Unicode characters (as integers) so you can check those rather than just whatever your current font wants to display.

我有一篇您可能会发现对调试 Unicode 问题有用的文章。尤其要注意编码可能出错的每个地方，并确保无论何时“显示”一个字符串，您都将转储出确切的 Unicode 字符（作为整数），以便您可以检查这些字符，而不仅仅是当前字体想要的任何内容显示。

EDIT: Okay, so the database isinvolved somewhere in the problem.

编辑：好的，所以数据库是在涉及问题的地方。

I stronglysuggest that you remove anything like ASP and HTML out of the equation. Write a simple console app that does nothingbut insert the string and fetch it again. Make it dump the individual Unicode characters (as integers) before and after. Then try to see what's in the database (e.g. using Toad). I don't know the Oracle functions to convert strings into sequences of individual Unicode characters and then convert those characters into integers, but that would quite possibly be the next thing I'd try.

我强烈建议您将 ASP 和 HTML 之类的东西从等式中删除。编写一个简单的控制台应用程序，除了插入字符串并再次获取它之外什么都不做。让它在前后转储单个 Unicode 字符（作为整数）。然后尝试查看数据库中的内容（例如使用 Toad）。我不知道 Oracle 函数将字符串转换为单个 Unicode 字符的序列，然后将这些字符转换为整数，但这很可能是我接下来要尝试的事情。

EDIT: Two more suggestions (good to see the console app, btw).

编辑：另外两个建议（很高兴看到控制台应用程序，顺便说一句）。

1. Specify the data type for the parameter, instead of just giving it an object. For instance:

   command.Parameters.Add (":UnicodeString",
                           OracleType.NVarChar).Value = stringToSave;
   

2. Consider using Oracle's own driver instead of the one built into .NET. You may wish to do this anyway, as it's generally reckoned to be faster and more reliable, I believe.

1. 为参数指定数据类型，而不是仅仅给它一个对象。例如：

   command.Parameters.Add (":UnicodeString",
                           OracleType.NVarChar).Value = stringToSave;
   

2. 考虑使用 Oracle 自己的驱动程序而不是内置于 .NET 的驱动程序。无论如何，您可能希望这样做，因为我相信它通常被认为更快、更可靠。

回答by Vincent Malgrat

You can determine what characterset your database uses for NCHAR with the query:

您可以通过查询确定您的数据库对 NCHAR 使用的字符集：

SQL> SELECT VALUE
  2    FROM nls_database_parameters
  3   WHERE parameter = 'NLS_NCHAR_CHARACTERSET';

VALUE
------------
AL16UTF16

to check if your database configuration is correct, you could run the following in SQL*Plus:

要检查您的数据库配置是否正确，您可以在 SQL*Plus 中运行以下命令：

SQL> CREATE TABLE unicodedata (ID NUMBER, unicodestring NVARCHAR2(100)); 

Table created
SQL> INSERT INTO unicodedata VALUES (11, 'Τι κ?νει?;');

1 row inserted
SQL> SELECT * FROM unicodedata;

        ID UNICODESTRING
---------- ---------------------------------------------------
        11 Τι κ?νει?;

回答by Noam

One more thing worth noting.

还有一点值得注意。

If you are using oracle client, and would like to include unicode characters in the CommandText, you should add the folloing line to the start of your application:

如果您使用 oracle 客户端，并希望在 CommandText 中包含 unicode 字符，则应将以下行添加到应用程序的开头：

System.Environment.SetEnvironmentVariable("ORA_NCHAR_LITERAL_REPLACE", "TRUE");

This will allow you, in case you need it, to use the following syntax:

这将允许您在需要时使用以下语法：

command.CommandText = "INSERT into UNICODEDATA (ID, UNICODESTRING) Values (11, N'Τι κ?νει?;')";

回答by TenB

On reading records, try

在阅读记录时，尝试

Encoding utf = Encoding.Default;   
var utfBytes = odatareader.GetOracleString(0).GetNonUnicodeBytes();//OracleDataReader
Console.WriteLine(utf.GetString(utfBytes));

回答by Sergey Bazarnik

After some investigations here we go:

经过一些调查，我们开始：

string input = "?"; char s = input[0];

字符串输入 = "?"; 字符 s = 输入 [0];

       //table kuuku with column kuku(nvarchar2(100))
        string connString = "your connection";

        //CLEAN TABLE
        using (System.Data.OracleClient.OracleConnection cn = new System.Data.OracleClient.OracleConnection(connString))
        {
            cn.Open();
            System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand("delete from  kuku ", cn);
            cmd.ExecuteNonQuery();
            cn.Close();
        }


        //INSERT WITH PARAMETER BINDING - UNICODE SAVED
        using (System.Data.OracleClient.OracleConnection cn = new System.Data.OracleClient.OracleConnection(connString))
        {
            cn.Open();
            System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand("insert into  kuku (kuku) values(:UnicodeString)", cn);
            cmd.Parameters.Add(":UnicodeString", System.Data.OracleClient.OracleType.NVarChar).Value = input + " OK" ;
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        //INSERT WITHOUT PARAMETER BINDING - UNICODE NOT SAVED
        using (System.Data.OracleClient.OracleConnection cn = new System.Data.OracleClient.OracleConnection(connString))
        {
            cn.Open();
            System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand("insert into  kuku (kuku) values('" +input+" WRONG')", cn);
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        //FETCH RESULT
        using (System.Data.OracleClient.OracleConnection cn = new System.Data.OracleClient.OracleConnection(connString))
        {
            cn.Open();
            System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand("select kuku from kuku", cn);
            System.Data.OracleClient.OracleDataReader dr = cmd.ExecuteReader();
            if(dr.Read())
            {
                string output = (string) dr[0];
                char sa = output[0];
            }
            cn.Close();
        }
    }

回答by Veljac

Solution: set NLS_LANG!

解决方案：设置 NLS_LANG!

Details: I just had the same problem, and actually had exact the same situation as described in Sergey Bazarnik's investigation. Using bind variables it works, and without them it doesn't.

详细信息：我刚刚遇到了同样的问题，实际上与 Sergey Bazarnik 的调查中描述的情况完全相同。使用绑定变量可以工作，没有它们就不行。

The SOLUTION is to set NLS_LANG in proper place. Since I have Windows server I set it in windows registry under HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_OraClient11g_home1

解决方案是在适当的位置设置 NLS_LANG。由于我有 Windows 服务器，我将它设置在 Windows 注册表下 HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_OraClient11g_home1

Please note that regitry location may difer so the easiest way is to search registry for "ORACLE_HOME" string. Also other systems like Linux, Unix can set this on different way (export NLS_LANG ...)

请注意，注册表位置可能不同，因此最简单的方法是在注册表中搜索“ORACLE_HOME”字符串。其他系统，如 Linux、Unix 也可以以不同的方式设置（导出 NLS_LANG ...）

In my case I put "NLS_LANG"="CROATIAN_CROATIA.UTF8". Since I had no that variable set it went to default value. After changing registry you should restart process. In my case I restarted IIS.

就我而言，我把"NLS_LANG"="CROATIAN_CROATIA.UTF8". 因为我没有设置那个变量，所以它变成了默认值。更改注册表后，您应该重新启动进程。就我而言，我重新启动了 IIS。

Regarding reason why it works with bind variables may be because it actually happens on server side, while without it actually happens on client side. So even that DB can insert proper values - before that happens, client does the unwanted corrections, since it thinks that is should do that. That is because NLS_LANG defaults to simpler code page. But instead of doing useful task, that creates a problem, which (as shown in investigation looks hard to understand).

关于它与绑定变量一起工作的原因可能是因为它实际上发生在服务器端，而没有它实际上发生在客户端。因此，即使该 DB 可以插入正确的值 - 在此之前，客户端会进行不需要的更正，因为它认为应该这样做。那是因为 NLS_LANG 默认为更简单的代码页。但是，这不是做有用的任务，而是产生了一个问题，这（如调查所示，看起来很难理解）。

In case you have multiple oracle versions, be sure to correct all versions in registry (in my case Oracle 10 had valid setting, but Oracle 11 had no NLS_LANG set at all).

如果您有多个 oracle 版本，请务必更正注册表中的所有版本（在我的情况下，Oracle 10 具有有效设置，但 Oracle 11 根本没有设置 NLS_LANG）。