Unit of work pattern with Dapper
Image from Pexels

Unit of work pattern with Dapper

Implementing unit of work pattern with Dapper in .NET 5

People often try to compare Dapper and Entity Framework. Although they both have same goal and that is reading and modifying the data against the database, they take completely different approach in doing so and therefore this comparison is not really valid.

Entity framework is more robust as the name says framework, while Dapper is simple object mapper which is down the line set of extension methods on top of the ADO classes which allow easier manipulation with data in the database. These two packages do not cancel each other out, but rather complement each other and there is no restriction in using both in the same project and even in the same repository class implementation.

Dapper is much lighter and has much simpler syntax for invoking stored procedures and database functions with its anonymous type parameter for invocation, unlike Entity Framework which is more robust and so suitable if you already have bunch of your logic in the database code, which is not the best practice but a lot of live projects still rely on stored procedures to retrieve the data. On the other hand Entity Framework is much more suitable if you are following code first approach and has out of the box unit of work with the DbContext class implementation.

If you are already solely using Dapper for you light project and introducing Entity Framework just so that you could have unit of work for few simple methods, does not justify adding more complexity to your project with Entity Framework with all it's configuration, than you can still achieve having unit of work with Dapper.

Utilizing DbTransaction for the unit of work

Although there is a small change that we'll ever switch to a different data source, we still want to keep our selves distanced from the data source where we keep our data. This means we'll rely on abstractions for accessing data.

For this reason, instead of using System.Data.SqlClient class for accessing data from Microsoft SQL Server, we'll rather rely on abstract classes from System.Data.Common. This way, we can make and easy switch to a different data provider anytime, which we'll actually show down.

First thing first, we need an interface for our generic unit of work implementation

using System.Data.Common;
using System.Threading.Tasks;

namespace Dapper.Data
{
    public class UnitOfWork : IUnitOfWork
    {
        private readonly DbConnection _connection;
        private DbTransaction _transaction;
        public UnitOfWork(DbConnection connection)
        {
            _connection = connection;
        }
        public DbConnection Connection => _connection;

        public DbTransaction Transaction => _transaction;

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

        public async Task BeginAsync()
        {
            _transaction = await _connection.BeginTransactionAsync();
        }

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

        public async Task CommitAsync()
        {
            await _transaction.CommitAsync();
        }

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

            _transaction = null;
        }

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

        public async Task RollbackAsync()
        {
            await _transaction.RollbackAsync();
        }
    }
}
    

We will not re-invent the state tracking like Entity Framework does because we want to keep our application simple as it is already. If you need complex and robust state tracking, you should actually consider introducing Entity Framework to your project.

As I mentioned earlier, Dapper is a different kind of library which allows you quick and easy access to alredy existing data. We'll relly on keeping our state inside the datasource by wrapping the data commend inside the transaction.

Once all the required commands for the specific operation are executed, we either commit the transaction or in case of any exception we rollback the transaction itself.

using System.Data.Common;
using System.Threading.Tasks;

namespace Dapper.Data
{
    public class UnitOfWork : IUnitOfWork
    {
        private readonly DbConnection _connection;
        private DbTransaction _transaction;
        public UnitOfWork(DbConnection connection)
        {
            _connection = connection;
        }
        public DbConnection Connection => _connection;

        public DbTransaction Transaction => _transaction;

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

        public async Task BeginAsync()
        {
            _transaction = await _connection.BeginTransactionAsync();
        }

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

        public async Task CommitAsync()
        {
            await _transaction.CommitAsync();
        }

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

            _transaction = null;
        }

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

        public async Task RollbackAsync()
        {
            await _transaction.RollbackAsync();
        }
    }
}
    

Using the concrete data access provider

Now when we have generic unit of work implementation, we should utilize the actual data provider for the type of our storage. Since System.Data.Common classes are common for each data provider in .NET, we'll first create a separate project for SQL Server dapper project, add necessary packages and setup the service for dependency injection container via extension methods

using Microsoft.Extensions.DependencyInjection;
using System.Data.Common;
using System.Data.SqlClient;

namespace Dapper.Data.SqlServer.Extensions
{
    public static class DependencyInjection
    {
        public static void AddDapperSqlServer(this IServiceCollection services, string connectionString)
        {
            services.AddScoped<DbConnection>(provider =>
            {
                return new SqlConnection(connectionString);
            });

            services.AddScoped<IUnitOfWork, UnitOfWork>();
        }
    }
}
    

Unit of work stays the same but we setup the different connection class. In this case for Microsoft SQL Server, we'll inject SqlConnection for DbConnection which is injected via constructor to our unit of work class.

This way we only need to reference the proper project with the data provider we are intending to use to store our data.

Switching to different data provider

Let assume we need to switch our data provider to PostreSQL or some cloud based database storage. Since we rely on abstraction it only a matter of injecting a different connection implementation.

using Microsoft.Extensions.DependencyInjection;
using Npgsql;
using System.Data.Common;

namespace Dapper.Data.PostgreSql.Extensions
{
    public static class DependencyInjection
    {
        public static void AddDapperSqlServer(this IServiceCollection services, string connectionString)
        {
            services.AddScoped<DbConnection>(provider =>
            {
                return new NpgsqlConnection(connectionString);
            });

            services.AddScoped<IUnitOfWork, UnitOfWork>();
        }
    }
}
    

This extension is sitting in a separate project, so we just need to swap the reference to to this new project and use it's extension method.

The whole solution is hosted in github at https://github.com/dejanstojanovic/dapper-unitofwork so you can grab it and start building on top of it. 

References

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.


About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN  Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

JavaScript

read more

SQL/T-SQL

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article