↓ Skip to main content
Nitin Kumar Singh
  1. Blog/

Using Dapper for Data Access and Repository Pattern

·9 mins
Nitin Kumar Singh
Author
Nitin Kumar Singh
I build enterprise AI solutions and cloud-native systems. I write about architecture patterns, AI agents, Azure, and modern development practices — with full source code.
Table of Contents

Introduction
#

In this article, we will explore how the Contact Management Application integrates Dapper for efficient data access. Dapper is a micro-ORM (Object-Relational Mapper) that excels in performance by directly interacting with SQL. We will focus on how Dapper is utilized within the Repository Pattern to ensure clean, maintainable, and efficient access to the database. Additionally, we will explore how Dapper works in tandem with the Unit of Work pattern to manage transactions.

Part of the Series
#

This article is part of a series on building and understanding the Contact Management Application, a sample project showcasing clean architecture principles. Below are the other articles in the series:

  1. Clean Architecture: Introduction to the Project Structure{:target="_blank" rel=“noopener noreferrer”} 

  2. Clean Architecture: Implementing AutoMapper for DTO Mapping and Audit Logging{:target="_blank" rel=“noopener noreferrer”} 

  3. Clean Architecture: Validating Inputs with FluentValidation{:target="_blank" rel=“noopener noreferrer”} 

  4. Clean Architecture: Dependency Injection Setup Across Layers{:target="_blank" rel=“noopener noreferrer”} 

  5. Clean Architecture: Handling Authorization and Role-Based Access Control (RBAC){:target="_blank" rel=“noopener noreferrer”} 

  6. Clean Architecture: Implementing Activity Logging with Custom Attributes{:target="_blank" rel=“noopener noreferrer”} 

  7. Clean Architecture: Unit of Work Pattern and Its Role in Managing Transactions{:target="_blank" rel=“noopener noreferrer”} 

  8. Clean Architecture: Using Dapper for Data Access and Repository Pattern (You are here)

  9. Clean Architecture: Best Practices for Creating and Using DTOs in the API{:target="_blank" rel=“noopener noreferrer”} 

  10. Clean Architecture: Error Handling and Exception Management in the API{:target="_blank" rel=“noopener noreferrer”} 

  11. Clean Architecture: Dockerizing the .NET Core API, Angular and MS SQL Server{:target="_blank" rel=“noopener noreferrer”} 

  12. Clean Architecture: Seeding Initial Data Using Docker Compose and SQL Scripts{:target="_blank" rel=“noopener noreferrer”} 

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 seamlessly with any relational database.

  • 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
#

1.Install the Dapper NuGet package:

Install-Package Dapper

2. Configure your database connection in appsettings.json

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

3. Register the helper and repository services in the DI container: Program.cs (If you are using any helper class or 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;
        }
    }
}

2.3 The Repository Pattern with Dapper

The Repository Pattern is used to abstract the data access logic, providing a consistent interface for the rest of the application. This pattern encapsulates the logic for accessing the data store and is responsible for managing CRUD operations.

2.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.

2.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.

3. Example Usage
#

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

ContactPersonRepository.cs{:target="_blank" rel=“noopener noreferrer”} 

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) : base(dapperHelper, "Contacts") 
    { 
    }
}

4. Benefits of Using Dapper with the Repository Pattern
#

4.1 Performance
#

Dapper provides fast query execution, making it suitable for high-performance applications. It maps SQL query results to C# objects with minimal overhead.

4.2 Flexibility
#

Using Dapper gives full control over the SQL queries being executed. You can easily optimize queries for specific use cases and scenarios without the limitations of traditional ORMs.

4.3 Clean Code
#

By using the Repository Pattern with Dapper, the data access logic is abstracted from the business logic. This makes the codebase cleaner and more maintainable.

4.4 Transactional Integrity
#

With the Unit of Work pattern, Dapper can participate in transactions that span multiple repository operations, ensuring data consistency.

Conclusion
#

This article explained how Dapper is used in conjunction with the Repository Pattern to provide efficient and flexible data access in the Contact Management Application. By using Dapper, we maintain full control over the SQL, while the repository pattern ensures clean and maintainable code.

In the next article, we will discuss the best practices for creating and using DTOs in the API.

For more details, check out the full project on GitHub:

GitHub Repository: Contact Management Application{:target="_blank" rel=“noopener noreferrer”}

Get Involved
#

  • Follow for Updates: Follow{:target="_blank" rel=“noopener noreferrer”} to stay informed about the latest developer tools and cloud development practices.
Deep Dive .NET Clean Architecture Dotnet Clean-Architecture Dapper Orm Repository-Pattern Data-Access

Related

Dependency Injection Setup Across Layers

·6 mins
Introduction # Dependency Injection (DI) is a key design pattern in Clean Architecture that facilitates loose coupling between components. By injecting dependencies rather than hard-coding them, we create more maintainable, testable code where components can be easily swapped or mocked. This article explores how to implement DI across different architectural layers in the Contact Management Application.

Implementing Activity Logging with Custom Attributes

·6 mins
Introduction # Tracking user activities is crucial for security, auditing, and troubleshooting in any application. This article explores how to implement activity logging in the Contact Management Application using custom .NET attributes and action filters. This approach seamlessly integrates logging into the application without cluttering controllers or business logic with repetitive logging code.

Clean Architecture: Introduction to the Project Structure

·11 mins
Overview # Clean Architecture is a powerful software design pattern that promotes a clear separation of concerns, making your application’s core business logic independent of external dependencies like databases, user interfaces, or frameworks. By following this architecture, systems become maintainable, testable, and adaptable, preparing them for both current demands and future growth.