We have migrated one legacy application from old asp.net web-form to asp.net mvc. But we cannot change the database structure as other applications also depends on this database. To communicate with the database we have used dapper in our mvc application. Dapper is an open source micro orm developed by some awesome people at stackoverflow. Dapper has emerged as one of the most powerful micro-ORM. After getting the data from the database, dapper will automatically map or deserialize the data into our custom object. But sometimes we are in a situation where we want the complete control on how database data is mapped to our custom complex object. This where a custom object type handler comes into picture. Dapper have this one of the powerful feature which you can achieve using SqlMapper.TypeHandler.
So i have a situation where my legacy application saves pipe separated data into table which looks something like this –
and i want to map this pipe separated data into C# Dictionary object. So to handle this situation, dapper provides a custom type handler. In today’s article we will learn about how to use it.
So let’s get started. To bind the data from the database into my model object. Here is the structure.
public class SiteMasterModel
{
public int SiteId { get; set; }
public string SIteName { get; set; }
public int ClientId { get; set; }
public SiteConfigurationDictionary SiteConfiguration { get; set; }
}
public class SiteConfigurationDictionary : Dictionary <string, string>
{
// code goes here..
}
So now if i try to bind my SiteConfiguration column data (shown in above screenshot) which is pipe separated string to SiteConfigurationDictionary object, then we will get the following exception –
Dapper here clearly tells us that it has no idea of how to deal with your complex SiteConfigurationDictionary object. So this is where the SqlMapper.TypeHandler comes to rescue us. So what you need to do is crate a separate class, let say called it – SiteConfigurationHandler class which is inherited by SqlMapper.TypeHandler base class. Then in that class you need to override two important following method –
1). Parse() :- This method is called when we select the data from the database and deserialize into our complex object.
2). SetValue() :- This method is called when we try to save our modified data back to the database.
public class SiteConfigurationHandler : SqlMapper.TypeHandler<SiteConfigurationDictionary>
{
public override void SetValue(IDbDataParameter parameter, SiteConfigurationDictionary value)
{
parameter.Value = value.ToString();
}
public override SiteConfigurationDictionary Parse(object value)
{
return SiteConfigurationDictionary.FromConfigurationString(value.ToString());
}
}
So as you see in the above code that Parse() method called FromConfigurationString() of SiteConfigurationDictionary class. Well, this is a static method where we actually implemented our custom mapping logic to deal with our complex object. So now our SiteConfigurationDictionary class looks like this –
public class SiteConfigurationDictionary : Dictionary<string, string>
{
public override string ToString()
{
return Join("|", this.Select(item => $"{item.Key}={item.Value}"));
}
public static SiteConfigurationDictionary FromConfigurationString(string value)
{
//IDictionary<string, string> configDict = new Dictionary<string, string>();
SiteConfigurationDictionary configDict = new SiteConfigurationDictionary();
if (string.IsNullOrEmpty(value))
return configDict;
var configArray = value.Split('|');
foreach (var configInfo in configArray)
{
var splitedData = configInfo.Split('=');
if (!configDict.ContainsKey(splitedData[0]))
{
configDict.Add(splitedData[0], splitedData[1]);
}
}
return configDict;
}
}
Now our handler is ready to use. We just need to register it with Dapper. If you are developing web application then you can register it in Application_Start event. In my case this is just a simple demo console application. To register handler following is the code –
// First we need to register our custom type handler SqlMapper.AddTypeHandler(new SiteConfigurationHandler());
Now when we fetch the data from the database using following code.
static void Main(string[] args)
{
// First we need to register our custom type handler
SqlMapper.AddTypeHandler(new SiteConfigurationHandler());
List<SiteMasterModel> siteList = GetSiteMasterList();
}
private static List<SiteMasterModel> GetSiteMasterList()
{
try
{
var connectionString = ConfigurationManager.ConnectionStrings["NorthwndConnectionString"].ConnectionString;
using (IDbConnection conn = new SqlConnection(connectionString))
{
return conn.Query
(
"SELECT SiteId, SIteName, ClientId, SiteConfiguration FROM dbo.WebSiteMaster"
).ToList();
}
}
catch (Exception ex)
{
throw ex;
}
}
Now when you run the application, this is how dapper mapped the data when you debug the code –
Well, you can download the full source code from my Github repo to play with this feature. Have you ever used this feature of dapper, you can share us your scenario in comment section. Hope you like this post. Thanks for reading.
Krishnraj Rana 