ASP.NET Core

Dapper - Micro ORM

Sometimes the best solution is the one that gets out of your way. Entity Framework is powerful, feature-rich, and handles 90% of database operations beautifully. But when you need raw speed, precise control over your SQL, or you're dealing with complex queries that make EF's query provider cry, you reach for Dapper.

The irony is that Dapper calls itself a "micro-ORM," but it powers some of the internet's highest-traffic sites. Stack Overflow, the very platform where developers debate ORM choices, runs on Dapper. When you're serving hundreds of millions of page views and every millisecond counts, the "micro" ORM becomes the heavyweight champion.

This isn't to say you should abandon Entity Framework or other full ORMs. Think of it like choosing between a Swiss Army knife and a scalpel. EF is your Swiss Army knife—versatile, handles most situations, packed with features. Dapper is your scalpel—specialized, precise, incredibly efficient at its one job: mapping SQL results to objects with minimal overhead.

For instance: A reporting dashboard executing 50+ complex analytical queries per page load? Dapper will shine. A standard CRUD application with simple entities and relationships? EF Core will serve you better with less code.

Dapper is a micro-ORM (Object-Relational Mapper) created by Stack Overflow that focuses on performance and simplicity. Unlike Entity Framework, which generates SQL for you, Dapper requires you to write your own SQL queries. What Dapper does exceptionally well is map those query results to your C# objects with blazing speed.

Key characteristics:

You write the SQL - Full control over queries
Dapper handles mapping - Results to objects automatically
Performance-focused - Minimal overhead, near-raw ADO.NET speeds
No change tracking - You manage updates explicitly
Database-agnostic - Works with any ADO.NET provider

Why Use Dapper?

1. Performance

Dapper is one of the fastest ORMs available. It's just a thin layer over ADO.NET, adding minimal overhead while saving you from manual mapping code.

2. SQL Control

When you need specific SQL features—CTEs, window functions, database-specific optimizations—Dapper doesn't fight you. You write exactly the SQL you need.

3. Simplicity

No configuration files, no fluent APIs, no migration generators. Install the package, write SQL, get objects. That's it.

4. Learning Curve

If you know SQL and C#, you know Dapper. There's almost nothing else to learn.

5. Legacy Database Integration

Perfect for working with existing databases where you can't or don't want to restructure to fit an ORM's conventions.

6. Stored Procedure Support

First-class support for stored procedures without ceremony or workarounds.

How Dapper Works

Basic Concept

Traditional ADO.NET:

using var connection = new SqlConnection(connectionString);
var command = new SqlCommand("SELECT * FROM Users WHERE Id = @Id", connection);
command.Parameters.AddWithValue("@Id", userId);
connection.Open();
var reader = command.ExecuteReader();

var user = new User();
while (reader.Read())
{
    user.Id = (int)reader["Id"];
    user.Name = reader["Name"].ToString();
    user.Email = reader["Email"].ToString();
}

With Dapper:

using var connection = new SqlConnection(connectionString);
var user = connection.QuerySingle<User>(
    "SELECT * FROM Users WHERE Id = @Id", 
    new { Id = userId }
);

Dapper handles the connection opening, parameter mapping, result reading, and object mapping—all with performance close to raw ADO.NET.

Core Dapper Operations

1. Query Operations

Query Single Object

public class UserRepository
{
    private readonly string _connectionString;
    
    public async Task<User> GetByIdAsync(int id)
    {
        using var connection = new SqlConnection(_connectionString);
        
        return await connection.QuerySingleAsync<User>(
            @"SELECT Id, Username, Email, CreatedDate 
              FROM Users 
              WHERE Id = @Id",
            new { Id = id }
        );
    }
    
    // Returns null if not found
    public async Task<User> GetByEmailOrDefaultAsync(string email)
    {
        using var connection = new SqlConnection(_connectionString);
        
        return await connection.QuerySingleOrDefaultAsync<User>(
            "SELECT * FROM Users WHERE Email = @Email",
            new { Email = email }
        );
    }
}

Query Multiple Objects

public async Task<IEnumerable<Product>> GetByCategoryAsync(int categoryId)
{
    using var connection = new SqlConnection(_connectionString);
    
    return await connection.QueryAsync<Product>(
        @"SELECT p.Id, p.Name, p.Price, p.StockQuantity
          FROM Products p
          WHERE p.CategoryId = @CategoryId
          ORDER BY p.Name",
        new { CategoryId = categoryId }
    );
}

// With pagination
public async Task<IEnumerable<Product>> GetPagedAsync(int pageNumber, int pageSize)
{
    using var connection = new SqlConnection(_connectionString);
    
    return await connection.QueryAsync<Product>(
        @"SELECT * FROM Products
          ORDER BY Id
          OFFSET @Offset ROWS
          FETCH NEXT @PageSize ROWS ONLY",
        new 
        { 
            Offset = (pageNumber - 1) * pageSize,
            PageSize = pageSize 
        }
    );
}

Query with Complex Conditions

public async Task<IEnumerable<Order>> SearchOrdersAsync(OrderSearchCriteria criteria)
{
    using var connection = new SqlConnection(_connectionString);
    
    var sql = @"
        SELECT o.Id, o.OrderNumber, o.OrderDate, o.TotalAmount,
               o.Status, o.CustomerId
        FROM Orders o
        WHERE (@FromDate IS NULL OR o.OrderDate >= @FromDate)
          AND (@ToDate IS NULL OR o.OrderDate <= @ToDate)
          AND (@Status IS NULL OR o.Status = @Status)
          AND (@MinAmount IS NULL OR o.TotalAmount >= @MinAmount)
          AND (@CustomerId IS NULL OR o.CustomerId = @CustomerId)
        ORDER BY o.OrderDate DESC";
    
    return await connection.QueryAsync<Order>(sql, criteria);
}

2. Execute Operations (INSERT, UPDATE, DELETE)

Insert

