Library

Dapper

Overview

Dapper is a lightweight and high-performance micro-ORM (Object-Relational Mapping) library for .NET, developed as a "simple object mapper for .NET". Actually used and proven in Stack Overflow's large-scale traffic site, it's a library with demonstrated performance. While allowing raw SQL writing, it provides automatic mapping with C# objects, achieving overwhelmingly faster execution speed and lower memory usage compared to Entity Framework and NHibernate, making it a practical data access solution focused on utility.

Details

Dapper 2025 edition leverages the latest features of .NET 8+ and achieves further performance improvements through compile-time optimization with Source Generators and complete support for Async/Await patterns. Implemented as extension methods for IDbConnection, it integrates easily with existing ADO.NET code while minimizing learning costs for adoption. It standardly supports prepared statements, parameterized queries, bulk operations, and multi-mapping functionality, balancing SQL injection protection and type safety. No complex ORM configuration or mapping definitions are required, enabling rapid development through intuitive APIs.

Key Features

• Ultra-fast Execution: Performance close to ADO.NET with minimal overhead
• Simple API: Low learning cost with intuitive method collections
• Type Safety: Compile-time checking leveraging C#'s type system
• Multi-database: Support for SQL Server, MySQL, PostgreSQL, SQLite
• Async Support: Complete Async/Await pattern support
• Rich Features: Bulk operations, multi-mapping, stored procedures

Pros and Cons

Pros

• Significantly faster execution performance than Entity Framework or NHibernate
• Lightweight with minimal dependencies, minimal impact on project size
• Can write raw SQL directly, enabling complex queries to be implemented as desired
• Easy integration with existing ADO.NET code allowing gradual migration
• Rich documentation and community support, proven by Stack Overflow
• Wide version support from .NET Core to .NET 8+

Cons

• Not a complete ORM, requiring manual implementation for relationship management
• No automatic migration or change tracking features provided
• Unsuitable for complex object graph operations
• SQL knowledge required, no high-level abstraction like LINQ to SQL provided
• More suited for database-first development than code-first development
• Additional considerations needed for SQL quality management in large team development

Reference Pages

• Dapper GitHub
• Dapper Documentation
• NuGet Package

Code Examples

Basic Setup

using Dapper;
using System.Data;
using System.Data.SqlClient;

// Connection string configuration
private readonly string connectionString = "Server=localhost;Database=TestDB;Trusted_Connection=true;";

// Get database connection
private IDbConnection GetConnection()
{
    return new SqlConnection(connectionString);
}

// Basic usage example
public void BasicExample()
{
    using var connection = GetConnection();
    connection.Open();
    
    // Simple query execution
    var users = connection.Query<User>("SELECT * FROM Users WHERE Age > @age", new { age = 18 });
    
    foreach (var user in users)
    {
        Console.WriteLine($"Name: {user.Name}, Email: {user.Email}");
    }
}

Model Definition and Basic Operations

// User model definition
public class User
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public int Age { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime? UpdatedAt { get; set; }
}

// User service class
public class UserService
{
    private readonly string _connectionString;

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

    private IDbConnection GetConnection() => new SqlConnection(_connectionString);

    // Create user
    public async Task<int> CreateUserAsync(User user)
    {
        const string sql = @"
            INSERT INTO Users (Name, Email, Age, CreatedAt) 
            VALUES (@Name, @Email, @Age, @CreatedAt);
            SELECT CAST(SCOPE_IDENTITY() as int);";

        using var connection = GetConnection();
        user.CreatedAt = DateTime.UtcNow;
        
        var id = await connection.QuerySingleAsync<int>(sql, user);
        return id;
    }

    // Get user (single)
    public async Task<User?> GetUserByIdAsync(int id)
    {
        const string sql = "SELECT * FROM Users WHERE Id = @id";
        
        using var connection = GetConnection();
        return await connection.QuerySingleOrDefaultAsync<User>(sql, new { id });
    }

