Using Dapper for Data Access and Repository Pattern

Introduction
#

On the Contact Management Application I reached for Dapper instead of Entity Framework Core, and the reason was boring: I wanted to see the SQL. EF Core is fine until a generated query does something surprising under load, and then you are reverse-engineering LINQ translations at 2 a.m.

Dapper is a micro-ORM that maps query results to typed models and otherwise stays out of the way. This post covers how it sits behind a generic Repository Pattern and cooperates with the Unit of Work when a write has to span multiple tables.

What you’ll build
#

A DapperHelper that owns connections, command execution, and optional transaction enlistment.
A generic GenericRepository<T> that generates INSERT and UPDATE SQL from an entity’s properties.
A concrete ContactPersonRepository built on top of that generic base.
The wiring that lets those repositories join a Unit of Work transaction.

Part of a series building the Contact Management Application, a sample project that walks through Clean Architecture in .NET end to end. This post covers the Dapper data-access layer.

1. What is Dapper?
#

Dapper is a lightweight ORM for .NET, providing high-performance data access by allowing developers to execute raw SQL queries while mapping query results to strongly typed models. Unlike full-fledged ORMs such as Entity Framework, Dapper offers direct SQL execution, making it an ideal choice for performance-sensitive applications.

Key features of Dapper include:

High Performance: Direct SQL execution with minimal overhead.
Flexibility: Full control over SQL queries.
Compatibility: Works with any relational database that has an ADO.NET provider.
Lightweight: No complex configuration; just a NuGet package.
Integration with Existing Patterns: Fits well into architectural patterns like Repository and Unit of Work.

Dapper stands out for its simplicity and efficiency. If your application requires frequent database interactions with complex SQL or operates under strict performance constraints, Dapper is an ideal choice. It allows developers to work with SQL directly, eliminating the abstraction layers introduced by heavier ORMs, which can be both a performance and a learning advantage

2. Setting Up Dapper
#

Getting started with Dapper is simple and requires minimal setup. This section provides a step-by-step guide, including a sample connection string, basic configuration, and code for executing queries.

2.1 Installing and Configuring Dapper
#

Install the Dapper NuGet package:

Install-Package Dapper

Configure your database connection in appsettings.json:

{
    "ConnectionStrings": {
        "DefaultConnection": "Server=localhost;Database=ContactManagementDb;User Id=sa;Password=your_password;"
    }
}

Register the helper and repository services in the DI container (Program.cs), if you use a helper class or a generic repository:

builder.Services.AddScoped<IDapperHelper, DapperHelper>();
builder.Services.AddScoped(typeof(IGenericRepository<>), typeof(GenericRepository<>));

2.2 DapperHelper: Managing Database Interactions
#

The DapperHelper simplifies database operations like querying and command execution. (Simplified Version)

using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data;

public class DapperHelper : IDapperHelper
{
    private readonly string _connectionString;

    public DapperHelper(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    public IDbConnection GetConnection()
    {
        return new SqlConnection(_connectionString);
    }

    public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object? parameters = null)
    {
        using var connection = GetConnection();
        return await connection.QueryAsync<T>(sql, parameters);
    }

    public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object? parameters = null)
    {
        using var connection = GetConnection();
        return await connection.QueryFirstOrDefaultAsync<T>(sql, parameters);
    }

    public async Task<int> ExecuteAsync(string sql, object? parameters = null)
    {
        using var connection = GetConnection();
        return await connection.ExecuteAsync(sql, parameters);
    }
}

GetConnection: Creates and manages a SQL connection.
QueryAsync<T>: Executes a query and returns a list of mapped results.
QueryFirstOrDefaultAsync<T>: Returns a single result or null if no match
ExecuteAsync: Executes a command (e.g., INSERT, UPDATE, DELETE).

But as we have also added the transacation support we have updated above helper class as below :

DapperHelper.cs

using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using System.Data;

public class DapperHelper : IDapperHelper
{
    private readonly AppSettings _config;
    private readonly ILogger _logger;

    public DapperHelper(IOptions<AppSettings> configValue, ILogger<DapperHelper> logger)
    {
        _config = configValue.Value;
        _logger = logger;
    }

    public IDbConnection GetConnection()
    {
        return new SqlConnection(_config.ConnectionStrings.DefaultConnection);
    }

    public async Task<int> Execute(string sp, object parms, CommandType commandType = CommandType.Text, IDbTransaction? transaction = null)
    { 
        var db = transaction?.Connection ?? GetConnection();
        try
        {
            if (db.State == ConnectionState.Closed)
                db.Open();

            var result = await db.ExecuteAsync(sp, parms, commandType: commandType, transaction: transaction);
            if (transaction == null)
                db.Close();
            return result;
        }
        catch (Exception ex)
        {
            if (transaction == null && db?.State == ConnectionState.Open)
                db.Close();
            _logger.LogError("SQL DB error: {Error}", ex.Message);
            throw;
        }
    }

