ストアド プロシージャによるデータの変更

CommandTypeをCommandType.StoredProcedureに設定し、CommandTextをスキーマ限定プロシージャ名に設定します。 各プロシージャ パラメーターごとに、型、サイズ、方向が一致するパラメーターを 1 つ追加します。

この記事の例では、connection という名前のオープン SqlConnection が AdventureWorks サンプル データベース をターゲットとしていることを前提としています。

C#の例は using Microsoft.Data.SqlClient;を必要とします。 SqlDbTypeやCommandTypeを使う例もusing System.Data;が必要です。

ストアド プロシージャを呼び出す

AdventureWorksには dbo.uspGetEmployeeManagersが含まれており、これは1つの入力パラメータを受け付け、結果セットを返します:

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 を使いましょう。 複数の結果セットが返された場合は、false が返されるまで NextResult または NextResultAsync を呼び出してください。

出力、入力/出力、または戻り値パラメーターを読み取る前に、reader を破棄してからにしてください。 リーダーが開いている間は、これらの値は最終的なものではありません。

パラメータの入出力方向を設定する

入力のみでないすべてのパラメータに対して 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になることがあります。

実行方法を選択する

プロシージャの結果に一致する実行メソッドを使いましょう:

ExecuteNonQuery は、利用可能な場合、影響を受けた行数を返します。 SET NOCOUNT ONを使う手続きは行を修正しても-1を返すことができます。 プロシージャが成功条件として出力パラメーターまたは戻り値を定義している場合は、それらを使用します。

障害に対応

実行呼び出しの周辺で SqlException をキャッチします。 プロシージャは自身のトランザクションやエラー処理を管理できますが、クライアントは操作の再試行が安全かどうか、また接続が使えるかどうかを判断しなければなりません。

サーバー エラーとPRINTメッセージについては、エラーとメッセージの処理を参照してください。

関連するコンテンツ

• パラメーターを構成する
• コマンドを実行する
• テーブル値パラメーター
• エラーとメッセージの処理