跳至主要內容

Dapper轻量ORM实战

SunnyFan大约 18 分钟约 5524 字

Dapper轻量ORM实战

简介

Dapper是Stack Overflow团队开发的一个轻量级ORM（Object-Relational Mapper），作为ADO.NET的扩展库，它在原生IDbConnection接口上提供了一系列扩展方法。Dapper的核心设计理念是"简单、快速、不侵入"，它不会改变你的SQL写法，也不会尝试把数据库操作完全抽象化。相比EF Core的全功能ORM路线，Dapper更接近原生SQL，性能接近手写ADO.NET，是目前.NET生态中最流行的Micro ORM。

特点

优点

缺点

安装与基础配置

NuGet 安装

# 核心包
dotnet add package Dapper

# Dapper本身不包含数据库驱动，需要额外安装对应驱动
# SQL Server
dotnet add package Microsoft.Data.SqlClient

# MySQL
dotnet add package MySqlConnector

# PostgreSQL
dotnet add package Npgsql

# SQLite
dotnet add package Microsoft.Data.Sqlite

ASP.NET Core 注册配置

/// <summary>
/// Program.cs 中注册数据库连接
/// </summary>
var builder = WebApplication.CreateBuilder(args);

// 注册 IDbConnection 工厂
builder.Services.AddScoped<IDbConnection>(sp =>
{
    var config = sp.GetRequiredService<IConfiguration>();
    return new SqlConnection(config.GetConnectionString("Default"));
});

// 或者注册自定义的连接工厂
builder.Services.AddScoped<IDbConnectionFactory, SqlConnectionFactory>();

var app = builder.Build();
app.Run();

/// <summary>
/// 数据库连接工厂接口
/// </summary>
public interface IDbConnectionFactory
{
    IDbConnection CreateConnection();
}

/// <summary>
/// SQL Server 连接工厂实现
/// </summary>
public class SqlConnectionFactory : IDbConnectionFactory
{
    private readonly string _connectionString;

    public SqlConnectionFactory(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("Default")
            ?? throw new InvalidOperationException("数据库连接字符串未配置");
    }

    public IDbConnection CreateConnection()
    {
        return new SqlConnection(_connectionString);
    }
}

基础 CRUD 操作

实体定义

/// <summary>
/// 产品实体
/// </summary>
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Category { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int Stock { get; set; }
    public string Description { get; set; } = string.Empty;
    public bool IsActive { get; set; } = true;
    public DateTime CreatedAt { get; set; }
    public DateTime? UpdatedAt { get; set; }
}

新增（Insert）

/// <summary>
/// Dapper 新增操作
/// </summary>
public class ProductRepository
{
    private readonly IDbConnection _db;

    public ProductRepository(IDbConnection db)
    {
        _db = db;
    }

    // 1. 基本插入
    public async Task<int> InsertAsync(Product product)
    {
        var sql = @"
            INSERT INTO Products (Name, Category, Price, Stock, Description, IsActive, CreatedAt)
            VALUES (@Name, @Category, @Price, @Stock, @Description, @IsActive, @CreatedAt)";

        return await _db.ExecuteAsync(sql, product);
    }

    // 2. 插入并返回自增ID
    public async Task<int> InsertAndGetIdAsync(Product product)
    {
        var sql = @"
            INSERT INTO Products (Name, Category, Price, Stock, Description, IsActive, CreatedAt)
            VALUES (@Name, @Category, @Price, @Stock, @Description, @IsActive, @CreatedAt);
            SELECT CAST(SCOPE_IDENTITY() AS INT);";

        // QuerySingleAsync 返回单个值
        return await _db.QuerySingleAsync<int>(sql, product);
    }

    // 3. 使用匿名对象插入（选择性字段）
    public async Task<int> InsertPartialAsync(string name, decimal price)
    {
        var sql = @"
            INSERT INTO Products (Name, Price, Category, Stock, IsActive, CreatedAt)
            VALUES (@Name, @Price, '未分类', 0, 1, GETUTCDATE())";

        return await _db.ExecuteAsync(sql, new { Name = name, Price = price });
    }
}

查询（Query）

/// <summary>
/// Dapper 查询操作
/// </summary>
public class ProductQueryRepository
{
    private readonly IDbConnection _db;

    public ProductQueryRepository(IDbConnection db)
    {
        _db = db;
    }

    // 1. 查询所有
    public async Task<IEnumerable<Product>> GetAllAsync()
    {
        var sql = "SELECT * FROM Products WHERE IsActive = 1 ORDER BY Id DESC";
        return await _db.QueryAsync<Product>(sql);
    }

    // 2. 根据ID查询单条
    public async Task<Product?> GetByIdAsync(int id)
    {
        var sql = "SELECT * FROM Products WHERE Id = @Id";
        return await _db.QueryFirstOrDefaultAsync<Product>(sql, new { Id = id });
    }

