使用預存程序修改資料

設定 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 替換員工所有的個人欄位。 以下範例在交易作業中執行以下步驟:

  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 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 訊息,請參見 處理錯誤與訊息。