35

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?

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Divya
  • 365
  • 1
  • 4
  • 4
  • You can see the performance benchmark and using simplicity here https://exceptionnotfound.net/dapper-vs-entity-framework-vs-ado-net-performance-benchmarking/ – Majedur Jan 15 '19 at 06:18

1 Answers1

66

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)
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • 3
    Another difference, Dapper makes using parameterized queries so easy that people forget to introduce SQL injection bugs by concatenating statements and raw values. – Panagiotis Kanavos Jun 23 '16 at 08:30
  • 4
    @PanagiotisKanavos well, I was including that in the "correctly" in the very first bullet :) – Marc Gravell Jun 23 '16 at 09:08
  • Doesn't type check either. – Ian Warburton Oct 04 '17 at 21:59
  • @MarcGravell so you mean is that dapper will never be faster than ado.net in normal circumstances ? – Ehsan Sajjad Oct 31 '17 at 14:01
  • 6
    @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` :) – Marc Gravell Oct 31 '17 at 14:22
  • @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) – Marc Gravell Oct 31 '17 at 14:24
  • Is it fair to say testing is problematic when using dapper? I think testing is a reason for NOT using dapper. – dragonfly02 Jan 15 '18 at 16:15
  • 2
    @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. – Marc Gravell Jan 15 '18 at 16:28
  • 1
    Dapper is best. I use it whether we need stored procedures or anything else. – vibs2006 Mar 16 '18 at 06:50
  • Besides, Dapper requires just 1 modest source file, not a real installation of a set of dll's for ADO – Roland Jan 26 '23 at 22:46
  • @Roland 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 :) – Marc Gravell Jan 27 '23 at 15:24