public async Task<int> CreateUserAsync(User user)
{
    using var connection = new SqlConnection(_connectionString);
    
    var sql = @"
        INSERT INTO Users (Username, Email, PasswordHash, CreatedDate)
        VALUES (@Username, @Email, @PasswordHash, @CreatedDate);
        
        SELECT CAST(SCOPE_IDENTITY() AS INT);";
    
    return await connection.QuerySingleAsync<int>(sql, user);
}

// Bulk insert
public async Task<int> CreateUsersAsync(IEnumerable<User> users)
{
    using var connection = new SqlConnection(_connectionString);
    
    var sql = @"
        INSERT INTO Users (Username, Email, PasswordHash, CreatedDate)
        VALUES (@Username, @Email, @PasswordHash, @CreatedDate)";
    
    return await connection.ExecuteAsync(sql, users);
}

Update

public async Task<bool> UpdateUserAsync(User user)
{
    using var connection = new SqlConnection(_connectionString);
    
    var sql = @"
        UPDATE Users 
        SET Username = @Username,
            Email = @Email,
            UpdatedDate = @UpdatedDate
        WHERE Id = @Id";
    
    var rowsAffected = await connection.ExecuteAsync(sql, user);
    return rowsAffected > 0;
}

// Partial update
public async Task<bool> UpdateEmailAsync(int userId, string email)
{
    using var connection = new SqlConnection(_connectionString);
    
    var rowsAffected = await connection.ExecuteAsync(
        "UPDATE Users SET Email = @Email, UpdatedDate = GETUTCDATE() WHERE Id = @Id",
        new { Id = userId, Email = email }
    );
    
    return rowsAffected > 0;
}

Delete

public async Task<bool> DeleteUserAsync(int id)
{
    using var connection = new SqlConnection(_connectionString);
    
    var rowsAffected = await connection.ExecuteAsync(
        "DELETE FROM Users WHERE Id = @Id",
        new { Id = id }
    );
    
    return rowsAffected > 0;
}

// Soft delete
public async Task<bool> SoftDeleteUserAsync(int id)
{
    using var connection = new SqlConnection(_connectionString);
    
    var rowsAffected = await connection.ExecuteAsync(
        @"UPDATE Users 
          SET IsDeleted = 1, 
              DeletedDate = GETUTCDATE() 
          WHERE Id = @Id",
        new { Id = id }
    );
    
    return rowsAffected > 0;
}

3. Multi-Mapping (Joins)

Dapper excels at mapping joined queries to related objects.

One-to-One Relationship

public class Order
{
    public int Id { get; set; }
    public string OrderNumber { get; set; }
    public decimal TotalAmount { get; set; }
    public Customer Customer { get; set; }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

public async Task<Order> GetOrderWithCustomerAsync(int orderId)
{
    using var connection = new SqlConnection(_connectionString);
    
    var sql = @"
        SELECT o.Id, o.OrderNumber, o.TotalAmount,
               c.Id, c.Name, c.Email
        FROM Orders o
        INNER JOIN Customers c ON o.CustomerId = c.Id
        WHERE o.Id = @OrderId";
    
    var order = await connection.QueryAsync<Order, Customer, Order>(
        sql,
        (order, customer) =>
        {
            order.Customer = customer;
            return order;
        },
        new { OrderId = orderId },
        splitOn: "Id"  // Where Customer properties start
    );
    
    return order.FirstOrDefault();
}

One-to-Many Relationship

public class Order
{
    public int Id { get; set; }
    public string OrderNumber { get; set; }
    public List<OrderItem> Items { get; set; } = new();
}

public class OrderItem
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public int Quantity { get; set; }
    public decimal Price { get; set; }
}

public async Task<Order> GetOrderWithItemsAsync(int orderId)
{
    using var connection = new SqlConnection(_connectionString);
    
    var sql = @"
        SELECT o.Id, o.OrderNumber,
               oi.Id, oi.ProductId, oi.Quantity, oi.Price,
               p.Name AS ProductName
        FROM Orders o
        LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
        LEFT JOIN Products p ON oi.ProductId = p.Id
        WHERE o.Id = @OrderId";
    
    var orderDictionary = new Dictionary<int, Order>();
    
    await connection.QueryAsync<Order, OrderItem, Order>(
        sql,
        (order, orderItem) =>
        {
            if (!orderDictionary.TryGetValue(order.Id, out var existingOrder))
            {
                existingOrder = order;
                orderDictionary.Add(order.Id, existingOrder);
            }
            
            if (orderItem != null)
            {
                existingOrder.Items.Add(orderItem);
            }
            
            return existingOrder;
        },
        new { OrderId = orderId },
        splitOn: "Id"
    );
    
    return orderDictionary.Values.FirstOrDefault();
}

Many-to-Many Relationship

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Category> Categories { get; set; } = new();
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public async Task<IEnumerable<Product>> GetProductsWithCategoriesAsync()
{
    using var connection = new SqlConnection(_connectionString);
    
    var sql = @"
        SELECT p.Id, p.Name,
               c.Id, c.Name
        FROM Products p
        LEFT JOIN ProductCategories pc ON p.Id = pc.ProductId
        LEFT JOIN Categories c ON pc.CategoryId = c.Id
        ORDER BY p.Id";
    
    var productDictionary = new Dictionary<int, Product>();
    
    await connection.QueryAsync<Product, Category, Product>(
        sql,
        (product, category) =>
        {
            if (!productDictionary.TryGetValue(product.Id, out var existingProduct))
            {
                existingProduct = product;
                productDictionary.Add(product.Id, existingProduct);
            }
            
            if (category != null)
            {
                existingProduct.Categories.Add(category);
            }
            
            return existingProduct;
        },
        splitOn: "Id"
    );
    
    return productDictionary.Values;
}

4. Stored Procedures

public async Task<IEnumerable<SalesReport>> GetSalesReportAsync(
    DateTime startDate, 
    DateTime endDate)
{
    using var connection = new SqlConnection(_connectionString);
    
    return await connection.QueryAsync<SalesReport>(
        "usp_GetSalesReport",
        new { StartDate = startDate, EndDate = endDate },
        commandType: CommandType.StoredProcedure
    );
}

