Generic Repository pattern for .net core with Dapper

We had a project which we had dapper generic repository but after project evolved we have ditched the generic repository to use the real power of dapper.

I would recommend to use Dapper as direct without generic CRUD operations.

To demonstrate what we had i will provide a sample code which is not production ready will give you an idea to implement your own generic repo.

public abstract class ConnectionBase : IDbConnection
{

    protected ConnectionBase(IDbConnection connection)
    {
        Connection = connection;
    }

    protected IDbConnection Connection { get; private set; }

    // Verbose but necessary implementation of IDbConnection:
    #region "IDbConnection implementation"

    public string ConnectionString
    {
        get
        {
            return Connection.ConnectionString;
        }

        set
        {
            Connection.ConnectionString = value;
        }
    }

    public int ConnectionTimeout
    {
        get
        {
            return Connection.ConnectionTimeout;
        }
    }

    public string Database
    {
        get
        {
            return Connection.Database;
        }
    }

    public ConnectionState State
    {
        get
        {
            return Connection.State;
        }
    }

    public IDbTransaction BeginTransaction()
    {
        return Connection.BeginTransaction();
    }



    public void Close()
    {
        Connection.Close();
    }

    public IDbCommand CreateCommand()
    {
        return Connection.CreateCommand();
    }

    public void Dispose()
    {
        Connection.Dispose();
    }

    public void Open()
    {
        Connection.Open();
    }

    #endregion
}

Generic Repository

public abstract class GenericRepository<T> : IRepository<T> where T : class //EntityBase, IAggregateRoot
    {

        private readonly string _tableName;


        internal IDbConnection Connection
        {
            get
            {
                return new SqlConnection(ConfigurationManager.ConnectionStrings["SmsQuizConnection"].ConnectionString);
            }
        }

        public GenericRepository(string tableName)
        {
            _tableName = tableName;
        }

        internal virtual dynamic Mapping(T item)
        {
            return item;
        }

        public virtual void Add(T item)
        {
            using (IDbConnection cn = Connection)
            {
                var parameters = (object)Mapping(item);
                cn.Open();
                item.ID = cn.Insert<Guid>(_tableName, parameters);
            }
        }

        public virtual void Update(T item)
        {
            using (IDbConnection cn = Connection)
            {
                var parameters = (object)Mapping(item);
                cn.Open();
                cn.Update(_tableName, parameters);
            }
        }

        public virtual void Remove(T item)
        {
            using (IDbConnection cn = Connection)
            {
                cn.Open();
                cn.Execute("DELETE FROM " + _tableName + " WHERE ID=@ID", new { ID = item.ID });
            }
        }

        public virtual T FindByID(Guid id)
        {
            T item = default(T);

            using (IDbConnection cn = Connection)
            {
                cn.Open();
                item = cn.Query<T>("SELECT * FROM " + _tableName + " WHERE ID=@ID", new { ID = id }).SingleOrDefault();
            }

            return item;
        }



        public virtual IEnumerable<T> FindAll()
        {
            IEnumerable<T> items = null;

            using (IDbConnection cn = Connection)
            {
                cn.Open();
                items = cn.Query<T>("SELECT * FROM " + _tableName);
            }

            return items;
        }



    }

The examples by @PathumLakshan request from comments. Provided examples are written in asynchronous manner, but of source can be implemented synchronous. Anyway it is just an illustration of how you can manage instrastructure with Dapper. Class Db provides some generic methods for getting data and executing SQL queries. For instance you can use overload Get<T>(string, object) for basic queries, or take Get<T>(Func<SqlConnection, SqlTransaction, int, Task<T>> to use let say QueryMultiple. Class Repository<Entity> shows, how can look basic repository for entity Entity.

Db class:

public class Db : IDb
{
    private readonly Func<SqlConnection> _dbConnectionFactory;

    public Db(Func<SqlConnection> dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory ?? throw new ArgumentNullException(nameof(dbConnectionFactory));
    }

    public async Task<T> CommandAsync<T>(Func<SqlConnection, SqlTransaction, int, Task<T>> command)
    {
        using (var connection = _dbConnectionFactory.Invoke())
        {
            await connection.OpenAsync();

            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    var result = await command(connection, transaction, Constants.CommandTimeout);

                    transaction.Commit();

                    return result;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    Logger.Instance.Error(ex);
                    throw;
                }
            }
        }
    }

    public async Task<T> GetAsync<T>(Func<SqlConnection, SqlTransaction, int, Task<T>> command)
    {
        return await CommandAsync(command);
    }

    public async Task<IList<T>> SelectAsync<T>(Func<SqlConnection, SqlTransaction, int, Task<IList<T>>> command)
    {
        return await CommandAsync(command);
    }

    public async Task ExecuteAsync(string sql, object parameters)
    {
        await CommandAsync(async (conn, trn, timeout) =>
        {
            await conn.ExecuteAsync(sql, parameters, trn, timeout);
                return 1;
        });

    public async Task<T> GetAsync<T>(string sql, object parameters)
    {
        return await CommandAsync(async (conn, trn, timeout) =>
        {
            T result = await conn.QuerySingleAsync<T>(sql, parameters, trn, timeout);
            return result;
        });
    }

    public async Task<IList<T>> SelectAsync<T>(string sql, object parameters)
    {
        return await CommandAsync<IList<T>>(async (conn, trn, timeout) =>
        {
            var result = (await conn.QueryAsync<T>(sql, parameters, trn, timeout)).ToList();
            return result;
        });
    }
}

Repository class:

public class Repository<Entity> : IRepository<Entity>
{
    protected readonly IDb _db;

    public Repository(IDb db)
    {
        _db = db ?? throw new
            ArgumentException(nameof(db));
    }

    public async Task Add(Entity entity)
    {
        await _db.ExecuteAsync("INSERT INTO ... VALUES...", entity);
    }

    public async Task Update(Entity entity)
    {
        await _db.ExecuteAsync("UPDATE ... SET ...", entity);
    }

    public async Task Remove(Entity entity)
    {
        await _db.ExecuteAsync("DELETE FROM ... WHERE ...", entity);
    }

    public async Task<Entity> FindByID(int id)
    {
        return await _db.GetAsync<Entity>("SELECT ... FROM ... WHERE Id = @id", new { id });
    }

    public async Task<IEnumerable<Entity>> FindAll()
    {
        return await _db.SelectAsync<Entity>("SELECT ... FROM ... ", new { });
    }
}

Db can be extended with other generic method, for example, ExecuteScalar, which you would need in your repositories. Hope it helps.