    // Get all users
    public async Task<IEnumerable<User>> GetAllUsersAsync()
    {
        const string sql = "SELECT * FROM Users ORDER BY CreatedAt DESC";
        
        using var connection = GetConnection();
        return await connection.QueryAsync<User>(sql);
    }

    // Update user
    public async Task<bool> UpdateUserAsync(User user)
    {
        const string sql = @"
            UPDATE Users 
            SET Name = @Name, Email = @Email, Age = @Age, UpdatedAt = @UpdatedAt 
            WHERE Id = @Id";

        using var connection = GetConnection();
        user.UpdatedAt = DateTime.UtcNow;
        
        var rowsAffected = await connection.ExecuteAsync(sql, user);
        return rowsAffected > 0;
    }

    // Delete user
    public async Task<bool> DeleteUserAsync(int id)
    {
        const string sql = "DELETE FROM Users WHERE Id = @id";
        
        using var connection = GetConnection();
        var rowsAffected = await connection.ExecuteAsync(sql, new { id });
        return rowsAffected > 0;
    }

    // Conditional search
    public async Task<IEnumerable<User>> GetUsersByAgeRangeAsync(int minAge, int maxAge)
    {
        const string sql = @"
            SELECT * FROM Users 
            WHERE Age BETWEEN @minAge AND @maxAge 
            ORDER BY Age";

        using var connection = GetConnection();
        return await connection.QueryAsync<User>(sql, new { minAge, maxAge });
    }

    // Pattern matching search
    public async Task<IEnumerable<User>> SearchUsersAsync(string searchTerm)
    {
        const string sql = @"
            SELECT * FROM Users 
            WHERE Name LIKE @searchTerm OR Email LIKE @searchTerm
            ORDER BY Name";

        using var connection = GetConnection();
        var parameter = new { searchTerm = $"%{searchTerm}%" };
        return await connection.QueryAsync<User>(sql, parameter);
    }
}

Advanced Query Operations

public class AdvancedUserOperations
{
    private readonly string _connectionString;

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

    private IDbConnection GetConnection() => new SqlConnection(_connectionString);

    // Pagination
    public async Task<(IEnumerable<User> Users, int TotalCount)> GetUsersPaginatedAsync(
        int page, int pageSize, string? sortBy = "Id", bool ascending = true)
    {
        var sortDirection = ascending ? "ASC" : "DESC";
        var offset = (page - 1) * pageSize;

        var sql = $@"
            SELECT * FROM Users 
            ORDER BY {sortBy} {sortDirection}
            OFFSET @offset ROWS 
            FETCH NEXT @pageSize ROWS ONLY;

            SELECT COUNT(*) FROM Users;";

        using var connection = GetConnection();
        using var multi = await connection.QueryMultipleAsync(sql, new { offset, pageSize });
        
        var users = await multi.ReadAsync<User>();
        var totalCount = await multi.ReadSingleAsync<int>();

        return (users, totalCount);
    }

    // Dynamic query building
    public async Task<IEnumerable<User>> SearchUsersWithFiltersAsync(UserSearchFilter filter)
    {
        var conditions = new List<string>();
        var parameters = new DynamicParameters();

        if (!string.IsNullOrEmpty(filter.Name))
        {
            conditions.Add("Name LIKE @name");
            parameters.Add("name", $"%{filter.Name}%");
        }

        if (!string.IsNullOrEmpty(filter.Email))
        {
            conditions.Add("Email LIKE @email");
            parameters.Add("email", $"%{filter.Email}%");
        }

        if (filter.MinAge.HasValue)
        {
            conditions.Add("Age >= @minAge");
            parameters.Add("minAge", filter.MinAge.Value);
        }

        if (filter.MaxAge.HasValue)
        {
            conditions.Add("Age <= @maxAge");
            parameters.Add("maxAge", filter.MaxAge.Value);
        }

        if (filter.CreatedAfter.HasValue)
        {
            conditions.Add("CreatedAt >= @createdAfter");
            parameters.Add("createdAfter", filter.CreatedAfter.Value);
        }

        var whereClause = conditions.Any() ? "WHERE " + string.Join(" AND ", conditions) : "";
        var sql = $"SELECT * FROM Users {whereClause} ORDER BY CreatedAt DESC";

        using var connection = GetConnection();
        return await connection.QueryAsync<User>(sql, parameters);
    }

