SqlParameter with default value set to 0 doesn't work as expected

Question

I was doing something like this:

SqlParameter param = new SqlParameter("@Param", 0) { SqlDbType = SqlDbType.Int };

private void TestParam(SqlParameter param) {
   string test = param.Value.ToString();  // Getting NullReferenceException here
}

But I stop getting the exception when I put it like this:

SqlParameter param = new SqlParameter("@Param", SqlDbType.Int)  { Value = 0 };

private void TestParam(SqlParameter param) {
    string test = param.Value.ToString();  // Everything OK
}

Can anyone tell me why SqlParameter assumes 0 is the same as null?

Edit: MSDN Explains this here: SqlParameter Constructor

Ok, it is explained here: http://msdn.microsoft.com/en-us/library/0881fz2y%28v=VS.80%29.aspx When creating a new SqlParameter with default value "0", you need to cast it explicitly to integer. Otherwise it assumes you're passing a SqlDbType enum as the second parameter. — Meryovi, Mar 18 '11 at 19:25

score 58 · Accepted Answer · edited Mar 18 '11 at 19:31

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", Convert.ToInt32(0));
If you do not perform this conversion, the compiler assumes that you are trying to call the SqlParameter (string, SqlDbType) constructor overload.

Thanks Msdn :)

score 8 · Answer 2 · answered Mar 18 '11 at 19:29

The 0 you are passing in is the type, not the value. 0 literals (and constant values) are allowed for any enum type - meaning the 0 of the underlying enum type, and are a better "match" than object, since it doesn't need boxing.

Personally, I would use;

Value = 0

perhaps in the object initializer.

SqlParameter with default value set to 0 doesn't work as expected

2 Answers2

Linked