    public async Task<T> Get<T>(string sp, Object parms, CommandType commandType = CommandType.Text, IDbTransaction? transaction = null)
    {
        var db = transaction?.Connection ?? GetConnection();
        try
        {
            if (db.State == ConnectionState.Closed)
                db.Open();

            var result = await db.QueryFirstOrDefaultAsync<T>(sp, parms, commandType: commandType, transaction: transaction);
            if (transaction == null)
                db.Close();
            return result;
        }
        catch (Exception ex)
        {
            if (transaction == null && db?.State == ConnectionState.Open)
                db.Close();
            _logger.LogError("SQL DB error: {Error}", ex.Message);
            throw;
        }
    }

    public async Task<IEnumerable<T>> GetAll<T>(string sp, Object parms, CommandType commandType = CommandType.Text)
    {
        try
        {
            using (IDbConnection db = GetConnection())
            {
                return await db.QueryAsync<T>(sp, parms, commandType: commandType);
            }
        }
        catch (Exception ex)
        {
            _logger.LogError("SQL DB error: {Error}", ex.Message);
            throw;
        }
    }

    public async Task<T> Insert<T>(string sp, Object parms, CommandType commandType = CommandType.Text, IDbTransaction? transaction = null)
    {
        var db = transaction?.Connection ?? GetConnection();
        try
        {
            if (db.State == ConnectionState.Closed)
                db.Open();

            var result = await db.QueryFirstOrDefaultAsync<T>(sp, parms, commandType: commandType, transaction: transaction);
            if (transaction == null)
                db.Close();
            return result;
        }
        catch (Exception ex)
        {
            if (transaction == null && db?.State == ConnectionState.Open)
                db.Close();
            _logger.LogError("SQL DB error: {Error}", ex.Message);
            throw;
        }
    }

    public async Task<T> Update<T>(string sp, object parms, CommandType commandType = CommandType.Text, IDbTransaction? transaction = null)
    {
        var db = transaction?.Connection ?? GetConnection();
        try
        {
            if (db.State == ConnectionState.Closed)
                db.Open();

            var result = await db.QueryFirstOrDefaultAsync<T>(sp, parms, commandType: commandType, transaction: transaction);
            if (transaction == null)
                db.Close();
            return result;
        }
        catch (Exception ex)
        {
            if (transaction == null && db?.State == ConnectionState.Open)
                db.Close();
            _logger.LogError("SQL DB error: {Error}", ex.Message);
            throw;
        }
    }
}

3. The Repository Pattern with Dapper
#

The Repository Pattern abstracts the data access logic behind a consistent interface for the rest of the application. It encapsulates how you reach the data store and owns the CRUD operations.

3.1 Defining the Repository Interface
#

We define a GenericRepository that provides basic CRUD operations for any entity in the application. Here is the interface definition for the repository:

public interface IGenericRepository<T> where T : BaseEntity
{
    Task<IEnumerable<T>> FindAll();
    Task<IEnumerable<T>> FindAll(Guid societyId);
    Task<T> FindByID(Guid id);
    Task<IEnumerable<T>> Find(string query, object? parameters = null);
    Task<T> Add(T entity, IDbTransaction? transaction = null);
    Task<T> Update(T entity, IDbTransaction? transaction = null);
    Task<bool> Delete(Guid id);
}

The IGenericRepository interface defines core methods for:

FindAll(): Fetches all records for an entity.
FindByID(): Fetches a specific entity by its ID.
Find(): Allows custom queries with optional parameters.
Add(): Adds a new entity to the database.
Update(): Updates an existing entity.
Delete(): Deletes an entity by its ID.

3.2 Implementing the Generic Repository
#

The GenericRepository class is responsible for executing SQL queries using Dapper and interacting with the database. It also manages SQL query generation for inserts and updates.

GenericRepository.cs

using Contact.Domain.Interfaces;
using Contact.Infrastructure.Persistence.Helper;
using System.Data;
using Dapper;

public class GenericRepository<T> : IGenericRepository<T> where T : BaseEntity
{
    protected readonly IDapperHelper _dapperHelper;
    protected readonly string _tableName;

    public GenericRepository(IDapperHelper dapperHelper, string tableName)
    {
        _dapperHelper = dapperHelper;
        _tableName = tableName;
    }

