Is DBNull.Value required for nullable types as SqlCommandParameter.Value

Question

Which approach is recommended:

void Add(int? value)
{
   command.Parameters.Add("@foo").Value = value;
}

or

void Add(int? value)
{
   command.Parameters.Add("@foo").Value = (object)value ?? DBNull.Value;
}

The code in your second example doesn't compile -- instead you get "Operator '??' cannot be applied to operands of type 'int?' and 'System.DBNull'." To make it work, you need to cast value as object, e.g. `(object)value ?? DBNull.Value;`. — Mike Powell, Mar 03 '10 at 16:33
@Mike Powell: Hi. Indeed you're right. My existing code is exactly as you mentioned. I just forgot to write it correctly. — abatishchev, Mar 03 '10 at 16:42

score 4 · Accepted Answer · answered Feb 27 '10 at 11:59

If you want to pass a null value in a parameter, you need to pass DBNull.Value as in your second example.

If you assign null as the parameter value, the parameter will not be sent to the procedure, which will make the procedure call to either fail (if the parameter is required) or use the default value for the parameter, which may or may not be null.

score 1 · Answer 2 · answered Feb 27 '10 at 12:05

It depends on the functionality you want.

If you pass null as the value then the SqlCommand will treat that the same as if the parameter hadn't been passed at all. If it's a required parameter in, for example, a stored procedure then that will cause your query to fail.

If you pass DBNull.Value then the SqlCommand will treat that as SQL null being passed.

Is DBNull.Value required for nullable types as SqlCommandParameter.Value

2 Answers2