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

Question

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?

Answer 1

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;