Using DBNull.Value with SqlParameter without knowing sqlDbType?

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