// With output parameters
public async Task<(int TotalCount, IEnumerable<Order> Orders)> GetOrdersPagedAsync(
    int pageNumber, 
    int pageSize)
{
    using var connection = new SqlConnection(_connectionString);
    
    var parameters = new DynamicParameters();
    parameters.Add("@PageNumber", pageNumber);
    parameters.Add("@PageSize", pageSize);
    parameters.Add("@TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
    
    var orders = await connection.QueryAsync<Order>(
        "usp_GetOrdersPaged",
        parameters,
        commandType: CommandType.StoredProcedure
    );
    
    var totalCount = parameters.Get<int>("@TotalCount");
    
    return (totalCount, orders);
}

5. Multiple Result Sets

public async Task<DashboardData> GetDashboardDataAsync(int userId)
{
    using var connection = new SqlConnection(_connectionString);
    
    var sql = @"
        SELECT * FROM Users WHERE Id = @UserId;
        
        SELECT * FROM Orders 
        WHERE CustomerId = @UserId 
        ORDER BY OrderDate DESC;
        
        SELECT SUM(TotalAmount) AS TotalSpent,
               COUNT(*) AS OrderCount
        FROM Orders 
        WHERE CustomerId = @UserId;";
    
    using var multi = await connection.QueryMultipleAsync(sql, new { UserId = userId });
    
    var dashboard = new DashboardData
    {
        User = await multi.ReadSingleAsync<User>(),
        RecentOrders = (await multi.ReadAsync<Order>()).ToList(),
        Statistics = await multi.ReadSingleAsync<OrderStatistics>()
    };
    
    return dashboard;
}

6. Dynamic Queries

// When you don't have a specific type
public async Task<IEnumerable<dynamic>> GetTableDataAsync(string tableName)
{
    using var connection = new SqlConnection(_connectionString);
    
    // DANGEROUS - Only use with validated input!
    return await connection.QueryAsync(
        $"SELECT * FROM {tableName}"
    );
}

// Safer dynamic usage
public async Task<dynamic> GetUserStatsAsync(int userId)
{
    using var connection = new SqlConnection(_connectionString);
    
    return await connection.QuerySingleAsync(
        @"SELECT 
            COUNT(DISTINCT o.Id) AS OrderCount,
            SUM(o.TotalAmount) AS TotalSpent,
            MAX(o.OrderDate) AS LastOrderDate
          FROM Orders o
          WHERE o.CustomerId = @UserId",
        new { UserId = userId }
    );
}

Advanced Dapper Patterns

1. Transaction Management

public async Task<bool> TransferFundsAsync(
    int fromAccountId, 
    int toAccountId, 
    decimal amount)
{
    using var connection = new SqlConnection(_connectionString);
    await connection.OpenAsync();
    
    using var transaction = await connection.BeginTransactionAsync();
    
    try
    {
        // Debit from source account
        var debitSql = @"
            UPDATE Accounts 
            SET Balance = Balance - @Amount,
                UpdatedDate = GETUTCDATE()
            WHERE Id = @AccountId 
              AND Balance >= @Amount";
        
        var debitRows = await connection.ExecuteAsync(
            debitSql,
            new { AccountId = fromAccountId, Amount = amount },
            transaction
        );
        
        if (debitRows == 0)
            throw new InsufficientFundsException();
        
        // Credit to destination account
        var creditSql = @"
            UPDATE Accounts 
            SET Balance = Balance + @Amount,
                UpdatedDate = GETUTCDATE()
            WHERE Id = @AccountId";
        
        await connection.ExecuteAsync(
            creditSql,
            new { AccountId = toAccountId, Amount = amount },
            transaction
        );
        
        // Record transaction
        var recordSql = @"
            INSERT INTO Transactions 
                (FromAccountId, ToAccountId, Amount, TransactionDate)
            VALUES (@FromAccountId, @ToAccountId, @Amount, GETUTCDATE())";
        
        await connection.ExecuteAsync(
            recordSql,
            new { FromAccountId = fromAccountId, ToAccountId = toAccountId, Amount = amount },
            transaction
        );
        
        await transaction.CommitAsync();
        return true;
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

2. Custom Type Handlers

For mapping custom types that Dapper doesn't handle by default:

public class JsonTypeHandler<T> : SqlMapper.TypeHandler<T>
{
    public override T Parse(object value)
    {
        var json = value.ToString();
        return string.IsNullOrEmpty(json) 
            ? default 
            : JsonSerializer.Deserialize<T>(json);
    }
    
    public override void SetValue(IDbDataParameter parameter, T value)
    {
        parameter.Value = value == null 
            ? DBNull.Value 
            : JsonSerializer.Serialize(value);
        parameter.DbType = DbType.String;
    }
}

// Registration (in application startup)
SqlMapper.AddTypeHandler(new JsonTypeHandler<Dictionary<string, object>>());

// Usage
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Dictionary<string, object> Metadata { get; set; }
}

// Dapper will now automatically handle the JSON conversion
var product = await connection.QuerySingleAsync<Product>(
    "SELECT Id, Name, Metadata FROM Products WHERE Id = @Id",
    new { Id = productId }
);

3. Query Builder Pattern

For complex dynamic queries:

public class ProductQueryBuilder
{
    private readonly List<string> _whereClauses = new();
    private readonly DynamicParameters _parameters = new();
    private string _orderBy = "p.Name";
    
    public ProductQueryBuilder WithCategory(int? categoryId)
    {
        if (categoryId.HasValue)
        {
            _whereClauses.Add("p.CategoryId = @CategoryId");
            _parameters.Add("CategoryId", categoryId.Value);
        }
        return this;
    }
    
    public ProductQueryBuilder WithPriceRange(decimal? minPrice, decimal? maxPrice)
    {
        if (minPrice.HasValue)
        {
            _whereClauses.Add("p.Price >= @MinPrice");
            _parameters.Add("MinPrice", minPrice.Value);
        }
        if (maxPrice.HasValue)
        {
            _whereClauses.Add("p.Price <= @MaxPrice");
            _parameters.Add("MaxPrice", maxPrice.Value);
        }
        return this;
    }
    
    public ProductQueryBuilder WithSearchTerm(string searchTerm)
    {
        if (!string.IsNullOrWhiteSpace(searchTerm))
        {
            _whereClauses.Add("(p.Name LIKE @Search OR p.Description LIKE @Search)");
            _parameters.Add("Search", $"%{searchTerm}%");
        }
        return this;
    }
    
    public ProductQueryBuilder OrderBy(string column, bool descending = false)
    {
        _orderBy = $"p.{column}" + (descending ? " DESC" : "");
        return this;
    }
    
    public async Task<IEnumerable<Product>> ExecuteAsync(IDbConnection connection)
    {
        var sql = "SELECT p.* FROM Products p";
        
        if (_whereClauses.Any())
        {
            sql += " WHERE " + string.Join(" AND ", _whereClauses);
        }
        
        sql += $" ORDER BY {_orderBy}";
        
        return await connection.QueryAsync<Product>(sql, _parameters);
    }
}

// Usage
var products = await new ProductQueryBuilder()
    .WithCategory(5)
    .WithPriceRange(10, 100)
    .WithSearchTerm("wireless")
    .OrderBy("Price", descending: false)
    .ExecuteAsync(connection);

4. Buffered vs Unbuffered Queries

// Buffered (default) - Loads all results into memory immediately
public async Task<IEnumerable<Order>> GetAllOrdersBufferedAsync()
{
    using var connection = new SqlConnection(_connectionString);
    
    // All results loaded into memory
    return await connection.QueryAsync<Order>(
        "SELECT * FROM Orders",
        buffered: true  // Default
    );
}

// Unbuffered - Yields results as they're read (better for large datasets)
public async IAsyncEnumerable<Order> GetAllOrdersStreamedAsync()
{
    using var connection = new SqlConnection(_connectionString);
    await connection.OpenAsync();
    
    // Results streamed one at a time
    var orders = await connection.QueryAsync<Order>(
        "SELECT * FROM Orders",
        buffered: false
    );
    
    foreach (var order in orders)
    {
        yield return order;
    }
}

5. Connection Pooling Best Practices

public class DapperRepository
{
    private readonly string _connectionString;
    
    // BAD - Creates new connection string builder each time
    public async Task<User> GetUserBadAsync(int id)
    {
        var builder = new SqlConnectionStringBuilder();
        builder.DataSource = "server";
        builder.InitialCatalog = "database";
        // ... more configuration
        
        using var connection = new SqlConnection(builder.ToString());
        return await connection.QuerySingleAsync<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = id });
    }
    
    // GOOD - Reuses connection string, benefits from pooling
    public async Task<User> GetUserGoodAsync(int id)
    {
        using var connection = new SqlConnection(_connectionString);
        return await connection.QuerySingleAsync<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = id });
    }
    
    // EXCELLENT - Async all the way
    public async Task<User> GetUserExcellentAsync(int id)
    {
        await using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        
        return await connection.QuerySingleAsync<User>(
            "SELECT * FROM Users WHERE Id = @Id", 
            new { Id = id }
        );
    }
}

Repository Pattern with Dapper

Base Repository

public interface IRepository<T> where T : class
{
    Task<T> GetByIdAsync(int id);
    Task<IEnumerable<T>> GetAllAsync();
    Task<int> InsertAsync(T entity);
    Task<bool> UpdateAsync(T entity);
    Task<bool> DeleteAsync(int id);
}

public abstract class DapperRepository<T> : IRepository<T> where T : class
{
    protected readonly string ConnectionString;
    protected abstract string TableName { get; }
    protected abstract string IdColumn { get; }
    
    protected DapperRepository(string connectionString)
    {
        ConnectionString = connectionString;
    }
    
    public virtual async Task<T> GetByIdAsync(int id)
    {
        using var connection = new SqlConnection(ConnectionString);
        
        return await connection.QuerySingleOrDefaultAsync<T>(
            $"SELECT * FROM {TableName} WHERE {IdColumn} = @Id",
            new { Id = id }
        );
    }
    
    public virtual async Task<IEnumerable<T>> GetAllAsync()
    {
        using var connection = new SqlConnection(ConnectionString);
        
        return await connection.QueryAsync<T>(
            $"SELECT * FROM {TableName}"
        );
    }
    
    public abstract Task<int> InsertAsync(T entity);
    public abstract Task<bool> UpdateAsync(T entity);
    
    public virtual async Task<bool> DeleteAsync(int id)
    {
        using var connection = new SqlConnection(ConnectionString);
        
        var rowsAffected = await connection.ExecuteAsync(
            $"DELETE FROM {TableName} WHERE {IdColumn} = @Id",
            new { Id = id }
        );
        
        return rowsAffected > 0;
    }
}

Concrete Repository

public class UserRepository : DapperRepository<User>
{
    protected override string TableName => "Users";
    protected override string IdColumn => "Id";
    
    public UserRepository(string connectionString) : base(connectionString) { }
    
    public override async Task<int> InsertAsync(User user)
    {
        using var connection = new SqlConnection(ConnectionString);
        
        var sql = @"
            INSERT INTO Users (Username, Email, PasswordHash, CreatedDate)
            VALUES (@Username, @Email, @PasswordHash, @CreatedDate);
            
            SELECT CAST(SCOPE_IDENTITY() AS INT);";
        
        return await connection.QuerySingleAsync<int>(sql, user);
    }
    
    public override async Task<bool> UpdateAsync(User user)
    {
        using var connection = new SqlConnection(ConnectionString);
        
        var sql = @"
            UPDATE Users 
            SET Username = @Username,
                Email = @Email,
                UpdatedDate = @UpdatedDate
            WHERE Id = @Id";
        
        var rowsAffected = await connection.ExecuteAsync(sql, user);
        return rowsAffected > 0;
    }
    
