Inferring... or just guessing!?

Yes, Yes, another bug...

We add support for multiple languages to out Web Application, and a lady from the QA asked me solve the following bug:

When inserting Hebrew (or any language other than english) characters to a TextBox and saving to the Database, you get Question Marks ("????????") instead of Hebrew characters.

But is it fun enough? No?

So lets add that
it doesn't happens everytime...

First, I have checked the Database field: [Name] [nvarchar] (50)
It was NVarChar, which is ok.

Then I have looked at the Stored Procedure : @deals ntext
It declared a parameter of the type - nText.
This is fine, because actually this parameter gets an xml string of the whole Dataset, which can become big.

Next, I have checked the SqlCommand's Parameter in the code:

StringWriter writer = new StringWriter();
newDeal.WriteXml( writer , XmlWriteMode.DiffGram );

sqlCmd.Parameters.Add ( "@deals" , writer.ToString() );

You can see that the code uses the overload which doesn't declare the SqlDbType.
So lets check it on Run-Time.

Usually, the SqlDbType was nVarChar, but when the bug occured it was VarChar !!!

But why is it has been set to VarChar?

After a lot of thinking and experiments, I finally got it:

when the value used in the "Add" statement is a string, the framework use nVarChar as its default SqlDbType.

But...

if the string is
longer than 4000 characters (which is the size limit of nVarChar on SQL Server) the framework uses VarVChar instead.

Yes...

Without checking if
you have any unicode characters in the string.
I won't call it "Inferring"
(microsoft term) - I'll call it "guessing"

Of course, after you know the problem, solving is not an issue.
Just declare the Parameter type explicitly!

And, by the way, after you know the problem you can easily find the Microsoft's document which tell you what you didn't know:

"String - NVarChar. This implicit conversion will fail if the string is greater than the maximum size of an NVarChar, which is 4000 characters. For strings greater than 4000 characters, explicitly set the SqlDbType."



See you soon

Jon.