    // Aggregate queries
    public async Task<UserStatistics> GetUserStatisticsAsync()
    {
        const string sql = @"
            SELECT 
                COUNT(*) as TotalUsers,
                AVG(CAST(Age as FLOAT)) as AverageAge,
                MIN(Age) as MinAge,
                MAX(Age) as MaxAge,
                COUNT(CASE WHEN Age < 30 THEN 1 END) as UsersUnder30,
                COUNT(CASE WHEN Age BETWEEN 30 AND 50 THEN 1 END) as UsersBetween30And50,
                COUNT(CASE WHEN Age > 50 THEN 1 END) as UsersOver50
            FROM Users";

        using var connection = GetConnection();
        return await connection.QuerySingleAsync<UserStatistics>(sql);
    }

    // Bulk operations
    public async Task<int> BulkInsertUsersAsync(IEnumerable<User> users)
    {
        const string sql = @"
            INSERT INTO Users (Name, Email, Age, CreatedAt) 
            VALUES (@Name, @Email, @Age, @CreatedAt)";

        using var connection = GetConnection();
        
        // Set creation date for all users
        var usersWithDate = users.Select(u => 
        {
            u.CreatedAt = DateTime.UtcNow;
            return u;
        });

        return await connection.ExecuteAsync(sql, usersWithDate);
    }

    // Transaction processing
    public async Task<bool> TransferUserDataAsync(int sourceUserId, int targetUserId)
    {
        using var connection = GetConnection();
        connection.Open();
        using var transaction = connection.BeginTransaction();

        try
        {
            // Get source user data
            const string selectSql = "SELECT * FROM Users WHERE Id = @id";
            var sourceUser = await connection.QuerySingleOrDefaultAsync<User>(
                selectSql, new { id = sourceUserId }, transaction);

            if (sourceUser == null)
                return false;

            // Update target user
            const string updateSql = @"
                UPDATE Users 
                SET Name = @Name, Email = @Email, UpdatedAt = @UpdatedAt 
                WHERE Id = @targetId";

            await connection.ExecuteAsync(updateSql, new 
            { 
                sourceUser.Name, 
                sourceUser.Email, 
                UpdatedAt = DateTime.UtcNow,
                targetId = targetUserId 
            }, transaction);

            // Delete source user
            const string deleteSql = "DELETE FROM Users WHERE Id = @id";
            await connection.ExecuteAsync(deleteSql, new { id = sourceUserId }, transaction);

            // Log entry
            const string logSql = @"
                INSERT INTO UserTransferLog (SourceUserId, TargetUserId, TransferredAt) 
                VALUES (@sourceUserId, @targetUserId, @transferredAt)";

            await connection.ExecuteAsync(logSql, new 
            { 
                sourceUserId, 
                targetUserId, 
                transferredAt = DateTime.UtcNow 
            }, transaction);

            transaction.Commit();
            return true;
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }
}

// Search filter class
public class UserSearchFilter
{
    public string? Name { get; set; }
    public string? Email { get; set; }
    public int? MinAge { get; set; }
    public int? MaxAge { get; set; }
    public DateTime? CreatedAfter { get; set; }
}

// Statistics data class
public class UserStatistics
{
    public int TotalUsers { get; set; }
    public double AverageAge { get; set; }
    public int MinAge { get; set; }
    public int MaxAge { get; set; }
    public int UsersUnder30 { get; set; }
    public int UsersBetween30And50 { get; set; }
    public int UsersOver50 { get; set; }
}

Relationship Operations

// Post model
public class Post
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string Content { get; set; } = string.Empty;
    public int UserId { get; set; }
    public bool IsPublished { get; set; }
    public DateTime CreatedAt { get; set; }
    