    // Custom methods
    public async Task<User> GetByEmailAsync(string email)
    {
        using var connection = new SqlConnection(ConnectionString);
        
        return await connection.QuerySingleOrDefaultAsync<User>(
            "SELECT * FROM Users WHERE Email = @Email",
            new { Email = email }
        );
    }
    
    public async Task<bool> EmailExistsAsync(string email)
    {
        using var connection = new SqlConnection(ConnectionString);
        
        var count = await connection.ExecuteScalarAsync<int>(
            "SELECT COUNT(*) FROM Users WHERE Email = @Email",
            new { Email = email }
        );
        
        return count > 0;
    }
}

Unit of Work Pattern

public interface IUnitOfWork : IDisposable
{
    IDbConnection Connection { get; }
    IDbTransaction Transaction { get; }
    void BeginTransaction();
    void Commit();
    void Rollback();
}

public class UnitOfWork : IUnitOfWork
{
    private IDbConnection _connection;
    private IDbTransaction _transaction;
    private bool _disposed;
    
    public UnitOfWork(string connectionString)
    {
        _connection = new SqlConnection(connectionString);
        _connection.Open();
    }
    
    public IDbConnection Connection => _connection;
    public IDbTransaction Transaction => _transaction;
    
    public void BeginTransaction()
    {
        _transaction = _connection.BeginTransaction();
    }
    
    public void Commit()
    {
        try
        {
            _transaction?.Commit();
        }
        catch
        {
            _transaction?.Rollback();
            throw;
        }
        finally
        {
            _transaction?.Dispose();
            _transaction = null;
        }
    }
    
    public void Rollback()
    {
        _transaction?.Rollback();
        _transaction?.Dispose();
        _transaction = null;
    }
    
    public void Dispose()
    {
        if (!_disposed)
        {
            _transaction?.Dispose();
            _connection?.Dispose();
            _disposed = true;
        }
    }
}

// Usage
public class OrderService
{
    private readonly string _connectionString;
    
    public async Task<bool> CreateOrderAsync(Order order, List<OrderItem> items)
    {
        using var uow = new UnitOfWork(_connectionString);
        
        try
        {
            uow.BeginTransaction();
            
            // Insert order
            var orderId = await uow.Connection.QuerySingleAsync<int>(
                @"INSERT INTO Orders (CustomerId, OrderDate, TotalAmount)
                  VALUES (@CustomerId, @OrderDate, @TotalAmount);
                  SELECT CAST(SCOPE_IDENTITY() AS INT);",
                order,
                uow.Transaction
            );
            
            // Insert order items
            foreach (var item in items)
            {
                item.OrderId = orderId;
            }
            
            await uow.Connection.ExecuteAsync(
                @"INSERT INTO OrderItems (OrderId, ProductId, Quantity, Price)
                  VALUES (@OrderId, @ProductId, @Quantity, @Price)",
                items,
                uow.Transaction
            );
            
            // Update inventory
            await uow.Connection.ExecuteAsync(
                @"UPDATE Products 
                  SET StockQuantity = StockQuantity - @Quantity
                  WHERE Id = @ProductId",
                items.Select(i => new { i.ProductId, i.Quantity }),
                uow.Transaction
            );
            
            uow.Commit();
            return true;
        }
        catch
        {
            uow.Rollback();
            throw;
        }
    }
}

Performance Optimization Techniques

1. Use Parameter Lists for IN Clauses

// INEFFICIENT - String concatenation
public async Task<IEnumerable<Product>> GetProductsBadAsync(List<int> ids)
{
    using var connection = new SqlConnection(_connectionString);
    
    var idsString = string.Join(",", ids);
    // SQL injection risk!
    return await connection.QueryAsync<Product>(
        $"SELECT * FROM Products WHERE Id IN ({idsString})"
    );
}

// EFFICIENT - Parameterized
public async Task<IEnumerable<Product>> GetProductsGoodAsync(List<int> ids)
{
    using var connection = new SqlConnection(_connectionString);
    
    return await connection.QueryAsync<Product>(
        "SELECT * FROM Products WHERE Id IN @Ids",
        new { Ids = ids }
    );
}

2. Bulk Operations

// SLOW - Individual inserts
public async Task InsertProductsSlowAsync(List<Product> products)
{
    using var connection = new SqlConnection(_connectionString);
    
    foreach (var product in products)
    {
        await connection.ExecuteAsync(
            "INSERT INTO Products (Name, Price) VALUES (@Name, @Price)",
            product
        );
    }
}

// FAST - Bulk insert
public async Task InsertProductsFastAsync(List<Product> products)
{
    using var connection = new SqlConnection(_connectionString);
    
    // Single database round-trip
    await connection.ExecuteAsync(
        "INSERT INTO Products (Name, Price) VALUES (@Name, @Price)",
        products
    );
}

// FASTEST - SQL Server specific bulk copy
public async Task InsertProductsFastestAsync(List<Product> products)
{
    using var connection = new SqlConnection(_connectionString);
    await connection.OpenAsync();
    
    using var bulkCopy = new SqlBulkCopy(connection);
    bulkCopy.DestinationTableName = "Products";
    
    var dataTable = CreateDataTable(products);
    await bulkCopy.WriteToServerAsync(dataTable);
}

3. Query Optimization

// BAD - SELECT *
public async Task<IEnumerable<Product>> GetProductsBadAsync()
{
    using var connection = new SqlConnection(_connectionString);
    
    return await connection.QueryAsync<Product>(
        "SELECT * FROM Products"
    );
}

// GOOD - Select only needed columns
public async Task<IEnumerable<ProductListDto>> GetProductsGoodAsync()
{
    using var connection = new SqlConnection(_connectionString);
    
    return await connection.QueryAsync<ProductListDto>(
        "SELECT Id, Name, Price FROM Products"
    );
}

