Dapper ORM Complete Cheat Sheet – .NET Micro ORM Guide

Introduction

Dapper is a lightweight, high-performance micro-ORM for .NET that extends IDbConnection. It provides a simple way to execute SQL queries and map results to strongly-typed objects while maintaining excellent performance and control over your data access layer.

Why Choose Dapper:

• Performance: Near-raw ADO.NET speed with minimal overhead
• Simplicity: Easy to learn and implement with minimal configuration
• Control: Write your own SQL queries for optimal performance
• Flexibility: Works with any database that supports ADO.NET
• Lightweight: Small footprint with no complex mapping configurations

Core Concepts & Principles

Key Components

• IDbConnection Extension Methods: Dapper extends database connections
• Dynamic Parameters: Type-safe parameter binding
• Multi-Mapping: Join multiple tables into complex objects
• Buffered vs Unbuffered: Control memory usage and performance

Fundamental Principles

• SQL-First Approach: You write the SQL, Dapper handles the mapping
• Convention-Based Mapping: Properties match column names by default
• Stateless: No change tracking or lazy loading
• Database Agnostic: Works with SQL Server, MySQL, PostgreSQL, SQLite, Oracle

Setup & Configuration

Installation

# Package Manager Console
Install-Package Dapper

# .NET CLI
dotnet add package Dapper

Basic Setup

using Dapper;
using System.Data.SqlClient;

// Connection string
string connectionString = "Server=.;Database=MyDB;Integrated Security=true;";

// Create connection
using var connection = new SqlConnection(connectionString);

Essential Query Methods

Core Query Operations

Basic CRUD Operations

SELECT Operations

// Get all users
var users = connection.Query<User>("SELECT * FROM Users");

// Get user by ID
var user = connection.QueryFirst<User>(
    "SELECT * FROM Users WHERE Id = @Id", 
    new { Id = 1 });

// Get users with condition
var activeUsers = connection.Query<User>(
    "SELECT * FROM Users WHERE IsActive = @IsActive", 
    new { IsActive = true });

INSERT Operations

// Insert single record
var sql = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
var rowsAffected = connection.Execute(sql, 
    new { Name = "John Doe", Email = "john@example.com" });

// Insert and get ID
var insertSql = @"INSERT INTO Users (Name, Email) 
                  OUTPUT INSERTED.Id 
                  VALUES (@Name, @Email)";
var newId = connection.QuerySingle<int>(insertSql, 
    new { Name = "Jane Doe", Email = "jane@example.com" });

// Bulk insert
var users = new[]
{
    new { Name = "User1", Email = "user1@example.com" },
    new { Name = "User2", Email = "user2@example.com" }
};
connection.Execute(sql, users);

UPDATE Operations

// Update single record
var updateSql = "UPDATE Users SET Name = @Name WHERE Id = @Id";
var rowsAffected = connection.Execute(updateSql, 
    new { Name = "Updated Name", Id = 1 });

// Update multiple records
var users = new[]
{
    new { Id = 1, Name = "Updated User 1" },
    new { Id = 2, Name = "Updated User 2" }
};
connection.Execute(updateSql, users);

DELETE Operations

// Delete by ID
var deleteSql = "DELETE FROM Users WHERE Id = @Id";
var rowsAffected = connection.Execute(deleteSql, new { Id = 1 });

// Delete with condition
var deleteInactiveSql = "DELETE FROM Users WHERE IsActive = 0";
connection.Execute(deleteInactiveSql);

Parameter Handling

Anonymous Objects

var result = connection.Query<User>(
    "SELECT * FROM Users WHERE Age > @MinAge AND City = @City",
    new { MinAge = 18, City = "New York" });

Dynamic Parameters

var parameters = new DynamicParameters();
parameters.Add("@Name", "John Doe");
parameters.Add("@Age", 30);
parameters.Add("@OutputId", dbType: DbType.Int32, direction: ParameterDirection.Output);

connection.Execute("sp_InsertUser", parameters, commandType: CommandType.StoredProcedure);
var newId = parameters.Get<int>("@OutputId");

Parameter Arrays

var ids = new[] { 1, 2, 3, 4, 5 };
var users = connection.Query<User>(
    "SELECT * FROM Users WHERE Id IN @Ids",
    new { Ids = ids });

Advanced Mapping Techniques

