使用DBNull。不知道sqlDbType的SqlParameter值?

I'm using a SqlParameter to pass in null values to a table for various columns that are nullable. The problem is that SqlParameter looks like it defaults to nvarchar if no sqlDbType is present. This presents a problem if the actual db type is varbinary; I get an exception saying

我使用SqlParameter将空值传递给可空列的表。问题是，如果不存在sqlDbType, SqlParameter看起来似乎默认为nvarchar。如果实际的db类型是varbinary，则会出现问题;我有个例外

Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

不允许从数据类型nvarchar到varbinary(max)进行隐式转换。使用CONVERT函数运行这个查询。

When I create the SqlParameter, all I know is the parameter's name, and object. If the object is null, SqlParameter obviously can't infer the right type to use, so is there a way to use SqlParameter with null values, without having to know the sqlDbType when creating the sql parameter?

当我创建SqlParameter时，我只知道参数的名称和对象。如果对象为null, SqlParameter显然不能推断要使用的类型，那么是否有一种方法可以使用带有null值的SqlParameter，而不需要在创建sql参数时知道sqlDbType ?

Essentially pass the DBNull to the database without specifying the type, and let the database handle it?

本质上是将DBNull传递给数据库而不指定类型，然后让数据库处理它?

5 个解决方案

#1

Old post but might be helpful someone else.

旧的帖子，但可能会对其他人有所帮助。

Convert.DBNull

Like this

像这样

command.Parameters.AddWithValue("@param", Convert.DBNull);

#2

Set the default value of the NULL columns (to NULL) and then don't pass any NULL columns in your insert statement.

设置空列的默认值(为NULL)，然后不要在insert语句中传递任何空列。

#3

There’s another way to do this. You can still use AddWithValue, however use SqlBinary.Null instead of DBNull.Value:

还有另一种方法。您仍然可以使用AddWithValue，但是使用SqlBinary。Null代替DBNull.Value:

c.Parameters.AddWithValue(“@cfp”, SqlBinary.Null);

c.Parameters。AddWithValue(“@cfp”,SqlBinary.Null);

Don’t forget to import System.Data.SqlTypes into your project.

不要忘记导入System.Data。SqlTypes到您的项目。

Source

源

#4

I'm dealing with a sort of in-house ORM that uses objects to generate parameters. Since they are essentially typeless when null it makes it difficult to differentiate the various SQL null types.

我正在处理一种内部ORM，它使用对象来生成参数。由于它们本质上是无类型的，所以很难区分不同的SQL null类型。

DBNull.Value works for almost every SQL type, but it fails for a varbinary(max) column I'm using. Instead you must use SqlBinary.Null -- don't ask me why.

DBNull。值几乎适用于每一种SQL类型，但对于我正在使用的varbinary(max)列来说都是无效的。相反，您必须使用SqlBinary。零，别问我为什么。

I decided to use a special tagged value here to indicate when this column type should be used. Full except from our "ORM" below:

我决定在这里使用一个特殊的带标记值来指示什么时候应该使用这个列类型。除了我们的“ORM”以下:

using System.Data.SqlTypes;

class MyModelType
{
    public Guid ID { get; set; }
    public byte[] Data { get; set; }
}

static readonly object NullSqlBinary = new object();

object SqlValueForObject(object val)
{
    if (val == null)
    {
        val = DBNull.Value;
    }
    else if (val == NullSqlBinary)
    {
        val = SqlBinary.Null;
    }
    return val;
}

IDictionary<string, object> Params(MyModelType x)
{
    return new Dictionary<string, object>
    {
        { "@ID", x.ID },
        { "@Data", x.Data ?? NullSqlBinary },
    };
}

private SqlCommand CreateCommand()
{
    var cmd = Connection.CreateCommand();
    cmd.CommandTimeout = 60;

    return cmd;
}

SqlCommand CreateCommand(string sql, IDictionary<string, object> values)
{
    var cmd = CreateCommand();
    cmd.CommandText = sql;
    cmd.Transaction = GetCurrentTransaction();

    cmd.Parameters.Clear();
    if (values != null)
    {
        foreach (var kvp in values)
        {
            object sqlVal = SqlValueForObject(kvp.Value);
            cmd.Parameters.AddWithValue(kvp.Key, sqlVal);
        }
    }
    return cmd;
}

int Execute(string sql, IDictionary<string, object> values)
{
    using (var cmd = CreateCommand(sql, values))
    {
        return cmd.ExecuteNonQuery();
    }
}

void InsertMyModel(MyModelType obj)
{
    DB.Execute(
        @"INSERT INTO MyTable (ID, Data)
        VALUES (@ID, @Data)", Params(obj));
}

#5

-1

command.Parameters.AddWithValue("@param", DBNull.Value);

#1