How does Dapper compare to ADO.NET?

Question

When should Dapper be used instead of ADO.NET?

I would like to understand the pros and cons of Dapper over ADO.NET. What are the advantages of Dapper that would motivate its use?

Answer 1

Dapper is just a tool. What it does is:

• make it trivially easy to correctly parameterize queries
• make it trivially easy to execute queries (scalar, multi-rows, multi-grids, and no-results)
• make it trivially easy to turn results into objects
• very efficiently and quickly

What it doesn't do is:

• generate a class model for you
• generate queries for you
• track objects and their changes so you can just call SubmitChanges() (or whatever)

The raw dapper library doesn't provide CRUD features, but the "contrib" additional package does provide basic CRUD.

Basically, it isn't a full-weight ORM, but if you just want to run queries without having to fight an ORM, or pay the overheads associated with an ORM, it is pretty great. If you don't know SQL, the raw library probably isn't for you ("contrib" should be fine, though), but lots of people not only know SQL, but they want to be in control of the SQL (rather than letting the ORM come up with some interpretation of your intent that has not been optimized, etc).

To summarize, reasons might be:

• you want excellent raw execution performance with minimal overheads
• you want to retain control over your SQL
• you don't need or want the object-tracking features of a full-fat ORM

As for "vs ADO.NET":

• raw ADO.NET involves a lot more code to write and a lot of edge-cases to remember about (that dapper deals with internally without you needing to worry about them)
• but it isn't actually faster - dapper does a lot of meta-programming to store and re-use strategies once it has done what it needs for your query
• if you are using provider-specific features that aren't available in raw ADO.NET (for example, passing/fetching SqlGeometry data), those are not directly availalbe in dapper - you'd need to implement an interface to tell it how to handle your scenario, but that isn't hard (note that the specific SqlGeometry example is handled by an additional dapper library)