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?

Babbage

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? – Niles

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? – Sidneysidoma

the first one is nvarchar.the second one is numeric(18,0). – Babbage

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. – Babbage

And in what tool do you get question marks? – Niles

Microsoft SQL Server management studio 2008, visual studio, everything. – Babbage

Your own program also shows question marks? Or do you mean a tooltip hovering over a variable in the visual studio debugger? – Niles

the problem is the update part. – Babbage

I have the exact problem in 34 Tables.cool? – Babbage

Can you check the .DbType property of .Parameters[0] after you've set the value but before you execute? (ie. cmd.Parameters[0].DbType) – Niles

as you know all the text in this site is saved in SQL. how does it work?anyone knows? – Babbage

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? – Sidneysidoma

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. – Niles

Answer 1

Dear Behrooz just use a simple SQL command

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

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

Maccaboy

Answer 2

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.

Frazier

Answer 3

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();  

Arenaceous