Building a Dapper CRUD API with generic repository from scratch

Introduction

Dapper is a micro-ORM: it maps query results to objects and parameterizes your SQL, and that is all. It is fast and predictable, but it hands you none of the CRUD conveniences EF Core gives you. Write it naively and you end up with INSERT INTO ..., UPDATE ... SET ..., SELECT ... WHERE Id = @id hand-typed for every table, and every schema change means editing strings in five places.

This video builds a generic Dapper repository from scratch on .NET 8. You decorate entities with [Table], [Column], and [Key] attributes, and the repository reads that metadata with reflection to generate the SQL for GetAll, GetById, Add, Update, and Delete - once, for every entity. The demo API manages Student records against SQL Server.

🎬 Watch the full video here:

Attribute-driven mapping

The entity carries the database mapping as attributes, because Dapper does not know your schema:

[Table("university_students")]
public class Student
{
    [Key]
    [Column("Id")]
    public int Id { get; set; }

    [Column("First_name")]
    public string FirstName { get; set; } = string.Empty;

    [Column("Last_name")]
    public string LastName { get; set; } = string.Empty;
    // ...
}

This solves the common Dapper friction where database column names (First_name) do not match C# conventions (FirstName). The SELECT is generated with aliases - First_name AS FirstName - so Dapper's default property mapping works without custom column mappers.

The metadata provider

A dedicated service (IEntityAttributeValuesProvider) does all the reflection so the repository does not have to:

• GetTableName<T>() reads [Table].
• GetColumnsAndModelPropertyNames<T>() builds a column-to-property dictionary from [Column], optionally skipping the [Key] column (you do not want to insert into an identity column).
• GetKeyColumnNamePropertyName<T>() finds the [Key] column for WHERE clauses.
• A formatter turns that dictionary into SQL fragments like col AS prop or col = @prop.

Keeping this in one class means the SQL-generation logic in the repository stays readable, and it is the only place that touches System.Reflection.

The generic repository

With metadata available, each CRUD method is a small string-build plus a Dapper call:

public async Task<IEnumerable<T>> GetAllAsync()
{
    await using var connection = _sqlConnectionProvider.GetSqlConnection();
    var columns = /* "First_name AS FirstName, Last_name AS LastName, ..." */;
    var sql = $"SELECT {columns} FROM {tableName}";
    return await connection.QueryAsync<T>(sql);
}

Add and Update pass the entity straight to connection.ExecuteAsync(sql, entity) - Dapper matches @FirstName in the SQL to the FirstName property automatically. GetById and Delete use DynamicParameters for the single id value. Every method returns bool (rows affected > 0) or the mapped result.

Wiring it up

builder.Services.AddSingleton<ISqlConnectionProvider, SqlConnectionProvider>();
builder.Services.AddSingleton<IEntityAttributeValuesProvider, EntityAttributeValuesProvider>();
builder.Services.AddScoped(typeof(IGenericRepository<>), typeof(GenericRepository<>));

The connection provider builds a fresh SqlConnection per call from a connection string bound via the Options pattern - Dapper works with short-lived connections, opened and disposed around each query. Endpoints are minimal API routes grouped in a module, each injecting IGenericRepository<Student>.

The honest trade-off

This pattern is a fun exercise and genuinely useful for simple, uniform CRUD, but be clear about what you are building: a tiny ORM. Reflection-generated SQL is opaque (harder to see exactly what runs), the reflection has a cost unless you cache the metadata per type, and the moment you need a join, a projection, or a filtered query, you are back to hand-written SQL anyway.

If you find yourself extending the generic repository with more and more special cases, that is the signal you wanted EF Core - or at least Dapper.Contrib / Dapper.SimpleCRUD, which do exactly this and are already tested. The value of building it yourself is understanding what those libraries do.

Common pitfalls

• No metadata caching. Reflecting over the type on every call is wasteful. Cache the table name, column map, and key per Type in a ConcurrentDictionary.
• Inserting the identity column. Always exclude the [Key] column from INSERT.
• SQL injection via table/column names. Values are parameterized, but table and column names come from attributes and are string-concatenated. That is fine when attributes are developer-controlled; never build them from user input.
• Long-lived connections. Open, query, dispose. Do not hold a SqlConnection across requests.
• Missing attributes. The provider throws if [Table] or a [Column] is absent - a deliberate fail-fast, but every property needs one.

Key Takeaways

• Dapper maps and parameterizes; it gives you no CRUD scaffolding, so naive Dapper means hand-written SQL per table.
• Decorate entities with [Table], [Column], [Key] and read that metadata with reflection to generate SQL once for all entities.
• Alias columns in the generated SELECT (db_col AS PropName) so snake_case columns map to PascalCase properties.
• Keep all reflection in one metadata provider; keep the repository to string-build plus Dapper call.
• Use fresh, short-lived SqlConnections and bind the connection string via the Options pattern.
• You are building a mini-ORM - cache the metadata, and switch to EF Core or an existing Dapper CRUD library once you need joins and projections.

Get the Full Source Code

The complete runnable solution - the attribute metadata provider, the generic Dapper repository, the connection provider, and the student CRUD endpoints - is available to Patreon supporters. If you want to run the full CRUD flow against SQL Server instead of rebuilding it from the walkthrough above, you can find it on Patreon.

Resources

• Dapper on GitHub
• Dapper.Contrib
• Options pattern in .NET