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

Question

I noticed a strange problem in a piece of code where the adhoc SQL query did not produce the expected result, although its parameters corresponded to the records in the data source. I decided to enter the following test expression in the next window:

new SqlParameter("Test", 0).Value 

This gave a null result, which made me scratch my head. It seems that the SqlParameter constructor treats zeros as zeros. The following code gives 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 behavior? Is this somehow intentional? If so, it is potentially dangerous ...

Answer 1

As indicated in the documentation for this constructor:

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

Use caution when using this SqlParameter constructor overload to specify integer parameter values. Since this overload takes a value of type Object , you must convert the integral value to Object 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 called a different constructor than you thought in your case.

The reason for this is that C # allows for implicit conversion from integer literal 0 to enum types (which are only integer types at the bottom), and this implicit conversion leads to the fact that the constructor (string, SqlDbType) will better correspond to overloading than box conversion, necessary to convert int to object for the constructor (string, object) .

This will never be a problem when passing an int variable, even if the value of this variable is 0 (because it is not a null literal) or any other expression of type int . This also will not happen if you explicitly pointed int to object , as shown above because then there is only one relevant overload.

Answer 2

Good practices for using typed data when passing / adding your parameters.

Below you can complete the task as shown below:

For data with the / varchar line:

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

For the entered data:

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

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