C# ?? null coalescing operator

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;

Duplicate of http://stackoverflow.com/questions/218808/c-ado-net-nulls-and-dbnull-is-there-more-efficient-syntax — sgriffinusa, Aug 06 '10 at 15:25
Got this from the other post: SqlDbType.SmallDateTime)).Value = person.Birthday ?? (object)DBNull.Value; Thanks! — Lill Lansey, Aug 06 '10 at 16:18

score 23 · Accepted Answer · answered Aug 06 '10 at 15:27

23

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

answered Aug 06 '10 at 15:27

Dave D

8,472
4
33
45

JYelton · Answer 2 · 2013-12-19T18:19:48.057

5

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.

edited Dec 19 '13 at 18:19

answered Aug 06 '10 at 16:16

JYelton

35,664
27
132
191

My issue with this is the extra overhead of iterating over the parameters after the fact, versus handling them immediately. – Andrew Mar 24 '17 at 17:25

score 3 · Answer 3 · answered Aug 06 '10 at 15:29

3

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

answered Aug 06 '10 at 15:29

James Curran

101,701
37
181
258

1

So what does `?:` do? edit: nevermind, I knew that, just never saw those 2 symbols put together like that before. http://msdn.microsoft.com/en-us/library/ty67wk28.aspx – Allen Rice Aug 06 '10 at 15:55

C# ?? null coalescing operator

3 Answers3