C# ?? null coalescing operator question

Question

I have defined Class Person property Birthday as nullable DateTime? , so why shouldn’t the null coalescing operator work in the following example?

cmd.Parameters.Add(new SqlParameter("@Birthday",
   SqlDbType.SmallDateTime)).Value =       
     person.Birthday ?? DBNull.Value; 

The compiler err I got was "Operator '??' cannot be applied to operands of type 'System.DateTime?' and 'System.DBNull'"

The following also got a compile error :

cmd.Parameters.Add(new SqlParameter("@Birthday", 
  SqlDbType.SmallDateTime)).Value = 
   (person.Birthday == null) ? person.Birthday:DBNull.Value;

I added a cast to (object) as recommended by Refactor, and it compiled, but didn’t work properly and the value was stored in the sqlserver db as null in both cases.

SqlDbType.SmallDateTime)).Value =       
         person.Birthday ?? (object)DBNull.Value;

Can someone explain what is going on here?

I needed to use the following clumsy code:

   if (person.Birthday == null) 
    cmd.Parameters.Add("@Birthday", SqlDbType.SmallDateTime).Value 
      = DBNull.Value;
     else cmd.Parameters.Add("@Birthday", SqlDbType.SmallDateTime).Value = 
          person.Birthday;

Answer 1

The problem is that DateTime? and DBNull.Value are not the same type so you can't use the null coalescing operator on them.

In your case you can do person.Birthday ?? (object)DBNull.Value to pass a value of type object through to Add()

Answer 2

Your first problem is that for the ?? or ?: operator, the objects for either choice must be the same type. Here they are different type.

Answer 3

I prefer to iterate over my parameters just before executing the query, changing all instances of null to DBNull as appropriate, for example:

foreach (IDataParameter param in cmd.Parameters)
if (param.Value == null) param.Value = DBNull.Value;

This lets me leave null values as-is and simply swap them out en masse later.