SqlParameter with Nullable value give error while ExecuteNonQuery?

Question

I have an sql query that has a parameter that can be null in the database (Sql Server). The update method work fine until that user put a blank in the field, this produce a null value for the DataTime object (this object is nullable). The problem is when the dbCommand.ExecuteNonQuery();.

Here is how I build the parameter for this field:

    IDataParameter dbParam_au_id = new SqlParameter();
    dbParam_au_id.ParameterName = "@birthday";
    dbParam_au_id.Value = birthday;
    dbParam_au_id.DbType = DbType.DateTime;
    dbCommand.Parameters.Add(dbParam_au_id);

I have try to convert the null value of birthday to DBNull.Value like that :

    IDataParameter dbParam_au_id = new SqlParameter();
    dbParam_au_id.ParameterName = "@birthday";
    dbParam_au_id.Value = birthday??DBNull.Value;
    dbParam_au_id.DbType = DbType.DateTime;
    dbCommand.Parameters.Add(dbParam_au_id);

But this code won't compile and I get error :

Error 1 Operator '??' cannot be applied to operands of type 'System.DateTime?' and 'System.DBNull'

Any idea?

On a side note, I strongly recomend that you don't set the DbType property unless you are using it as an output parameter. I've never seen countless subtle bugs caused by and not a single one solved by using it. — Jonathan Allen, May 15 '11 at 04:07

score 57 · Accepted Answer · answered May 14 '09 at 13:35

57

The types are not compatible. Try something like this:

dbParam_au_id.Value = (object)birthday ?? DBNull.Value;

answered May 14 '09 at 13:35

David M

71,481
13
158
186

score 15 · Answer 2 · answered Sep 14 '09 at 22:28

If the SqlParameter class was written correctly the first time... a C# null value would be handled as DBNull.Value. That would be intuitive, so OF COURSE setting an SqlParameter value to null is functionally equivalent to removing it from the SqlParameterCollection.

To correct this ridiculous API design error, create your own AddParameter method (with overloads), which takes a SqlParameterCollection, a String (parameter name), and an Object (parameter value).

#region Add by Name/Value.
/// <summary>
/// Adds an input parameter with a name and value.  Automatically handles conversion of null object values to DBNull.Value.
/// </summary>
/// <param name="parameters">SqlParameterCollection from an SqlCommand instance.</param>
/// <param name="name">The name of the parameter to add.</param>
/// <param name="value">The value of the parameter to add.</param>
private static void AddParameter( SqlParameterCollection parameters, string name, object value )
{
    parameters.Add( new SqlParameter( name, value ?? DBNull.Value ) );
}

/// <summary>
/// Adds a parameter with a name and value.  You specify the input/output direction.  Automatically handles conversion of null object values to DBNull.Value.
/// </summary>
/// <param name="parameters">SqlParameterCollection from an SqlCommand instance.</param>
/// <param name="name">The name of the parameter to add.</param>
/// <param name="value">The value of the parameter to add.  If null, this is automatically converted to DBNull.Value.</param>
/// <param name="direction">The ParameterDirection of the parameter to add (input, output, input/output, or return value).</param>
private static void AddParameter( SqlParameterCollection parameters, string name, object value, ParameterDirection direction )
{
    SqlParameter parameter = new SqlParameter( name, value ?? DBNull.Value );
    parameter.Direction = direction;
    parameters.Add( parameter );
}
#endregion

#region Add by Name, Type, and Value.
/// <summary>
/// Adds an input parameter with a name, type, and value.  Automatically handles conversion of null object values to DBNull.Value.
/// </summary>
/// <param name="parameters">SqlParameterCollection from an SqlCommand instance.</param>
/// <param name="name">The name of the parameter to add.</param>
/// <param name="type">Specifies the SqlDbType of the parameter.</param>
/// <param name="value">The value of the parameter to add.  If null, this is automatically converted to DBNull.Value.</param>
private static void AddParameter( SqlParameterCollection parameters, string name, SqlDbType type, object value )
{
    AddParameter( parameters, name, type, 0, value ?? DBNull.Value, ParameterDirection.Input );
}

