How to use DbNull.Value in conditional assignment to column value?

Question

I'm constructing a Command object, with parameters, to set column values. I'd like to use a simple conditional to set a certain nullable int column to either an integer value or DbNull.Value, depending on whether a variable is null or not.

Example:

dbCmd.Parameters.Add("@MyIntColumn", SqlDbType.Int).Value =
    myColumnValue == null ? DBNull.Value : myColumnValue;

Of course, that gives the compiler error: "no implicit conversion between DbNull and int".

So what is the correct way to use a conditional there, whose result could be either an int or DbNull?

Answer 1

The problem is the compiler cannot infer what type the result of the expression will be - whether it is a DBNull or an int, since they are incompatible types.

Explicitly cast at least one side of that to object (the highest common denominator) and it will understand:

dbCmd.Parameters.Add("@MyIntColumn", SqlDbType.Int).Value =
    myColumnValue == null ? (object)DBNull.Value : myColumnValue;