c# - Trouble using/displaying special characters from Oracle db in .Net app

Question

I have a C#.Net application that accesses data from a commercial application backed by an Oracle 10 db. A couple of fields in the commercial app's database (declared as varchar2(n)) contain special characters. The "smart quote" apostrophe, for example. The commercial client app displays these characters correctly, but my application is displaying them as an inverted question mark. The Oracle character set is "WE8ISO8859P1".

My application reads the commercial database using System.Data.OracleClient.OracleDataAdapter, converted into a table via DataSet.Tables. The tablerows are converted into objects, and the fields in question are stored as strings.

If I examine (in the debugger) the data in the DataSet immediately after reading it from the db, and the special characters are already displayed incorrectly. I can't figure out how to examine the data as hex bytes to see what's really there, nor am I certain what I should be looking for.

I have also noted that Toad displays the characters as inverted question marks as well.

One aspect of our application writes these records to a separate table in our own database; when that occurs the special characters get modified, and subsequently display as boxes instead of upside-down question marks.

I can provide further information if needed. Thank you for any and all help!

Answer 1

Certain characters in the WE8ISO8859P1 character set have a different binary representation than the same character in UTF8.

What I suggest are 2 possible ways

1) Try using Oracle native data providers for .NET (ODP.NET). May be there is a bug/feature in Microsoft's library System.Data.OracleClient that this adapter do not automatically support converting WE8ISO8859P1 to unicode. Here is a link to ODP.NET

I hope that there will be a support for this encoding in ODP (but to say true I never checked this, it is only a suggestion)

2) Workaround: in Dataset, you should create a binary field (mapped to the original table field) and a String field (not mapped to the database). When you load data to the dataset, iterate for each row and perfrom convertion from binary array to string.

Code should be something like this

Encoding e = Encoding.GetEncoding("iso-8859-1");
foreach(DataRow row in dataset.Tables["MyTable"])
{
    if (!row.IsNull("MyByteArrayField"))
        row["MyStringField"] = e.GetString((row["MyByteArrayField"] as byte[]));
}