[C#] 追加・更新・削除とトランザクションを理解する

前回は、接続、コマンド、データ読み取りを使って SELECT を実行しました。

今回は、データの追加、更新、削除、ストアドプロシージャ、トランザクションを扱います。
読み取りよりもデータを変更する処理の方が、失敗時の影響が大きくなります。パラメーターとトランザクションを丁寧に使いましょう。

追加用のモデル

まず、車を表すモデルを用意します。

public sealed class Car
{
    public int Id { get; init; }
    public string Make { get; init; } = "";
    public string Color { get; init; } = "";
    public string PetName { get; init; } = "";
}

INSERT では、Id は自動採番されるため、メーカー、色、愛称を渡します。

データを追加する

using Microsoft.Data.SqlClient;
using System.Data;

static async Task<int> AddCarAsync(
    string connectionString,
    string make,
    string color,
    string petName)
{
    await using SqlConnection connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    await using SqlCommand command = connection.CreateCommand();
    command.CommandText = """
        INSERT INTO Inventory (Make, Color, PetName)
        OUTPUT INSERTED.Id
        VALUES (@make, @color, @petName);
        """;

    command.Parameters.Add(new SqlParameter("@make", SqlDbType.NVarChar, 50)
    {
        Value = make
    });
    command.Parameters.Add(new SqlParameter("@color", SqlDbType.NVarChar, 50)
    {
        Value = color
    });
    command.Parameters.Add(new SqlParameter("@petName", SqlDbType.NVarChar, 50)
    {
        Value = petName
    });

    object? result = await command.ExecuteScalarAsync();

    return Convert.ToInt32(result);
}

OUTPUT INSERTED.Id を使うと、追加された行の ID を取得できます。

データを更新する

using Microsoft.Data.SqlClient;
using System.Data;

static async Task<bool> UpdatePetNameAsync(
    string connectionString,
    int id,
    string newPetName)
{
    await using SqlConnection connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    await using SqlCommand command = connection.CreateCommand();
    command.CommandText = """
        UPDATE Inventory
        SET PetName = @petName
        WHERE Id = @id;
        """;

    command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)
    {
        Value = id
    });
    command.Parameters.Add(new SqlParameter("@petName", SqlDbType.NVarChar, 50)
    {
        Value = newPetName
    });

    int affectedRows = await command.ExecuteNonQueryAsync();

    return affectedRows == 1;
}

ExecuteNonQueryAsync() は、影響を受けた行数を返します。
主キーで 1 件更新する想定なら、戻り値が 1 かどうかを確認するとよいです。

データを削除する

using Microsoft.Data.SqlClient;
using System.Data;

static async Task<bool> DeleteCarAsync(
    string connectionString,
    int id)
{
    await using SqlConnection connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    await using SqlCommand command = connection.CreateCommand();
    command.CommandText = """
        DELETE FROM Inventory
        WHERE Id = @id;
        """;

    command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)
    {
        Value = id
    });

    int affectedRows = await command.ExecuteNonQueryAsync();

    return affectedRows == 1;
}

外部キー制約がある場合、関連する注文が残っている車を削除できないことがあります。
このような失敗は、データ整合性を守るための大切な仕組みです。

パラメーターを使う理由

SQL 文字列へ値を直接埋め込むのは避けます。

// 避けたい例
command.CommandText =
    $"DELETE FROM Inventory WHERE PetName = '{petName}'";

この書き方は、SQL インジェクションやクォート処理の問題を招きます。

パラメーターを使うと、SQL の構造と値を分離できます。

command.CommandText = "DELETE FROM Inventory WHERE PetName = @petName";
command.Parameters.Add(new SqlParameter("@petName", SqlDbType.NVarChar, 50)
{
    Value = petName
});

SQL を書くときは、外部入力は必ずパラメーターとして渡す、という習慣を持ちましょう。

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

前に作成した GetPetName を呼び出します。

using Microsoft.Data.SqlClient;
using System.Data;

static async Task<string?> GetPetNameAsync(
    string connectionString,
    int carId)
{
    await using SqlConnection connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    await using SqlCommand command = connection.CreateCommand();
    command.CommandText = "GetPetName";
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add(new SqlParameter("@carId", SqlDbType.Int)
    {
        Value = carId
    });

    SqlParameter output = new SqlParameter("@petName", SqlDbType.NVarChar, 50)
    {
        Direction = ParameterDirection.Output
    };

    command.Parameters.Add(output);

    await command.ExecuteNonQueryAsync();

    return output.Value == DBNull.Value
        ? null
        : (string)output.Value;
}

ストアドプロシージャでは、CommandType.StoredProcedure を指定します。
出力パラメーターを使う場合は、Direction を設定します。

トランザクションとは

トランザクションは、複数の操作を 1 つの成功/失敗単位として扱う仕組みです。

たとえば、注文を作成し、在庫状態も更新する場合、片方だけ成功するとデータが不整合になります。
両方成功したら確定し、途中で失敗したら元に戻す、という制御が必要です。

処理A 成功
処理B 成功
  -> コミット

処理A 成功
処理B 失敗
  -> ロールバック

トランザクションを使う

using Microsoft.Data.SqlClient;
using System.Data;

static async Task CreateOrderAsync(
    string connectionString,
    int carId,
    string customerName)
{
    await using SqlConnection connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    await using SqlTransaction transaction =
        (SqlTransaction)await connection.BeginTransactionAsync();

    try
    {
        await using SqlCommand insertOrder = connection.CreateCommand();
        insertOrder.Transaction = transaction;
        insertOrder.CommandText = """
            INSERT INTO Orders (CarId, CustomerName)
            VALUES (@carId, @customerName);
            """;

        insertOrder.Parameters.Add(new SqlParameter("@carId", SqlDbType.Int)
        {
            Value = carId
        });
        insertOrder.Parameters.Add(new SqlParameter("@customerName", SqlDbType.NVarChar, 100)
        {
            Value = customerName
        });

        await insertOrder.ExecuteNonQueryAsync();

        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

トランザクションを使うときは、コマンドに Transaction を設定します。
設定し忘れると、そのコマンドはトランザクション外で実行される可能性があります。

トランザクションの注意点

トランザクションは便利ですが、長く開きっぱなしにするとデータベースへの負荷やロック競合が増えます。

基本方針は次の通りです。

必要な範囲だけをトランザクションに含める
ユーザー入力待ちをトランザクション内に入れない
外部 API 呼び出しなど時間の読めない処理を避ける
失敗時は必ずロールバックする
例外はログに残す

トランザクションは「一貫性を守るための短い境界」と考えると扱いやすいです。

小さなデータアクセスクラスにまとめる

処理が増えてきたら、データアクセス用のクラスにまとめます。

public sealed class InventoryRepository
{
    private readonly string connectionString;

    public InventoryRepository(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public Task<int> AddAsync(string make, string color, string petName)
    {
        return AddCarAsync(connectionString, make, color, petName);
    }

    public Task<bool> UpdatePetNameAsync(int id, string petName)
    {
        return UpdatePetNameAsync(connectionString, id, petName);
    }

    public Task<bool> DeleteAsync(int id)
    {
        return DeleteCarAsync(connectionString, id);
    }
}

実務では、接続文字列、ログ、例外変換、トランザクション管理なども含めて設計します。

まとめ

更新系 SQL では、ExecuteNonQueryAsync() とパラメーターが基本になります。
新規追加で ID を取得したい場合は ExecuteScalarAsync() も便利です。

複数の変更を一貫した単位として扱う場合は、トランザクションを使います。
次回は、大量データを効率よく投入する一括コピーを見ていきます。