C# Unit Of Work Pattern with Dapper

PUBLISHED ON APR 28, 2017 — .NET, DATABASES, DESIGN PATTERNS

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:

public class EmployeeRepository 
{ 
    private IDbTransaction _transaction; 
    private IDbConnection _connection { get { return _transaction.Connection; } } 
  
    public EmployeeRepository(IDbTransaction transaction) 
    { 
        _transaction = transaction; 
    } 
  
    public int Add(Employee entity) 
    { 
        return _connection.ExecuteScalar<int>(@"INSERT INTO Employees(FirstName, LastName, JobTitle)  
                                                VALUES(@FirstName, @LastName, @JobTitle); 
                                                SELECT SCOPE_IDENTITY()", 
                                                new { 
                                                    FirstName = entity.FirstName, 
                                                    LastName = entity.LastName, 
                                                    JobTitle = entity.JobTitle 
                                                }, _transaction); 
    } 
  
    public void Update(Employee entity) 
    { 
        _connection.Execute(@"UPDATE Employees  
                                SET FirstName = @FirstName,  
                                    LastName = @LastName,  
                                    JobTitle = @JobTitle  
                                WHERE EmployeeId = @EmployeeId", 
                                new 
                                { 
                                    FirstName = entity.FirstName, 
                                    LastName = entity.LastName, 
                                    JobTitle = entity.JobTitle, 
                                    EmployeeId = entity.EmployeeId 
                                }, _transaction); 
    } 
  
    public void Remove(int employeeId) 
    { 
        _connection.Execute(@"DELETE FROM Employees WHERE EmployeeId = @EmployeeId", 
                            new 
                            { 
                                EmployeeId = employeeId 
                            }, _transaction); 
    } 
}

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:

public class DapperUnitOfWork : IDisposable 
{ 
    private IDbConnection _connection; 
    private IDbTransaction _transaction; 
  
    private EmployeeRepository _employeeRepository; 
    private CustomerRepository _customerRepository; 
  
    public DapperUnitOfWork(string connectionString) 
    { 
        _connection = new SqlConnection(connectionString); 
        _connection.Open(); 
        _transaction = _connection.BeginTransaction(); 
    } 
  
    public EmployeeRepository EmployeeRepository 
    { 
        get 
        { 
            return _employeeRepository ?? (_employeeRepository = new EmployeeRepository(_transaction)); 
        } 
    } 
  
    public CustomerRepository CustomerRepository 
    { 
        get 
        { 
            return _customerRepository ?? (_customerRepository = new CustomerRepository(_transaction)); 
        } 
    } 
  
    public void Commit() 
    { 
        try 
        { 
            _transaction.Commit(); 
        } 
        catch 
        { 
            _transaction.Rollback(); 
            throw; 
        } 
        finally 
        { 
            _transaction.Dispose(); 
            _transaction = _connection.BeginTransaction(); 
            ResetRepositories(); 
        } 
    } 
  
    public void Dispose() 
    { 
        if (_transaction != null) 
        { 
            _transaction.Dispose(); 
            _transaction = null; 
        } 
  
        if (_connection != null) 
        { 
            _connection.Dispose(); 
            _connection = null; 
        } 
    } 
  
    private void ResetRepositories() 
    { 
        _employeeRepository = null; 
    } 
}

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:

using (var unitOfWork = new DapperUnitOfWork(_connectionString)) 
{ 
    var newEmployee = new Employee 
    { 
        FirstName = "Ian", 
        LastName = "Rufus", 
        JobTitle = "Developer" 
    }; 
  
    newEmployee.EmployeeId = unitOfWork.EmployeeRepository.Add(newEmployee); 
  
    newEmployee.JobTitle = "Fired!"; 
  
    unitOfWork.EmployeeRepository.Update(newEmployee);

    unitOfWork.Commit();
}

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.

comments powered by Disqus