How to map MySQL JSON column using Dapper

Question

I have a table in MySQL database which contains a JSON data type column. Is it possible to retrieve the JSON data stored in that column and map to my c# class using Dapper? Below is a sample JSON about how data is stored in column.

[
        {
            "ServerName": "",
            "Priority": 1,
            "FilesystemBasePath": "",
            "IsAvailable": 1,
            "ApplicationDocumentType": ""
        },
        {
            "ServerName": "",
            "Priority": 2,
            "FilesystemBasePath": "",
            "IsAvailable": 1,
            "ApplicationDocumentType": ""
        }
]

I want the data to be mapped to a List<MyObject> type in C#.

kyrylomyr · Accepted Answer · 2021-06-18 08:05:58Z

14

You register an ITypeHandler for List<MyObject>, or whatever the property type is.

public class MyClass
{
    public List<MyObject> MyObjects {get; set; }
}

public class JsonTypeHandler: SqlMapper.ITypeHandler
{
   public void SetValue(IDbDataParameter parameter, object value)
   {
       parameter.Value = JsonConvert.SerializeObject(value);
   }

   public object Parse(Type destinationType, object value)
   {
       return JsonConvert.DeserializeObject(value as string, destinationType);
   }
}

SqlMapper.AddTypeHandler(typeof(List<MyObject>),new JsonTypeHandler());

After that if you have a column name that maps to a property of type List<MyObject> it will be serialized and deserialized properly.

edited Jun 18, 2021 at 8:05

kyrylomyr

12.7k9 gold badges57 silver badges81 bronze badges

answered Nov 1, 2017 at 23:25

Darren Clark

8039 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to map MySQL JSON column using Dapper

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related