Fluent NHibernate Problems with SQL Server 2008 DATE Column Values

Question

Greetings,

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

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.

Here's my model file:

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);
  }
}

My mapping file:

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");
  }
}

The code being executed:

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

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>

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

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)

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.

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?

Thanks in advance,

~ Jim Fennell

Answer 1

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!"

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.

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.

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.

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.

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.