NHibernate不会持久的DateTime SqlDateTime溢出 [英] NHibernate won't persist DateTime SqlDateTime overflow
问题描述
我正在使用NHibernate作为后端处理一个ASP.NET MVC项目，并且有一些麻烦，要求一些日期写回到我的SQL Server数据库表。 >这些日期字段不可空，所以这里有关如何设置可空数据时间的许多答案没有帮助。
基本上当我尝试保存具有DateAdded的实体和一个LastUpdated字段，我得到一个SqlDateTime溢出异常。我曾经遇到过类似的问题，我试图将datetime字段写入一个smalldatetime列，更新列上的类型似乎解决了这个问题。我的直觉感觉是它会对表的定义或某种类型的不兼容的数据类型产生一些问题，溢出异常是一个令人讨厌的转变。
我附上了一个表定义和NHibernate试图运行的查询的例子，任何帮助或建议将不胜感激。
CREATE TABLE [dbo]。[CustomPages]（
[ID] [uniqueidentifier] NOT NULL，
[StoreID] [uniqueidentifier] NOT NULL，
[DateAdded] [datetime] NOT NULL ，
[AddedByID] [uniqueidentifier] NOT NULL，
[LastUpdated] [datetime] NOT NULL，
[LastUpdatedByID] [uniqueidentifier] NOT NULL，
[标题] [nvarchar] （150）NOT NULL，
[Term] [nvarchar]（150）NOT NULL，
[Content] [ntext] NULL
）
exec sp_executesql N' INSERT INTO CustomPages（Title，Term，Content，LastUpdated，DateAdded，StoreID，LastUpdatedById，AddedById，ID）VALUES（@ p0，@ p1，@ p2，@ p3，@ p4，@ p5，@ p6，@ p7，@ p8）'，N'@ p0
nvarchar（21），@ p1 nvarchar（21），@ p2 nvarchar（33），@ p3 datetime，@ p4 datetime，@ p5 uniqueidentifier，@ p6 uniqueidentifier， @ p7 uniqueidentifier，@ p8 uniqueidentifier'，@ p0 = N'Size and Color
Chart'，@ p1 = N'size-and-color-chart'，@ p2 = N'This是大小和颜色图表'，@ p3 =''2009-03-14 14：29：37：000'@ p4 =''2009-03-14
14：29：37：000''@ p5 =' 48315F9F-0E00-4654-A2C0-62FB466E529D'，@ p6 ='1480221A-605A-4D72-B0E5-E1FE72C5D43C'，@ p7 ='1480221A-605A-4D72-B0E5-E1FE72C5D43C'，@ p8 ='1E421F9E-9A00-49CF -9180-DCD22FCE7F55'
为回应答案/评论，我使用Fluent NHibernate和生成映射在
public CustomPageMap（）{
WithTable（CustomPages）;
Id（x => x.ID，ID）
.WithUnsavedValue（Guid.Empty）
。 GeneratedBy.Guid（）;
引用（x => x.Store，StoreID）;
地图（x => x.DateAdded，DateAdded）;
引用（x => x.AddedBy，AddedById）;
Map（x => x.LastUpdated，LastUpdated）;
引用（x => x.LastUpdatedBy，LastUpdatedById）;
地图（x => x.Title，标题）;
Map（x => x.Term，Term）;
Map（x => x.Content，Content）;
}
<？xml version =1.0encoding =utf-8？>
< hibernate-mapping xmlns =urn：nhibernate-mapping-2.2default-lazy =falseassembly =MyNamespace.Corenamespace =MyNamespace.Core>
< class name =CustomPagetable =CustomPagesxmlns =urn：nhibernate-mapping-2.2>
< id name =IDcolumn =IDtype =Guidunsaved-value =00000000-0000-0000-0000-000000000000>< generator class =guid/> < / id>
< property name =Titlecolumn =Titlelength =100type =String>< column name =Title/>< / property>
< property name =Termcolumn =Termlength =100type =String>< column name =Term/>< / property>
< property name =Contentcolumn =Contentlength =100type =String>< column name =Content/>< / property>
< property name =LastUpdatedcolumn =LastUpdatedtype =DateTime>< column name =LastUpdated/>< / property>
< property name =DateAddedcolumn =DateAddedtype =DateTime>< column name =DateAdded/>< / property>
< many-to-one name =Storecolumn =StoreID/>< many-to-one name =LastUpdatedBycolumn =LastUpdatedById/>
< many-to-one name =AddedBycolumn =AddedById/>< / class>< / hibernate-mapping>
事实背后的原因是：
当NHibernate从数据库读取行时，该值为null，这就是会话记忆。当对象被NHibernate重新水化时，Date被设置为DateTime.MinValue的值。当Session与db同步时，NHibernate会假定某些事情发生了变化，因为currentState和previousState是不同的，并尝试更新该行。因为DateTime.MinValue不适合SqlServer datetime列。
解决方案：
使您的日期时间可以放置吗？在日期时间结束时，如 DateTime？或 Nullable
完整的文章可以在以下网址找到： nhibernate-sqldatetime-overflow-issue
I am working on an ASP.NET MVC project with NHibernate as the backend and am having some trouble getting some dates to write back to my SQL Server database tables.
These date fields are NOT nullable, so the many answers here about how to setup nullable datetimes have not helped.
Basically when I try to save the entity which has a DateAdded and a LastUpdated fields, I am getting a SqlDateTime overflow exception. I have had a similar problem in the past where I was trying to write a datetime field into a smalldatetime column, updating the type on the column appeared to fix the problem. My gut feeling is that its going to be some problem with the table definition or some type of incompatible data types, and the overflow exception is a bit of a bum steer.
I have attached an example of the table definition and the query that NHibernate is trying to run, any help or suggestions would be greatly appreciated.
CREATE TABLE [dbo].[CustomPages](
[ID] [uniqueidentifier] NOT NULL,
[StoreID] [uniqueidentifier] NOT NULL,
[DateAdded] [datetime] NOT NULL,
[AddedByID] [uniqueidentifier] NOT NULL,
[LastUpdated] [datetime] NOT NULL,
[LastUpdatedByID] [uniqueidentifier] NOT NULL,
[Title] [nvarchar](150) NOT NULL,
[Term] [nvarchar](150) NOT NULL,
[Content] [ntext] NULL
)
exec sp_executesql N'INSERT INTO CustomPages (Title, Term, Content, LastUpdated, DateAdded, StoreID, LastUpdatedById, AddedById, ID) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)',N'@p0
nvarchar(21),@p1 nvarchar(21),@p2 nvarchar(33),@p3 datetime,@p4 datetime,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier',@p0=N'Size and Colour
Chart',@p1=N'size-and-colour-chart',@p2=N'This is the size and colour chart',@p3=''2009-03-14 14:29:37:000'',@p4=''2009-03-14
14:29:37:000'',@p5='48315F9F-0E00-4654-A2C0-62FB466E529D',@p6='1480221A-605A-4D72-B0E5-E1FE72C5D43C',@p7='1480221A-605A-4D72-B0E5-E1FE72C5D43C',@p8='1E421F9E-9A00-49CF-9180-DCD22FCE7F55'
In response the the answers/comments, I am using Fluent NHibernate and the generated mapping is below
public CustomPageMap() {
WithTable("CustomPages");
Id( x => x.ID, "ID" )
.WithUnsavedValue(Guid.Empty)
. GeneratedBy.Guid();
References(x => x.Store, "StoreID");
Map(x => x.DateAdded, "DateAdded");
References(x => x.AddedBy, "AddedById");
Map(x => x.LastUpdated, "LastUpdated");
References(x => x.LastUpdatedBy, "LastUpdatedById");
Map(x => x.Title, "Title");
Map(x => x.Term, "Term");
Map(x => x.Content, "Content");
}
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false" assembly="MyNamespace.Core" namespace="MyNamespace.Core">
<class name="CustomPage" table="CustomPages" xmlns="urn:nhibernate-mapping-2.2">
<id name="ID" column="ID" type="Guid" unsaved-value="00000000-0000-0000-0000-000000000000"><generator class="guid" /></id>
<property name="Title" column="Title" length="100" type="String"><column name="Title" /></property>
<property name="Term" column="Term" length="100" type="String"><column name="Term" /></property>
<property name="Content" column="Content" length="100" type="String"><column name="Content" /></property>
<property name="LastUpdated" column="LastUpdated" type="DateTime"><column name="LastUpdated" /></property>
<property name="DateAdded" column="DateAdded" type="DateTime"><column name="DateAdded" /></property>
<many-to-one name="Store" column="StoreID" /><many-to-one name="LastUpdatedBy" column="LastUpdatedById" />
<many-to-one name="AddedBy" column="AddedById" /></class></hibernate-mapping>
Actually the reason behind the scene is:
When NHibernate reads the row from the db, the value is null, and that is what the session remembers. When the object is rehydrated by NHibernate the Date is set to the value DateTime.MinValue. When the Session is synchronized with the db, NHibernate assumes that something has changed, because the currentState and the previousState are different and tries to update the row. Which in turn fails, because DateTime.MinValue will not fit into a SqlServer datetime column.
The solution: make your datetime nullable either by putting ? at end of Datetime like DateTime? or Nullable
complet article can be found at: nhibernate-sqldatetime-overflow-issue
这篇关于NHibernate不会持久的DateTime SqlDateTime溢出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