/// <summary>
/// Adds a parameter with a name, type, and value.  You specify the input/output direction.  Automatically handles conversion of null object values to DBNull.Value.
/// </summary>
/// <param name="parameters">SqlParameterCollection from an SqlCommand instance.</param>
/// <param name="name">The name of the parameter to add.</param>
/// <param name="type">Specifies the SqlDbType of the parameter.</param>
/// <param name="value">The value of the parameter to add.  If null, this is automatically converted to DBNull.Value.</param>
/// <param name="direction">The ParameterDirection of the parameter to add (input, output, input/output, or return value).</param>
private static void AddParameter( SqlParameterCollection parameters, string name, SqlDbType type, object value, ParameterDirection direction )
{
    AddParameter( parameters, name, type, 0, value ?? DBNull.Value, direction );
}
#endregion

#region Add by Name, Type, Size, and Value.
/// <summary>
/// Adds an input parameter with a name, type, size, and value.  Automatically handles conversion of null object values to DBNull.Value.
/// </summary>
/// <param name="parameters">SqlParameterCollection from an SqlCommand instance.</param>
/// <param name="name">The name of the parameter to add.</param>
/// <param name="type">Specifies the SqlDbType of the parameter.</param>
/// <param name="size">Specifies the size of the parameter for parameter types of variable size.  Set to zero to use the default size.</param>
/// <param name="value">The value of the parameter to add.  If null, this is automatically converted to DBNull.Value.</param>
private static void AddParameter( SqlParameterCollection parameters, string name, SqlDbType type, int size, object value )
{
    AddParameter( parameters, name, type, size, value ?? DBNull.Value, ParameterDirection.Input );
}

/// <summary>
/// Adds a parameter with a name, type, size, and value.  You specify the input/output direction.  Automatically handles conversion of null object values to DBNull.Value.
/// </summary>
/// <param name="parameters">SqlParameterCollection from an SqlCommand instance.</param>
/// <param name="name">The name of the parameter to add.</param>
/// <param name="type">Specifies the SqlDbType of the parameter.</param>
/// <param name="size">Specifies the size of the parameter for parameter types of variable size.  Set to zero to use the default size.</param>
/// <param name="value">The value of the parameter to add.  If null, this is automatically converted to DBNull.Value.</param>
/// <param name="direction">The ParameterDirection of the parameter to add (input, output, input/output, or return value).</param>
private static void AddParameter( SqlParameterCollection parameters, string name, SqlDbType type, int size, object value, ParameterDirection direction )
{
    SqlParameter parameter;
    if (size < 1)
        parameter = new SqlParameter( name, type );
    else
        parameter = new SqlParameter( name, type, size );
    parameter.Value = value ?? DBNull.Value;
    parameter.Direction = direction;
    parameters.Add( parameter );
}
#endregion

As you can see, inside that method (and overloads), where the value is already typed as an object, I use the "value ?? DBNull.Value" statement to enforce the null = DBNull.Value rule.

Now, when you pass null object references or nullable types with no values to your AddParameter method, you get the expected, intuitive behavior, where a DBNull.Value is passed to the query.

I can't imagine why the API was implemented as it is, because if I wanted a parameter to be ignored, I would not ADD it and then SET it's value to null. I would either NOT add it in the first place, or I would REMOVE it from the SqlParameterCollection. If I ADD a parameter, and SET it's value (even if set to null), I expect it to be USED in the query, I expect null to mean null value.

I've heard they didn't implement it the "correct" way for performance reasons, but that's ridiculous, as demonstrated, because calling SqlParameterCollection.AddWithValue method converts everything to an object anyway, and converting a Nullable instance with no value to a null object is an intrinsic part of the C# language which is not a performance hit at all. Microsoft should really fix this.

nice set of utility functions. If you are in 3.5 or higher, you can adjust these to be extensions of the SqlParameterCollection and you'll have command.Paramters.AddParameter(...) experience — Roman, Aug 14 '11 at 06:57
Seven years later, this is still the behavior of the SqlParameterCollection Add method. Do you still use this solution? Just wondering if a better fix has come along in the meantime. I currently use AddRange with a parameter array, so I'm looking at having to loop through the array and replace the value of each one if null... — Doug, Oct 03 '16 at 15:15
@Doug Another five years later, using this for a couple of years. I've added an extension method that adds an array and converts null values to DBNull. `public static void AddRangeIncludingNullValues(this SqlParameterCollection parameters, SqlParameter[] sqlParametersToAdd) { for (int i = 0; i < sqlParametersToAdd.Length; i++) { sqlParametersToAdd[i].Value = sqlParametersToAdd[i].Value ?? DBNull.Value; } parameters.AddRange(sqlParametersToAdd); }` — ArieKanarie, Aug 19 '21 at 08:52

SqlParameter with Nullable value give error while ExecuteNonQuery?

2 Answers2

Linked