設定 CommandType 為 CommandType.StoredProcedure ,且 設定 CommandText 為 schema-限定程序名稱。 為每個程序參數新增一個參數,並匹配類型、大小與方向。
本文範例假設有一個已開啟且名為 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 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 訊息,請參見 處理錯誤與訊息。