    // 3. 条件查询
    public async Task<IEnumerable<Product>> SearchAsync(string keyword, string category)
    {
        var sql = @"
            SELECT * FROM Products
            WHERE IsActive = 1
              AND (@Keyword IS NULL OR Name LIKE '%' + @Keyword + '%')
              AND (@Category IS NULL OR Category = @Category)
            ORDER BY CreatedAt DESC";

        return await _db.QueryAsync<Product>(sql, new
        {
            Keyword = string.IsNullOrEmpty(keyword) ? null : keyword,
            Category = string.IsNullOrEmpty(category) ? null : category
        });
    }

    // 4. 查询标量值
    public async Task<int> CountAsync()
    {
        var sql = "SELECT COUNT(*) FROM Products WHERE IsActive = 1";
        return await _db.ExecuteScalarAsync<int>(sql);
    }

    // 5. 查询单个字段
    public async Task<string> GetNameByIdAsync(int id)
    {
        var sql = "SELECT Name FROM Products WHERE Id = @Id";
        return await _db.ExecuteScalarAsync<string>(sql, new { Id = id });
    }

    // 6. 分页查询
    public async Task<PagedResult<Product>> GetPagedAsync(int page, int pageSize)
    {
        var countSql = "SELECT COUNT(*) FROM Products WHERE IsActive = 1";
        var dataSql = @"
            SELECT * FROM Products
            WHERE IsActive = 1
            ORDER BY Id
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        var total = await _db.ExecuteScalarAsync<int>(countSql);
        var items = await _db.QueryAsync<Product>(dataSql, new
        {
            Offset = (page - 1) * pageSize,
            PageSize = pageSize
        });

        return new PagedResult<Product>
        {
            Items = items.ToList(),
            Total = total,
            Page = page,
            PageSize = pageSize
        };
    }
}

public class PagedResult<T>
{
    public List<T> Items { get; set; } = new();
    public int Total { get; set; }
    public int Page { get; set; }
    public int PageSize { get; set; }
    public int TotalPages => (int)Math.Ceiling(Total / (double)PageSize);
}

更新与删除

/// <summary>
/// Dapper 更新和删除操作
/// </summary>
public class ProductCommandRepository
{
    private readonly IDbConnection _db;

    public ProductCommandRepository(IDbConnection db)
    {
        _db = db;
    }

    // 更新全部字段
    public async Task<int> UpdateAsync(Product product)
    {
        var sql = @"
            UPDATE Products
            SET Name = @Name,
                Category = @Category,
                Price = @Price,
                Stock = @Stock,
                Description = @Description,
                IsActive = @IsActive,
                UpdatedAt = GETUTCDATE()
            WHERE Id = @Id";

        return await _db.ExecuteAsync(sql, product);
    }

    // 选择性更新（只更新指定字段）
    public async Task<int> UpdatePriceAsync(int id, decimal newPrice)
    {
        var sql = @"
            UPDATE Products
            SET Price = @Price, UpdatedAt = GETUTCDATE()
            WHERE Id = @Id";

        return await _db.ExecuteAsync(sql, new { Id = id, Price = newPrice });
    }

    // 更新库存（使用SQL表达式）
    public async Task<int> UpdateStockAsync(int id, int quantity)
    {
        var sql = @"
            UPDATE Products
            SET Stock = Stock + @Quantity, UpdatedAt = GETUTCDATE()
            WHERE Id = @Id AND Stock + @Quantity >= 0";

        return await _db.ExecuteAsync(sql, new { Id = id, Quantity = quantity });
    }

    // 物理删除
    public async Task<int> DeleteAsync(int id)
    {
        var sql = "DELETE FROM Products WHERE Id = @Id";
        return await _db.ExecuteAsync(sql, new { Id = id });
    }

    // 软删除
    public async Task<int> SoftDeleteAsync(int id)
    {
        var sql = @"
            UPDATE Products
            SET IsActive = 0, UpdatedAt = GETUTCDATE()
            WHERE Id = @Id";

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

    // 批量删除
    public async Task<int> DeleteByIdsAsync(IEnumerable<int> ids)
    {
        var sql = "DELETE FROM Products WHERE Id IN @Ids";
        return await _db.ExecuteAsync(sql, new { Ids = ids });
    }
}

参数化查询

多种参数传递方式

/// <summary>
/// Dapper 参数化查询的各种方式
/// </summary>
public class ParameterDemo
{
    private readonly IDbConnection _db;

    public ParameterDemo(IDbConnection db)
    {
        _db = db;
    }

    // 1. 匿名对象参数（最常用）
    public async Task<Product?> GetByCategoryAndPriceAsync(string category, decimal minPrice)
    {
        var sql = @"
            SELECT * FROM Products
            WHERE Category = @Category AND Price >= @MinPrice";

        return await _db.QueryFirstOrDefaultAsync<Product>(sql,
            new { Category = category, MinPrice = minPrice });
    }

