How in Dapper can I map one column to a jSON?

c# dapper


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?

5/2/2017 1:18:28 PM

Popular Answer

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.

10/23/2017 10:16:52 AM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow