High-performance data access using Dapper with the Repository pattern

Dapper is a lightweight, high-performance micro-ORM that sits right on top of ADO.NET. It gives you the best of both worlds: the speed of raw ADO.NET with the convenience of object mapping. When combined with the Repository pattern, it becomes a powerful tool for performance-critical applications.

Why Dapper with Repository Pattern?

Dapper provides exceptional performance for read-heavy workloads and complex queries. The Repository pattern adds structure and maintainability:

• Performance: Direct SQL execution with minimal overhead
• Control: Full control over SQL queries and stored procedures
• Simplicity: No complex configuration or change tracking
• Flexibility: Works with any database that supports ADO.NET

Domain Layer

// YourApp.Domain/Entities/Product.cs
namespace YourApp.Domain.Entities
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public decimal Price { get; set; }
        public string? Description { get; set; }
        public int Stock { get; set; }
        public DateTime CreatedDate { get; set; }
        public DateTime? ModifiedDate { get; set; }
        public bool IsActive { get; set; }
    }
}

// YourApp.Domain/Repositories/IProductRepository.cs
namespace YourApp.Domain.Repositories
{
    public interface IProductRepository
    {
        // Read operations
        Task<Product?> GetByIdAsync(int id, CancellationToken cancellationToken = default);
        Task<List<Product>> GetAllAsync(CancellationToken cancellationToken = default);
        Task<List<Product>> GetLowStockProductsAsync(int threshold, CancellationToken cancellationToken = default);
        Task<List<Product>> GetProductsByPriceRangeAsync(decimal minPrice, decimal maxPrice, CancellationToken cancellationToken = default);
        Task<bool> AnyAsync(int id, CancellationToken cancellationToken = default);

        // Write operations
        Task AddAsync(Product entity, CancellationToken cancellationToken = default);
        Task AddRangeAsync(IEnumerable<Product> entities, CancellationToken cancellationToken = default);
        Task UpdateAsync(Product entity, CancellationToken cancellationToken = default);
        Task DeleteAsync(int id, CancellationToken cancellationToken = default);
        Task DeleteAsync(Product entity, CancellationToken cancellationToken = default);
    }
}

Dapper Repository Implementation

The Dapper repository uses raw SQL with parameterized queries for maximum performance and security:

// YourApp.Data/Repositories/DapperProductRepository.cs
using Dapper;
using System.Data.SqlClient;
using YourApp.Domain.Entities;
using YourApp.Domain.Repositories;

namespace YourApp.Data.Repositories
{
    public class DapperProductRepository : IProductRepository
    {
        private readonly string _connectionString;

        public DapperProductRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        // Get a product by ID
        public async Task<Product?> GetByIdAsync(
            int id,
            CancellationToken cancellationToken = default)
        {
            const string sql = @"
                SELECT Id, Name, Price, Description, Stock,
                       CreatedDate, ModifiedDate, IsActive
                FROM Products
                WHERE Id = @Id AND IsActive = 1";

            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync(cancellationToken);

            return await connection.QueryFirstOrDefaultAsync<Product>(
                sql,
                new { Id = id });
        }

        // Get all active products
        public async Task<List<Product>> GetAllAsync(
            CancellationToken cancellationToken = default)
        {
            const string sql = @"
                SELECT Id, Name, Price, Description, Stock,
                       CreatedDate, ModifiedDate, IsActive
                FROM Products
                WHERE IsActive = 1
                ORDER BY Name";

            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync(cancellationToken);

            var products = await connection.QueryAsync<Product>(sql);
            return products.ToList();
        }

        // Get products with low stock
        public async Task<List<Product>> GetLowStockProductsAsync(
            int threshold,
            CancellationToken cancellationToken = default)
        {
            const string sql = @"
                SELECT Id, Name, Price, Description, Stock,
                       CreatedDate, ModifiedDate, IsActive
                FROM Products
                WHERE Stock <= @Threshold AND IsActive = 1
                ORDER BY Stock";

            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync(cancellationToken);

            var products = await connection.QueryAsync<Product>(
                sql,
                new { Threshold = threshold });
            return products.ToList();
        }