    // 2. DynamicParameters 动态参数
    public async Task<IEnumerable<Product>> SearchWithDynamicParamsAsync(
        string? name, string? category, decimal? minPrice, decimal? maxPrice)
    {
        var sql = new StringBuilder("SELECT * FROM Products WHERE IsActive = 1");
        var parameters = new DynamicParameters();

        if (!string.IsNullOrEmpty(name))
        {
            sql.Append(" AND Name LIKE @Name");
            parameters.Add("Name", "%" + name + "%");
        }

        if (!string.IsNullOrEmpty(category))
        {
            sql.Append(" AND Category = @Category");
            parameters.Add("Category", category);
        }

        if (minPrice.HasValue)
        {
            sql.Append(" AND Price >= @MinPrice");
            parameters.Add("MinPrice", minPrice.Value);
        }

        if (maxPrice.HasValue)
        {
            sql.Append(" AND Price <= @MaxPrice");
            parameters.Add("MaxPrice", maxPrice.Value);
        }

        sql.Append(" ORDER BY CreatedAt DESC");

        return await _db.QueryAsync<Product>(sql.ToString(), parameters);
    }

    // 3. 使用 DbString 指定参数类型和大小（优化性能）
    public async Task<Product?> GetByNameOptimizedAsync(string name)
    {
        var sql = "SELECT * FROM Products WHERE Name = @Name";

        return await _db.QueryFirstOrDefaultAsync<Product>(sql,
            new { Name = new DbString { Value = name, IsAnsi = true, Length = 100 } });
    }

    // 4. IN 查询参数
    public async Task<IEnumerable<Product>> GetByCategoriesAsync(string[] categories)
    {
        var sql = "SELECT * FROM Products WHERE Category IN @Categories";
        return await _db.QueryAsync<Product>(sql, new { Categories = categories });
    }

    // 5. 表值参数（SQL Server）
    public async Task<IEnumerable<Product>> GetByProductIdsAsync(
        IEnumerable<int> productIds)
    {
        var dataTable = new DataTable();
        dataTable.Columns.Add("Id", typeof(int));
        foreach (var id in productIds)
        {
            dataTable.Rows.Add(id);
        }

        var sql = @"
            SELECT p.* FROM Products p
            INNER JOIN @Ids tvp ON p.Id = tvp.Id";

        return await _db.QueryAsync<Product>(sql,
            new { Ids = dataTable.AsTableValuedParameter("dbo.IntTableType") });
    }

    // 6. 输出参数
    public async Task<(Product? Product, int TotalCount)> GetWithTotalCountAsync(int id)
    {
        var sql = @"
            SELECT @TotalCount = COUNT(*) FROM Products WHERE IsActive = 1;
            SELECT * FROM Products WHERE Id = @Id;";

        var parameters = new DynamicParameters();
        parameters.Add("Id", id);
        parameters.Add("TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

        using var multi = await _db.QueryMultipleAsync(sql, parameters);
        var product = await multi.ReadFirstOrDefaultAsync<Product>();
        var totalCount = parameters.Get<int>("TotalCount");

        return (product, totalCount);
    }
}

多映射（Multi-Mapping）

一对多映射

/// <summary>
/// 订单与订单明细的一对多映射
/// </summary>
public class Order
{
    public int Id { get; set; }
    public string OrderNo { get; set; } = string.Empty;
    public decimal TotalAmount { get; set; }
    public DateTime CreatedAt { get; set; }

    // 导航属性
    public List<OrderItem> Items { get; set; } = new();
}

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public string ProductName { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int Quantity { get; set; }
}

public class OrderMappingRepository
{
    private readonly IDbConnection _db;

    public OrderMappingRepository(IDbConnection db)
    {
        _db = db;
    }

    /// <summary>
    /// 一对多映射 — 使用 splitOn 参数
    /// Dapper 按 splitOn 指定的列拆分结果集，映射到不同对象
    /// </summary>
    public async Task<IEnumerable<Order>> GetOrdersWithItemsAsync()
    {
        var sql = @"
            SELECT
                o.Id, o.OrderNo, o.TotalAmount, o.CreatedAt,
                oi.Id AS ItemId, oi.OrderId, oi.ProductName, oi.Price, oi.Quantity
            FROM Orders o
            LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
            ORDER BY o.Id, oi.Id";

        var orderDict = new Dictionary<int, Order>();

        var result = await _db.QueryAsync<Order, OrderItem, Order>(
            sql,
            (order, item) =>
            {
                // 利用字典去重，确保每个 Order 只创建一次
                if (!orderDict.TryGetValue(order.Id, out var existingOrder))
                {
                    existingOrder = order;
                    existingOrder.Items = new List<OrderItem>();
                    orderDict.Add(order.Id, existingOrder);
                }

                // 添加子项（注意 LEFT JOIN 可能返回 NULL）
                if (item != null)
                {
                    existingOrder.Items.Add(item);
                }

                return existingOrder;
            },
            splitOn: "ItemId" // 告诉 Dapper 从 ItemId 列开始属于 OrderItem
        );

        return orderDict.Values;
    }
}

多表关联映射

/// <summary>
/// 用户 + 订单 + 订单项 三表关联映射
/// </summary>
public class UserOrderDto
{
    public int UserId { get; set; }
    public string UserName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public List<OrderInfo> Orders { get; set; } = new();
}

public class OrderInfo
{
    public int OrderId { get; set; }
    public string OrderNo { get; set; } = string.Empty;
    public decimal TotalAmount { get; set; }
    public List<OrderItemInfo> Items { get; set; } = new();
}

public class OrderItemInfo
{
    public string ProductName { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int Quantity { get; set; }
}

public class MultiMappingRepository
{
    private readonly IDbConnection _db;

