Is it possible to coalesce string and DBNull in C#?

Question

I'm writing a C# routine to call a stored proc. In the parameter list I'm passing in, it is possible that one of the values can legally be null. So I thought I'd use a line like this:

cmd.Parameters.Add(new SqlParameter("@theParam", theParam ?? DBNull.Value));

Unfortunately, this returns the following error:

CS0019: Operator '??' cannot be applied to operands of type 'string' and 'System.DBNull'

Now, this seems clear enough, but I don't understand the rationale behind it. Why would this not work? (And often, when I don't understand why something isn't working, it's not that it can't work...it's that I'm doing it wrong.)

Do I really have to stretch this out into a longer if-then statement?

EDIT: (As an aside, to those suggesting to just use "null" as is, it doesn't work. I originally figured null would auto-translated into DBNull too, but it apparently does not. (Who knew?))

Answer 1

Not like that, no. The types have to match. The same is true for the ternary.

Now, by "match", I don't mean they have to be the same. But they do have to be assignment compatible. Basically: in the same inheritance tree.

One way to get around this is to cast your string to object:

var result = (object)stringVar ?? DBNull.Value;

But I don't like this, because it means you're relying more on the SqlParameter constructor to get your types right. Instead, I like to do it like this:

cmd.Parameters.Add("@theParam", SqlDbTypes.VarChar, 50).Value = theParam;
// ... assign other parameters as well, don't worry about nulls yet

// all parameters assigned: check for any nulls
foreach (var p in cmd.Parameters) 
{ 
    if (p.Value == null) p.Value = DBNull.Value; 
}

Note also that I explicitly declared the parameter type.