// EXCELLENT - With proper indexing hints
public async Task<IEnumerable<ProductListDto>> GetProductsExcellentAsync()
{
    using var connection = new SqlConnection(_connectionString);
    
    return await connection.QueryAsync<ProductListDto>(
        @"SELECT Id, Name, Price 
          FROM Products WITH (INDEX(IX_Products_Name))
          WHERE IsActive = 1
          ORDER BY Name"
    );
}

4. Caching Strategy

public class CachedProductRepository
{
    private readonly IMemoryCache _cache;
    private readonly string _connectionString;
    
    public async Task<Product> GetByIdAsync(int id)
    {
        var cacheKey = $"product_{id}";
        
        if (_cache.TryGetValue(cacheKey, out Product cachedProduct))
        {
            return cachedProduct;
        }
        
        using var connection = new SqlConnection(_connectionString);
        
        var product = await connection.QuerySingleOrDefaultAsync<Product>(
            "SELECT * FROM Products WHERE Id = @Id",
            new { Id = id }
        );
        
        if (product != null)
        {
            _cache.Set(cacheKey, product, TimeSpan.FromMinutes(10));
        }
        
        return product;
    }
    
    public async Task<bool> UpdateAsync(Product product)
    {
        using var connection = new SqlConnection(_connectionString);
        
        var rowsAffected = await connection.ExecuteAsync(
            "UPDATE Products SET Name = @Name, Price = @Price WHERE Id = @Id",
            product
        );
        
        if (rowsAffected > 0)
        {
            // Invalidate cache
            _cache.Remove($"product_{product.Id}");
        }
        
        return rowsAffected > 0;
    }
}

Testing with Dapper

1. Unit Testing with Mocks

public interface IDbConnectionFactory
{
    IDbConnection CreateConnection();
}

public class UserRepository
{
    private readonly IDbConnectionFactory _connectionFactory;
    
    public UserRepository(IDbConnectionFactory connectionFactory)
    {
        _connectionFactory = connectionFactory;
    }
    
    public async Task<User> GetByIdAsync(int id)
    {
        using var connection = _connectionFactory.CreateConnection();
        
        return await connection.QuerySingleOrDefaultAsync<User>(
            "SELECT * FROM Users WHERE Id = @Id",
            new { Id = id }
        );
    }
}

// Test
[Fact]
public async Task GetByIdAsync_ReturnsUser_WhenUserExists()
{
    // Arrange
    var mockConnection = new Mock<IDbConnection>();
    var mockFactory = new Mock<IDbConnectionFactory>();
    
    mockFactory
        .Setup(f => f.CreateConnection())
        .Returns(mockConnection.Object);
    
    var repository = new UserRepository(mockFactory.Object);
    
    // Act & Assert would require more complex setup
    // Consider integration tests instead
}

2. Integration Testing

public class UserRepositoryIntegrationTests : IDisposable
{
    private readonly SqlConnection _connection;
    private readonly UserRepository _repository;
    
