Implementing a Generic Dapper Repository in ASP.NET Core

My team is continuing to work on another large project, and this time we're using ASP.NET Core with Dapper as our main data-access tool. It's no secret that I'm a fan of Dapper, and once again something we ended up implementing in our projects might be useful to you, my dear readers.

In this post, I'm going to show how we implemented a "base" Dapper repository class with common functionality in our ASP.NET Core project, in the hopes that someone out there (which will most likely be future me) can make use of it.

Let's get started!

Background

We noticed, as we have continued building this new, large ASP.NET Core system, that many of our repository classes needed similar or even identical functionality. Most of said repos had methods such as GetByID(), and many shared other common methods like GetForUser() or Search(). Problem was, these classes got very large, because of all the boilerplate code necessary to get Dapper running and to log exceptions within each method.

My team and I decided over nachos that we needed a way to try and "shrink" these classes, with the goal of improving their readability, and being object-oriented programmers, agreed that a "base" class would be the best way to share functionality.

It occurred to my team and I that the actual functionality all of these repository classes shared was querying the database, and that if we could reduce that down to a set of defined methods we could implement them in the base class and share them to all repositories. We also wanted logging at that level, since it was possible that queries could be generated that, for example, should have gotten exactly one item from the database but actually got zero.

A "Regular" Repo

Here's an example of a C# repository class that does not include a base Dapper repo:

public interface ISampleRepository
{
    Sample GetByID(int id);
}

public class SampleRepository : ISampleRepository
{
    private ILogger _logger;
    private IDbConnection _conn;

    public DrawingRepository(ILogger logger, IDbConnection conn)
    {
        _logger = logger;
        _conn = conn;
    }

    public Sample GetByID(int id)
    {
        try
        {
             return await QuerySingle<Sample>("SELECT * FROM Sample WHERE ID = @id", new { id });
        }
        catch(Exception ex)
        {
            _logger.Log(ex);
        }
    }
}

All by itself, this isn't very complicated or difficult to read. Refactoring this to use a "base" Dapper repository would be a waste of time if this repository was the only such data access class in our system.

But imagine that you have, as we do, a great many of these repositories. Large systems that I've worked on have had twenty, thirty, or even more of these. All of them have at least a GetByID() method or similar functionality, and many of them have other methods in common as well. Further, in a "normal" app each of them have to log something every time querying the database goes wrong.

So how can we combine the common functionality (e.g. logging) in a way that allows us to clean up our code? By implementing a "base" Dapper repository class.

The Base Dapper Repository Class

To achieve our goals, we wrote the following "base" Dapper repository class in C#:

public class DapperBaseRepository
{
    //This class handles the logging.
    public readonly ILogger _logger;

    //This class represents a connection to the database.
    //Dapper will automatically open this connection whenever
    //it is used in a query.
    private readonly IDbConnection _conn;

    public BaseRepository(ILogger logger,
                            IDbConnection conn)
    {
        _logger = logger;
        _conn = conn;
    }

    public List<T> Query<T>(string query, object parameters = null)
    {
        try
        {
            return _conn.Query<T>(query, parameters).ToList();
        }
        catch (Exception ex)
        {
            _logger.Log(ex);
            return new List<T>();
        }
    }

    public T QuerySingle<T>(string query, object parameters = null)
    {
        try
        {
            return _conn.QuerySingle<T>(query, parameters);
        }
        catch (Exception ex)
        {
            _logger.LogException(ex);
            throw;
        }
    }

    public T QueryFirst<T>(string query, object parameters = null)
    {
        try
        {
            return _conn.QueryFirst<T>(query, parameters);
        }
        catch (Exception ex)
        {
            var args = CreateArgs(query, parameters, methodName);
            _logger.LogException(ex, args);
            return default(T);
        }
    }
}

This class allows us to call the methods Query(), QuerySingle(), and QueryFirst() to get information out of our database. You can add more methods to it: for example, you might want asynchronous calls, or to include the QueryFirstOrDefault() and QuerySingleOrDefault() methods.

Whatever methods you end up using, the point is that all the other repositories, once they inherit from this "base" one, get considerably cleaner and easier to read. Here's that SampleRepository C# class from earlier, refactored to use the new "base" Dapper repo:

public class SampleRepository : DapperBaseRepository, ISampleRepository
{
    private ILogger _logger;
    private IDbConnection _conn;

    public DrawingRepository(ILogger logger, IDbConnection conn)
                            : base(logger, conn) { }

    public async Task<Sample> GetByID(int id)
    {
        return await QuerySingleAsync<Sample>("SELECT * FROM Sample WHERE ID = @id", new { id });
    }
}

Again, this isn't terribly different from the first, non-refactored repository. If this were a new project that I was assigned to, and I encountered this repo inheriting from a base class, I'd happily refactor that base class out. But when you have twenty, thirty, or more repositories, each calling the same database and needing similar methods, the amount of code you save and the clarity of the code you write both improve dramatically.

Drawbacks

There are a couple of minor drawbacks to this architecture.

First, you are introducing dependencies. Each class which inherits from the base Dapper repo must pass the injected values (In our case, ILogger and IDbConnection) to the base repo's constructor. If the base repo's constructor ever changes, you are going to need to refactor all the child repos as well.

Second, at the moment there is no way to say what child repository submitted the query that caused a logged exception. You can solve this many ways (we ended up merely passing the name of the child class into the base class constructor).
All that said, though, this architecture has proven rather resilient and useful for me and my team. I hope it will for yours, as well.

Summary

By implementing a "base" Dapper repository class in our ASP.NET Core projects, we allow many data-access classes to use a common set of functionality, and we can log issues when they arise. With minor improvements, this architecture can become the backbone of your ASP.NET Core app's data access strategy!

Happy Coding!