        // Get products by price range
        public async Task<List<Product>> GetProductsByPriceRangeAsync(
            decimal minPrice,
            decimal maxPrice,
            CancellationToken cancellationToken = default)
        {
            const string sql = @"
                SELECT Id, Name, Price, Description, Stock,
                       CreatedDate, ModifiedDate, IsActive
                FROM Products
                WHERE Price BETWEEN @MinPrice AND @MaxPrice
                    AND IsActive = 1
                ORDER BY Price";

            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync(cancellationToken);

            var products = await connection.QueryAsync<Product>(
                sql,
                new { MinPrice = minPrice, MaxPrice = maxPrice });
            return products.ToList();
        }

        // Check if a product exists
        public async Task<bool> AnyAsync(
            int id,
            CancellationToken cancellationToken = default)
        {
            const string sql = @"
                SELECT COUNT(1)
                FROM Products
                WHERE Id = @Id AND IsActive = 1";

            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync(cancellationToken);

            var count = await connection.ExecuteScalarAsync<int>(sql, new { Id = id });
            return count > 0;
        }

        // Add a new product
        public async Task AddAsync(
            Product entity,
            CancellationToken cancellationToken = default)
        {
            const string sql = @"
                INSERT INTO Products (Name, Price, Description, Stock, CreatedDate, IsActive)
                VALUES (@Name, @Price, @Description, @Stock, @CreatedDate, @IsActive);
                SELECT CAST(SCOPE_IDENTITY() as int)";

            entity.CreatedDate = DateTime.UtcNow;
            entity.IsActive = true;

            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync(cancellationToken);

            var id = await connection.QuerySingleAsync<int>(sql, entity);
            entity.Id = id;
        }

        // Add multiple products using bulk operation
        public async Task AddRangeAsync(
            IEnumerable<Product> entities,
            CancellationToken cancellationToken = default)
        {
            const string sql = @"
                INSERT INTO Products (Name, Price, Description, Stock, CreatedDate, IsActive)
                VALUES (@Name, @Price, @Description, @Stock, @CreatedDate, @IsActive);
                SELECT CAST(SCOPE_IDENTITY() as int)";

            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync(cancellationToken);

            foreach (var entity in entities)
            {
                entity.CreatedDate = DateTime.UtcNow;
                entity.IsActive = true;
                var id = await connection.QuerySingleAsync<int>(sql, entity);
                entity.Id = id;
            }
        }

        // Update a product
        public async Task UpdateAsync(
            Product entity,
            CancellationToken cancellationToken = default)
        {
            const string sql = @"
                UPDATE Products
                SET Name = @Name,
                    Price = @Price,
                    Description = @Description,
                    Stock = @Stock,
                    ModifiedDate = GETUTCDATE()
                WHERE Id = @Id AND IsActive = 1";

            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync(cancellationToken);

            await connection.ExecuteAsync(sql, entity);
        }

        // Delete a product by ID (soft delete)
        public async Task DeleteAsync(
            int id,
            CancellationToken cancellationToken = default)
        {
            const string sql = @"
                UPDATE Products
                SET IsActive = 0,
                    ModifiedDate = GETUTCDATE()
                WHERE Id = @Id";

            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync(cancellationToken);

            await connection.ExecuteAsync(sql, new { Id = id });
        }

        // Delete a product entity (soft delete)
        public async Task DeleteAsync(
            Product entity,
            CancellationToken cancellationToken = default)
        {
            await DeleteAsync(entity.Id, cancellationToken);
        }
    }
}

Using Stored Procedures with Dapper

Dapper also supports stored procedures, which can improve performance for complex operations:

// Add a product using a stored procedure
public async Task AddProductWithStoredProcedureAsync(
    Product entity,
    CancellationToken cancellationToken = default)
{
    const string procedure = "usp_InsertProduct";

    using var connection = new SqlConnection(_connectionString);
    await connection.OpenAsync(cancellationToken);

    var parameters = new
    {
        entity.Name,
        entity.Price,
        entity.Description,
        entity.Stock
    };

    // Execute the stored procedure and get the new ID
    entity.Id = await connection.QuerySingleAsync<int>(
        procedure,
        parameters,
        commandType: CommandType.StoredProcedure);
}

Service Layer

The service layer remains the same regardless of your data access technology. This is the power of the Repository pattern:

// YourApp.Services/ProductService.cs
using Microsoft.Extensions.Logging;
using YourApp.Domain.Entities;
using YourApp.Domain.Repositories;

