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?

Distant

You can see the performance benchmark and using simplicity here exceptionnotfound.net/… – Sunken

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)

Overtrick

Another difference, Dapper makes using parameterized queries so easy that people forget to introduce SQL injection bugs by concatenating statements and raw values. – Grishilde

@PanagiotisKanavos well, I was including that in the "correctly" in the very first bullet :) – Overtrick

Doesn't type check either. – Ruvolo

@MarcGravell so you mean is that dapper will never be faster than ado.net in normal circumstances ? – Faustena

@EhsanSajjad it can't be faster than the raw API that it sits on top of; it can, however, be faster than the typical ADO.NET consuming code - most code that consumes ADO.NET tends to be badly written, inefficient etc; and don't even get me started on DataTable :) – Overtrick

@EhsanSajjad side note: there's rumors (on twitter, so public) that the might be some major ADO.NET pipeline work coming up in the foreseeable future - we will ensure that Dapper can talk whatever new API comes out (even if it means a "dapper v2" to talk to "ado.net v2" - naming notwithstanding) – Overtrick

Is it fair to say testing is problematic when using dapper? I think testing is a reason for NOT using dapper. – Fitly

@stt106 define "testing". If you mean unit testing: then frankly that is unrelated - no matter what data access mechanism you use, you would have to abstract it for pure unit testing; if you mean integration testing, then Dapper is perfectly happy with that. So: no additional barrier is added in any way related to testing. If you mean something more specific, please let me know what you had in mind. – Overtrick

Dapper is best. I use it whether we need stored procedures or anything else. – Appellate

Besides, Dapper requires just 1 modest source file, not a real installation of a set of dll's for ADO – Lacker

@Lacker Dapper hasn't been available as a source package in many years - it is a nuget package/dll; if you want to use it another way, that's fine, but: you're on your own :) – Overtrick