    public MultiMappingRepository(IDbConnection db)
    {
        _db = db;
    }

    /// <summary>
    /// 三层嵌套映射 — 先查询扁平数据，再在内存中组装
    /// 这种方式比多层 splitOn 更直观可控
    /// </summary>
    public async Task<List<UserOrderDto>> GetUserOrdersAsync(int userId)
    {
        var sql = @"
            SELECT
                u.Id AS UserId, u.UserName, u.Email,
                o.Id AS OrderId, o.OrderNo, o.TotalAmount,
                oi.ProductName, oi.Price, oi.Quantity
            FROM Users u
            LEFT JOIN Orders o ON u.Id = o.UserId
            LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
            WHERE u.Id = @UserId
            ORDER BY o.Id, oi.Id";

        // 定义扁平的DTO用于接收数据
        var flatResults = await _db.QueryAsync<UserOrderFlatRow>(sql,
            new { UserId = userId });

        // 在内存中组装层级结构
        var userDict = new Dictionary<int, UserOrderDto>();
        var orderDict = new Dictionary<int, OrderInfo>();

        foreach (var row in flatResults)
        {
            // 组装用户
            if (!userDict.TryGetValue(row.UserId, out var user))
            {
                user = new UserOrderDto
                {
                    UserId = row.UserId,
                    UserName = row.UserName,
                    Email = row.Email,
                    Orders = new List<OrderInfo>()
                };
                userDict[row.UserId] = user;
            }

            // 组装订单
            if (row.OrderId > 0 && !orderDict.TryGetValue(row.OrderId, out var order))
            {
                order = new OrderInfo
                {
                    OrderId = row.OrderId,
                    OrderNo = row.OrderNo,
                    TotalAmount = row.TotalAmount,
                    Items = new List<OrderItemInfo>()
                };
                orderDict[row.OrderId] = order;
                user.Orders.Add(order);
            }

            // 组装订单项
            if (row.OrderId > 0 && !string.IsNullOrEmpty(row.ProductName))
            {
                orderDict[row.OrderId].Items.Add(new OrderItemInfo
                {
                    ProductName = row.ProductName,
                    Price = row.Price,
                    Quantity = row.Quantity
                });
            }
        }

        return userDict.Values.ToList();
    }
}

// 扁平行DTO — 用于接收JOIN查询的扁平结果
public class UserOrderFlatRow
{
    public int UserId { get; set; }
    public string UserName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public int OrderId { get; set; }
    public string OrderNo { get; set; } = string.Empty;
    public decimal TotalAmount { get; set; }
    public string ProductName { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int Quantity { get; set; }
}

QueryMultiple 多结果集

/// <summary>
/// QueryMultiple — 一次查询返回多个结果集
/// </summary>
public class MultiResultRepository
{
    private readonly IDbConnection _db;

    public MultiResultRepository(IDbConnection db)
    {
        _db = db;
    }

    /// <summary>
    /// 一次数据库调用获取订单详情 + 订单项 + 操作日志
    /// </summary>
    public async Task<OrderDetailResult> GetOrderDetailAsync(int orderId)
    {
        var sql = @"
            SELECT * FROM Orders WHERE Id = @OrderId;
            SELECT * FROM OrderItems WHERE OrderId = @OrderId;
            SELECT * FROM OrderLogs WHERE OrderId = @OrderId ORDER BY CreatedAt DESC;";

        using var multi = await _db.QueryMultipleAsync(sql, new { OrderId = orderId });

        return new OrderDetailResult
        {
            Order = await multi.ReadFirstOrDefaultAsync<Order>(),
            Items = (await multi.ReadAsync<OrderItem>()).ToList(),
            Logs = (await multi.ReadAsync<OrderLog>()).ToList()
        };
    }
}

public class OrderDetailResult
{
    public Order? Order { get; set; }
    public List<OrderItem> Items { get; set; } = new();
    public List<OrderLog> Logs { get; set; } = new();
}

public class OrderLog
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public string Action { get; set; } = string.Empty;
    public string Message { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; }
}

存储过程调用

/// <summary>
/// Dapper 调用存储过程
/// </summary>
public class StoredProcedureRepository
{
    private readonly IDbConnection _db;

    public StoredProcedureRepository(IDbConnection db)
    {
        _db = db;
    }

    // 1. 基本存储过程调用
    public async Task<IEnumerable<Product>> GetProductsByCategoryAsync(string category)
    {
        return await _db.QueryAsync<Product>(
            "sp_GetProductsByCategory",
            new { Category = category },
            commandType: CommandType.StoredProcedure);
    }

    // 2. 带输出参数的存储过程
    public async Task<PagedResult<Product>> GetProductsPagedAsync(
        int page, int pageSize)
    {
        var parameters = new DynamicParameters();
        parameters.Add("Page", page);
        parameters.Add("PageSize", pageSize);
        parameters.Add("TotalCount", dbType: DbType.Int32,
            direction: ParameterDirection.Output);

        var items = await _db.QueryAsync<Product>(
            "sp_GetProductsPaged",
            parameters,
            commandType: CommandType.StoredProcedure);

        return new PagedResult<Product>
        {
            Items = items.ToList(),
            Total = parameters.Get<int>("TotalCount"),
            Page = page,
            PageSize = pageSize
        };
    }

