Correctly convert DateTime property using Dapper to SQLite

Question

I use Dapper to insert and retrieve objects to / from SQLite: one object has a property of type DateTime (and DateTimeOffset), which I have to save and retrieve to the nearest millisecond. I can’t find a way to get the value correctly, because Dapper crashes:

    System.FormatException : String was not recognized as a valid DateTime.
   in System.DateTimeParse.ParseExactMultiple(String s, String[] formats, DateTimeFormatInfo dtfi, DateTimeStyles style)
   in System.DateTime.ParseExact(String s, String[] formats, IFormatProvider provider, DateTimeStyles style)
   in System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText, SQLiteDateFormats format, DateTimeKind kind, String formatString)
   in System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
   in System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt, SQLiteConnectionFlags flags, Int32 index, SQLiteType typ)
   in System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
   in System.Data.SQLite.SQLiteDataReader.GetValues(Object[] values)
   in Dapper.SqlMapper.<>c__DisplayClass5d.<GetDapperRowDeserializer>b__5c(IDataReader r) in SqlMapper.cs: line 2587
   in Dapper.SqlMapper.<QueryImpl>d__11`1.MoveNext() in SqlMapper.cs: line 1572
   in System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   in System.Linq.Enumerable.ToList(IEnumerable`1 source)
   in Dapper.SqlMapper.Query(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in SqlMapper.cs: line 1443
   in Dapper.SqlMapper.Query(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in SqlMapper.cs: line 1382

What do i need to try? The column is of type DATETIME.

Do I need to create a custom TypeHandler and convert the DateTime to and from an SQLite string in o format?

Dapper version 1.38

Answer 1

, TypeHandler - Map, TypeHandler.

dapper-dot-net, Map , , DateTime, DateTime?, DateTimeOffset, DateTimeOffset?

Answer 2

, . Dapper ( , 2019 ):

:

public class DateTimeHandler : SqlMapper.TypeHandler<DateTimeOffset>
{
    private readonly TimeZoneInfo databaseTimeZone = TimeZoneInfo.Local;
    public static readonly DateTimeHandler Default = new DateTimeHandler();

    public DateTimeHandler()
    {

    }

    public override DateTimeOffset Parse(object value)
    {
        DateTime storedDateTime;
        if (value == null)
            storedDateTime = DateTime.MinValue;
        else
            storedDateTime = (DateTime)value;

        if (storedDateTime.ToUniversalTime() <= DateTimeOffset.MinValue.UtcDateTime)
            return DateTimeOffset.MinValue;
        else
            return new DateTimeOffset(storedDateTime, databaseTimeZone.BaseUtcOffset);
    }

    public override void SetValue(IDbDataParameter parameter, DateTimeOffset value)
    {
        DateTime paramVal = value.ToOffset(this.databaseTimeZone.BaseUtcOffset).DateTime;
        parameter.Value = paramVal;
    }
}

, Dapper .Net DateTimeOffset dbType - DateTimeOffset. , , :

SqlMapper.RemoveTypeMap(typeof(DateTimeOffset));
SqlMapper.AddTypeHandler(DateTimeHandler.Default);

. , Dapper DateTimeOffset , DateTimeHandler .