Using DBNull.Value with SqlParameter without knowing sqlDbType?

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

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

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 para开发者_如何转开发meter?

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

Old post but might be helpful someone else.

Convert.DBNull

Like this

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

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

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

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

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

Source

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.

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.

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

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));
}

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