How to implement Unit Of Work pattern with Dapper?

Solution 1:

This Git project is very helpful. I started from the same and did some changes as per my need.

public sealed class DalSession : IDisposable
{
    public DalSession()
    {
        _connection = new OleDbConnection(DalCommon.ConnectionString);
        _connection.Open();
        _unitOfWork = new UnitOfWork(_connection);
    }

    IDbConnection _connection = null;
    UnitOfWork _unitOfWork = null;

    public UnitOfWork UnitOfWork
    {
        get { return _unitOfWork; }
    }

    public void Dispose()
    {
        _unitOfWork.Dispose();
        _connection.Dispose();
    }
}

public sealed class UnitOfWork : IUnitOfWork
{
    internal UnitOfWork(IDbConnection connection)
    {
        _id = Guid.NewGuid();
        _connection = connection;
    }

    IDbConnection _connection = null;
    IDbTransaction _transaction = null;
    Guid _id = Guid.Empty;

    IDbConnection IUnitOfWork.Connection
    {
        get { return _connection; }
    }
    IDbTransaction IUnitOfWork.Transaction
    {
        get { return _transaction; }
    }
    Guid IUnitOfWork.Id
    {
        get { return _id; }
    }

    public void Begin()
    {
        _transaction = _connection.BeginTransaction();
    }

    public void Commit()
    {
        _transaction.Commit();
        Dispose();
    }

    public void Rollback()
    {
        _transaction.Rollback();
        Dispose();
    }

    public void Dispose()
    {
        if(_transaction != null)
            _transaction.Dispose();
        _transaction = null;
    }
}

interface IUnitOfWork : IDisposable
{
    Guid Id { get; }
    IDbConnection Connection { get; }
    IDbTransaction Transaction { get; }
    void Begin();
    void Commit();
    void Rollback();
}

Now, your repositories should accept this UnitOfWork in some way. I choose Dependency Injection with Constructor.

public sealed class MyRepository
{
    public MyRepository(IUnitOfWork unitOfWork) 
    {
        this.unitOfWork = unitOfWork;
    }

    IUnitOfWork unitOfWork = null;

    //You also need to handle other parameters like 'sql', 'param' ect. This is out of scope of this answer.
    public MyPoco Get()
    {
        return unitOfWork.Connection.Query(sql, param, unitOfWork.Transaction, .......);
    }

    public void Insert(MyPoco poco)
    {
        return unitOfWork.Connection.Execute(sql, param, unitOfWork.Transaction, .........);
    }
}

And then you call it like this:

With transaction:

using(DalSession dalSession = new DalSession())
{
    UnitOfWork unitOfWork = dalSession.UnitOfWork;
    unitOfWork.Begin();
    try
    {
        //Your database code here
        MyRepository myRepository = new MyRepository(unitOfWork);
        myRepository.Insert(myPoco);
        //You may create other repositories in similar way in same scope of UoW.

        unitOfWork.Commit();
    }
    catch
    {
        unitOfWork.Rollback();
        throw;
    }
}

Without Transaction:

using(DalSession dalSession = new DalSession())
{
    //Your database code here
    MyRepository myRepository = new MyRepository(dalSession.UnitOfWork);//UoW have no effect here as Begin() is not called.
    myRepository.Insert(myPoco);
}

Please note that, UnitOfWork is more than DBTransaction.

More details about Repository in above code could be found here.

I have already post this code here. But this question looks more relevant to me for this code; so I am posting again instead of just link to original answer.

Solution 2:

Edit 2018-08-03: Amit's comment really got me thinking, and made me realize that the repository's don't in fact NEED to be properties on the context itself. But rather, repositories could have a dependency on the context. Rather than continue to make incremental changes to the code samples below. I will simply reference a git repo I've put together to contain this concept.

Standing on the shoulders of others here.

Considering this answer is top in most Google searches pertaining to "dapper" and "unit of work". I wanted to provide my approach, which I've used to great effect several times now.

Using a ficitious (and overly simplified) example:

public interface IUnitOfWorkFactory
{
    UnitOfWork Create();
}

public interface IDbContext 
{
    IProductRepository Product { get; set; }

    void Commit();
    void Rollback();
}

public interface IUnitOfWork
{
    IDbTransaction Transaction { get;set; }

    void Commit();
    void Rollback();
}


public interface IProductRepository 
{
    Product Read(int id);
}

Note how neither IDbContext or IUnitOfWorkFactory implements IDisposable. This is purposefully done to avoid a leaky abstraction. Instead the reliance is on Commit()/Rollback() to take care of cleanup and disposal.

