Getting startes Repository pattern with Dapper (WinForms)

Question

I want to start using Dapper and also try to use Repository pattern. But I don't understand how to do. I tried this, but this is with EF Repository Pattern C#

So can anybody tell me a simple example for a gerneric repository pattern and the usage of Dapper?

---

What I tried/found:

IGenericRepository.cs

public interface IGenericRepository<T>
{
    Task<IEnumerable<T>> GetAllAsync();
    Task DeleteRowAsync(Guid id);
    Task<T> GetAsync(Guid id);
    Task<int> SaveRangeAsync(IEnumerable<T> list);
    Task UpdateAsync(T t);
    Task InsertAsync(T t);
}

GenericRepository.cs

 public abstract class GenericRepository<T> : IGenericRepository<T> where T : class
{
    private readonly string _tableName;

    protected GenericRepository(string tablename)
    {
        _tableName = tablename;
    }
    private MySqlConnection SqlConnection()
    {
        return new MySqlConnection("xxx");
    }
    private IDbConnection CreateConnection()
    {
        var conn = SqlConnection();
        conn.Open();
        return conn;
    }

    private IEnumerable<PropertyInfo> GetProperties => typeof(T).GetProperties();
    public async Task DeleteRowAsync(Guid id)
    {
        using (var connection = CreateConnection())
        {
            await connection.ExecuteAsync($"DELETE FROM {_tableName} WHERE Id=@Id", new { Id = id });
        }
    }

    public async Task<IEnumerable<T>> GetAllAsync()
    {
        using (var connection = CreateConnection())
        {
            return await connection.QueryAsync<T>($"SELECT * FROM {_tableName}");
        }
    }

    public async Task InsertAsync(T t)
    {
        var insertQuery = GenerateInsertQuery();
        using (var connection = CreateConnection())
        {
            await connection.ExecuteAsync(insertQuery, t);
        }
    }

    public async Task<int> SaveRangeAsync(IEnumerable<T> list)
    {
        var inserted = 0;
        var query = GenerateInsertQuery();
        using (var connection = CreateConnection())
        {
            inserted += await connection.ExecuteAsync(query, list);
        }
        return inserted;
    }
    private string GenerateInsertQuery()
    {
        var insertQuery = new StringBuilder($"INSERT INTO {_tableName} ");
        insertQuery.Append("(");
        var properties = GenerateListOfProperties(GetProperties);
        properties.ForEach(prop => { insertQuery.Append($"[{prop}],"); });

        insertQuery
            .Remove(insertQuery.Length - 1, 1)
            .Append(") VALUES (");

        properties.ForEach(prop => { insertQuery.Append($"@{prop},"); });

        insertQuery
            .Remove(insertQuery.Length - 1, 1)
            .Append(")");

        return insertQuery.ToString();
    }
    private string GenerateUpdateQuery()
    {
        var updateQuery = new StringBuilder($"UPDATE {_tableName} SET ");
        var properties = GenerateListOfProperties(GetProperties);

        properties.ForEach(property =>
        {
            if (!property.Equals("Id"))
            {
                updateQuery.Append($"{property}=@{property},");
            }
        });
        updateQuery.Remove(updateQuery.Length - 1, 1);
        updateQuery.Append(" WHERE Id=@Id");

        return updateQuery.ToString();
    }
    private static List<string> GenerateListOfProperties(IEnumerable<PropertyInfo> listOfProperties)
    {
        return (from prop in listOfProperties
                let attributes = prop.GetCustomAttributes(typeof(DescriptionAttribute), false)
                where attributes.Length <= 0 || (attributes[0] as DescriptionAttribute)?.Description != "ignore"
                select prop.Name).ToList();
    }
    public async Task UpdateAsync(T t)
    {
        var updateQuery = GenerateUpdateQuery();
        using (var connection = CreateConnection())
        {
            await connection.ExecuteAsync(updateQuery, t);
        }
    }

    public async Task<T> GetAsync(Guid id)
    {
        using (var connection = CreateConnection())
        {
            var result = await connection.QuerySingleOrDefaultAsync<T>($"SELECT * FROM {_tableName} WHERE Id=@Id", new { Id = id });
            if (result == null)
                throw new KeyNotFoundException($"{_tableName} com o id {id} não foi encontrado");
            return result;
        }
    }
}

Person.cs

public class Person
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

PersonRepository.cs

public class PersonRepository : GenericRepository<Person>
{
    public PersonRepository(string tablename) : base(tablename) { }
}

Now my problem: What should I do to fill a dataGridView with all persons? Is there a need to change something in the PersonRepository.cs file? What? Ans how does the code in my form look like? e.g. dataGridView1.DataSource = ... and also filter this?

And for example, if I add another Class Car, what points I have to do? Add another CarRepository.cs and Car.cs? Anything else?

Kind regards!

Answer 1

First of all you need to understand the differences between EF and Dapper. Basically Dapper is a micro-ORM that has minimal features compared with EF. Entity Framework has a lot of features included along with performance improvements and others (more details here).

As the dapper is a Micro-ORM you don't have the same features available in EF. You can use dapper.contrib that has some abstractions that helps you with generic repository.

I have an example in my github an application that use dapper in repository (without dapper.contrib) with clean arch.