What happens to Unicode in a System.Data.SQLCommand

Question

I have a SQLCommand :

"Update Customers Set Name = @name where code = @code"

and this code:

cmd.Parameters[0].Value = "بهروز";//(some Unicode characters)  
cmd.Parameters[1].Value = 1;  
cmd.ExecuteNonQuery();

or this code:

        UpdateCommand.CommandText = "UPDATE [Customers] SET [Name] = @p1 WHERE (([Code] = @p2) AND ((@p3 = 1 AND [Name] IS NULL) OR ([Name] = @p4)))";
        UpdateCommand.Parameters.Add("@p1", System.Data.SqlDbType.SmallInt, 0, "Name");
        UpdateCommand.Parameters.Add("@p2", System.Data.SqlDbType.NVarChar, 0, "Code");
        UpdateCommand.Parameters.Add("@p3", System.Data.SqlDbType.NText, 0, "Name");
        UpdateCommand.Parameters.Add("@p4", System.Data.SqlDbType.SmallInt, 0, "Name");

and when I Select Customers Table I have lots of "?"s.
why does SQLCommand work Correct when working with a SQLDataAdapter?
How can i convert my Unicode Data to ANSI?

edit:
in other words :
what code does SQLDataAdapter use?
anyone has the source code of that part of .net framework?

What data type is that column? What tool do you use to verify the contents? Does it work if you use a SQLCommand to retrieve it with back into .NET? — Lasse V. Karlsen, Jan 02 '10 at 20:20
What type is the Name column in the Customers table? If you check with Microsoft Management Studio or alike, does the table contain the correct data? In other words: Is the problem in the update or select part? — Lars Truijens, Jan 02 '10 at 20:22
the problem is not related to the data type.as i said before, SqlDataAdapter works correct.the reason I don't want to use it anymore is the speed. — Behrooz, Jan 02 '10 at 20:23
Microsoft SQL Server management studio 2008, visual studio, everything. — Behrooz, Jan 02 '10 at 20:30
Your own program also shows question marks? Or do you mean a tooltip hovering over a variable in the visual studio debugger? — Lasse V. Karlsen, Jan 02 '10 at 20:32
Can you check the .DbType property of .Parameters[0] after you've set the value but before you execute? (ie. cmd.Parameters[0].DbType) — Lasse V. Karlsen, Jan 02 '10 at 20:35
as you know all the text in this site is saved in SQL. how does it work?anyone knows? — Behrooz, Jan 02 '10 at 20:36
It works by default. You have to do your best to not make it work. So the question is: What have you done to *not* make it work? — Lars Truijens, Jan 02 '10 at 20:39
My guess is that he's using SQL Server Management Studio to verify the contents of his table, and it's not able to show his unicode characters properly. I base this on the fact that he said he has the same problem in 34 tables and somehow I doubt he altered his program 34 times to dump the contents of one table at a time into a grid. I'd like for him to verify what happens if he actually runs a select from his own program and shows it in a grid, what happens then? But "the problem is the update part", he's locked into this, so I doubt he'll be persuaded that there is a problem elsewhere. — Lasse V. Karlsen, Jan 02 '10 at 20:45

score 7 · Accepted Answer · answered Jan 04 '10 at 15:20

7

Dear Behrooz just use a simple SQL command

N'ناصر حاجلو'

whenever you use N you forcesql to use unicode and nothing will corrept.

answered Jan 04 '10 at 15:20

Nasser Hadjloo

12,312
15
69
100

Gabriel McAdams · Answer 2 · 2010-01-02T20:41:10.670

All strings in .NET are Unicode. There is no such thing as an ANSI string within .NET. If you want a string encoded into a byte array as ANSI, use Encoding.GetBytes.

Your issue may be with the way its sending data to the stored procedure. I think you need to add the sql datatype to the parameter. Try the following code:

cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 30);

See the SqlParameterCollection.Add Method for more information.

score 0 · Answer 3 · answered Jan 02 '10 at 20:43

0

At a guess: try setting the SqlDbType of the parameter explicitly:

cmd.Parameters[0].Value = "بهروز";//(some Unicode characters)  
cmd.Parameters[0].SqlDbType = SqlDbType.NVarChar;
cmd.Parameters[1].Value = 1;  
cmd.ExecuteNonQuery();

answered Jan 02 '10 at 20:43

Jeremy McGee

24,842
10
63
95

What happens to Unicode in a System.Data.SQLCommand

3 Answers3

Linked