    public async Task<IEnumerable<T>> FindAll()
    {
        var query = $"SELECT * FROM {_tableName}";
        return await _dapperHelper.GetAll<T>(query, null);
    }

    public async Task<T> FindByID(Guid id)
    {
        var query = $"SELECT * FROM {_tableName} WHERE Id = @Id";
        return await _dapperHelper.Get<T>(query, new { Id = id });
    }

    public async Task<T> Add(T entity, IDbTransaction? transaction = null)
    {
        var query = GenerateInsertQuery();
        return await _dapperHelper.Insert<T>(query, entity, CommandType.Text, transaction);
    }

    public async Task<T> Update(T entity, IDbTransaction? transaction = null)
    {
        var query = GenerateUpdateQuery();
        return await _dapperHelper.Update<T>(query, entity, CommandType.Text, transaction);
    }

    public async Task<bool> Delete(Guid id)
    {
        var query = $"DELETE FROM {_tableName} WHERE Id = @Id";
        var result = await _dapperHelper.Execute(query, new { Id = id });
        return result != 0;
    }

    private string GenerateInsertQuery()
    {
        var properties = typeof(T).GetProperties()
            .Where(p => p.Name != "Id" && p.Name != "UpdatedOn" && p.Name != "UpdatedBy")
            .Select(p => p.Name);

        var columns = string.Join(", ", properties);
        var values = string.Join(", ", properties.Select(p => $"@{p}"));

        return $@"
                    INSERT INTO {_tableName} ({columns})
                    OUTPUT INSERTED.*
                    VALUES ({values})
                ";
    }

    private string GenerateUpdateQuery()
    {
        var properties = typeof(T).GetProperties()
            .Where(p => p.Name != "Id" && p.Name != "CreatedOn" && p.Name != "CreatedBy" && p.Name != "SocietyId")
            .Select(p => $"{p.Name} = @{p.Name}");

        var setClause = string.Join(", ", properties);

        return $@"
                    UPDATE {_tableName}
                    SET {setClause}
                    OUTPUT INSERTED.*
                    WHERE Id = @Id
                    ";
    }
}

In this implementation:

FindAll() retrieves all entities from the table.
FindByID() fetches an entity by its ID using parameterized queries.
Add() and Update() use SQL queries generated dynamically based on the entity’s properties.
Delete() removes an entity from the database.

The DapperHelper is used for executing the SQL queries.

4. Example Usage
#

Here’s how to use the GenericRepository to interact with the Contacts table.

ContactPersonRepository.cs

using Contact.Domain.Entities;
using Contact.Domain.Interfaces;
using Contact.Infrastructure.Persistence.Helper;

namespace Contact.Infrastructure.Persistence.Repositories;

public class ContactPersonRepository : GenericRepository<ContactPerson>, IContactPersonRepository
{
    public ContactPersonRepository(IDapperHelper dapperHelper) : base(dapperHelper, "Contacts")
    {
    }
}

5. Where Dapper fits, and where it doesn’t
#

Dapper earns its place when the data access is SQL-heavy and you want to read the exact query that hits the database. The trade you make is losing EF Core’s change tracking, migrations, and LINQ translation in exchange for direct control and a smaller surface area. On this project that trade paid off; on a CRUD-heavy admin panel with simple entities, EF Core would have saved code.

	Dapper	EF Core
Query control	You write the SQL	Generated from LINQ
Change tracking	None (you manage it)	Built in
Migrations	Hand-written scripts	Tooling generated
Best fit	Read-heavy, tuned queries	Rapid CRUD, rich domain models

Watch out: FindAll() interpolates the table name straight into $"SELECT * FROM {_tableName}". That is safe here only because _tableName is set in code, never from a request. Let a caller-supplied value reach that string and you have SQL injection.

Key takeaways
#

Dapper maps SQL to typed models with almost no overhead, and you keep full control of the query.
A generic repository plus a DapperHelper removes the per-entity boilerplate while leaving the SQL visible.
Thread an IDbTransaction through the helper so repositories can enlist in a Unit of Work when a write spans tables.
Interpolated identifiers like table names are only safe when they come from code, not from callers.

Next, wire these repositories into the API surface. See best practices for creating and using DTOs. The full project is on GitHub: Contact Management Application.

Introduction#

What you’ll build#

1. What is Dapper?#

2. Setting Up Dapper#

2.1 Installing and Configuring Dapper#

2.2 DapperHelper: Managing Database Interactions#

3. The Repository Pattern with Dapper#

3.1 Defining the Repository Interface#

3.2 Implementing the Generic Repository#

4. Example Usage#

5. Where Dapper fits, and where it doesn’t#

Key takeaways#

Related