聆途笔记

C#存储过程调用：从基础到高级实践

存储过程（Stored Procedure）是数据库编程中的重要组成部分，它提供了一种在数据库服务器端预编译和执行SQL语句的高效方式。在C#应用程序中调用存储过程可以带来性能优势、安全提升和业务逻辑封装等好处。本文将全面介绍C#中调用存储过程的各种方法和技术。

一、存储过程基础

1.1 存储过程的优势

  • 性能优化：预编译执行计划，减少网络传输
  • 安全性：减少SQL注入风险，实现权限控制
  • 代码复用：多个应用共享同一业务逻辑
  • 维护便利：修改存储过程无需重新部署应用

1.2 基本调用流程

  1. 创建数据库连接
  2. 配置SqlCommand对象
  3. 添加输入/输出参数
  4. 执行存储过程
  5. 处理返回结果

二、基础调用方法

2.1 使用ADO.NET调用

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

public DataTable GetEmployeeData(int departmentId)
{
    DataTable resultTable = new DataTable();

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // 创建Command对象并指定为存储过程
        using (SqlCommand command = new SqlCommand("usp_GetEmployeesByDepartment", connection))
        {
            command.CommandType = CommandType.StoredProcedure;

            // 添加输入参数
            command.Parameters.AddWithValue("@DepartmentID", departmentId);

            // 添加输出参数（可选）
            command.Parameters.Add("@EmployeeCount", SqlDbType.Int).Direction = ParameterDirection.Output;

            connection.Open();

            // 执行存储过程
            using (SqlDataReader reader = command.ExecuteReader())
            {
                resultTable.Load(reader);
            }

            // 获取输出参数值
            int employeeCount = Convert.ToInt32(command.Parameters["@EmployeeCount"].Value);
            Console.WriteLine($"总员工数: {employeeCount}");
        }
    }

    return resultTable;
}

2.2 参数类型详解

参数方向描述示例
ParameterDirection.Input输入参数（默认）command.Parameters.AddWithValue("@param", value)
ParameterDirection.Output输出参数command.Parameters.Add("@param", SqlDbType.Int).Direction = ParameterDirection.Output
ParameterDirection.InputOutput输入输出参数同上，设置Direction为InputOutput
ParameterDirection.ReturnValue返回值参数第一个参数通常为返回值参数

三、高级调用技术

3.1 处理复杂参数

表值参数(Table-Valued Parameters)

// 创建DataTable作为表值参数
DataTable employeeTable = new DataTable();
employeeTable.Columns.Add("EmployeeID", typeof(int));
employeeTable.Columns.Add("Name", typeof(string));
// 添加数据行...

// 配置SqlCommand
using (SqlCommand command = new SqlCommand("usp_BulkUpdateEmployees", connection))
{
    command.CommandType = CommandType.StoredProcedure;

    SqlParameter param = command.Parameters.AddWithValue("@EmployeeList", employeeTable);
    param.SqlDbType = SqlDbType.Structured;
    param.TypeName = "dbo.EmployeeTableType";  // 数据库中定义的表类型

    connection.Open();
    command.ExecuteNonQuery();
}

自定义类型参数

// 使用JSON传递复杂对象
var employeeData = new {
    Name = "张三",
    Details = new {
        Position = "开发工程师",
        Level = "Senior"
    }
};

string jsonParam = JsonConvert.SerializeObject(employeeData);

using (SqlCommand command = new SqlCommand("usp_ProcessEmployeeData", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@JsonData", jsonParam);
    // 执行...
}

3.2 事务处理

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlTransaction transaction = connection.BeginTransaction();

    try
    {
        using (SqlCommand command = new SqlCommand("usp_UpdateEmployeeSalary", connection, transaction))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@EmployeeID", 1001);
            command.Parameters.AddWithValue("@SalaryIncrease", 500);

            command.ExecuteNonQuery();
        }

        // 可以继续调用其他存储过程...

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

四、使用ORM框架调用存储过程

4.1 Entity Framework Core

// 返回实体列表
var employees = context.Employees
    .FromSqlRaw("EXEC usp_GetEmployeesByDepartment @DepartmentID={0}", departmentId)
    .ToList();

// 执行无返回的存储过程
context.Database.ExecuteSqlRaw("EXEC usp_UpdateEmployeeStatus @EmployeeID={0}, @NewStatus={1}", 
    employeeId, newStatus);

// 使用输出参数
var param = new SqlParameter("@ResultCode", SqlDbType.Int) 
{ 
    Direction = ParameterDirection.Output 
};

context.Database.ExecuteSqlRaw("EXEC usp_ComplexOperation @Param1={0}, @ResultCode={1} OUT", 
    param1, param);

int resultCode = (int)param.Value;

4.2 Dapper调用存储过程

using Dapper;

// 简单调用
var employees = connection.Query<Employee>(
    "usp_GetEmployeesByDepartment",
    new { DepartmentID = departmentId },
    commandType: CommandType.StoredProcedure);

// 使用输出参数
var parameters = new DynamicParameters();
parameters.Add("@DepartmentID", departmentId);
parameters.Add("@EmployeeCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

connection.Execute(
    "usp_GetDepartmentInfo",
    parameters,
    commandType: CommandType.StoredProcedure);

int employeeCount = parameters.Get<int>("@EmployeeCount");

五、性能优化技巧

5.1 参数嗅探问题解决

// 使用OPTION(RECOMPILE)提示
using (SqlCommand command = new SqlCommand(
    "EXEC usp_GetSalesData @StartDate, @EndDate OPTION(RECOMPILE)", 
    connection))
{
    // 参数设置...
}

// 或使用本地变量"屏蔽"参数
ALTER PROCEDURE usp_GetSalesData
    @StartDate DATETIME,
    @EndDate DATETIME
AS
BEGIN
    DECLARE @LocalStartDate DATETIME = @StartDate
    DECLARE @LocalEndDate DATETIME = @EndDate

    SELECT * FROM Sales 
    WHERE SaleDate BETWEEN @LocalStartDate AND @LocalEndDate
END

5.2 异步调用

// 异步执行存储过程
public async Task<List<Employee>> GetEmployeesAsync(int departmentId)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();

        return (await connection.QueryAsync<Employee>(
            "usp_GetEmployeesByDepartment",
            new { DepartmentID = departmentId },
            commandType: CommandType.StoredProcedure)).ToList();
    }
}

