Using dapper and mapping one column to json

Question

I have a table where I store a dictionary as json in one column. When saving / loading the content to/from the database I'd like to be able to make the json de/serialisation invisible if possible. Is it possible to do this using dapper in some way without using another property in Foo that contains the json representation?

// where someData is the dictionary column
void Save(IDbConnection conn, Foo foo){
    conn.Execute("INSERT INTO foos (name, <othercols...>, someData) VALUES (@Name, <othercols...>, @SomeData, foo);
}

I could manually map out Foo into a new dynamic object, however as there are othercolumns that'd be tedious, so I was wondering if there was any other way?

Answer 1

You can achieve it by implementation of ITypeHandler.

Example implementation for Dictionary<int, string>:

class DictTypeHandler : ITypeHandler
{
    public object Parse(Type destinationType, object value)
    {
        return JsonConvert.DeserializeObject<Dictionary<int, string>>(value.ToString());
    }

    public void SetValue(IDbDataParameter parameter, object value)
    {
        parameter.Value = (value == null)
            ? (object)DBNull.Value
            : JsonConvert.SerializeObject(value);
        parameter.DbType = DbType.String;
    }
}

Dapper will use the handler if you add it to its known handlers with SqlMapper.AddTypeHandler(typeof(DictTypeHandler), new DictTypeHandler())

Take care of setting IDbDataParameter.DbType to string if your sql-column is varchar(max) otherwise Dapper will use sql_variant which is not convertible to varchar(max).

If you implement the persistence via repository pattern you could add the AddTypeHandler code into the constructor.