Deep Dive.NETPart 8 of 12

Using Dapper for Data Access and Repository Pattern

High-performance data access with Dapper in Clean Architecture — integrating the Repository Pattern and Unit of Work for SQL and transactions.

Nitin Kumar Singh1 December 2024Updated 21 August 20265 min read

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.

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

References

Contact Management Application — github.com ↩ ↩²
DapperHelper.cs — github.com ↩
GenericRepository.cs — github.com ↩
ContactPersonRepository.cs — github.com ↩

Comments

Comments are GitHub discussions. Sign in with GitHub to post; reactions need no account.

Introduction

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

References

Share this

Comments