流畅的NHibernate SQL Server 2008 DATE列值问题 [英] Fluent NHibernate Problems with SQL Server 2008 DATE Column Values

问题描述

问候语

我在使用Fluent NHibernate的C＃中使用SQL Server 2008 DATE 列时遇到问题。

I'm having problems using a SQL Server 2008 DATE column in C# using Fluent NHibernate.

当我尝试更新一个具有不可为空的 DATE 列之前的值的记录， 1/1753（DATETIME的最小日期），我收到一条错误，表示无法在该列中插入NULL。如果值大于1/1/1753，则没有问题，保留正确的日期值。

When I try to update a record that has a value in a non-nullable DATE column prior to 1/1/1753 (the min date for a DATETIME), I'm getting an error saying that it can't insert a NULL into that column. If the value is greater than 1/1/1753, there are no problems and the correct date value is preserved.

这是我的模型文件：

public class Table1 : model.DBObject
{
  public virtual Int32 TestID { get; private set; }
  public virtual String Description { get; set; }
  public virtual DateTime TestDate { get; set; }

  public Table1()
  {
  }

  public static Table1 Load(DBSess sess, Int32 TestID)
  {
      return (Table1)sess.Session.Get(typeof(Table1), TestID);
  }
}

我的映射文件：

public class Table1Map : ClassMap<Table1>
{
  public Table1Map()
  {
    Table("[Table1]");
    Id(x => x.TestID).GeneratedBy.Identity();
    Map(x => x.Description).Not.Nullable();
    Map(x => x.TestDate).Not.Nullable().CustomType("date");
  }
}

正在执行的代码：

using (DBSess sess = DBSess.Create())
{
  Table1 tbl = dal.Table1.Load(sess, 1);
  tbl.Description = String.Format("Updated {0}", DateTime.Now);
  tbl.Save(sess);
  sess.Commit();
}

导出的NHibernate映射：

The exported NHibernate mappings:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property" auto-import="true" default-cascade="none" default-lazy="true">
  <class xmlns="urn:nhibernate-mapping-2.2" mutable="true" name="dal.Table1, dal, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="[Table1]">
    <id name="TestID" type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="TestID" />
      <generator class="identity" />
    </id>
    <property name="Description" type="System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="Description" not-null="true" />
    </property>
    <property name="TestDate" type="date">
      <column name="TestDate" not-null="true" />
    </property>
  </class>
</hibernate-mapping>

log4net捕获的NHibernate.SQL日志条目：

The NHibernate.SQL log entries captured by log4net:

DEBUG2011-03-24 05:00:18 – SELECT table1x0_.TestID as TestID0_0_, table1x0_.Description as Descript2_0_0_, table1x0_.TestDate as TestDate0_0_ FROM [Table1] table1x0_ WHERE table1x0_.TestID=@p0;@p0 = 1
DEBUG2011-03-24 05:00:18 – UPDATE [Table1] SET Description = @p0, TestDate = @p1 WHERE TestID = @p2;@p0 = 'Updated 3/24/2011 5:00:18 PM', @p1 = NULL, @p2 = 1

log4net捕获的NHibernate日志条目的相关部分：

The pertinent section of the NHibernate log entries captured by log4net:

DEBUG2011-03-24 05:00:18 – Building an IDbCommand object for the SqlString: UPDATE [Table1] SET Description = ?, TestDate = ? WHERE TestID = ?
DEBUG2011-03-24 05:00:18 – Dehydrating entity: [dal.Table1#1]
DEBUG2011-03-24 05:00:18 – binding 'Updated 3/24/2011 5:00:18 PM' to parameter: 0
DEBUG2011-03-24 05:00:18 – binding '6/12/1700' to parameter: 1
DEBUG2011-03-24 05:00:18 – binding '1' to parameter: 2
DEBUG2011-03-24 05:00:18 – Obtaining IDbConnection from Driver
ERROR2011-03-24 05:00:19 – Could not execute command: UPDATE [Table1] SET Description = @p0, TestDate = @p1 WHERE TestID = @p2
System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'TestDate', table 'test2.dbo.Table1'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd)

日志似乎显示了6/12/1700值与DATE列的参数的正确绑定，但是SQL语句抛出一个异常，表示它试图插入一个NULL。如果记录中的值大于1/1/1753，则不会有异常，并且值正确保存。

The logs seem to show the proper binding of the value '6/12/1700' to the parameter for the DATE column, but the SQL statement throws an exception saying it's trying to insert a NULL. If the value that's in the record is greater than '1/1/1753' there is no exception and the value is preserved properly.

我可以发布完整的NHibernate日志文件，如果有更多的信息可能有帮助。我不知道从哪里可以找到答案。

I can post the full NHibernate log file, if there is more information in there that might help. I'm not sure where to look from here for the answer.

有没有人想知道在哪里寻找解决方案？

Does anyone have any thoughts about where to look for a solution?

提前感谢

〜Jim Fennell

~ Jim Fennell

推荐答案

对不起，只是意识到我应该发布这个问题的答案，而不是只是评论...请不要笑新的人！

Sorry, just realized I should post this as an answer to the question instead of just commenting... Please don't laugh at the "new guy!"

通过进一步的讨论和测试，似乎这个问题与ADO.NET和NHibernate对数据类型DATE的MS SQL 2008列的DbType.Date的使用有关。如果代码使用SqlDbType.Date而不是DbType.Date，则不会发生任何这样的问题，并且所有操作都将按照指定执行。

Through further discussions and testing, it seems that this issue is related to ADO.NET and NHibernate's use of the DbType.Date for MS SQL 2008 columns of the datatype DATE. If the code were using the SqlDbType.Date instead of DbType.Date, no such problems would occur and everything would perform as specified.

虽然使用DbType.Date可能对其他数据库类型更加便携，但它确实会引入此问题，因为ADO.NET DbType.Date具有限制的最小值为1/1/1753。

While the use of DbType.Date may seem more "portable" to other database types, it does introduce this problem, since the ADO.NET DbType.Date has the restriction of a minimum value of 1/1/1753.

此问题的解决方法是告诉NHibernate列的类型是DATETIME2，它也支持早于1/1/1753的日期值。如果NHibernate映射是：

A workaround to this problem is to tell NHibernate that the type of the column is a DATETIME2, which also supports date values earlier than 1/1/1753. If the NHibernate mapping is:

<property name="TestDate" type="datetime2">
  <column name="TestDate" sql-type="date" />
</property>

代码按预期执行，无异常。

The code performs as expected with no exceptions.

这太糟糕了，NHibernate不能识别它使用的是MsSql2008方言，而是使用SqlDbTypes而不是DbTypes，但是现在这种解决方法似乎是有效的。

It's too bad NHibernate doesn't recognize it's using the MsSql2008 dialect and use the SqlDbTypes rather than the DbTypes, but this workaround appears to work for now.

有关ADO.NET方面的更多信息，请参阅 ADO.NET将最小值插入SQL Server 2008日期列崩溃。感谢@Graham Bunce的帮助。

For more information on the ADO.NET aspects, see ADO.NET Insert Min Value into SQL Server 2008 Date column crashes. Thanks to @Graham Bunce for his help with this.

我希望这些信息有助于将来遇到这个问题的任何人。

I hope this information assists anyone who runs into this problem in the future.

这篇关于流畅的NHibernate SQL Server 2008 DATE列值问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！