A couple of points before sharing implementations.

• IUnitOfWorkFactory is responsible for instantiating the UnitOfWork and brokering the database connection.
• IDbContext is the repository backbone.
• IUnitOfWork is an encapsulation of IDbTransaction, and ensures that when working with multiple repositories, they share a single database context.

Implementation of IUnitOfWorkFactory

public class UnitOfWorkFactory<TConnection> : IUnitOfWorkFactory where TConnection : IDbConnection, new()
{
    private string connectionString;

    public UnitOfWorkFactory(string connectionString)
    {
        if (string.IsNullOrWhiteSpace(connectionString))
        {
            throw new ArgumentNullException("connectionString cannot be null");
        }

        this.connectionString = connectionString;
    }

    public UnitOfWork Create()
    {
        return new UnitOfWork(CreateOpenConnection());
    }

    private IDbConnection CreateOpenConnection()
    {
        var conn = new TConnection();
        conn.ConnectionString = connectionString;

        try
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
        }
        catch (Exception exception)
        {
            throw new Exception("An error occured while connecting to the database. See innerException for details.", exception);
        }

        return conn;
    }
}

Implementation of IDbContext

public class DbContext : IDbContext
{
    private IUnitOfWorkFactory unitOfWorkFactory;

    private UnitOfWork unitOfWork;

    private IProductRepository product;

    public DbContext(IUnitOfWorkFactory unitOfWorkFactory)
    {
        this.unitOfWorkFactory = unitOfWorkFactory;
    }

    public ProductRepository Product =>
        product ?? (product = new ProductRepository(UnitOfWork));

    protected UnitOfWork UnitOfWork =>
        unitOfWork ?? (unitOfWork = unitOfWorkFactory.Create());

    public void Commit()
    {
        try
        {
            UnitOfWork.Commit();
        }
        finally
        {
            Reset();
        }
    }

    public void Rollback()
    {
        try
        {
            UnitOfWork.Rollback();
        }
        finally
        {
            Reset();
        }
    }

    private void Reset()
    {
        unitOfWork = null;
        product = null;
    }
}

Implementation of IUnitOfWork

public class UnitOfWork : IUnitOfWork
{
    private IDbTransaction transaction;

    public UnitOfWork(IDbConnection connection)
    {
        transaction = connection.BeginTransaction();
    }

    public IDbTransaction Transaction =>
        transaction;

    public void Commit()
    {
        try
        {
            transaction.Commit();
            transaction.Connection?.Close();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
        finally
        {
            transaction?.Dispose();
            transaction.Connection?.Dispose();
            transaction = null;
        }
    }

    public void Rollback()
    {
        try
        {
            transaction.Rollback();
            transaction.Connection?.Close();
        }
        catch
        {
            throw;
        }
        finally
        {
            transaction?.Dispose();
            transaction.Connection?.Dispose();
            transaction = null;
        }
    }
}

Implementation of IProductRepository

public class ProductRepository : IProductRepository
{
    protected readonly IDbConnection connection;
    protected readonly IDbTransaction transaction;

    public ProductRepository(UnitOfWork unitOfWork)
    {
      connection = unitOfWork.Transaction.Connection;
      transaction = unitOfWork.Transaction;
    }

    public Product Read(int id)
    {
        return connection.QuerySingleOrDefault<Product>("select * from dbo.Product where Id = @id", new { id }, transaction: Transaction);
    }
}

To access the database, simply instantiate DbContext or inject using the IoC container of your choice (I personnally use the IoC container provided by .NET Core).

var unitOfWorkFactory = new UnitOfWorkFactory<SqlConnection>("your connection string");
var db = new DbContext(unitOfWorkFactory);

Product product = null;

try 
{
    product = db.Product.Read(1);
    db.Commit();
}
catch (SqlException ex)
{
    //log exception
    db.Rollback();
}

The explicit need for Commit() for this simple read-only operation seems excessive, but pays dividends as the system grows. And apparently, offers a minor performance benefit according to Sam Saffron. You "can" also omit the db.Commit() on simple read operations, by doing this though you leaving the connection hanging open and put the onus of cleaning things up onto the garbage collector. So this isn't recommended.

I typically bring the DbContext into the fold at the service-tier, where it works in unison with other services to form the "ServiceContext". I then reference this ServiceContext in the actual MVC layer.

As another point of mention, it's recommended to use async throughout the stack if you can. It is omitted here for simplicity.