Thursday, 3 July 2014

Dapper gets type handlers and learns how to read maps

A recurring point of contention in dapper has been that it is a bit limited in terms of the types it handles. If you are passing around strings and integers: great. If you are passing around DataTable – that’s a bit more complicated (although moderate support was added for table valued parameters). If you were passing around an entity framework spatial type: forget it.
Part of the problem here is that we don’t want dapper to take a huge pile of dependencies on external libraries, that most people aren’t using – and often don’t even have installed or readily available. What we needed was a type handler API. So: I added a type handler API! Quite a simple one, really – dapper still deals with most of the nuts and bolts, and to add your own handler all you need to provide is some basic parameter setup.
For example, here’s the code for DbGeographyHandler; the only interesting thing that dapper doesn’t do internally is set the value – but the type-handler can also do other things to configure the ADO.NET parameter (in this case, set the type name). It also needs to convert between the Entity Framework representation of geography and the ADO.NET representation, but that is pretty easy:
public override void SetValue(IDbDataParameter parameter, DbGeography value)
    parameter.Value = value == null ? (object)DBNull.Value
        : (object)SqlGeography.Parse(value.AsText());
    if (parameter is SqlParameter)
        ((SqlParameter)parameter).UdtTypeName = "GEOGRAPHY";

and… that’s it. All you need to do is register any additional handlers (SqlMapper.AddTypeHandler()) and it will hopefully work. We can now use geography values in parameters without pain – i.e.
conn.Execute("... @geo ...",
    new { id = 123, name = "abc", geo = myGeography }

Plugin packages

This means that handlers for exotic types with external dependencies can be shipped separately to dapper, meaning we can now add packages like Dapper.EntityFramework, which brings in support for the Entity Framework types. Neat.

Free to a good home: better DataTable support

At the same time, we can also make a custom handler for DataTable, simplifying a lot of code. There is one slight wrinkle, though: if you are using stored procedures, the type name of the custom type is known implicitly, but a lot of people (including us) don’t really use stored procedures much : we just use raw command text. In this situation, it is necessary to specify the custom type name along with the parameter. Previously, support for this has been provided via the AsTableValuedParameter() extension method, which created a custom parameter with an optional type name – but dapper now internally registers a custom type handler for DataTable to make this easier. We still might need the type name, though, so dapper adds a separate extension method for this, exploiting the extended-properties feature of DataTable:
DataTable table = ...
conn.Execute(someSql, new { id = 123, values = table });

That should make things a bit cleaner! Custom type handlers are welcome and encouraged - please do share them with the community (ideally in ready-to-use packages).


abatishchev said...

Hey Marc,
Why do you use for reference type operator 'is' and then cast rather than 'as' and check for null?

Marc Gravell said...

@abatishchev because both work fine, and when measured, there is no significant performance difference between them (there are, IIRC, plenty of times when the is/cast can out-perform the as). But ultimately, this is *not* going to be any bottleneck.

Patrick Huizinga said...


Form me using is/as is not about performance, but about preventing mistakes.

For the same reason I don't like having two methods that are basically the same, I don't like having the same type twice. (I know, distance and tool support make a big difference.)

Plus that I think that cast + method call looks horrible.

Anonymous said...

Hi Marc. I try to use TypeHandler for parameters and it doesn't wok.
My code:
public class TimestampTypeHandler : SqlMapper.TypeHandler
public override void SetValue(IDbDataParameter parameter, ulong value)
parameter.Value = TimestampConverter.FromUInt64(value);

public override ulong Parse(object value)
return TimestampConverter.ToUInt64((byte[])value);
SqlMapper.AddTypeHandler(new TimestampTypeHandler());
_connection.Query("SELECT RowTimestamp WHERE RowTimestamp = @tm", new { tm = rowTimestamp })
Exception "No mapping exists from DbType UInt64 to a known SqlDbType".

For returning types everything is ok, but for query parameters method SetValue doesn't execute. I use Dapper 1.42. Please advice. Thank you.

Anushka Sharma said...

nice article… simple and useful :).... Plz visit my site...! Thanks
Technical Support Engineering Services in Delhi,

Vani Shree said...

You shared very useful post. Thanks for sharing.

Data recovery services in chennai