    // Navigation property (manually managed in Dapper)
    public User? User { get; set; }
}

// Composite object for user and posts
public class UserWithPosts
{
    public User User { get; set; } = new();
    public List<Post> Posts { get; set; } = new();
}

public class PostService
{
    private readonly string _connectionString;

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

    private IDbConnection GetConnection() => new SqlConnection(_connectionString);

    // Get posts with users using multi-mapping
    public async Task<IEnumerable<Post>> GetPostsWithUsersAsync()
    {
        const string sql = @"
            SELECT p.*, u.Id, u.Name, u.Email, u.Age, u.CreatedAt, u.UpdatedAt
            FROM Posts p
            INNER JOIN Users u ON p.UserId = u.Id
            ORDER BY p.CreatedAt DESC";

        using var connection = GetConnection();
        
        return await connection.QueryAsync<Post, User, Post>(
            sql,
            (post, user) =>
            {
                post.User = user;
                return post;
            },
            splitOn: "Id");
    }

    // Get specific user's posts
    public async Task<UserWithPosts?> GetUserWithPostsAsync(int userId)
    {
        const string sql = @"
            SELECT * FROM Users WHERE Id = @userId;
            SELECT * FROM Posts WHERE UserId = @userId ORDER BY CreatedAt DESC;";

        using var connection = GetConnection();
        using var multi = await connection.QueryMultipleAsync(sql, new { userId });

        var user = await multi.ReadSingleOrDefaultAsync<User>();
        if (user == null) return null;

        var posts = (await multi.ReadAsync<Post>()).ToList();

        return new UserWithPosts
        {
            User = user,
            Posts = posts
        };
    }

    // Get hierarchical data (using Dictionary)
    public async Task<IEnumerable<UserWithPosts>> GetAllUsersWithPostsAsync()
    {
        const string sql = @"
            SELECT u.*, p.Id as PostId, p.Title, p.Content, p.UserId, p.IsPublished, p.CreatedAt as PostCreatedAt
            FROM Users u
            LEFT JOIN Posts p ON u.Id = p.UserId
            ORDER BY u.Id, p.CreatedAt DESC";

        using var connection = GetConnection();
        
        var userDictionary = new Dictionary<int, UserWithPosts>();

        await connection.QueryAsync<User, Post, UserWithPosts>(
            sql,
            (user, post) =>
            {
                if (!userDictionary.TryGetValue(user.Id, out var userWithPosts))
                {
                    userWithPosts = new UserWithPosts { User = user };
                    userDictionary[user.Id] = userWithPosts;
                }

                if (post != null)
                {
                    userWithPosts.Posts.Add(post);
                }

                return userWithPosts;
            },
            splitOn: "PostId");

        return userDictionary.Values;
    }

    // Post statistics
    public async Task<IEnumerable<UserPostStatistics>> GetUserPostStatisticsAsync()
    {
        const string sql = @"
            SELECT 
                u.Id,
                u.Name,
                u.Email,
                COUNT(p.Id) as TotalPosts,
                COUNT(CASE WHEN p.IsPublished = 1 THEN 1 END) as PublishedPosts,
                COUNT(CASE WHEN p.IsPublished = 0 THEN 1 END) as DraftPosts,
                MAX(p.CreatedAt) as LastPostDate
            FROM Users u
            LEFT JOIN Posts p ON u.Id = p.UserId
            GROUP BY u.Id, u.Name, u.Email
            ORDER BY TotalPosts DESC";

        using var connection = GetConnection();
        return await connection.QueryAsync<UserPostStatistics>(sql);
    }

    // Complex search (find users by post content)
    public async Task<IEnumerable<User>> FindUsersByPostContentAsync(string searchTerm)
    {
        const string sql = @"
            SELECT DISTINCT u.*
            FROM Users u
            INNER JOIN Posts p ON u.Id = p.UserId
            WHERE p.Title LIKE @searchTerm 
               OR p.Content LIKE @searchTerm
            ORDER BY u.Name";

        using var connection = GetConnection();
        var parameter = new { searchTerm = $"%{searchTerm}%" };
        return await connection.QueryAsync<User>(sql, parameter);
    }
}

