使用存储过程修改数据

将 CommandType 设置为 CommandType.StoredProcedure,并将 CommandText 设置为架构限定的过程名称。 为每个存储过程参数添加一个参数,匹配类型、大小和方向。

本文中的示例假定一个名为 connection 的已打开 SqlConnection 指向 AdventureWorks 示例数据库。

C# 示例要求 using Microsoft.Data.SqlClient;。 使用 SqlDbType 或 CommandType 的示例也需要 using System.Data;。

调用存储过程

AdventureWorks 包含 dbo.uspGetEmployeeManagers,接受一个输入参数并返回结果集:

using var command = new SqlCommand(
    "dbo.uspGetEmployeeManagers",
    connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(
    "@BusinessEntityID",
    SqlDbType.Int).Value = 6;

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetString(2)} {reader.GetString(3)}");
}

使用该例程声明的参数名称。 架构限定可避免名称解析歧义和不必要的过程计划变体。

运行数据修改过程

AdventureWorks 还包含修改数据的过程。 HumanResources.uspUpdateEmployeePersonalInfo 替换员工所有的个人字段。 以下示例在事务中运行以下步骤:

  1. 读取员工的当前值。
  2. 改变婚姻状态。
  3. 将更改读回。
  4. 回滚事务,使样本数据库保持不变。
const int businessEntityId = 1;
using SqlTransaction transaction = connection.BeginTransaction();

string nationalIdNumber;
DateTime birthDate;
string maritalStatus;
string gender;

using (var select = new SqlCommand(
    """
    SELECT NationalIDNumber, BirthDate, MaritalStatus, Gender
    FROM HumanResources.Employee
    WHERE BusinessEntityID = @BusinessEntityID;
    """,
    connection,
    transaction))
{
    select.Parameters.Add(
        "@BusinessEntityID",
        SqlDbType.Int).Value = businessEntityId;

    using SqlDataReader reader = select.ExecuteReader();
    if (!reader.Read())
    {
        throw new InvalidOperationException(
            $"Employee {businessEntityId} wasn't found.");
    }

    nationalIdNumber = reader.GetString(0);
    birthDate = reader.GetDateTime(1);
    maritalStatus = reader.GetString(2);
    gender = reader.GetString(3);
}

string newMaritalStatus = maritalStatus == "M" ? "S" : "M";

using (var command = new SqlCommand(
    "HumanResources.uspUpdateEmployeePersonalInfo",
    connection,
    transaction))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(
        "@BusinessEntityID",
        SqlDbType.Int).Value = businessEntityId;
    command.Parameters.Add(
        "@NationalIDNumber",
        SqlDbType.NVarChar,
        15).Value = nationalIdNumber;
    command.Parameters.Add(
        "@BirthDate",
        SqlDbType.DateTime).Value = birthDate;
    command.Parameters.Add(
        "@MaritalStatus",
        SqlDbType.NChar,
        1).Value = newMaritalStatus;
    command.Parameters.Add(
        "@Gender",
        SqlDbType.NChar,
        1).Value = gender;

    command.ExecuteNonQuery();
}

using (var verify = new SqlCommand(
    """
    SELECT MaritalStatus
    FROM HumanResources.Employee
    WHERE BusinessEntityID = @BusinessEntityID;
    """,
    connection,
    transaction))
{
    verify.Parameters.Add(
        "@BusinessEntityID",
        SqlDbType.Int).Value = businessEntityId;
    Console.WriteLine(
        $"Marital status: {maritalStatus} -> {verify.ExecuteScalar()}");
}

transaction.Rollback();

该过程设置 SET NOCOUNT ON,因此 ExecuteNonQuery 返回 -1 而不是行数。 不要用返回值来确认更新。 再读一遍那一行。

在执行某个过程之前,请先确认其调用约定和副作用。 每个在具有待处理本地事务的连接上运行的命令都必须设置 Transaction。 确保过程的事务处理支持外部事务。 该过程会发现错误并用 dbo.uspLogError 将其记录下来,而不是直接将错误抛给调用方。

读取结果集

使用 ExecuteReader 或 ExecuteReaderAsync 当过程返回行时。 当返回多个结果集时,调用 NextResult 或 NextResultAsync,直到返回 false。

在读取输出参数、输入/输出参数或返回值参数之前,请先释放读取器。 阅读器打开时,这些数值并非最终定论。

配置参数方向

为所有不是仅输入的参数设置 Direction :

SqlParameter output = command.Parameters.Add(
    "@outputValue",
    SqlDbType.NVarChar,
    50);
output.Direction = ParameterDirection.Output;

SqlParameter inputOutput = command.Parameters.Add(
    "@counter",
    SqlDbType.Int);
inputOutput.Direction = ParameterDirection.InputOutput;
inputOutput.Value = 1;

SqlParameter returnValue = command.Parameters.Add(
    "@returnValue",
    SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;

对于可变长度的输出和输入/输出参数,请设置为 Size。 输入/输出参数也需要一个初始 Value。 返回值参数接收过程的 Transact-SQL(T-SQL) RETURN 语句返回的整数。 它不会接收该过程返回的结果集或值。

仅在执行完成并且所有读取器都已关闭后,才读取每个参数的 Value:

command.ExecuteNonQuery();

Console.WriteLine(output.Value);
Console.WriteLine(inputOutput.Value);
Console.WriteLine(returnValue.Value);

当过程未给输出参数赋值时,该参数的值可能是 DBNull.Value。

选择一种执行方法

使用与过程结果匹配的执行方法:

方法 何时使用
ExecuteReader 该过程返回行或多个结果集。
ExecuteNonQuery 你需要输出参数或返回值,但不需要结果行。 客户端在调用完成前会消耗返回的任何行。
ExecuteScalar 你只需要第一行的第一列。

ExecuteNonQuery 在可用时返回受影响的行数。 一个可以 SET NOCOUNT ON 修改行且仍然返回 -1的过程。 当过程将某个输出参数或返回值定义为其成功约定时,请使用该输出参数或返回值。

注意

当 SqlDataAdapter 使用存储过程更新或删除行时,如果适配器依赖受影响行计数来实现乐观并发,就不要使用 SET NOCOUNT ON。 计数为零时,可能导致 DBConcurrencyException。

处理故障

在执行调用周围捕获 SqlException。 过程可以管理自身的事务和错误处理,但客户端仍需决定操作是否安全可重试,以及其连接是否仍可用。

关于服务器错误和 PRINT 消息,请参见 处理错误和消息。