    public UserRepositoryIntegrationTests()
    {
        var connectionString = "Server=(localdb)\\mssqllocaldb;Database=TestDb;";
        _connection = new SqlConnection(connectionString);
        _connection.Open();
        
        // Setup test database
        _connection.Execute(@"
            IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Users')
            CREATE TABLE Users (
                Id INT PRIMARY KEY IDENTITY,
                Username NVARCHAR(100),
                Email NVARCHAR(255)
            )
        ");
        
        _repository = new UserRepository(connectionString);
    }
    
    [Fact]
    public async Task InsertAsync_AddsUserToDatabase()
    {
        // Arrange
        var user = new User 
        { 
            Username = "testuser", 
            Email = "[email protected]" 
        };
        
        // Act
        var userId = await _repository.InsertAsync(user);
        
        // Assert
        Assert.True(userId > 0);
        
        var retrievedUser = await _repository.GetByIdAsync(userId);
        Assert.Equal("testuser", retrievedUser.Username);
        Assert.Equal("[email protected]", retrievedUser.Email);
    }
    
    [Fact]
    public async Task GetByEmailAsync_ReturnsNull_WhenUserDoesNotExist()
    {
        // Act
        var user = await _repository.GetByEmailAsync("[email protected]");
        
        // Assert
        Assert.Null(user);
    }
    
    public void Dispose()
    {
        _connection.Execute("DROP TABLE IF EXISTS Users");
        _connection.Dispose();
    }
}

Common Pitfalls and Solutions

1. Connection Not Disposed

// BAD - Connection leak
public async Task<User> GetUserBadAsync(int id)
{
    var connection = new SqlConnection(_connectionString);
    return await connection.QuerySingleAsync<User>(
        "SELECT * FROM Users WHERE Id = @Id",
        new { Id = id }
    );
    // Connection never disposed!
}

// GOOD - Proper disposal
public async Task<User> GetUserGoodAsync(int id)
{
    using var connection = new SqlConnection(_connectionString);
    return await connection.QuerySingleAsync<User>(
        "SELECT * FROM Users WHERE Id = @Id",
        new { Id = id }
    );
}

2. SQL Injection Risk

// DANGEROUS - SQL injection vulnerability
public async Task<IEnumerable<User>> SearchUsersDangerousAsync(string searchTerm)
{
    using var connection = new SqlConnection(_connectionString);
    
    return await connection.QueryAsync<User>(
        $"SELECT * FROM Users WHERE Username LIKE '%{searchTerm}%'"
    );
}

// SAFE - Parameterized query
public async Task<IEnumerable<User>> SearchUsersSafeAsync(string searchTerm)
{
    using var connection = new SqlConnection(_connectionString);
    
    return await connection.QueryAsync<User>(
        "SELECT * FROM Users WHERE Username LIKE @SearchTerm",
        new { SearchTerm = $"%{searchTerm}%" }
    );
}

3. Wrong Query Method

// WRONG - QueryAsync for single result
public async Task<User> GetUserWrongAsync(int id)
{
    using var connection = new SqlConnection(_connectionString);
    
    var users = await connection.QueryAsync<User>(
        "SELECT * FROM Users WHERE Id = @Id",
        new { Id = id }
    );
    
    return users.FirstOrDefault(); // Unnecessary enumeration
}

// RIGHT - QuerySingleOrDefaultAsync
public async Task<User> GetUserRightAsync(int id)
{
    using var connection = new SqlConnection(_connectionString);
    
    return await connection.QuerySingleOrDefaultAsync<User>(
        "SELECT * FROM Users WHERE Id = @Id",
        new { Id = id }
    );
}

4. N+1 Query Problem

// BAD - N+1 queries
public async Task<IEnumerable<OrderWithItems>> GetOrdersBadAsync()
{
    using var connection = new SqlConnection(_connectionString);
    
    var orders = await connection.QueryAsync<Order>("SELECT * FROM Orders");
    
    foreach (var order in orders)
    {
        // N additional queries!
        order.Items = (await connection.QueryAsync<OrderItem>(
            "SELECT * FROM OrderItems WHERE OrderId = @OrderId",
            new { OrderId = order.Id }
        )).ToList();
    }
    
    return orders;
}

// GOOD - Single query with join
public async Task<IEnumerable<Order>> GetOrdersGoodAsync()
{
    using var connection = new SqlConnection(_connectionString);
    
    var sql = @"
        SELECT o.*, oi.*
        FROM Orders o
        LEFT JOIN OrderItems oi ON o.Id = oi.OrderId";
    
    var orderDictionary = new Dictionary<int, Order>();
    
    await connection.QueryAsync<Order, OrderItem, Order>(
        sql,
        (order, item) =>
        {
            if (!orderDictionary.TryGetValue(order.Id, out var existingOrder))
            {
                existingOrder = order;
                existingOrder.Items = new List<OrderItem>();
                orderDictionary.Add(order.Id, existingOrder);
            }
            
            if (item != null)
            {
                existingOrder.Items.Add(item);
            }
            
            return existingOrder;
        },
        splitOn: "Id"
    );
    
    return orderDictionary.Values;
}

When to Choose What

Use Dapper when:

Performance is critical
You need complex SQL queries
Working with legacy databases
You prefer SQL over abstractions
Building read-heavy applications

Use Entity Framework when:

Rapid application development
Team prefers working with objects
Need change tracking
Simple CRUD operations dominate
Database migrations needed
Learning ORM concepts

Use Both when:

Use EF for CRUD operations
Use Dapper for complex reporting queries
Best of both worlds approach

Best Practices

1. Connection Management

Always use using statements or await using
Never share connections across threads
Let connection pooling handle optimization
Don't manually open connections unless necessary

2. Parameter Usage

Always use parameters, never string concatenation
Use anonymous objects for simple parameters
Use DynamicParameters for complex scenarios
Be careful with parameter names (case-sensitive)

3. Query Design

Select only columns you need
Use appropriate indexes
Avoid N+1 queries with joins
Consider pagination for large result sets
Use QueryFirstOrDefaultAsync for single results

4. Error Handling

public async Task<User> GetUserWithErrorHandlingAsync(int id)
{
    try
    {
        using var connection = new SqlConnection(_connectionString);
        
        return await connection.QuerySingleOrDefaultAsync<User>(
            "SELECT * FROM Users WHERE Id = @Id",
            new { Id = id }
        );
    }
    catch (SqlException ex)
    {
        _logger.LogError(ex, "Database error retrieving user {UserId}", id);
        throw new DataAccessException("Failed to retrieve user", ex);
    }
    catch (InvalidOperationException ex)
    {
        _logger.LogWarning(ex, "Multiple users found with ID {UserId}", id);
        throw;
    }
}

5. Async All the Way

Use async methods consistently
Don't mix sync and async
Don't block on async code with .Result or .Wait()
Use ConfigureAwait(false) in library code

6. Mapping Considerations

Property names must match column names (or use column aliases)
Use DTOs for query results instead of entities
Consider custom type handlers for complex types
Use [Key] attribute or conventions for identity columns

Real-World Example: E-Commerce Order System

public class OrderService
{
    private readonly string _connectionString;
    private readonly ILogger<OrderService> _logger;
    
    public async Task<OrderDetailsDto> GetOrderDetailsAsync(int orderId)
    {
        using var connection = new SqlConnection(_connectionString);
        
        var sql = @"
            -- Order info
            SELECT o.Id, o.OrderNumber, o.OrderDate, o.Status, o.TotalAmount,
                   c.Id, c.FirstName, c.LastName, c.Email,
                   a.Street, a.City, a.State, a.ZipCode
            FROM Orders o
            INNER JOIN Customers c ON o.CustomerId = c.Id
            INNER JOIN Addresses a ON o.ShippingAddressId = a.Id
            WHERE o.Id = @OrderId;
            
            -- Order items
            SELECT oi.Id, oi.Quantity, oi.UnitPrice,
                   p.Id, p.Name, p.SKU
            FROM OrderItems oi
            INNER JOIN Products p ON oi.ProductId = p.Id
            WHERE oi.OrderId = @OrderId;
            
            -- Payment info
            SELECT PaymentMethod, PaymentStatus, TransactionId
            FROM Payments
            WHERE OrderId = @OrderId;";
        
        using var multi = await connection.QueryMultipleAsync(sql, new { OrderId = orderId });
        
        var order = await multi.ReadAsync<OrderDetailsDto, Customer, Address, OrderDetailsDto>(
            (o, c, a) =>
            {
                o.Customer = c;
                o.ShippingAddress = a;
                return o;
            },
            splitOn: "Id,Street"
        );
        
        var orderDto = order.First();
        orderDto.Items = (await multi.ReadAsync<OrderItemDto, Product, OrderItemDto>(
            (oi, p) =>
            {
                oi.Product = p;
                return oi;
            },
            splitOn: "Id"
        )).ToList();
        
        orderDto.Payment = await multi.ReadSingleOrDefaultAsync<PaymentDto>();
        
        return orderDto;
    }
    