// Statistics data class
public class UserPostStatistics
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public int TotalPosts { get; set; }
    public int PublishedPosts { get; set; }
    public int DraftPosts { get; set; }
    public DateTime? LastPostDate { get; set; }
}

Practical Examples

// Practical application example
public class BlogApplicationService
{
    private readonly string _connectionString;
    private readonly ILogger<BlogApplicationService> _logger;

    public BlogApplicationService(string connectionString, ILogger<BlogApplicationService> logger)
    {
        _connectionString = connectionString;
        _logger = logger;
    }

    private IDbConnection GetConnection() => new SqlConnection(_connectionString);

    // Get dashboard data
    public async Task<DashboardData> GetDashboardDataAsync()
    {
        const string sql = @"
            -- User statistics
            SELECT COUNT(*) as TotalUsers FROM Users;
            
            -- Post statistics
            SELECT 
                COUNT(*) as TotalPosts,
                COUNT(CASE WHEN IsPublished = 1 THEN 1 END) as PublishedPosts,
                COUNT(CASE WHEN IsPublished = 0 THEN 1 END) as DraftPosts
            FROM Posts;
            
            -- Recent posts
            SELECT TOP 5 p.*, u.Name as UserName
            FROM Posts p
            INNER JOIN Users u ON p.UserId = u.Id
            WHERE p.IsPublished = 1
            ORDER BY p.CreatedAt DESC;
            
            -- Monthly post counts (last 12 months)
            SELECT 
                YEAR(CreatedAt) as Year,
                MONTH(CreatedAt) as Month,
                COUNT(*) as PostCount
            FROM Posts
            WHERE CreatedAt >= DATEADD(MONTH, -12, GETDATE())
            GROUP BY YEAR(CreatedAt), MONTH(CreatedAt)
            ORDER BY Year, Month;";

        using var connection = GetConnection();
        using var multi = await connection.QueryMultipleAsync(sql);

        var userStats = await multi.ReadSingleAsync<dynamic>();
        var postStats = await multi.ReadSingleAsync<dynamic>();
        var recentPosts = await multi.ReadAsync<dynamic>();
        var monthlyStats = await multi.ReadAsync<dynamic>();

        return new DashboardData
        {
            TotalUsers = userStats.TotalUsers,
            TotalPosts = postStats.TotalPosts,
            PublishedPosts = postStats.PublishedPosts,
            DraftPosts = postStats.DraftPosts,
            RecentPosts = recentPosts.ToList(),
            MonthlyPostCounts = monthlyStats.ToList()
        };
    }

    // Stored procedure execution example
    public async Task<IEnumerable<dynamic>> ExecuteReportProcedureAsync(DateTime startDate, DateTime endDate)
    {
        using var connection = GetConnection();
        
        var parameters = new DynamicParameters();
        parameters.Add("@StartDate", startDate);
        parameters.Add("@EndDate", endDate);
        parameters.Add("@TotalRecords", dbType: DbType.Int32, direction: ParameterDirection.Output);

        var result = await connection.QueryAsync(
            "sp_GenerateUserActivityReport",
            parameters,
            commandType: CommandType.StoredProcedure);

        var totalRecords = parameters.Get<int>("@TotalRecords");
        _logger.LogInformation("Report generated with {TotalRecords} records", totalRecords);

        return result;
    }

    // Efficient processing of large datasets
    public async Task ProcessLargeDatasetAsync(Func<User, Task> processor)
    {
        const string sql = "SELECT * FROM Users ORDER BY Id";
        const int batchSize = 1000;
        int offset = 0;

        using var connection = GetConnection();

        while (true)
        {
            var batchSql = $"{sql} OFFSET {offset} ROWS FETCH NEXT {batchSize} ROWS ONLY";
            var users = await connection.QueryAsync<User>(batchSql);

            if (!users.Any()) break;

            var tasks = users.Select(processor);
            await Task.WhenAll(tasks);

            offset += batchSize;
            _logger.LogInformation("Processed batch of {BatchSize} users, offset: {Offset}", batchSize, offset);
        }
    }

