C#怎样利用System.Data.SqlClient实现SQLServer的存储过程执行

-- 创建获取用户信息的存储过程
CREATE PROCEDURE GetUserDetails
    @UserId INT,
    @UserLevel INT OUTPUT
AS
BEGIN
    SELECT UserName, RegisterDate FROM Users WHERE UserId = @UserId
    SELECT @UserLevel = Level FROM UserLevels WHERE UserId = @UserId
END

using System.Data.SqlClient;

public class DataAccess
{
    // 连接字符串建议存储在配置文件中
    private const string connStr = "Server=.;Database=TestDB;Integrated Security=True";

    public void ExecuteStoredProcedure(int userId)
    {
        using (var conn = new SqlConnection(connStr))
        {
            // 创建命令对象并指定类型为存储过程
            using (var cmd = new SqlCommand("GetUserDetails", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                
                // 添加输入参数
                cmd.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int) {
                    Value = userId
                });

                // 添加输出参数
                var outputParam = new SqlParameter("@UserLevel", SqlDbType.Int) {
                    Direction = ParameterDirection.Output
                };
                cmd.Parameters.Add(outputParam);

                conn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    // 处理第一个结果集
                    while (reader.Read())
                    {
                        Console.WriteLine($"用户名：{reader["UserName"]}，注册时间：{reader["RegisterDate"]}");
                    }

                    // 处理第二个结果集（如果有）
                    if (reader.NextResult())
                    {
                        // 处理其他结果集...
                    }
                }

                // 获取输出参数值
                Console.WriteLine($"用户等级：{outputParam.Value}");
            }
        }
    }
}

public void TransferMoney(int fromId, int toId, decimal amount)
{
    using (var conn = new SqlConnection(connStr))
    {
        conn.Open();
        using (var transaction = conn.BeginTransaction())
        {
            try
            {
                using (var cmd = new SqlCommand("MoneyTransferProc", conn, transaction))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    
                    cmd.Parameters.AddRange(new[] {
                        new SqlParameter("@FromAccount", fromId),
                        new SqlParameter("@ToAccount", toId),
                        new SqlParameter("@Amount", amount)
                    });

                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            catch (SqlException ex)
            {
                transaction.Rollback();
                Console.WriteLine($"转账失败：{ex.Message}");
                // 记录日志等操作...
            }
        }
    }
}

// 精确指定参数类型避免隐式转换
new SqlParameter("@BirthDate", SqlDbType.DateTime2) {
    Value = DateTime.Now
}

cmd.CommandTimeout = 120; // 单位：秒