将 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 替换员工所有的个人字段。 以下示例在事务中运行以下步骤:
- 读取员工的当前值。
- 改变婚姻状态。
- 将更改读回。
- 回滚事务,使样本数据库保持不变。
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 消息,请参见 处理错误和消息。