SqlDateTimeOverflow exception with DateTime null fields with Dapper update

Question

I have a db table with multiple DateTime fields with null values. They map to null DateTimes in my class.

If I try to upgrade using Dapper in my data layer:

using (IDbConnection cnn = new SqlConnection(DB.getConString())) { cnn.Open(); return cnn.Execute((this.OptionID == 0 ? _insertSQL : _updateSQL), this); } 

I get a SqlDateTimeOverflow exception (because the DateTime field is '01 / 01/0001 00:00:00 ', not null.

The only way around this is to specify each parameter separately and switch the value to zero, like this:

  using (IDbConnection cnn = new SqlConnection(DB.getConString())) { cnn.Open(); return cnn.Execute("UPDATE MyTable SET MyDateField = @MyDateField", new {MyDateField = (MyDateField.HasValue? MyDateField : Null), etc etc... ); 

I have about 50 fields in the table, so this will be very little code, plus there is an INSERT method for updating in a similar way. Is there a simpler syntax that I skip?

Answer 1

The problem here is that 01/01/0001 00:00:00 not a "null value"; if you used DateTime? I suspect this would work well. However, I must also admit that DateTime.MinValue often (mainly due to .NET 1.1 without null structures) used to represent a null value. My preferred suggestion here would be to just use a DateTime? . The minimum value map is a bit complicated, as we can also consider whether this should be automatically matched with the minimum sql value (January 1, 1753).

Repeat the update statement - maybe add an extension method to display between the minimum value and the null value?

 public static DateTime? NullIfZero(this DateTime when) { return when == DateTime.MinValue ? (DateTime?)null : when; } 

and use:

 new { MyDateField = MyDateField.NullIfZero() } 

but then again, if MyDateField was a DateTime? you could just use:

 new { MyDateField }