    public async Task<int> CreateOrderAsync(CreateOrderDto orderDto)
    {
        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        
        using var transaction = await connection.BeginTransactionAsync();
        
        try
        {
            // 1. Create order
            var orderSql = @"
                INSERT INTO Orders (OrderNumber, CustomerId, OrderDate, Status, TotalAmount, ShippingAddressId)
                VALUES (@OrderNumber, @CustomerId, GETUTCDATE(), 'Pending', @TotalAmount, @ShippingAddressId);
                SELECT CAST(SCOPE_IDENTITY() AS INT);";
            
            var orderId = await connection.QuerySingleAsync<int>(orderSql, orderDto, transaction);
            
            // 2. Create order items
            var itemsSql = @"
                INSERT INTO OrderItems (OrderId, ProductId, Quantity, UnitPrice)
                VALUES (@OrderId, @ProductId, @Quantity, @UnitPrice)";
            
            var items = orderDto.Items.Select(i => new
            {
                OrderId = orderId,
                i.ProductId,
                i.Quantity,
                i.UnitPrice
            });
            
            await connection.ExecuteAsync(itemsSql, items, transaction);
            
            // 3. Update inventory
            var inventorySql = @"
                UPDATE Products
                SET StockQuantity = StockQuantity - @Quantity
                WHERE Id = @ProductId AND StockQuantity >= @Quantity";
            
            foreach (var item in orderDto.Items)
            {
                var affected = await connection.ExecuteAsync(
                    inventorySql,
                    new { item.ProductId, item.Quantity },
                    transaction
                );
                
                if (affected == 0)
                {
                    throw new InsufficientStockException($"Product {item.ProductId} out of stock");
                }
            }
            
            // 4. Create payment record
            var paymentSql = @"
                INSERT INTO Payments (OrderId, PaymentMethod, PaymentStatus, Amount)
                VALUES (@OrderId, @PaymentMethod, 'Pending', @Amount)";
            
            await connection.ExecuteAsync(
                paymentSql,
                new
                {
                    OrderId = orderId,
                    orderDto.PaymentMethod,
                    Amount = orderDto.TotalAmount
                },
                transaction
            );
            
            await transaction.CommitAsync();
            
            _logger.LogInformation("Order {OrderId} created successfully", orderId);
            
            return orderId;
        }
        catch (Exception ex)
        {
            await transaction.RollbackAsync();
            _logger.LogError(ex, "Failed to create order");
            throw;
        }
    }
}

Quick Reference

Common Query Patterns

// Single result
var user = await connection.QuerySingleAsync<User>(sql, parameters);
var user = await connection.QuerySingleOrDefaultAsync<User>(sql, parameters);
var user = await connection.QueryFirstAsync<User>(sql, parameters);
var user = await connection.QueryFirstOrDefaultAsync<User>(sql, parameters);

// Multiple results
var users = await connection.QueryAsync<User>(sql, parameters);

// Scalar value
var count = await connection.ExecuteScalarAsync<int>(sql, parameters);

// Non-query
var rowsAffected = await connection.ExecuteAsync(sql, parameters);

// Multi-mapping
var results = await connection.QueryAsync<Order, Customer, Order>(
    sql,
    (order, customer) => { order.Customer = customer; return order; },
    splitOn: "Id"
);

// Multiple result sets
using var multi = await connection.QueryMultipleAsync(sql, parameters);
var users = await multi.ReadAsync<User>();
var orders = await multi.ReadAsync<Order>();

Parameter Patterns

// Anonymous object
new { Id = 1, Name = "John" }

// DynamicParameters
var p = new DynamicParameters();
p.Add("@Id", 1);
p.Add("@Name", "John");
p.Add("@Output", dbType: DbType.Int32, direction: ParameterDirection.Output);

// List parameters
new { Ids = new[] { 1, 2, 3 } }

// Custom object
var user = new User { Id = 1, Name = "John" };

Conclusion

Dapper strikes an elegant balance between the raw power of ADO.NET and the convenience of full-featured ORMs. By requiring you to write SQL but automating the tedious mapping work, it delivers exceptional performance while keeping your codebase clean and maintainable.

It's not trying to be everything to everyone—and that's exactly why it excels. When you need speed, SQL control, and minimal overhead, Dapper is the scalpel in your toolkit. When you need rapid development with change tracking and migrations, reach for Entity Framework. And when you need both? Use them together.

The best tool is the one that fits your specific problem. Dapper's popularity and longevity prove that sometimes, doing one thing exceptionally well is better than doing everything adequately.

References and Further Reading

Official Documentation

Dapper

Dapper GitHub Repository - Official source code and documentation
Dapper Tutorial - Comprehensive guides and examples
Dapper Plus - High-performance bulk operations extension

Microsoft

ADO.NET Documentation - Understanding the foundation
Data Access Guidance

Performance Benchmarks

ORM Benchmarks

Industry Resources

Stack Overflow

How Dapper Helps Stack Overflow - Real-world usage at scale
Dapper Tag on Stack Overflow

Books

Essential Reading

C# in Depth by Jon Skeet - Deep dive into C# features that make Dapper possible
Pro ASP.NET Core MVC by Adam Freeman - Includes practical Dapper usage
Designing Data-Intensive Applications by Martin Kleppmann - Context for data access patterns

Community Resources

Blogs and Articles

Sam Saffron's Blog - Dapper co-creator
Marc Gravell's Blog - Dapper co-creator
Khalid Abuhakmeh - .NET data access patterns

Video Tutorials

Attribution

Dapper was created by Sam Saffron and Marc Gravell at Stack Overflow and is maintained by the .NET community. This guide draws upon community best practices and real-world usage patterns.

Cursor Pagination

Complete guide to implementing efficient cursor-based pagination in C# with practical examples, best practices, and real-world use cases.

DbUp - Database Migrations