    // 3. 带返回值的存储过程
    public async Task<(int Result, string Message)> CreateProductAsync(Product product)
    {
        var parameters = new DynamicParameters();
        parameters.Add("Name", product.Name);
        parameters.Add("Category", product.Category);
        parameters.Add("Price", product.Price);
        parameters.Add("Stock", product.Stock);
        parameters.Add("NewId", dbType: DbType.Int32,
            direction: ParameterDirection.Output);
        parameters.Add("ReturnCode", dbType: DbType.Int32,
            direction: ParameterDirection.ReturnValue);

        await _db.ExecuteAsync("sp_CreateProduct", parameters,
            commandType: CommandType.StoredProcedure);

        var newId = parameters.Get<int>("NewId");
        var returnCode = parameters.Get<int>("ReturnCode");

        return (returnCode, returnCode == 0 ? $"创建成功，ID: {newId}" : "创建失败");
    }
}

事务处理

/// <summary>
/// Dapper 事务操作
/// </summary>
public class OrderTransactionService
{
    private readonly IDbConnection _db;

    public OrderTransactionService(IDbConnection db)
    {
        _db = db;
    }

    /// <summary>
    /// 创建订单事务 — 扣库存 + 创建订单 + 创建订单明细
    /// </summary>
    public async Task<int> CreateOrderAsync(CreateOrderRequest request)
    {
        using var transaction = _db.BeginTransaction();

        try
        {
            // 1. 扣减库存
            foreach (var item in request.Items)
            {
                var affected = await _db.ExecuteAsync(@"
                    UPDATE Products
                    SET Stock = Stock - @Quantity
                    WHERE Id = @ProductId AND Stock >= @Quantity",
                    new { item.ProductId, item.Quantity },
                    transaction);

                if (affected == 0)
                    throw new InvalidOperationException(
                        $"产品 {item.ProductId} 库存不足");
            }

            // 2. 创建订单主表
            var orderNo = $"ORD{DateTime.UtcNow:yyyyMMddHHmmss}";
            var orderId = await _db.QuerySingleAsync<int>(@"
                INSERT INTO Orders (OrderNo, UserId, TotalAmount, Status, CreatedAt)
                VALUES (@OrderNo, @UserId, @TotalAmount, 0, GETUTCDATE());
                SELECT CAST(SCOPE_IDENTITY() AS INT);",
                new
                {
                    OrderNo = orderNo,
                    UserId = request.UserId,
                    TotalAmount = request.Items.Sum(i => i.Price * i.Quantity)
                },
                transaction);

            // 3. 批量创建订单明细
            var orderItems = request.Items.Select(i => new
            {
                OrderId = orderId,
                i.ProductId,
                i.ProductName,
                i.Price,
                i.Quantity
            });

            await _db.ExecuteAsync(@"
                INSERT INTO OrderItems (OrderId, ProductId, ProductName, Price, Quantity)
                VALUES (@OrderId, @ProductId, @ProductName, @Price, @Quantity)",
                orderItems,
                transaction);

            // 4. 记录操作日志
            await _db.ExecuteAsync(@"
                INSERT INTO OrderLogs (OrderId, Action, Message, CreatedAt)
                VALUES (@OrderId, 'Created', @Message, GETUTCDATE())",
                new { OrderId = orderId, Message = $"创建订单，共{request.Items.Count}件商品" },
                transaction);

            // 提交事务
            transaction.Commit();
            return orderId;
        }
        catch
        {
            // 回滚事务
            transaction.Rollback();
            throw;
        }
    }

    /// <summary>
    /// 使用 TransactionScope（分布式事务场景）
    /// </summary>
    public async Task TransferStockAsync(int fromId, int toId, int quantity)
    {
        using var scope = new TransactionScope(
            TransactionScopeAsyncFlowOption.Enabled);

        await _db.ExecuteAsync(
            "UPDATE Products SET Stock = Stock - @Qty WHERE Id = @Id",
            new { Id = fromId, Qty = quantity });

        await _db.ExecuteAsync(
            "UPDATE Products SET Stock = Stock + @Qty WHERE Id = @Id",
            new { Id = toId, Qty = quantity });

        scope.Complete();
    }
}

public class CreateOrderRequest
{
    public int UserId { get; set; }
    public List<CreateOrderItem> Items { get; set; } = new();
}

public class CreateOrderItem
{
    public int ProductId { get; set; }
    public string ProductName { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int Quantity { get; set; }
}

批量插入

多种批量插入方式

/// <summary>
/// Dapper 批量插入的各种方式
/// </summary>
public class BulkInsertService
{
    private readonly IDbConnection _db;

    public BulkInsertService(IDbConnection db)
    {
        _db = db;
    }

    // 方式1：ExecuteAsync 传入集合 — 内部循环执行（简单但较慢）
    public async Task<int> BatchInsertSimpleAsync(IEnumerable<Product> products)
    {
        var sql = @"
            INSERT INTO Products (Name, Category, Price, Stock, IsActive, CreatedAt)
            VALUES (@Name, @Category, @Price, @Stock, @IsActive, GETUTCDATE())";

        return await _db.ExecuteAsync(sql, products);
    }

