<CharlieDigital/> Programming, Politics, and uhh…pineapples

4Apr/14Off

FluentNHibernate and SQL Date Generation

So you'd like your SQL entry to have a system generated date/time, eh?

Here is a sample table:

CREATE TABLE [dbo].[AuditLog] (
	Id int IDENTITY(1,1) PRIMARY KEY,
	EventUtc datetime2(7) DEFAULT(SYSUTCDATETIME()) NOT NULL,
	EventOffsetUtc datetimeoffset(7) DEFAULT(SYSDATETIMEOFFSET()) NOT NULL,
	EntityContextUid uniqueidentifier,
	EntityContextName nvarchar(256),
	EntityContextType varchar(128),
	UserLogin nvarchar(128),
	EventName varchar(128),
	AppContext varchar(64),
	EventData nvarchar(max),
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

To spare you hours dealing with this error:

System.Data.SqlTypes.SqlTypeException:
   SqlDateTime overflow. Must be between
   1/1/1753 12:00:00 AM and 12/31/9999
   11:59:59 PM.

What you need to do is to use the following mapping for your date/time columns:

Map(a => a.EventUtc).Column("EventUtc")
	.CustomSqlType("datetime2(7)")
	.Not.Nullable()
	.Default("SYSUTCDATETIME()")
	.Generated.Insert();
Map(a => a.EventOffsetUtc).Column("EventOffsetUtc")
	.CustomSqlType("datetimeoffset(7)")
	.Not.Nullable()
	.Default("SYSDATETIMEOFFSET()")
	.Generated.Insert();

Special thanks to this Stackoverflow thread.

Posted by Charles Chen

Comments (0) Trackbacks (0)

Sorry, the comment form is closed at this time.

Trackbacks are disabled.

  • vocal
  • trap
  • trance
  • techno
  • symphonic-rock
  • spain
  • soundtrack
  • soul
  • singer
  • score
  • rock
  • rnb
  • reggaeton
  • reggae
  • rap
  • punk
  • progressive
  • post-grunge
  • pop
  • other
  • new-audio
  • metalcore
  • lounge
  • latino
  • jazz
  • instrumental
  • indie
  • house
  • hip-hop
  • heavy-metal
  • hard-rock
  • funk
  • folk
  • electronic
  • dubstep
  • drum-and-bass
  • downtempo
  • deep-house
  • dance
  • country
  • club-house
  • classical
  • classic-rock
  • chillout
  • breakbeat
  • blues
  • ambient
  • alternative-rock