CODEWITHSUNDEEP
c#dapper
Dirk
Dirk

Reputation: 934

How can I map .NET DateTime to database DateTime2 using Dapper to avoid "SqlDateTime overflow" exception?

I'm converting our existing system from Entity Framework to Dapper. For various corporate reasons, we can't really change the database. Some of the tables have columns that are of type DateTime2.

Dapper converts any .NET DateTime to DbType.DateTime. This causes exception:

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

Is there any way to map .NET DateTime to database DateTime2 using Dapper?

Upvotes: 32

Views: 17617

Answers (4)

John Tseng
John Tseng

Reputation: 6352

There's a much easier solution now in a similar question, but it is about string.

For DateTime:

SqlMapper.AddTypeMap(typeof(DateTime), System.Data.DbType.DateTime2);

This must be applied before any database call like INSERT.

You can put it in your Main or Startup class, or any place that runs on Startup to configure data access. SqlMapper is a static class and changes apply to all calls.

Upvotes: 66

Ariel Moraes
Ariel Moraes

Reputation: 648

The solutions provided will map the types globally because the list of mappings is static.

To create a list of parameters for a single command a better approach is to use the DynamicParameters class.

var parameters = new DynamicParameters(template);
parameters.Add("@DateTimeParam", dateTimeValue, DbType.DateTime2);
    
await connection.ExecuteAsync(sql, parameters);

Where template can be any object (including the previous parameters used). The template properties are going to be merged with the added parameters.

Upvotes: 3

André Mendonça
André Mendonça

Reputation: 678

For Date and time data with time zone awareness.

SqlMapper.AddTypeMap(typeof(DateTime), System.Data.DbType.DateTimeOffset);

I do not know why when I tried to use Datetime2, I still kept losing the milliseconds. This DateTimeOffset type is also Datetime2.

The date value range is from January 1,1 AD through December 31, 9999 AD. The time value range is 00:00:00 through 23:59:59.9999999 with an accuracy of 100 nanoseconds. Time zone value range is -14:00 through +14:00.

Upvotes: 1

Randolpho
Randolpho

Reputation: 56439

Dapper is litterally a single file that you include into your code base. Just edit the file:

Replace (around line 300):

        typeMap[typeof(Guid)] = DbType.Guid;
        typeMap[typeof(DateTime)] = DbType.DateTime;
        typeMap[typeof(DateTimeOffset)] = DbType.DateTimeOffset;
        typeMap[typeof(byte[])] = DbType.Binary;

With: 

        typeMap[typeof(Guid)] = DbType.Guid;
        typeMap[typeof(DateTime)] = DbType.DateTime2;
        typeMap[typeof(DateTimeOffset)] = DbType.DateTimeOffset;
        typeMap[typeof(byte[])] = DbType.Binary;

Edit:
There's also a nullable DateTime further down that block of mappings, around line 319: 

        typeMap[typeof(DateTime?)] = DbType.DateTime;
        typeMap[typeof(DateTimeOffset?)] = DbType.DateTimeOffset;

To:

        typeMap[typeof(DateTime?)] = DbType.DateTime2;
        typeMap[typeof(DateTimeOffset?)] = DbType.DateTimeOffset;

Upvotes: 9

Related Questions