C# 具有 Nullable 值的 SqlParameter 在 ExecuteNonQuery 时出错？

SqlParameter with Nullable value give error while ExecuteNonQuery?

提问by Patrick Desjardins

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();.

我有一个 sql 查询，它的参数在数据库（Sql Server）中可以为空。update 方法可以正常工作，直到该用户在该字段中输入空白，这会为 DataTime 对象生成一个空值（该对象可以为空）。问题是当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 :

我尝试将生日的空值转换为 DBNull.Value 像这样：

    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'

错误 1 ​​运算符“??” 不能应用于“System.DateTime?”类型的操作数 和'System.DBNull'

Any idea?

任何的想法？

采纳答案by David M

The types are not compatible. Try something like this:

类型不兼容。尝试这样的事情：

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

回答by Triynko

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.

如果第一次正确编写了 SqlParameter 类……C# 空值将作为 DBNull.Value 处理。这很直观，因此当然将 SqlParameter 值设置为 null 在功能上等同于从 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).

要纠正这个荒谬的 API 设计错误，请创建您自己的 AddParameter 方法（带有重载），该方法采用 SqlParameterCollection、字符串（参数名称）和对象（参数值）。

#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.

正如您所看到的，在该方法（和重载）中，值已经被输入为对象，我使用“value ?? DBNull.Value”语句来强制执行 null = DBNull.Value 规则。

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.

现在，当您将空对象引用或没有值的可为空类型传递给 AddParameter 方法时，您将获得预期的、直观的行为，其中将 DBNull.Value 传递给查询。

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.

我无法想象为什么 API 是这样实现的，因为如果我想要一个参数被忽略，我不会添加它然后将它的值设置为 null。我要么不首先添加它，要么从 SqlParameterCollection 中删除它。如果我添加一个参数，并设置它的值（即使设置为 null），我希望它在查询中使用，我希望 null 表示空值。

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.

我听说他们出于性能原因没有以“正确”的方式实现它，但这很荒谬，正如所演示的，因为调用 SqlParameterCollection.AddWithValue 方法无论如何都会将所有内容转换为对象，并将没有值的 Nullable 实例转换为 null object 是 C# 语言的固有部分，它根本不会影响性能。微软真的应该解决这个问题。