Multi-Mapping (Joins)

// One-to-One mapping
var sql = @"SELECT u.*, p.* 
            FROM Users u 
            INNER JOIN Profiles p ON u.Id = p.UserId";

var users = connection.Query<User, Profile, User>(sql,
    (user, profile) =>
    {
        user.Profile = profile;
        return user;
    },
    splitOn: "Id"); // Column that separates User from Profile

One-to-Many Mapping

var sql = @"SELECT u.*, o.* 
            FROM Users u 
            LEFT JOIN Orders o ON u.Id = o.UserId 
            ORDER BY u.Id";

var userDict = new Dictionary<int, User>();
var users = connection.Query<User, Order, User>(sql,
    (user, order) =>
    {
        if (!userDict.TryGetValue(user.Id, out var existingUser))
        {
            existingUser = user;
            existingUser.Orders = new List<Order>();
            userDict.Add(user.Id, existingUser);
        }
        
        if (order != null)
            existingUser.Orders.Add(order);
            
        return existingUser;
    },
    splitOn: "Id");
    
return userDict.Values;

Custom Type Mapping

// Map to different property names
SqlMapper.SetTypeMap(typeof(User), new ColumnAttributeTypeMapper<User>());

public class User
{
    [Column("user_id")]
    public int Id { get; set; }
    
    [Column("user_name")]
    public string Name { get; set; }
}

Working with Stored Procedures

Basic Stored Procedure Call

var users = connection.Query<User>("sp_GetActiveUsers", 
    commandType: CommandType.StoredProcedure);

Stored Procedure with Parameters

var parameters = new DynamicParameters();
parameters.Add("@MinAge", 18);
parameters.Add("@City", "New York");
parameters.Add("@TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

var users = connection.Query<User>("sp_GetUsersByAgeAndCity", 
    parameters, commandType: CommandType.StoredProcedure);

var totalCount = parameters.Get<int>("@TotalCount");

Transaction Management

Basic Transactions

using var transaction = connection.BeginTransaction();
try
{
    connection.Execute("INSERT INTO Users (Name) VALUES (@Name)", 
        new { Name = "User1" }, transaction);
    
    connection.Execute("INSERT INTO Profiles (UserId, Bio) VALUES (@UserId, @Bio)", 
        new { UserId = 1, Bio = "Bio text" }, transaction);
    
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Async Transactions

using var transaction = await connection.BeginTransactionAsync();
try
{
    await connection.ExecuteAsync("INSERT INTO Users (Name) VALUES (@Name)", 
        new { Name = "User1" }, transaction);
        
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Async Operations

Async Query Methods

// Async equivalents of all sync methods
var users = await connection.QueryAsync<User>("SELECT * FROM Users");
var user = await connection.QueryFirstAsync<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = 1 });
var rowsAffected = await connection.ExecuteAsync("UPDATE Users SET Name = @Name WHERE Id = @Id", 
    new { Name = "New Name", Id = 1 });

Async with Cancellation

var cancellationToken = new CancellationTokenSource(TimeSpan.FromSeconds(30)).Token;
var users = await connection.QueryAsync<User>("SELECT * FROM Users", 
    cancellationToken: cancellationToken);

Performance Optimization

Query Performance Comparison

Buffered vs Unbuffered

// Buffered (default) - loads all results into memory
var users = connection.Query<User>("SELECT * FROM Users");

// Unbuffered - streams results
var users = connection.Query<User>("SELECT * FROM Users", buffered: false);

Connection Pooling Best Practices

// Use connection pooling effectively
using var connection = new SqlConnection(connectionString);
// Don't call connection.Open() - Dapper opens as needed

// For multiple operations, consider keeping connection open
connection.Open();
try
{
    var users = connection.Query<User>("SELECT * FROM Users");
    var orders = connection.Query<Order>("SELECT * FROM Orders");
}
finally
{
    connection.Close();
}

Common Challenges & Solutions

Challenge: Dynamic SQL Generation

Problem: Building queries dynamically based on conditions

Solution: Use SqlBuilder or conditional SQL

var builder = new SqlBuilder();
var template = builder.AddTemplate("SELECT * FROM Users /**where**/");

if (!string.IsNullOrEmpty(name))
    builder.Where("Name LIKE @Name", new { Name = $"%{name}%" });

if (minAge.HasValue)
    builder.Where("Age >= @MinAge", new { MinAge = minAge });

var users = connection.Query<User>(template.RawSql, template.Parameters);

Challenge: Handling NULL Values

Problem: Database NULL values causing mapping issues

Solution: Use nullable types and null coalescing

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime? LastLogin { get; set; } // Nullable for NULL values
    public string Email { get; set; } = string.Empty; // Default for NULL strings
}

