Dapper is a micro ORM (Object Relational Mapper) for .NET that is nearly as fast as using a raw ADO.NET data reader. It is a great alternative to Entity Framework especially when performance is a top priority and you don’t need all the features of a “heavy” ORM.

In this post, I will provide an example of creating a generic CRUD repository that leverages the performance of Dapper while providing flexibility for a multitude of scenarios.

Let’s Get Started

When I first started looking into using Dapper, I had no trouble finding articles praising its performance and ease of use. There are plenty of articles that have benchmarked the performance of Dapper vs other ORMs. There is also the helpful site for getting started with Dapper at the following link: https://dapper-tutorial.net/dapper

There are also examples online that attempt to show how to implement a generic CRUD repository with Dapper. However, I find that many online examples set up the repository in an inflexible manner.

The most common approach I see is something similar to the following.

public interface IRepository<TModel>
    {
        TModel Create(TModel model);
        TModel Get(int id);
        IEnumerable<TModel> Get();
        void Update(int id, TModel model);
        void Delete(int id);
    }

This approach provides some flexibility with a generic TModel type but assumes that all of your models and therefore tables are identified by an integer. It cannot handle a scenario where a model is identified by a string, GUID, or has multiple primary keys. Simply adding a second generic type makes the repository more flexible.

public interface IRepository<TModel, in TIdentifier>
    {
        TModel Create(TModel model);
        TModel Get(TIdentifier identifier);
        IEnumerable<TModel> Get();
        void Update(TIdentifier identifier, TModel model);
        void Delete(TIdentifier identifier);
    }

I can then begin to implement the repository. It’s here where the Dapper library makes its appearance. I start by injecting an interface called IDbConnectionProvider. This interface has one method which is to get an IDbConnection and is ultimately used by our Dapper library to execute SQL commands over the connection.

My initial implementation is as follows.

 public class Repository<TModel, TIdentifier> : IRepository<TModel, TIdentifier>
    {
        private readonly IDbConnectionProvider _connectionProvider;

        public Repository(IDbConnectionProvider connectionProvider)
        {
            _connectionProvider = connectionProvider;
        }
        public TModel Create(TModel model)
        {
            using (var connection = _connectionProvider.GetConnection())
            {
                return connection.QuerySingleOrDefault<TModel>(new CommandDefinition());
            }
        }

        public TModel Get(TIdentifier identifier)
        {
            using (var connection = _connectionProvider.GetConnection())
            {
                return connection.QuerySingleOrDefault<TModel>(new CommandDefinition());
            }
        }

        public IEnumerable<TModel> Get()
        {
            using (var connection = _connectionProvider.GetConnection())
            {
                return connection.Query<TModel>(new CommandDefinition());
            }
        }

        public void Update(TIdentifier identifier, TModel model)
        {
            using (var connection = _connectionProvider.GetConnection())
            {
                connection.Execute(new CommandDefinition());
            }
        }

        public void Delete(TIdentifier identifier)
        {
            using (var connection = _connectionProvider.GetConnection())
            {
                connection.Execute(new CommandDefinition());
            }
        }

As you can see in the prior example, Dapper simplifies the querying and execution of SQL commands with the IDbConnection extension methods QuerySingleOrDefault, Query, and Execute. Dapper also provides a class called CommandDefinition where a number of command properties are defined including the actual SQL or the name of a stored procedure to be executed.

As you’ve probably noticed, this implementation won’t actually work because the Execute and Query methods have a Dapper class called CommandDefinition newly created within the method.

In order to fix this issue, I created an interface to define the command definitions to be injected into the generic repository implementation.

The interface is defined in the following way.

public interface ICrudCommandDefinitions<TModel, in TIdentifier>
    {
        CommandDefinition Create(TModel model);
        CommandDefinition Get();
        CommandDefinition Get(TIdentifier identifier);
        CommandDefinition Update(TIdentifier identifier, TModel model);
        CommandDefinition Delete(TIdentifier identifier);
    }

As you can see, this interface requires us to define a CommandDefinition class for each of the CRUD methods in my generic repository.

After updating my repository to use the command definitions interface, it looks like the following.

   public class Repository<TModel, TIdentifier> : IRepository<TModel, TIdentifier>
    {
        private readonly IDbConnectionProvider _connectionProvider;
        private readonly ICrudCommandDefinitions<TModel, TIdentifier> _commandDefinitions;

        public Repository(IDbConnectionProvider connectionProvider, ICrudCommandDefinitions<TModel,TIdentifier> commandDefinitions)
        {
            _connectionProvider = connectionProvider;
            _commandDefinitions = commandDefinitions;
        }
        public TModel Create(TModel model)
        {
            using (var connection = _connectionProvider.GetConnection())
            {
                return connection.QuerySingleOrDefault<TModel>(_commandDefinitions.Create(model));
            }
        }

        public TModel Get(TIdentifier identifier)
        {
            using (var connection = _connectionProvider.GetConnection())
            {
                return connection.QuerySingleOrDefault<TModel>(_commandDefinitions.Get(identifier));
            }
        }

        public IEnumerable<TModel> Get()
        {
            using (var connection = _connectionProvider.GetConnection())
            {
                return connection.Query<TModel>(_commandDefinitions.Get());
            }
        }

        public void Update(TIdentifier identifier, TModel model)
        {
            using (var connection = _connectionProvider.GetConnection())
            {
                connection.Execute(_commandDefinitions.Update(identifier,model));
            }
        }

        public void Delete(TIdentifier identifier)
        {
            using (var connection = _connectionProvider.GetConnection())
            {
                connection.Execute(_commandDefinitions.Delete(identifier));
            }
        }
    }

Final Thoughts

In this post, we learned how to create a generic CRUD repository that leverages the performance of Dapper while providing flexibility for a variety of scenarios. I hope that you have found it helpful and will consider giving Dapper a try on your next project.