5.3 连接池优化

// 连接字符串中配置连接池
string connectionString = 
    "Server=myServer;Database=myDB;Integrated Security=True;" +
    "Max Pool Size=100;Min Pool Size=10;Connection Timeout=30;";

六、安全最佳实践

6.1 参数化调用

// 绝对不要这样拼接SQL
string unsafeSql = $"EXEC usp_GetUser '{userInput}'";

// 总是使用参数化查询
using (SqlCommand command = new SqlCommand("usp_GetUser", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@Username", userInput);
    // 执行...
}

6.2 权限最小化

-- 为应用创建专用数据库用户
CREATE LOGIN AppUser WITH PASSWORD = 'ComplexP@ssw0rd!';
CREATE USER AppUser FOR LOGIN AppUser;

-- 只授予执行特定存储过程的权限
GRANT EXECUTE ON usp_GetEmployees TO AppUser;
GRANT EXECUTE ON usp_UpdateEmployee TO AppUser;

6.3 敏感数据保护

// 使用SqlParameter的敏感参数特性
SqlParameter param = new SqlParameter("@Password", SqlDbType.NVarChar, 128)
{
    Value = encryptedPassword,
    IsSensitive = true  // 防止在日志中记录
};
command.Parameters.Add(param);

七、实际应用场景

7.1 分页查询存储过程

// 调用分页存储过程
public PagedResult<Employee> GetEmployeesPaged(int pageIndex, int pageSize)
{
    var parameters = new DynamicParameters();
    parameters.Add("@PageIndex", pageIndex);
    parameters.Add("@PageSize", pageSize);
    parameters.Add("@TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

    using (var connection = new SqlConnection(connectionString))
    {
        var employees = connection.Query<Employee>(
            "usp_GetEmployeesPaged",
            parameters,
            commandType: CommandType.StoredProcedure).ToList();

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

        return new PagedResult<Employee>(employees, pageIndex, pageSize, totalCount);
    }
}

7.2 批量数据处理

// 批量更新员工状态
public void BulkUpdateEmployeeStatus(List<int> employeeIds, string newStatus)
{
    DataTable idTable = new DataTable();
    idTable.Columns.Add("ID", typeof(int));
    foreach (var id in employeeIds)
    {
        idTable.Rows.Add(id);
    }

    using (SqlConnection connection = new SqlConnection(connectionString))
    using (SqlCommand command = new SqlCommand("usp_BulkUpdateEmployeeStatus", connection))
    {
        command.CommandType = CommandType.StoredProcedure;

        SqlParameter param = command.Parameters.AddWithValue("@IDList", idTable);
        param.SqlDbType = SqlDbType.Structured;
        param.TypeName = "dbo.IntIDList";

        command.Parameters.AddWithValue("@NewStatus", newStatus);

        connection.Open();
        command.ExecuteNonQuery();
    }
}

八、调试与问题排查

8.1 捕获存储过程错误

try
{
    using (SqlCommand command = new SqlCommand("usp_ComplexOperation", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        // 设置参数...

        connection.Open();
        command.ExecuteNonQuery();
    }
}
catch (SqlException ex)
{
    // 处理特定错误代码
    if (ex.Number == 50000) // 用户定义的错误
    {
        Console.WriteLine("业务逻辑错误: " + ex.Message);
    }
    else
    {
        Console.WriteLine($"数据库错误 #{ex.Number}: {ex.Message}");
    }

    // 访问错误集合
    foreach (SqlError error in ex.Errors)
    {
        Console.WriteLine($"严重度 {error.Class}, 状态 {error.State}: {error.Message}");
    }
}

8.2 性能分析

-- 在SQL Server中查看存储过程执行统计
SELECT 
    OBJECT_NAME(st.objectid) AS ProcedureName,
    qs.execution_count,
    qs.total_elapsed_time/1000 AS total_elapsed_time_ms,
    qs.total_logical_reads,
    qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.objectid IS NOT NULL
ORDER BY qs.total_elapsed_time DESC;

结语

通过本文的介绍，您应该已经掌握了C#中调用存储过程的核心技术，包括：

  1. 基础调用方法：使用ADO.NET的基本模式
  2. 高级技术：表值参数、事务处理、输出参数
  3. ORM集成：EF Core和Dapper中的存储过程调用
  4. 性能优化：参数嗅探处理、异步调用
  5. 安全实践：参数化查询、权限控制

在实际项目中使用存储过程时，建议：

  • 将业务逻辑合理地分配在应用层和数据库层
  • 为存储过程编写清晰的文档说明
  • 建立统一的错误处理机制
  • 定期审查存储过程的性能

存储过程作为数据库编程的强大工具，在正确使用时可以显著提升应用程序的性能和安全性。希望本指南能帮助您在C#项目中高效、安全地使用存储过程。

a_a_a

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注