    // Error handling and retry functionality
    public async Task<T?> ExecuteWithRetryAsync<T>(string sql, object? parameters = null, int maxRetries = 3)
    {
        for (int retry = 0; retry <= maxRetries; retry++)
        {
            try
            {
                using var connection = GetConnection();
                return await connection.QuerySingleOrDefaultAsync<T>(sql, parameters);
            }
            catch (SqlException ex) when (retry < maxRetries && IsTransientError(ex))
            {
                var delay = TimeSpan.FromSeconds(Math.Pow(2, retry)); // Exponential backoff
                _logger.LogWarning("Database operation failed (attempt {Retry}/{MaxRetries}). Retrying in {Delay}s. Error: {Error}", 
                    retry + 1, maxRetries + 1, delay.TotalSeconds, ex.Message);
                
                await Task.Delay(delay);
            }
        }

        return default;
    }

    private static bool IsTransientError(SqlException ex)
    {
        // Check SQL Server transient error codes
        var transientErrorNumbers = new[] { 2, 53, 121, 233, 10053, 10054, 10060, 40197, 40501, 40613 };
        return transientErrorNumbers.Contains(ex.Number);
    }

    // Cached data retrieval
    public async Task<IEnumerable<User>> GetCachedUsersAsync(IMemoryCache cache, TimeSpan? cacheExpiry = null)
    {
        const string cacheKey = "all_users";
        
        if (cache.TryGetValue(cacheKey, out IEnumerable<User>? cachedUsers) && cachedUsers != null)
        {
            _logger.LogInformation("Returning cached user data");
            return cachedUsers;
        }

        const string sql = "SELECT * FROM Users ORDER BY Name";
        using var connection = GetConnection();
        var users = await connection.QueryAsync<User>(sql);

        var options = new MemoryCacheEntryOptions
        {
            AbsoluteExpirationRelativeToNow = cacheExpiry ?? TimeSpan.FromMinutes(15)
        };

        cache.Set(cacheKey, users, options);
        _logger.LogInformation("User data cached for {CacheExpiry} minutes", options.AbsoluteExpirationRelativeToNow?.TotalMinutes);

        return users;
    }

    // Query execution with performance monitoring
    public async Task<T> ExecuteWithPerformanceLoggingAsync<T>(string sql, object? parameters = null)
    {
        var stopwatch = Stopwatch.StartNew();
        
        try
        {
            using var connection = GetConnection();
            var result = await connection.QuerySingleAsync<T>(sql, parameters);
            
            stopwatch.Stop();
            _logger.LogInformation("Query executed in {ElapsedMs}ms: {Sql}", 
                stopwatch.ElapsedMilliseconds, sql.Substring(0, Math.Min(sql.Length, 100)));
            
            return result;
        }
        catch (Exception ex)
        {
            stopwatch.Stop();
            _logger.LogError(ex, "Query failed after {ElapsedMs}ms: {Sql}", 
                stopwatch.ElapsedMilliseconds, sql.Substring(0, Math.Min(sql.Length, 100)));
            throw;
        }
    }
}

// Dashboard data class
public class DashboardData
{
    public int TotalUsers { get; set; }
    public int TotalPosts { get; set; }
    public int PublishedPosts { get; set; }
    public int DraftPosts { get; set; }
    public List<dynamic> RecentPosts { get; set; } = new();
    public List<dynamic> MonthlyPostCounts { get; set; } = new();
}

// Service registration example with dependency injection (ASP.NET Core)
public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddScoped<UserService>();
        services.AddScoped<PostService>();
        services.AddScoped<BlogApplicationService>();
        services.AddScoped<AdvancedUserOperations>();
        
        // Connection string configuration
        services.AddScoped<string>(provider => 
            provider.GetService<IConfiguration>()?.GetConnectionString("DefaultConnection") ?? "");
    }
}