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 = ...
table.SetTypeName("MyCustomType");
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).

8 comments:

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...

Marc,

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

Fangyaya said...

oakley vault
oakley sunglasses wholesale
jordan shoes
adidas yeezy
ugg boots
adidas stan smith
jordan 3s
louis vuitton bags
ray ban sunglasses
cartier watches outlet
kobe shoes
ugg outlet online
gucci outlet online
adidas originals store
coach outlet clearance
ray ban sunglasses wholesale
adidas superstar shoes
ray ban outlet
michael kors uk
tiffany rings
ralph lauren outlet
tommy hilfiger outlet
cheap true religion
louis vuitton handbags
abercrombie and fitch outlet
clarisonic
kate spade outlet
cheap uggs
michael kors outlet
ralph lauren polo shirts
christian louboutin outlet
oakley outlet
true religion outlet
michael kors outlet
coach outlet online
montblanc pen
michael kors outlet
coach factory outlet online
coach outlet store online clearances
ugg italia
20168.22chenjinyan

Eric Yao said...

Coach Outlet http://www.coachstoreoutlet.us.org

Christian Louboutin Shoes http://www.christianlouboutinshoe.eu.com

Valentino Shoes http://www.valentinoshoes.eu.com

Michael Kors Outlet http://www.michaelkorsoutlet.us.org

Coach Factory Outlet http://www.coachfactoryoutlet.us.org

Coach Outlet Online http://www.coachoutletonlinestore.us.org

Coach Purses http://www.coachpurse.us.com

Kate Spade Outlet http://www.katespadefactoryoutlet.us.com

Toms Shoes http://www.toms-shoes.name

Hermes Belts http://www.hermesbelts.us.org

Louis Vuitton http://www.louisvuitton-outletstores.us.com

Fendi Handbags http://www.fendibelt.us.com

Giuseppe Shoes http://www.giuseppezanotti.eu.com

Michael Kors Outlet http://www.michael-kors-outlet.us.com

Stephen Curry Shoes http://www.stephencurryshoes.us.com

Salomon Shoes http://www.salomonshoes.eu.com

North Face Outlet http://www.northfacesoutlet.in.net

Coach Outlet http://www.outletcoach.in.net

North Face Outlet http://www.northfacesoutlet.us.com

Burberry Outlet http://www.burberryoutletstore.us.org

North Face Outlet http://www.north-faceoutlets.in.net

North Face Jackets http://www.outletnorthface.in.net

Skechers Shoes http://www.skechersshoes.us.com

Toms Outlethttp://www.tomsoutletstore.us.org

North Face Outlet http://www.northfacestoreoutlet.us.org

Nike Air Max http://www.nikeoutlet.us.org

Nike Hoodies http://www.nikehoodies.us.com

Marc Jacobs Handbags http://www.marcjacobshandbags.name

Marc Jacobs Outlet http://www.marcjacobsoutletstore.us.com

Jimmy Choo Shoes http://www.jimmy-chooshoes.us.com

Jimmy Choos http://www.jimmychoos.in.net

Burberry Belt http://www.burberrybelt.us.com

Louis Vuitton Belt http://www.louisvuitton-belt.com

Salvatore Ferragamo http://www.ferragamo-belt.us.com

Marc Jacobs Handbags http://marcjacobshandbagsoutlet.blog.com

Lululemon Outlet http://www.lululemonsoutlet.us.com

True Religion Outlet http://www.truereligion-outlets.in.net

Tommy Hilfiger http://www.tommyhilfigeroutlet.us.com

Michael Kors Outlet http://www.outletmichaelkors.eu.com

Coach Outlet http://www.outletcoach.eu.com

Red Bottoms http://www.redbottom.in.net

Kevin Durant Shoes http://www.kevindurantshoes.us.org

New Balance Outlet http://www.newbalanceoutlet.in.net

Adidas Outlet http://www.adidasoutlet.in.net

Coach Outlet Online http://www.coachoutlet-online.eu.com

Stephen Curry Jersey http://www.stephencurryjersey.us.com

Vans Outlet http://www.vansoutlet.us.com

Ralph Lauren Outlet http://www.ralphlauren-outletstore.in.net

True Religion Outlet http://www.true-religionoutlets.in.net

ED Hardy Outlet http://www.edhardyoutlet.in.net

North Face Outlet http://www.northfaceoutlet.eu.com

UGG Outlet http://www.uggoutlet.us.org

UGG Outlet http://www.outletugg.in.net

North Face Outlet http://www.northface--outlet.us.com

Ugg Boots Sale http://www.uggbootssale.in.net

UGGS For Women http://www.uggsforwomen.us.com

Skechers Go Walk http://www.skechersgowalk.us.com

Adidas Yeezy Boost http://www.adidasyeezyboost.in.net

Adidas Yeezy http://www.adidasyeezy.us.com

Adidas NMD http://www.adidas-nmd.us.org

Coach Outlet http://www.coachoutletinc.us.com