Why does the SqlParameter name/value constructor treat 0 as null?

Question

I observed a strange problem in a piece of code where an adhoc SQL query was not producing the expected output, even though its parameters matched records in the data source. I decided to enter the following test expression into the immediate window:

new SqlParameter("Test", 0).Value

This gave a result of null, which leaves me scratching my head. It seems that the SqlParameter constructor treats zeroes as nulls. The following code produces the correct result:

SqlParameter testParam = new SqlParameter();
testParam.ParameterName = "Test";
testParam.Value = 0;
// subsequent inspection shows that the Value property is still 0

Can anyone explain this behaviour? Is it somehow intentional? If so, it's potentially rather dangerous...

Slavophile

Further reading on this topic: #14224965 – Screamer

Answer 1

As stated in the documentation for that constructor:

When you specify an Object in the value parameter, the SqlDbType is inferred from the Microsoft .NET Framework type of the Object.

Use caution when you use this overload of the SqlParameter constructor to specify integer parameter values. Because this overload takes a value of type Object, you must convert the integral value to an Object type when the value is zero, as the following C# example demonstrates.

Parameter = new SqlParameter("@pname", (object)0);

If you do not perform this conversion, the compiler assumes that you are trying to call the SqlParameter (string, SqlDbType) constructor overload.

You simply were calling a different constructor than you thought in your case.

The reason for this is that C# allows an implicit conversion from the integer literal 0 to enum types (which are just integral types underneath), and this implicit conversion causes the (string, SqlDbType) constructor to be a better match for overload resolution than the boxing conversion necessary to convert int to object for the (string, object) constructor.

This will never be a problem when you pass an int variable, even if the value of that variable is 0 (because it is not a zero literal), or any other expression that has the type int. It will also not happen if you explicitly cast the int to object as seen above, because then there is only one matching overload.

Garwood

I was having this problem and tried a different solution which didn't work: Why does the proposed Parameter = new SqlParameter("@pname", Convert.ToInt32(0)); work when Parameter = new SqlParameter("@pname", (int)0); which I tried doesn't? – Loveliesbleeding

@mortb, you'd have to consult the language spec for that, but my guess would be that overload resolution would care for the exact typ in the first case but might allow enums in the second because you're still passing a literal of type int. – Garwood

I have found out why: #3154341 The literal 0 will always be evaluated as matching the type of an enum while other numbers won't. Seems a little obscure... – Loveliesbleeding

There's something wrong with the quoted example: Convert.ToInt32(0) does not change the overload that is chosen. The code should be new SqlParameter("@pname", (object)0);. – Capsule

@Steven: Correct me if I'm wrong, but only the literal 0 may be implicitly converted to any enum type. Thus, a method call that returns int is fine and picks another overload. Admittedly, it's been a few months since I looked at the spec, but afaik it worked that way. – Garwood

You're right. But still, it looks weird and I wouldn't recommend doing that. Someone looking at the code might wonder why an int value is wrapped in Convert.ToInt32() and remove the surrounding call without knowing that it's required for the compiler to do the right thing. – Capsule

@Steven: Apparently the documentation switched from recommending Convert.ToInt32 to a simple object cast with .NET 4 (older versions of the documentation still show the Convert call). Since the boxing conversion is necessary in either case the cast saves the method call and is probably a little clearer (considering that few people read the C# specification). I'll adjust the answer and extend it a bit. – Garwood

If 0 is converted to an enum type, then why is not 1 converted to an enum type? – Newfangled

@AndersLindén: Because the C# language specification dictates that only the numeric integer literal 0 is implicitly convertible into any enum type. Any other literal or expression can be converted explicitly if required. Why they made that decision, I cannot say. – Garwood

Answer 2

It is good practices to use typed data while passing/adding your parameters.

Below way you can accomplish the task as below:

For string/varchar typed data:

SqlParameter pVarchar = new SqlParameter
                    {
                        ParameterName = "Test",
                        SqlDbType = System.Data.SqlDbType.VarChar,
                        Value = string.Empty,
                    };

For int typed data:

SqlParameter pInt = new SqlParameter
                    {
                        ParameterName = "Test",
                        SqlDbType = System.Data.SqlDbType.Int,
                        Value = 0,
                    };

You can change the value of SqlDbType according to your used data.

Cryogenics

While generally helpful this is technically not an answer to the question. Thus it should be a comment. – Garwood