    // 方式2：拼接多行 VALUES（较快，但SQL长度有限制）
    public async Task BatchInsertValuesAsync(List<Product> products)
    {
        // SQL Server 单条SQL最大长度约65536 * 服务器网络包大小
        // 建议每批 500-1000 条
        var batchSize = 500;

        for (int i = 0; i < products.Count; i += batchSize)
        {
            var batch = products.Skip(i).Take(batchSize).ToList();
            var sql = new StringBuilder();
            sql.Append(@"INSERT INTO Products (Name, Category, Price, Stock, IsActive, CreatedAt)
                VALUES ");

            var parameters = new DynamicParameters();
            for (int j = 0; j < batch.Count; j++)
            {
                if (j > 0) sql.Append(", ");
                sql.Append($"(@Name{j}, @Category{j}, @Price{j}, @Stock{j}, @IsActive{j}, GETUTCDATE())");
                parameters.Add($"Name{j}", batch[j].Name);
                parameters.Add($"Category{j}", batch[j].Category);
                parameters.Add($"Price{j}", batch[j].Price);
                parameters.Add($"Stock{j}", batch[j].Stock);
                parameters.Add($"IsActive{j}", batch[j].IsActive);
            }

            await _db.ExecuteAsync(sql.ToString(), parameters);
        }
    }

    // 方式3：使用 DapperPlus 第三方库（最快，但商业许可）
    // dotnet add package Z.Dapper.Plus
    public async Task BulkInsertWithDapperPlusAsync(List<Product> products)
    {
        // DapperPlus 自动使用 SqlBulkCopy 实现
        await _db.BulkInsertAsync(products);
    }

    // 方式4：使用原生 SqlBulkCopy（最快，适合大数据量）
    public async Task BulkInsertWithSqlBulkCopyAsync(List<Product> products)
    {
        // 将 List 转换为 DataTable
        var dataTable = new DataTable();
        dataTable.Columns.Add("Name", typeof(string));
        dataTable.Columns.Add("Category", typeof(string));
        dataTable.Columns.Add("Price", typeof(decimal));
        dataTable.Columns.Add("Stock", typeof(int));
        dataTable.Columns.Add("IsActive", typeof(bool));
        dataTable.Columns.Add("CreatedAt", typeof(DateTime));

        foreach (var p in products)
        {
            dataTable.Rows.Add(
                p.Name, p.Category, p.Price, p.Stock, p.IsActive, DateTime.UtcNow);
        }

        // 使用 SqlBulkCopy
        if (_db is SqlConnection sqlConnection)
        {
            using var bulkCopy = new SqlBulkCopy(sqlConnection);
            bulkCopy.DestinationTableName = "Products";
            bulkCopy.BatchSize = 5000;
            bulkCopy.BulkCopyTimeout = 60;

            // 列映射
            bulkCopy.ColumnMappings.Add("Name", "Name");
            bulkCopy.ColumnMappings.Add("Category", "Category");
            bulkCopy.ColumnMappings.Add("Price", "Price");
            bulkCopy.ColumnMappings.Add("Stock", "Stock");
            bulkCopy.ColumnMappings.Add("IsActive", "IsActive");
            bulkCopy.ColumnMappings.Add("CreatedAt", "CreatedAt");

            await bulkCopy.WriteToServerAsync(dataTable);
        }
    }
}

与 EF Core 对比

功能与性能对比

/// <summary>
/// 同一个查询需求，EF Core 和 Dapper 的实现对比
/// </summary>

// ===== EF Core 方式 =====
public class EfCoreOrderService
{
    private readonly AppDbContext _context;

    public EfCoreOrderService(AppDbContext context) => _context = context;

    public async Task<List<OrderSummaryDto>> GetOrderSummariesAsync(int userId)
    {
        return await _context.Orders
            .AsNoTracking()
            .Where(o => o.UserId == userId)
            .Select(o => new OrderSummaryDto
            {
                OrderId = o.Id,
                OrderNo = o.OrderNo,
                TotalAmount = o.TotalAmount,
                ItemCount = o.Items.Count,
                Status = o.Status.ToString()
            })
            .OrderByDescending(o => o.OrderId)
            .ToListAsync();
    }
}

// ===== Dapper 方式 =====
public class DapperOrderService
{
    private readonly IDbConnection _db;

    public DapperOrderService(IDbConnection db) => _db = db;

