带有Dapper更新的可空DateTime字段的SqlDateTimeOverflow异常

I have a db table with several DateTime fields with null values. These are mapped to nullable DateTimes in my class.

我有一个db表,其中包含多个具有空值的DateTime字段。这些映射到我的类中可为空的DateTimes。

If I try to perform an update with Dapper, within my data layer:

如果我尝试使用Dapper在我的数据层中执行更新:

 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' rather than null.

我得到一个SqlDateTimeOverflow异常(因为DateTime字段是'01 / 01/0001 00:00:00'而不是null。

Is the only way around this to specify each parameter individually and switch the value to null like this:

是唯一的方法来单独指定每个参数并将值切换为null,如下所示:

 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 would be quite a bit of code, plus there is an INSERT method to update similarly too. Is there an easier syntax I am missing?

我在表中有大约50个字段,所以这将是相当多的代码,另外还有一个INSERT方法也可以类似地更新。我错过了更简单的语法吗?

1 个解决方案

#1

The issue here is that 01/01/0001 00:00:00 is not a "null value"; if you had used DateTime? I suspect it would have worked fine. However, I also have to recognize that DateTime.MinValue has often (mainly due to .NET 1.1 lacking nullable structs) been used to represent a null value. My preferred suggestion here would be to simply use DateTime?. The min-value map is a bit complicated as we might also consider whether that should be automatically mapped instead to the sql min-value (January 1, 1753).

这里的问题是01/01/0001 00:00:00不是“空值”;如果你使用过DateTime?我怀疑它会运转正常。但是,我还必须认识到DateTime.MinValue经常(主要是由于.NET 1.1缺少可空结构)用于表示空值。我在这里的首选建议是简单地使用DateTime?。最小值映射有点复杂,因为我们也可以考虑是否应该自动映射到sql min-value(1753年1月1日)。

Re the update statement - maybe add an extension method to map between min-value and null?

重新更新语句 - 可能添加一个扩展方法来映射min-value和null?

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

and use:

new { MyDateField = MyDateField.NullIfZero() }

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

但是,如果MyDateField是DateTime?,你可以使用:

new { MyDateField }

#1