如何高效使用Dapper调用存储过程提升数据库性能？

在.NET开发中，Dapper作为轻量级ORM工具，能显著提升数据库操作效率，本文将通过实际案例演示如何通过Dapper调用SQL Server存储过程，并提供企业级项目中的最佳实践。

准备工作

1. 安装NuGet包

   

   Install-Package Dapper
   Install-Package System.Data.SqlClient

2. 创建示例存储过程

   CREATE PROCEDURE GetEmployeeDetails
       @EmployeeId INT,
       @DepartmentCode VARCHAR(10) OUTPUT
   AS
   BEGIN
       SELECT * FROM Employees WHERE Id = @EmployeeId
       SELECT @DepartmentCode = DepartmentCode FROM Departments WHERE EmployeeId = @EmployeeId
   END

基础调用方法

using (var connection = new SqlConnection(connectionString))
{
    var parameters = new DynamicParameters();
    parameters.Add("@EmployeeId", 1001);
    parameters.Add("@DepartmentCode", dbType: DbType.String, direction: ParameterDirection.Output, size: 10);
    var result = await connection.QueryAsync<Employee>(
        "GetEmployeeDetails",
        parameters,
        commandType: CommandType.StoredProcedure
    );
    var departmentCode = parameters.Get<string>("@DepartmentCode");
}

关键点解析：

• 使用DynamicParameters管理输入/输出参数
• 必须指定CommandType.StoredProcedure
• 通过泛型QueryAsync<T>实现强类型映射

高级应用场景

场景1：处理多结果集

using (var multi = connection.QueryMultiple(
    "GetSalesReport", 
    new { Year = 2025 }, 
    commandType: CommandType.StoredProcedure))
{
    var summary = multi.Read<SalesSummary>().First();
    var details = multi.Read<SalesDetail>().ToList();
}

场景2：事务处理

using (var transaction = connection.BeginTransaction())
{
    try
    {
        connection.Execute(
            "UpdateInventory",
            new { ProductId = 501, Quantity = -5 },
            transaction: transaction,
            commandType: CommandType.StoredProcedure
        );
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
    }
}

性能优化技巧

1. 参数预编译

   var lookup = new Dictionary<int, Employee>();
   connection.Query<Employee, Department, Employee>(
       "GetEmployeeWithDepartment",
       (e, d) => {
           if (!lookup.TryGetValue(e.Id, out var emp))
               lookup.Add(e.Id, emp = e);
           emp.Department = d;
           return emp;
       },
       splitOn: "DepartmentId",
       commandType: CommandType.StoredProcedure
   );

2. 异步流式处理

   await foreach (var employee in connection.QueryUnbufferedAsync<Employee>(
       "GetLargeDataset",
       commandType: CommandType.StoredProcedure))
   {
       // 流式处理数据
   }

安全注意事项

1. 使用参数化查询防御SQL注入
2. 限制存储过程的执行权限
3. 对输出参数进行类型验证
4. 记录存储过程执行日志

常见问题排查

引用说明：

• Dapper官方文档：https://github.com/DapperLib/Dapper
• ADO.NET最佳实践：https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/
• SQL Server存储过程指南：https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/