    public async Task<List<OrderSummaryDto>> GetOrderSummariesAsync(int userId)
    {
        var sql = @"
            SELECT
                o.Id AS OrderId,
                o.OrderNo,
                o.TotalAmount,
                COUNT(oi.Id) AS ItemCount,
                CAST(o.Status AS VARCHAR(20)) AS Status
            FROM Orders o
            LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
            WHERE o.UserId = @UserId
            GROUP BY o.Id, o.OrderNo, o.TotalAmount, o.Status
            ORDER BY o.Id DESC";

        var result = await _db.QueryAsync<OrderSummaryDto>(sql, new { UserId = userId });
        return result.ToList();
    }
}

public class OrderSummaryDto
{
    public int OrderId { get; set; }
    public string OrderNo { get; set; } = string.Empty;
    public decimal TotalAmount { get; set; }
    public int ItemCount { get; set; }
    public string Status { get; set; } = string.Empty;
}
对比维度EF CoreDapper
性能（简单查询）较快极快
性能（复杂查询）一般，生成SQL可能不优极快，SQL完全可控
开发效率高，LINQ一行搞定中等，需要写SQL
学习曲线较陡平缓
变更追踪内置支持不支持
Migration内置支持不支持
复杂映射自动处理需手动配置
跨数据库LINQ屏蔽差异需要写不同SQL
适用场景CRUD为主的标准业务复杂查询、报表、高性能场景

何时选择 Dapper vs EF Core

/// <summary>
/// 选择建议：
///
/// 使用 EF Core 的场景：
/// - CRUD 为主的业务系统
/// - 频繁变更的领域模型
/// - 需要自动 Migration 管理
/// - 团队对 SQL 不够熟悉
/// - 需要变更追踪
///
/// 使用 Dapper 的场景：
/// - 高性能要求的查询
/// - 复杂报表和统计
/// - 需要精细控制 SQL
/// - 已有数据库，不需要 Migration
/// - 数据库有复杂存储过程
///
/// 混合使用（推荐）：
/// - 标准CRUD用 EF Core
/// - 复杂查询用 Dapper
/// </summary>

Repository 模式封装

/// <summary>
/// Dapper 泛型 Repository 基类
/// </summary>
public abstract class DapperRepositoryBase<TEntity> where TEntity : class
{
    private readonly IDbConnectionFactory _connectionFactory;

    protected DapperRepositoryBase(IDbConnectionFactory connectionFactory)
    {
        _connectionFactory = connectionFactory;
    }

    protected IDbConnection CreateConnection() => _connectionFactory.CreateConnection();

    /// <summary>
    /// 表名，子类可以重写
    /// </summary>
    protected virtual string TableName => typeof(TEntity).Name + "s";

    public async Task<TEntity?> GetByIdAsync(int id)
    {
        using var db = CreateConnection();
        var sql = $"SELECT * FROM {TableName} WHERE Id = @Id";
        return await db.QueryFirstOrDefaultAsync<TEntity>(sql, new { Id = id });
    }

    public async Task<IEnumerable<TEntity>> GetAllAsync()
    {
        using var db = CreateConnection();
        var sql = $"SELECT * FROM {TableName}";
        return await db.QueryAsync<TEntity>(sql);
    }

    public async Task<PagedResult<TEntity>> GetPagedAsync(
        int page, int pageSize, string whereClause = "", object? param = null)
    {
        using var db = CreateConnection();

        var where = string.IsNullOrEmpty(whereClause) ? "" : $" WHERE {whereClause}";
        var countSql = $"SELECT COUNT(*) FROM {TableName}{where}";
        var dataSql = $@"
            SELECT * FROM {TableName}{where}
            ORDER BY Id
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        var total = await db.ExecuteScalarAsync<int>(countSql, param);
        var items = await db.QueryAsync<TEntity>(dataSql,
            param is null
                ? new { Offset = (page - 1) * pageSize, PageSize = pageSize }
                : new DynamicParameters(param).Also(p =>
                    p.Add("Offset", (page - 1) * pageSize)
                     .Add("PageSize", pageSize)));

        return new PagedResult<TEntity>
        {
            Items = items.ToList(),
            Total = total,
            Page = page,
            PageSize = pageSize
        };
    }

    public async Task<int> InsertAsync(string columns, string values, object param)
    {
        using var db = CreateConnection();
        var sql = $@"INSERT INTO {TableName} ({columns})
            VALUES ({values});
            SELECT CAST(SCOPE_IDENTITY() AS INT);";
        return await db.QuerySingleAsync<int>(sql, param);
    }

    public async Task<int> UpdateAsync(string setClause, string whereClause, object param)
    {
        using var db = CreateConnection();
        var sql = $"UPDATE {TableName} SET {setClause} WHERE {whereClause}";
        return await db.ExecuteAsync(sql, param);
    }

    public async Task<int> DeleteAsync(int id)
    {
        using var db = CreateConnection();
        var sql = $"DELETE FROM {TableName} WHERE Id = @Id";
        return await db.ExecuteAsync(sql, new { Id = id });
    }
}

/// <summary>
/// DynamicParameters 扩展方法（链式调用）
/// </summary>
public static class DynamicParametersExtensions
{
    public static DynamicParameters Also(this DynamicParameters parameters,
        Action<DynamicParameters> configure)
    {
        configure(parameters);
        return parameters;
    }
}

/// <summary>
/// 具体仓储实现示例
/// </summary>
public class ProductDapperRepository : DapperRepositoryBase<Product>
{
    public ProductDapperRepository(IDbConnectionFactory factory)
        : base(factory) { }

    public async Task<IEnumerable<Product>> GetByCategoryAsync(string category)
    {
        using var db = CreateConnection();
        var sql = "SELECT * FROM Products WHERE Category = @Category AND IsActive = 1";
        return await db.QueryAsync<Product>(sql, new { Category = category });
    }

