Mapping TimeSpan in SQLite and Dapper

Question

I'm attempting to use Dapper to interface to an existing database format that has a table with a duration encoded as ticks in a BIGINT column. How do I tell Dapper to map my POCO's TimeSpan-typed property to ticks when inserting into and reading from the database?

I've tried to set the type map for TimeSpan to DbType.Int64:

SqlMapper.AddTypeMap(typeof(TimeSpan), DbType.Int64);

And I've also created an ITypeHandler, but the SetValue method is never called:

public class TimeSpanToTicksHandler : SqlMapper.TypeHandler<TimeSpan>
{
    public override TimeSpan Parse(object value)
    {
        return new TimeSpan((long)value);
    }

    public override void SetValue(IDbDataParameter parameter, TimeSpan value)
    {
        parameter.Value = value.Ticks;
    }
}

Here's my POCO:

public class Task
{
    public TimeSpan Duration { get; set; }

    // etc.
}

When executing a simple insert statement like this:

string sql = "INSERT INTO Tasks (Duration) values (@Duration);";

And passing the POCO as the object to insert:

Task task = new Task { Duration = TimeSpan.FromSeconds(20) };
connection.Execute(sql, task);

I get this exception:

System.InvalidCastException : Unable to cast object of type 'System.TimeSpan' to type 'System.IConvertible'.
   at System.Convert.ToInt64(Object value, IFormatProvider provider)
   at System.Data.SQLite.SQLiteStatement.BindParameter(Int32 index, SQLiteParameter param)
   at System.Data.SQLite.SQLiteStatement.BindParameters()
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, ref CommandDefinition command, Action`2 paramReader) in SqlMapper.cs: line 3310
   at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, ref CommandDefinition command) in SqlMapper.cs: line 1310
   at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in SqlMapper.cs: line 1185

If I leave the TimeSpan type mapping as-is (it defaults to DbType.Time), it writes the string version of the TimeSpan, i.e. `00:00:20.000", which isn't helpful as it does not match the format of the other data in the column.

Answer 1

Could you do the following instead?

public class Task
{
    public TimeSpan Duration { get; set; }
    public long Ticks 
    { 
        get { return Duration.Ticks; }
        set { Duration = new TimeSpan(value); }
    }
    // etc.
}

string sql = "INSERT INTO Tasks (Duration) values (@Ticks);";