C# – How to Coalesce String and DBNull

c++null-coalescing-operator

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

Best Answer

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.

Related Question