    public async Task<int> InsertProductAsync(Product product)
    {
        return await InsertAsync(
            "Name, Category, Price, Stock, IsActive, CreatedAt",
            "@Name, @Category, @Price, @Stock, @IsActive, GETUTCDATE()",
            product);
    }

    public async Task<int> UpdatePriceAsync(int id, decimal price)
    {
        return await UpdateAsync(
            "Price = @Price, UpdatedAt = GETUTCDATE()",
            "Id = @Id",
            new { Id = id, Price = price });
    }
}

在 ASP.NET Controller 中使用

/// <summary>
/// Controller 中使用 Dapper 的完整示例
/// </summary>
[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly ProductDapperRepository _repository;
    private readonly ILogger<ProductsController> _logger;

    public ProductsController(
        ProductDapperRepository repository,
        ILogger<ProductsController> logger)
    {
        _repository = repository;
        _logger = logger;
    }

    [HttpGet]
    public async Task<ActionResult<PagedResult<Product>>> GetList(
        [FromQuery] int page = 1, [FromQuery] int pageSize = 20)
    {
        var result = await _repository.GetPagedAsync(
            page, pageSize, "IsActive = 1");
        return Ok(result);
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<Product>> GetById(int id)
    {
        var product = await _repository.GetByIdAsync(id);
        if (product == null) return NotFound();
        return Ok(product);
    }

    [HttpPost]
    public async Task<ActionResult<int>> Create(Product product)
    {
        product.IsActive = true;
        var id = await _repository.InsertProductAsync(product);
        return CreatedAtAction(nameof(GetById), new { id }, id);
    }

    [HttpPut("{id}/price")]
    public async Task<IActionResult> UpdatePrice(int id, [FromBody] decimal price)
    {
        var affected = await _repository.UpdatePriceAsync(id, price);
        if (affected == 0) return NotFound();
        return NoContent();
    }

    [HttpDelete("{id}")]
    public async Task<IActionResult> Delete(int id)
    {
        var affected = await _repository.DeleteAsync(id);
        if (affected == 0) return NotFound();
        return NoContent();
    }
}

总结

Dapper 是轻量级 Micro-ORM，适合需要高性能 SQL 查询的场景。核心优势：原生 SQL 控制、极低的性能开销、自动对象映射。适合报表查询、批量操作、复杂 SQL 等场景。建议与 EF Core 搭配使用：EF Core 负责常规 CRUD，Dapper 负责性能敏感的查询。

关键知识点

  • 先分清这个主题位于请求链路、后台任务链路还是基础设施链路。
  • 服务端主题通常不只关心功能正确，还关心稳定性、性能和可观测性。
  • 任何框架能力都要结合配置、生命周期、异常传播和外部依赖一起看。
  • 先把数据模型、访问模式和执行代价绑定起来理解。

项目落地视角

  • 画清请求进入、业务执行、外部调用、日志记录和错误返回的完整路径。
  • 为关键链路补齐超时、重试、熔断、追踪和结构化日志。
  • 把配置与敏感信息分离，并明确不同环境的差异来源。
  • 保留执行计划、样本 SQL、索引定义和优化前后指标。

常见误区

  • 只会堆中间件或组件，不知道它们在链路中的执行顺序。
  • 忽略生命周期和线程池、连接池等运行时资源约束。
  • 没有监控和测试就对性能或可靠性下结论。
  • 脱离真实数据分布设计索引。

进阶路线

  • 继续向运行时行为、可观测性、发布治理和微服务协同深入。
  • 把主题和数据库、缓存、消息队列、认证授权联动起来理解。
  • 沉淀团队级模板，包括统一异常处理、配置约定和基础设施封装。
  • 继续深入存储引擎、复制机制、归档与冷热分层治理。

适用场景

  • 当你准备把《Dapper轻量ORM实战》真正落到项目里时，最适合先在一个独立模块或最小样例里验证关键路径。
  • 适合 API 服务、后台任务、实时通信、认证授权和微服务协作场景。
  • 当需求开始涉及稳定性、性能、可观测性和发布流程时，这类主题会成为基础设施能力。

落地建议

  • 先定义请求链路与失败路径，再决定中间件、过滤器、服务边界和依赖方式。
  • 为关键链路补日志、指标、追踪、超时与重试策略。
  • 环境配置与敏感信息分离，避免把生产参数写死在代码或镜像里。

排错清单

  • 先确认问题发生在路由、模型绑定、中间件、业务层还是基础设施层。
  • 检查 DI 生命周期、配置来源、序列化规则和认证上下文。
  • 查看线程池、连接池、缓存命中率和外部依赖超时。

复盘问题

  • 如果把《Dapper轻量ORM实战》放进你的当前项目，最先要验证的输入、输出和失败路径分别是什么？
  • 《Dapper轻量ORM实战》最容易在什么规模、什么边界条件下暴露问题？你会用什么指标或日志去确认？
  • 相比默认实现或替代方案，采用《Dapper轻量ORM实战》最大的收益和代价分别是什么？

延伸阅读

Copyright © 2026 SunnyFan