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
– Mike Powell, Commented Mar 3, 2010 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
– abatishchev, Commented Mar 3, 2010 at 16:42

Fredrik Mörk · Accepted Answer · 2010-02-27 11:59:01Z

4

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.

answered Feb 27, 2010 at 11:59

Fredrik Mörk

159k31 gold badges296 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave D · Accepted Answer · 2010-02-27 12:05:45Z

1

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.

answered Feb 27, 2010 at 12:05

Dave D

9,2104 gold badges36 silver badges47 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related