namespace YourApp.Services
{
    public class ProductService
    {
        private readonly IProductRepository _productRepository;
        private readonly ILogger<ProductService> _logger;

        public ProductService(
            IProductRepository productRepository,
            ILogger<ProductService> logger)
        {
            _productRepository = productRepository;
            _logger = logger;
        }

        public async Task<List<Product>> GetAllProductsAsync(
            CancellationToken cancellationToken = default)
        {
            try
            {
                return await _productRepository.GetAllAsync(cancellationToken);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error retrieving all products");
                throw;
            }
        }

        public async Task<Product?> GetProductByIdAsync(
            int id,
            CancellationToken cancellationToken = default)
        {
            if (id <= 0)
            {
                throw new ArgumentException("Invalid product ID", nameof(id));
            }

            try
            {
                var product = await _productRepository.GetByIdAsync(id, cancellationToken);
                if (product == null)
                {
                    _logger.LogWarning("Product with ID {ProductId} not found", id);
                }
                return product;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error retrieving product with ID {ProductId}", id);
                throw;
            }
        }

        public async Task<List<Product>> GetLowStockProductsAsync(
            int threshold,
            CancellationToken cancellationToken = default)
        {
            if (threshold < 0)
            {
                throw new ArgumentException("Threshold must be non-negative", nameof(threshold));
            }

            try
            {
                return await _productRepository.GetLowStockProductsAsync(threshold, cancellationToken);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error retrieving low stock products");
                throw;
            }
        }

        public async Task<Product> CreateProductAsync(
            Product product,
            CancellationToken cancellationToken = default)
        {
            ValidateProduct(product);

            try
            {
                await _productRepository.AddAsync(product, cancellationToken);
                _logger.LogInformation("Created product with ID {ProductId}", product.Id);
                return product;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error creating product");
                throw;
            }
        }

        public async Task UpdateProductAsync(
            Product product,
            CancellationToken cancellationToken = default)
        {
            ValidateProduct(product);

            try
            {
                var exists = await _productRepository.AnyAsync(product.Id, cancellationToken);
                if (!exists)
                {
                    throw new InvalidOperationException(
                        $"Product with ID {product.Id} not found");
                }

                await _productRepository.UpdateAsync(product, cancellationToken);
                _logger.LogInformation("Updated product with ID {ProductId}", product.Id);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error updating product with ID {ProductId}", product.Id);
                throw;
            }
        }

        public async Task DeleteProductAsync(
            int id,
            CancellationToken cancellationToken = default)
        {
            if (id <= 0)
            {
                throw new ArgumentException("Invalid product ID", nameof(id));
            }

            try
            {
                var exists = await _productRepository.AnyAsync(id, cancellationToken);
                if (!exists)
                {
                    throw new InvalidOperationException($"Product with ID {id} not found");
                }

                await _productRepository.DeleteAsync(id, cancellationToken);
                _logger.LogInformation("Deleted product with ID {ProductId}", id);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error deleting product with ID {ProductId}", id);
                throw;
            }
        }

        private static void ValidateProduct(Product product)
        {
            ArgumentNullException.ThrowIfNull(product);

            if (string.IsNullOrWhiteSpace(product.Name))
            {
                throw new ArgumentException("Product name is required", nameof(product));
            }

            if (product.Price < 0)
            {
                throw new ArgumentException("Product price cannot be negative", nameof(product));
            }

            if (product.Stock < 0)
            {
                throw new ArgumentException("Product stock cannot be negative", nameof(product));
            }
        }
    }
}

Dependency Injection Setup

// Program.cs
using YourApp.Data.Repositories;
using YourApp.Domain.Repositories;
using YourApp.Services;

var builder = WebApplication.CreateBuilder(args);

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");

// Register repository with connection string
builder.Services.AddScoped<IProductRepository>(provider =>
    new DapperProductRepository(connectionString));

// Register services
builder.Services.AddScoped<ProductService>();

builder.Services.AddControllers();

var app = builder.Build();

app.MapControllers();
app.Run();

Dapper Performance Tips

Conclusion

Dapper, combined with the Repository pattern, provides an excellent balance of performance and maintainability. You get the raw speed of ADO.NET with the convenience of object mapping, all wrapped in a clean, testable architecture.