C# Unit Of Work Pattern with Dapper

C# Unit Of Work Pattern with Dapper

UPDATE: The code here is based on an existing example on GitHub by Tim Schreiber, you can find the original code here.

At work I’ve started looking into using Dapper instead of Entity Framework. In our case, this is because of performance – we’re on EF 6, not Core (and Dapper is faster still over EF Core even).

I’ve found it really easy to get started with, given there’s very little that Dapper makes you do that you weren’t already if you were using SqlConnection. If you’re used to Entity Framework, you’ll find potentially more work is required to set everything up.

We wanted to get this working with the Unit of Work as our process involved updating half a dozen tables, and if something went wrong we wanted to roll back the changes and report the error.
As always, the code for this post can be found here.
Implementing this pattern will comprise of two parts – the unit of work implementation, and our repositories.

The first thing we’ll do is set up our repository classes, I’ll add two, but as they’re extremely common I’ll only cover one here. If you really want to see both, check out the source code.
I’ve also created entity classes for each of these, but as these are just POCOs there isn’t much point in covering them.
I’ve added a folder called ‘Repositories’ to keep these in, and in there I’ve added two new classes – EmployeeRepository and CustomerRepository.
Each of these has three methods – Add, Update and Remove.
We’ll pass in an IDbTransaction in the constructor of each repository, and from this we can get the connection which we’ll use to execute our SQL queries.
Add and Update take in an entity class representing a record in the table, and Remove simply takes in an int for the id column. Only Add will return anything, the newly set Id of the entity.
All in, this is a simple class:

Since this is more of an example around the Unit of Work pattern, the SQL we’re using is really simple. The main thing to take note of here is the IDbTransaction being passed as a parameter to the Dapper Execute calls.
This does exactly what you’d expect, linking all changes to that transaction, so that if something goes wrong, we can rollback everything in that transaction rather than having to figure anything out for ourselves.
So let’s go ahead and make use of this by implementing the Unit of Work – I’ve done this in a class I’ve called DapperUnitOfWork.
This class will hold our repository – instantiating with the transaction if null, and will include a commit method with attempt to save our changes, and roll back if something goes wrong. I’ve also implemented IDisposable, and in the Dispose method I’m clearing off the IDbTransaction and IDbConnection.
Also worth noting the private ResetRepositories method – this is needed as we instantiate our repository classes with an IDbTransaction. When we’ve committed changes, we’ll be on a new transaction, so we’ll want to re-instantiate our repositories to reflect this.
Your class should look something like this:

No surprises there, it’s easy to see what it’s doing, and it’s easy to use – simply create a new instance of the DapperUnitOfWork class in a using statement, and call the methods on your repository:

Obviously this code could be improved in numerous ways – better error handling, abstraction etc.
But hopefully it’s clear enough to follow!
As always, get in touch if you think there’s a way to improve things 🙂
Or feel free to submit an issue/PR to the repo on Github here.

  • Alex

    First of all, you missed commit(). Then, not sure who copied, I will leave it here. https://github.com/timschreiber/DapperUnitOfWork about the code. What if I dont wanna use transactions? Lets say for select queries. Why do I need initialize new transaction? Even if I will not run it.

    • Ian Rufus

      Thanks for pointing that out!
      I imagine it’s my code that’s newer – I’ll update the article accordingly.

      The Unit of Work pattern, as I understand it, is inherently transaction based. You want everything to be in one transaction, so that the unit of work can be entirely rolled back if something goes wrong. Select queries could be moved out into some other class – one way I’ve seen this done is by having separate ‘read’ repositories, with ‘write’ being handle by the transaction based unit of work.

      Sorry for the late reply – I haven’t been on to maintain my blog in a while, had a lot going on.
      I’m also about to move my comments to Disqus, and I’m not sure what happens to existing comments when I do so. Hopefully you see this beforehand, or get some notification. I wanted to make sure you didn’t think I was ignoring it/trying to hide from this 🙂
      Thanks again!
      Ian