Using Dapper for Data Access and Repository Pattern

by Nitin Singh · Published December 1, 2024 · Updated December 16, 2024

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

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") 
    { 
    }
}

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

Discover more from Nitin Singh

Subscribe to get the latest posts sent to your email.

Comments0
Pingbacks2

AutoMapper: Simplifying Data Model Transformation

December 3, 2024

[…] Clean Architecture: Using Dapper for Data Access and Repository Pattern […]

Loading...
Role-Based Access Control (RBAC): Ensuring Secure User Actions

December 16, 2024

[…] Clean Architecture: Using Dapper for Data Access and Repository Pattern […]

Loading...

Using Dapper for Data Access and Repository Pattern

2 Responses

Leave a ReplyCancel reply

Using Dapper for Data Access and Repository Pattern

Introduction

Part of the Series

1. What is Dapper?

2. Setting Up Dapper

2.1 Installing and Configuring Dapper

2.2 DapperHelper: Managing Database Interactions

2.1 Defining the Repository Interface

2.2 Implementing the Generic Repository

3. Example Usage

4. Benefits of Using Dapper with the Repository Pattern

4.1 Performance

4.2 Flexibility

4.3 Clean Code

4.4 Transactional Integrity

Conclusion

Like this:

Discover more from Nitin Singh

2 Responses

Leave a ReplyCancel reply