bucket-sort logo bucket-sort

プログラミングとインフラエンジニアリングの覚え書き

[C#] 接続・コマンド・データ読み取りを深掘りする

C# , .NET , Data Access bucket-sort

前回は、データプロバイダーを抽象化して扱う方法を見ました。

今回は、SQL Server 用の具象型を使って、接続、コマンド、データ読み取りを深掘りします。
ADO.NET の基本形をしっかり理解しておくと、更新処理やトランザクションも読みやすくなります。

接続の寿命を短く保つ

データベース接続は、必要なときに開き、使い終わったらすぐ閉じるのが基本です。

using Microsoft.Data.SqlClient;

await using SqlConnection connection = new SqlConnection(connectionString);
await connection.OpenAsync();

// 必要な処理だけ行う

SqlConnection は内部的に接続プールを利用します。
Dispose しても物理接続が毎回完全に破棄されるとは限らず、再利用可能な接続としてプールへ戻されます。

そのため、アプリ側では接続を長く持ち回すより、短く開いて短く閉じる方が扱いやすくなります。

コマンドを作成する

SQL を実行するには SqlCommand を使います。

await using SqlCommand command = connection.CreateCommand();

command.CommandText = """
    SELECT Id, Make, Color, PetName
    FROM Inventory
    ORDER BY Id
    """;

CommandText には SQL 文またはストアドプロシージャ名を設定します。

SQL 文を直接設定する場合、CommandType は既定値のままで構いません。
ストアドプロシージャを呼ぶ場合は、後の記事で見るように CommandType.StoredProcedure を指定します。

データ読み取りオブジェクトで 1 行ずつ読む

ExecuteReaderAsync() を呼ぶと、結果セットを読み取るための SqlDataReader が返ります。

await using SqlDataReader reader = await command.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
    int id = reader.GetInt32(0);
    string make = reader.GetString(1);
    string color = reader.GetString(2);
    string petName = reader.GetString(3);

    Console.WriteLine($"{id}: {color} {make} ({petName})");
}

ReadAsync() は次の行へ進みます。
読み取れる行があれば true、もうなければ false を返します。

SqlDataReader は前方へ 1 行ずつ読むための軽量な仕組みです。
大量データを一括でメモリへ読み込まずに処理できます。

列番号ではなく列名で読む

列番号で読むと高速ですが、SQL の列順に依存します。

int id = reader.GetInt32(0);

列名から番号を取得すると、読みやすくなります。

int idOrdinal = reader.GetOrdinal("Id");
int makeOrdinal = reader.GetOrdinal("Make");
int colorOrdinal = reader.GetOrdinal("Color");
int petNameOrdinal = reader.GetOrdinal("PetName");

while (await reader.ReadAsync())
{
    int id = reader.GetInt32(idOrdinal);
    string make = reader.GetString(makeOrdinal);
    string color = reader.GetString(colorOrdinal);
    string petName = reader.GetString(petNameOrdinal);

    Console.WriteLine($"{id}: {color} {make} ({petName})");
}

GetOrdinal() はループの外で呼ぶと効率的です。

null 値を扱う

データベースの NULL は、C# の null と似ていますが、読み取り時には明示的な確認が必要です。

たとえば PetName が NULL になり得る場合です。

int petNameOrdinal = reader.GetOrdinal("PetName");

string? petName = reader.IsDBNull(petNameOrdinal)
    ? null
    : reader.GetString(petNameOrdinal);

GetString() などの型付きメソッドは、値が NULL だと例外になります。
nullable な列では IsDBNull() を確認しましょう。

モデルへマッピングする

読み取った値をそのまま表示するだけでなく、モデルへ変換するとアプリケーション側で扱いやすくなります。

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

読み取り処理です。

static async Task<List<Car>> GetAllCarsAsync(string connectionString)
{
    var cars = new List<Car>();

    await using SqlConnection connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    await using SqlCommand command = connection.CreateCommand();
    command.CommandText = """
        SELECT Id, Make, Color, PetName
        FROM Inventory
        ORDER BY Id
        """;

    await using SqlDataReader reader = await command.ExecuteReaderAsync();

    int idOrdinal = reader.GetOrdinal("Id");
    int makeOrdinal = reader.GetOrdinal("Make");
    int colorOrdinal = reader.GetOrdinal("Color");
    int petNameOrdinal = reader.GetOrdinal("PetName");

    while (await reader.ReadAsync())
    {
        cars.Add(new Car
        {
            Id = reader.GetInt32(idOrdinal),
            Make = reader.GetString(makeOrdinal),
            Color = reader.GetString(colorOrdinal),
            PetName = reader.GetString(petNameOrdinal)
        });
    }

    return cars;
}

ADO.NET は ORM ではないため、このマッピングは自分で書きます。
小さな処理では明示的で分かりやすい一方、テーブルや列が増えると手間も増えます。

1 件だけ取得する

主キーで 1 件取得する例です。

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

    await using SqlCommand command = connection.CreateCommand();
    command.CommandText = """
        SELECT Id, Make, Color, PetName
        FROM Inventory
        WHERE Id = @id
        """;

    command.Parameters.AddWithValue("@id", id);

    await using SqlDataReader reader = await command.ExecuteReaderAsync();

    if (!await reader.ReadAsync())
    {
        return null;
    }

    return new Car
    {
        Id = reader.GetInt32(reader.GetOrdinal("Id")),
        Make = reader.GetString(reader.GetOrdinal("Make")),
        Color = reader.GetString(reader.GetOrdinal("Color")),
        PetName = reader.GetString(reader.GetOrdinal("PetName"))
    };
}

@id のようなパラメーターを使うことで、値を SQL 文字列へ直接埋め込まずに渡せます。

AddWithValue の注意点

AddWithValue() は短く書けるためサンプルでは便利です。

command.Parameters.AddWithValue("@id", id);

ただし、実務では SQL Server 側の型推論が意図とずれることがあります。
文字列長や数値型を明示したい場合は、SqlParameter を明示的に作る方が安全です。

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

このあたりは、更新系の記事でさらに扱います。

接続エラーを扱う

データベースアクセスは失敗する可能性があります。

try
{
    List<Car> cars = await GetAllCarsAsync(connectionString);

    foreach (Car car in cars)
    {
        Console.WriteLine($"{car.Id}: {car.PetName}");
    }
}
catch (SqlException ex)
{
    Console.WriteLine($"SQL Server エラー: {ex.Message}");
}
catch (InvalidOperationException ex)
{
    Console.WriteLine($"操作エラー: {ex.Message}");
}

接続先が落ちている、認証に失敗する、SQL が間違っている、列名が変わった、などさまざまな原因で例外が発生します。

本番コードでは、ユーザーへ見せるメッセージと、ログへ残す詳細情報を分けるとよいです。

まとめ

ADO.NET の読み取り処理は、接続、コマンド、データ読み取りオブジェクトの組み合わせで構成されます。

SqlDataReader は高速で軽量ですが、前方読み取り専用です。
列番号、NULL、型変換、モデルへのマッピングを丁寧に扱う必要があります。

次回は、INSERT / UPDATE / DELETE、パラメーター、ストアドプロシージャ、トランザクションを扱います。

Related Posts