Challenge: Large Result Sets

Problem: Memory issues with large datasets

Solution: Use unbuffered queries or paging

// Unbuffered approach
var users = connection.Query<User>("SELECT * FROM Users", buffered: false);

// Paging approach
var pageSize = 1000;
var offset = pageNumber * pageSize;
var users = connection.Query<User>(
    "SELECT * FROM Users ORDER BY Id OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY",
    new { Offset = offset, PageSize = pageSize });

Best Practices & Tips

Query Writing Best Practices

• Use parameterized queries to prevent SQL injection
• Specify column names instead of SELECT * for better performance
• Use appropriate indexes for WHERE clause columns
• Consider query plans for complex joins

Parameter Best Practices

// ✅ Good: Use parameters
var users = connection.Query<User>("SELECT * FROM Users WHERE Name = @Name", new { Name = userName });

// ❌ Bad: String concatenation (SQL injection risk)
var users = connection.Query<User>($"SELECT * FROM Users WHERE Name = '{userName}'");

Connection Management

// ✅ Good: Use using statements
using var connection = new SqlConnection(connectionString);

// ✅ Good: Let Dapper manage connection state
var users = connection.Query<User>("SELECT * FROM Users");

// ⚠️ Optional: Manual connection management for multiple operations
connection.Open();
try
{
    // Multiple operations
}
finally
{
    connection.Close();
}

Error Handling

try
{
    var users = connection.Query<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = userId });
}
catch (SqlException ex)
{
    // Handle database-specific errors
    logger.LogError(ex, "Database error occurred");
    throw;
}
catch (InvalidOperationException ex)
{
    // Handle Dapper mapping errors
    logger.LogError(ex, "Mapping error occurred");
    throw;
}

Configuration Tips

// Set command timeout globally
SqlMapper.Settings.CommandTimeout = 30;

// Configure type mapping
SqlMapper.AddTypeHandler(new JsonTypeHandler<List<string>>());

Dapper vs Other ORMs

Quick Reference Commands

Essential Queries

// Single record
var user = connection.QueryFirst<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = 1 });

// Multiple records
var users = connection.Query<User>("SELECT * FROM Users WHERE IsActive = 1");

// Execute command
var rows = connection.Execute("UPDATE Users SET LastLogin = @Now WHERE Id = @Id", 
    new { Now = DateTime.Now, Id = 1 });

// Scalar value
var count = connection.ExecuteScalar<int>("SELECT COUNT(*) FROM Users");

Common Patterns

// Repository pattern
public class UserRepository
{
    private readonly IDbConnection _connection;
    
    public UserRepository(IDbConnection connection)
    {
        _connection = connection;
    }
    
    public async Task<User> GetByIdAsync(int id)
    {
        return await _connection.QueryFirstOrDefaultAsync<User>(
            "SELECT * FROM Users WHERE Id = @Id", new { Id = id });
    }
}

Resources for Further Learning

Official Documentation

• Dapper GitHub Repository: https://github.com/DapperLib/Dapper
• Dapper Tutorial: https://dapper-tutorial.net/
• Microsoft Docs: Search for “Dapper micro ORM”

Advanced Topics

• Custom Type Handlers: For complex type mapping
• SqlMapper.GridReader: For multiple result sets
• Dapper Extensions: Additional functionality
• Dapper.Contrib: Simplified CRUD operations

Performance Resources

• Dapper Performance Tests: Compare with other ORMs
• SQL Server Execution Plans: Optimize your queries
• Profiling Tools: SQL Server Profiler, MiniProfiler

Community Resources

• Stack Overflow: Tag “dapper”
• Reddit: r/dotnet discussions
• YouTube: Dapper tutorial videos
• Blogs: .NET community blogs and tutorials

Remember: Dapper is about simplicity and performance. Write good SQL, use parameters, and let Dapper handle the object mapping efficiently!