Go to Qiita Advent Calendar 2024 Top
LoginSignup
13
23

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

C#Advent Calendar 2022

Day 25
@YuMo_tea(おこめ)

【C#】Dapper忘備録【基本編】

Last updated at Posted at 2022-12-25

Qiita Advent Calendar 3回目の参加です。よろしくお願いします。
今年も個人的にお世話になった、Dapperに関するあれこれをまとめました。

Dapper とは　

.NETプラットフォーム用のマイクロORM（Object-relational mapping）です。
Dapperを使用する場合、SQLとマッピング用のクラスは自力で書く必要があります。SQLを書けることが前提のオブジェクトマッパーです。
通常のORMを使用したことがないのでなんとも言えないのですが、
単純にデータベースとオブジェクトをマッピングする場合、Dapperは非常に使いやすいのではないでしょうか。

Is Dapper an ORM? の項目に、通常のORMとの違いが記載されています。

dapper専用のChat GPTが出来ていました。便利です。

IDbConnection と Dapper

DapperはC#の IDbConnection を拡張して作られています。そのため、Connection型の変数から直接メソッドを呼び出すことができます。

using var connection = new SqliteConnection(DataSource);
var result = connection.Query<ArimaKinenDTO>(sql);

下準備：arima_kinen テーブルの作成

以下のようなテーブルを作成しました。
※日本語と英語バラバラですがご容赦ください……

NO カラム名 日本語名 型 備考
1 umaban 馬番 INTEGER PRIMARY KEY 自動採番
2 wakuban 枠番 INTEGER NOT NULL
3 bamei 馬名 TEXT NOT NULL
4 seibetu 性別 TEXT NOT NULL
5 barei 馬齢 INTEGER NOT NULL
6 kinryo 斤量 INTEGER NOT NULL
7 kisyu 騎手 TEXT NOT NULL
8 kyusya 厩舎 TEXT NOT NULL
9 createdate 作成日 TEXT NOT NULL
10 updatedate 更新日 TEXT NOT NULL

下準備：arima_kinen テーブルをマッピングするクラスの作成（SELECT文実行時に使用）

ざっくり言うと、DBから取得したデータを受け取るクラスです。取得するテーブルと対になります。今回はテーブルのカラム名とクラスのプロパティ名が完全一致している前提で作成しました。

※DTOなのかエンティティなのかは諸説分かれる気がするのですが、とりあえずここではDTOとして作成しました。

DTOクラス
public class ArimaKinenDTO
{
    /// <summary>
    /// 枠番
    /// </summary>
    public int Wakuban { get; set; }

    /// <summary>
    /// 馬番
    /// </summary>
    public int Umaban { get; set; }

    /// <summary>
    /// 馬名
    /// </summary>
    public string Bamei { get; set; }

    /// <summary>
    /// 性別
    /// </summary>
    public string Seibetu { get; set; }

    /// <summary>
    /// 馬齢
    /// </summary>
    public int Barei { get; set; }

    /// <summary>
    /// 斤量
    /// </summary>
    public int Kinryo { get; set; }

    /// <summary>
    /// 騎手
    /// </summary>
    public string Kisyu { get; set; }

    /// <summary>
    /// 厩舎
    /// </summary>
    public string Kyusya { get; set; }
}

【補足】 テーブルのカラム名とクラスのプロパティ名を一致させず、Dapperの機能である Dapper.SqlMapper.SetTypeMap と CustomPropertyTypeMap でクラスのプロパティとテーブル列のカスタムマッピングを定義することも可能です。

データを取得する場合【SELECT】

複数行のデータを取得する場合

以下のメソッドが使用できます。

メソッド名 SELECTした結果 戻り値
Query 1つ以上の行 列挙可能なdynamic型を返す。該当するデータがない場合は空のコレクションを返す。

Query を使用すると IEnumerable<dynamic> でデータが返りますが、dynamic型で受け取る理由が特別にない限り Query<T> で戻り値の型を指定して受け取ることが多いです。型指定をした場合 IEnumerable<T> が戻ります。下記の例では戻り値の型を先ほど用意した XXXDTO とします。
非同期の場合、メソッド名の末尾に Async が付きます。

Query は、第一引数に string 型のSQLを渡します。
第二引数以降は無くても構いません。しかし、後述する「動的なパラメーター」を設定するための param 引数や、トランザクションを指定するための transaction 引数は使用頻度が高いです。
image.png

型指定あり
image.png

型指定なし
image.png

Query
private static void QueryMultipleRows(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。

    var sql = @"SELECT * FROM arima_kinen;";
    var rows = connection.Query<ArimaKinenDTO>(sql);

    foreach (var row in rows)
    {
        Console.WriteLine($"{row.Wakuban} {row.Umaban} {row.Bamei} {row.Seibetu}{row.Barei} {row.Kinryo} {row.Kisyu} {row.Kyusya}");
    }
}

1 1 アカイイト 牝5 55 幸 中竹 
1 2 イズジョーノキセキ 牝5 55 岩田康 石坂        
2 3 ボルドグフーシュ 牡3 55 福永 宮本
2 4 アリストテレス 牡5 57 武豊 音無
3 5 ジェラルディーナ 牝4 55 Ｃデムーロ 斉藤崇    
3 6 ヴェラアズール 牡5 57 松山 渡辺
4 7 エフフォーリア 牡4 57 横山武 鹿戸
4 8 ウインマイティー 牝5 55 和田竜 五十嵐        
5 9 イクイノックス 牡3 55 ルメール 木村
5 10 ジャスティンパレス 牡3 55 マーカンド 杉山晴 
6 11 ラストドラフト 牡6 57 三浦 戸田
6 12 ポタジェ 牡5 57 吉田隼 友道
7 13 タイトルホルダー 牡4 57 横山和 栗田
7 14 ボッケリーニ 牡6 57 浜中 池江
8 15 ブレークアップ 牡4 57 戸崎圭 黒岩
8 16 ディープボンド 牡5 57 川田 大久保

単一のデータを取得する場合

以下のメソッドが使用できます。

メソッド名 SELECTした結果 戻り値
QuerySingle 1行のみ dynamic型を返す。該当するデータがない、または複数行を返す場合は例外がスローされる。
QuerySingleOrDefault 0行または1行 dynamic型を返す。該当するデータがない場合はnullを返すが、複数行を返す場合は例外がスローされる。
QueryFirst 1つ以上の行 最初の行をdynamic型で返す。該当するデータがない場合は例外がスローされる。
QueryFirstOrDefault 1つ以上の行 最初の行をdynamic型で返す。該当するデータがない場合はnullを返す。

複数行取得時と同じく、型指定なしで使用すると dynamic でデータが返りますが、dynamic型で受け取る理由が特別にない限り、QueryXXX<T>で戻り値の型を指定して受け取ることが多いです。型指定をした場合 T が戻ります。下記の例では戻り値の型を先ほど用意した XXXDTO とします。
非同期の場合、メソッド名の末尾にAsyncが付きます。

複数行取得時と同じく QueryXXX の第一引数に string 型のSQLを渡し、第二引数以降は任意で設定します。
image.png

QuerySingleOrDefault
private static void QuerySingleRow(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。
    
    // QuerySingleOrDefault(QuerySingle)の場合、複数行を取得するSELECT文を投げるとエラーになります。必ず単一行を返すSELECT文を作成してください。
    var sql = @"SELECT * FROM arima_kinen WHERE umaban = 8;";
    var singleRow = connection.QuerySingleOrDefault<ArimaKinenDTO>(sql);

    // 1行を取得する場合、取得したデータがnullかどうかをチェックして処理を分岐することがある
    if (singleRow == null)
    {
        // エラーの処理など
    }
    else
    {
        Console.WriteLine($"{singleRow.Wakuban} {singleRow.Umaban} {singleRow.Bamei} {singleRow.Seibetu}{singleRow.Barei} {singleRow.Kinryo} {singleRow.Kisyu} {singleRow.Kyusya}");
    }
}

4 8 ウインマイティー 牝5 55 和田竜 五十嵐
QueryFirstOrDefault
private static void QueryFirstRow(SqliteConnection connection)
{   
    // SqliteConnectionは事前に用意し、引数から渡します。
    
    var sql = @"SELECT * FROM arima_kinen;";
    var firstRow = connection.QueryFirstOrDefault<ArimaKinenDTO>(sql);

    // 1行を取得する場合、取得したデータがnullかどうかをチェックして処理を分岐することがある
    if (firstRow == null)
    {
        // エラーの処理など
    }
    else
    {
        Console.WriteLine($"{firstRow.Wakuban} {firstRow.Umaban} {firstRow.Bamei} {firstRow.Seibetu}{firstRow.Barei} {firstRow.Kinryo} {firstRow.Kisyu} {firstRow.Kyusya}");
    }
}

1 1 アカイイト 牝5 55 幸 中竹

複数のSELECT文を実行し、データを取得する場合

以下のメソッドが使用できます。

メソッド名 説明
QueryMultiple 複数のSELECT文を実行できる。SqlMapper.GridReader を返す。

このメソッドは型指定できません。 非同期の場合、メソッド名の末尾に Async が付きます。
売上情報＋売上明細や、請求情報＋請求明細など、関連するテーブルを一括で取得したい場合によく使用します。

複数行取得時、単一行取得時と同じく QueryMultiple の第一引数に string 型のSQLを渡し、第二引数以降は任意で設定します。
image.png

各SELECT文の結果を受け取る

QueryMultiple で複数のSELECT文を実行した場合、戻り値からさらに下記のメソッドを呼び出し、各SQLごとの結果を取得します。

メソッド名 取得対象の行数 SELECTした結果 戻り値
Read 複数行 1つ以上の行 列挙可能なdynamic型を返す。該当するデータがない場合は空のコレクションを返す。
ReadSingle 単一行 1行のみ dynamic型を返す。該当するデータがない、または複数行を返す場合は例外がスローされる。
ReadSingleOrDefault 単一行 0行または1行 dynamic型を返す。該当するデータがない場合はnullを返すが、複数行を返す場合は例外がスローされる。
ReadFirst 単一行 1つ以上の行 最初の行をdynamic型で返す。該当するデータがない場合は例外がスローされる。
ReadFirstOrDefault 単一行 1つ以上の行 最初の行をdynamic型で返す。該当するデータがない場合はnullを返す。

複数行取得時、単一行取得時と同じく、型指定なしで使用すると dynamic でデータが返りますが、dynamic型で受け取る理由が特別にない限り、ReadXXX<T>で戻り値の型を指定して受け取ることが多いです。型指定をした場合 T が戻ります。下記の例では戻り値の型を先ほど用意した XXXDTO とします。
非同期の場合、メソッド名の末尾に Async が付きます。

QueryMultiple
private static void QueryMultipleSelects(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。
    
    var sql = @"
        SELECT * FROM arima_kinen;
        SELECT * FROM arima_kinen WHERE umaban = 13;
        SELECT COUNT(*) FROM arima_kinen;
        ";

    // SELECT文をまとめて実行します。
    var multi = connection.QueryMultiple(sql);

    // 戻り値の型を指定して1番目のSELECT文の取得結果を受け取ります。
    var allRows = multi.Read<ArimaKinenDTO>();

    // 戻り値の型を指定して2番目のSELECT文の取得結果を受け取ります。
    var firstReadRow = multi.ReadFirstOrDefault<ArimaKinenDTO>();

    // 戻り値の型を指定して3番目のSELECT文の取得結果を受け取ります。
    var count = multi.ReadFirstOrDefault<int>();

    foreach (var row in allRows)
    {
        Console.WriteLine($"{row.Wakuban} {row.Umaban} {row.Bamei} {row.Seibetu}{row.Barei} {row.Kinryo} {row.Kisyu} {row.Kyusya}");
    }

    Console.WriteLine($"\r\n{firstReadRow?.Wakuban} {firstReadRow?.Umaban} {firstReadRow?.Bamei} {firstReadRow?.Seibetu}{firstReadRow?.Barei} {firstReadRow?.Kinryo} {firstReadRow?.Kisyu} {firstReadRow?.Kyusya}");

    Console.WriteLine($"\r\n2022年の有馬記念は計{count}頭が出走しました。");
}

1 1 アカイイト 牝5 55 幸 中竹
1 2 イズジョーノキセキ 牝5 55 岩田康 石坂
2 3 ボルドグフーシュ 牡3 55 福永 宮本
2 4 アリストテレス 牡5 57 武豊 音無
3 5 ジェラルディーナ 牝4 55 Ｃデムーロ 斉藤崇
3 6 ヴェラアズール 牡5 57 松山 渡辺
4 7 エフフォーリア 牡4 57 横山武 鹿戸
4 8 ウインマイティー 牝5 55 和田竜 五十嵐
5 9 イクイノックス 牡3 55 ルメール 木村
5 10 ジャスティンパレス 牡3 55 マーカンド 杉山晴
6 11 ラストドラフト 牡6 57 三浦 戸田
6 12 ポタジェ 牡5 57 吉田隼 友道
7 13 タイトルホルダー 牡4 57 横山和 栗田
7 14 ボッケリーニ 牡6 57 浜中 池江
8 15 ブレークアップ 牡4 57 戸崎圭 黒岩
8 16 ディープボンド 牡5 57 川田 大久保

4 7 エフフォーリア 牡4 57 横山武 鹿戸

2022年の有馬記念は計16頭が出走しました。

動的なパラメーターを設定する

実際のSQLではSELECTのWHERE句などに固定の値を渡すことは少なく、プレースホルダーを使用することが多いです。
パラメーター部分を @xxx と記述し、動的にパラメーターを指定できるようにします。

SELECT * FROM arima_kinen WHERE umaban = @umaban;

Dapperでは、以下のようにパラメーターを設定できます。

var result = connection.QueryFirstOrDefault<ArimaKinenDTO>(sql, xxx); // 第2引数にパラメーターを渡す

この章では一例としてSELECTのWHERE句にパラメーターを使用する方法を載せていますが、SELECT以外にもINSERTのVALUES、UPDATEのSETなど @xxx のパラメーターは様々な箇所で利用することができます。

Anonymous ParameterとDynamic Parameters

複数あるパラメーターのうち、よく使う2つのパラメーターを紹介します。
どちらが良いというものはなく、実行するSQL文に応じてどちらかを選択しますが、使用頻度が高いのは「匿名パラメーター(Anonymous Parameter)」です。

  • 匿名パラメーター(Anonymous Parameter)
    各メソッドの第2引数に new {xxx = XXX} の匿名型を渡す。
    Dapper - Anonymous Parameter

  • 動的パラメーター(Dynamic Parameters)
    各メソッドの第2引数に DynamicParameters 型の変数を渡す。
    Dapper - Dynamic Parameter

パラメーターの指定
private static void QueryWithSingleParameter(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。
    
    var sql = @"SELECT * FROM arima_kinen WHERE umaban = @umaban;";

    // 匿名パラメーター(Anonymous Parameter)の場合
    // new {[カラム名] = [値]} でパラメーターを作成します。
    var param1 = 3;
    var apResult = connection.QueryFirstOrDefault<ArimaKinenDTO>(sql, new { umaban = param1 });

    Console.WriteLine($"{apResult?.Wakuban} {apResult?.Umaban} {apResult?.Bamei} {apResult?.Seibetu}{apResult?.Barei} {apResult?.Kinryo} {apResult?.Kisyu} {apResult?.Kyusya}");

    // 動的パラメーター(Dynamic Parameters)の場合
    // Dynamic Parametersを作成し、Addメソッドでパラメーターを作成します。
    var parameters = new DynamicParameters();
    var param2 = "4";
    parameters.Add("@umaban", param2, DbType.Int32); // 第3引数はパラメータの型を指定します。 
    var dpResult = connection.QueryFirstOrDefault<ArimaKinenDTO>(sql, parameters);

    Console.WriteLine($"{dpResult?.Wakuban} {dpResult?.Umaban} {dpResult?.Bamei} {dpResult?.Seibetu}{dpResult?.Barei} {dpResult?.Kinryo} {dpResult?.Kisyu} {dpResult?.Kyusya}");
}

2 3 ボルドグフーシュ 牡3 55 福永 宮本
2 4 アリストテレス 牡5 57 武豊 音無

WHERE IN パラメーター

各メソッドの第2引数に new {xxx = XXX} の匿名型を渡すとき、配列（リスト）形式の変数を使用する。
Dapper - List Parameter

SELECT * FROM arima_kinen WHERE umaban in (1, 5, 9); -- (1, 5, 9)の部分を配列（リスト）で指定することができる　
IN句のパラメーター
private static void QueryWithInClause(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。
    
    var sql = @"SELECT * FROM arima_kinen WHERE umaban in @umaban;";

    // in句に設定したい値で配列を作成します。new List<int> { 1, 5, 9 }; でもOK
    var numbers = new[] { 1, 5, 9 };
    var numbersList = new List<int> { 1, 5, 9 };

    // Anonymous Parameterで配列のパラメータを作成します。
    var targetRows = connection.Query<ArimaKinenDTO>(sql, new { umaban = numbers });

    foreach (var row in targetRows)
    {
        Console.WriteLine($"{row.Wakuban} {row.Umaban} {row.Bamei} {row.Seibetu}{row.Barei} {row.Kinryo} {row.Kisyu} {row.Kyusya}");
    }
}

1 1 アカイイト 牝5 55 幸 中竹
3 5 ジェラルディーナ 牝4 55 Ｃデムーロ 斉藤崇
5 9 イクイノックス 牡3 55 ルメール 木村

その他のパラメーター

他にもいろいろなパラメーターの作成方法があります。

データを変更する場合【INSERT, UPDATE, DELETE 等】

以下のメソッドが使用できます。

メソッド名 説明 戻り値
Execute クエリやストアドプロシージャを実行 影響を受けた行（int型）

影響を受けた行とは、SQL文が実行され正常に処理できた行数のことです。例えば1件のINSERTを実行した場合 Execute の戻り値が「1」かどうかで成功または失敗を判断することができます。
非同期の場合、メソッド名の末尾に Async が付きます。

Query と同じく Execute の第一引数に string 型のSQLを渡し、第二引数以降は任意で設定します。
image.png

SELECT文の実行にも使用できますが、基本的に 結果を返さない（データを取得しない） INSERT, UPDATE, DELETE や ストアドプロシージャの実行 に使用します。

パラメーターが1つの場合

INSERT

Execute（単一レコードの挿入）
private static void InsertSingleRecord(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。
    // 「arima_kinen」と構造が同じ、別のテーブル「takarazuka_kinen」を使用しています。
    
    // 追加
        var insertSql = @"
            INSERT INTO takarazuka_kinen
            (
                wakuban,
                bamei,
                seibetu,
                barei,
                kinryo,
                kisyu,
                kyusya,
                createdate,
                updatedate
            )
            VALUES
            (
                @wakuban,
                @bamei,
                @seibetu,
                @barei,
                @kinryo,
                @kisyu,
                @kyusya,
                @createdate,
                @updatedate
            );";

        // 匿名パラメーター(Anonymous Parameter) でパラメーターを作成します。
        // ※takarazuka_kinenの馬番(umaban)は自動採番のため、パラメーターを使用しません。
        var insertResult = connection.Execute(insertSql, new
        {
            wakuban = "1",
            bamei = "ライラック",
            seibetu = "牝",
            barei = "4",
            kinryo = "56",
            kisyu = "Mデムーロ",
            kyusya = "相沢",
            createdate = DateTime.Now,
            updatedate = DateTime.Now,
        });

        Console.WriteLine($"\r\n{insertResult}件追加しました。");
}

1件追加しました。

UPDATE・DELETE

Execute（単一レコードの更新と削除）
private static void UpdateAndDeleteSingleRecord(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。
    // 「arima_kinen」と構造が同じ、別のテーブル「takarazuka_kinen」を使用しています。
    
    var targetNo = 1;

    // 更新
    var updateSql = @"UPDATE takarazuka_kinen SET barei = 5 WHERE umaban = @umaban";
    // 匿名パラメーター(Anonymous Parameter) でパラメーターを作成します。
    var updateResult = connection.Execute(updateSql, new { umaban = targetNo });
    Console.WriteLine($"\r\n{updateResult}件更新しました。");

    // 削除
    var deleteSql = @"DELETE FROM takarazuka_kinen WHERE umaban = @umaban";
    // 匿名パラメーター(Anonymous Parameter) でパラメーターを作成します。
    var deleteResult = connection.Execute(deleteSql, new { umaban = targetNo });
    Console.WriteLine($"\r\n{deleteResult}件削除しました。");
}

1件更新しました。

1件削除しました。

パラメーターが複数ある場合

単一のパラメーターではなく、複数のパラメーターを扱いたいときも Execute を使うことができます。Execute に渡す匿名パラメーター(Anonymous Parameter)をリストにすることで、複数のデータをまとめて追加したり、更新することができます。

下記は複数のデータをINSERT、UPDATEする例です。
追加の場合、追加対象のデータを TakarazukaKinen クラスとして作成し、そのリスト List<TakarazukaKinen> をもとに、匿名パラメーター(Anonymous Parameter) のリストを作成しています。
更新の場合、馬番と現在の日付だけを使用するため、TakarazukaKinen クラス内のデータは使用せず、そのまま匿名パラメーターのリストを作成しています。

追加したいデータのクラス
public class TakarazukaKinen(int wakuban, int umaban, string bamei, string seibetu, int barei, int kinryo, string Kisyu, string kyusya)
{
    /// <summary>
    /// 枠番
    /// </summary>
    public int Wakuban { get; } = wakuban;

    /// <summary>
    /// 馬番
    /// </summary>
    public int Umaban { get; } = umaban;

    /// <summary>
    /// 馬名
    /// </summary>
    public string Bamei { get; } = bamei;

    /// <summary>
    /// 性別
    /// </summary>
    public string Seibetu { get; } = seibetu;

    /// <summary>
    /// 馬齢
    /// </summary>
    public int Barei { get; } = barei;

    /// <summary>
    /// 斤量
    /// </summary>
    public int Kinryo { get; } = kinryo;

    /// <summary>
    /// 騎手
    /// </summary>
    public string Kisyu { get; } = Kisyu;

    /// <summary>
    /// 厩舎
    /// </summary>
    public string Kyusya { get; } = kyusya;
}

INSERT

Execute（複数レコードの挿入）
private static void InsertMultipleRecords(SqliteConnection connection)
{
    var insertSql = @"
        INSERT INTO takarazuka_kinen
        (
            wakuban,
            bamei,
            seibetu,
            barei,
            kinryo,
            kisyu,
            kyusya,
            createdate,
            updatedate
        )
        VALUES
        (
            @wakuban,
            @bamei,
            @seibetu,
            @barei,
            @kinryo,
            @kisyu,
            @kyusya,
            @createdate,
            @updatedate
        );";

    // SqliteConnectionは事前に用意し、引数から渡します。
    // 「arima_kinen」と構造が同じ、別のテーブル「takarazuka_kinen」を使用しています。

    // 複数のINSERT
    // パラメーター用のリストをTakarazukaKinenクラスを使用して作成します。
    var addList = new List<TakarazukaKinen>
    {
        new(1, 1, "ライラック", "牝", 4, 56, "Mデムーロ", "相沢"),
        new(1, 2, "カラテ", "牡", 7, 58, "菅原明", "辻野"),
        new(2, 3, "ダノンザキッド", "牡", 5, 58, "北村友", "安田隆"),
        new(2, 4, "ボッケリーニ", "牡", 7, 58, "浜中", "池江"),
        new(3, 5, "イクイノックス", "牡", 4, 58, "ルメール", "木村"),
        new(3, 6, "スルーセブンシーズ", "牝", 5, 56, "池添", "尾関"),
        new(4, 7, "プラダリア", "牡", 4, 58, "菱田", "池添"),
        new(4, 8, "ヴェラアズール", "牡", 6, 58, "松山", "渡辺"),
        new(5, 9, "ジャスティンパレス", "牡", 4, 58, "鮫島駿", "杉山晴"),
        new(5, 10, "ディープボンド", "牡", 6, 58, "和田竜", "大久保"),
        new(6, 11, "ジェラルディーナ", "牝", 5, 56, "武豊", "斉藤崇"),
        new(6, 12, "アスクビクターモア", "牡", 4, 58, "横山武", "田村"),
        new(7, 13, "ジオグリフ", "牡", 4, 58, "岩田望", "木村"),
        new(7, 14, "ブレークアップ", "牡", 5, 58, "川田", "吉岡"),
        new(8, 15, "ユニコーンライオン", "牡", 7, 58, "坂井", "矢作"),
        new(8, 16, "モズベッロ", "牡", 7, 58, "角田河", "森田"),
        new(8, 17, "ドゥラエレーデ", "牡", 3, 53, "幸", "池添"),
    };

    // パラメーター用のリストをDapperに渡すための匿名パラメーター(Anonymous Parameter) に変換します。
    // ※takarazuka_kinenの馬番(umaban)は自動採番のため、パラメーターを使用しません。
    var multiInsertParams = addList.Select(x => new
    {
        wakuban = x.Wakuban,
        bamei = x.Bamei,
        seibetu = x.Seibetu,
        barei = x.Barei,
        kinryo = x.Kinryo,
        kisyu = x.Kisyu,
        kyusya = x.Kyusya,
        createdate = DateTime.Now,
        updatedate = DateTime.Now
    });

    // 17件のINSERTを実行します。
    var multiInsertResult = connection.Execute(insertSql, multiInsertParams);
    Console.WriteLine($"\r\n{multiInsertResult}件追加しました。");

    /*
    匿名パラメーター(Anonymous Parameter) を先に作成するのではなく、追加したいデータを foreach で回す方法もあります。

    foreach (var item in addList)
    {
        var multiInsertResult = connection.Execute(insertSql, new
        {
            wakuban = x.Wakuban,
            bamei = x.Bamei,
            seibetu = x.Seibetu,
            barei = x.Barei,
            kinryo = x.Kinryo,
            kisyu = x.Kisyu,
            kyusya = x.Kyusya,
            createdate = DateTime.Now,
            updatedate = DateTime.Now
        });

        Console.WriteLine($"\r\n{multiInsertResult}件追加しました。");
    }   

    */
}

17件追加しました。

UPDATE

Execute（複数レコードの更新）
private static void UpdateMultipleRecords(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。
    // 「arima_kinen」と構造が同じ、別のテーブル「takarazuka_kinen」を使用しています。
    
    // 複数のUPDATE: その1
    var multiUpdateSql = @"
        UPDATE takarazuka_kinen 
        SET updatedate = @updatedate
        WHERE umaban = @umaban;";

    var updateDate = DateTime.Now;

    // Dapperに渡すための匿名パラメーター(Anonymous Parameter) を作成します。        
    var multiUpdateParams = Enumerable.Range(1, 17).Select(index => new
    {
        umaban = index,
        updatedate = updateDate
    }).ToList();

    // 17件のUPDATEを実行します。
    var multiUpdateResult = connection.Execute(multiUpdateSql, multiUpdateParams);
    Console.WriteLine($"\r\n{multiUpdateResult}件更新しました。");
}

17件更新しました。

これらの方法は下記のSQLを手動実行するのと同様の結果が得られます。

追加
INSERT INTO takarazuka_kinen
(wakuban, bamei, seibetu, barei, kinryo, kisyu, kyusya, createdate, updatedate)
VALUES
(1, 'ライラック', '牝', 4, 56, 'Mデムーロ', '相沢', '2024-09-29 15:38:50', '2024-09-29 15:38:50');

INSERT INTO takarazuka_kinen
(wakuban, bamei, seibetu, barei, kinryo, kisyu, kyusya, createdate, updatedate)
VALUES
(1, 'カラテ', '牡', 7, 58, '菅原明', '辻野', '2024-09-29 15:38:50', '2024-09-29 15:38:50');

-- 以下、続く
更新
UPDATE takarazuka_kinen 
SET updatedate = '2024-09-29 15:38:50'
WHERE umaban = 1;

UPDATE takarazuka_kinen 
SET updatedate = '2024-09-29 15:38:50'
WHERE umaban = 2;

-- 省略

UPDATE takarazuka_kinen 
SET updatedate = '2024-09-29 15:38:50'
WHERE umaban = 17;

パラメーターが複数ある場合 2

上記の複数UPDATEのように、片方のカラムは変動し、片方のカラムは固定値を設定したい場合、簡略化することができます。
下記の例では、IN句を使用して1回のUPDATE文を実行するようSQLを修正しています。更新時に個別の値を設定しない場合、WHEREにIN句を使用するほうが効率よく処理できます。

UPDATE

Execute（複数レコードの更新）
private static void UpdateMultipleRecords(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。
    // 「arima_kinen」と構造が同じ、別のテーブル「takarazuka_kinen」を使用しています。
    
    // 複数のUPDATE: その2
    var multiUpdateSql2 = @"
        UPDATE takarazuka_kinen 
        SET updatedate = @updatedate
        WHERE umaban IN @umabanList";

    // 馬番のIN句に展開するためのリスト
    var umabanList = Enumerable.Range(1, 17).ToList();

    // パラメーター名と変数名が同じため「umabanList = umabanList」はイコールを省略できます
    var multiUpdateParams2 = new { updatedate = updateDate, umabanList };
    var multiUpdateResult2 = connection.Execute(multiUpdateSql2, multiUpdateParams2);
    Console.WriteLine($"\r\n{multiUpdateResult2}件更新しました。");
}

17件更新しました。

この方法は下記のSQLを手動実行するのと同様の結果が得られます。

UPDATE takarazuka_kinen 
SET updatedate = '2024-09-29 15:38:50'
WHERE umaban IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17);

大量のデータ処理

大量のデータを処理する場合は BulkInsert などの一括処理を検討する必要があります。Dapperで一括処理を実施する場合、Dapper Plusライブラリ（有料）が必要となります。

SQLServerにもBULK INSAERTの機能があります。

データを計算する場合【Sum, Count 等】

単一の値を受け取りたい場合
以下のメソッドが使用できます。

メソッド名 説明 戻り値
ExecuteScalar クエリやストアドプロシージャを実行 最初の行の最初の列の値をobject型で返す。該当するデータがない場合はnullを返す。

型指定なしで使用すると、戻り値の型は object 型となります。ExecuteScalar<T>を使用すると、戻り値が指定した型となります。
非同期の場合、メソッド名の末尾に Async が付きます。

  • 単一の値を返す Count(*) や Sum() など、集計関数の結果を受け取る場合に使用することが多いです。
  • クエリによって複数行が返された場合でも、最初の行の最初の列以外の値は返しません。

Query と同じく ExecuteScalar の第一引数に string 型のSQLを渡し、第二引数以降は任意で設定します。
image.png

ExecuteScalar
private static void ExecuteScalarQueries(SqliteConnection connection)
{
    // COUNT
    var countSql = @"SELECT COUNT(*) FROM arima_kinen";
    var count = connection.ExecuteScalar(countSql);
    Console.WriteLine($"\r\n2022年の有馬記念は計{count}頭が出走しました。");

    // SUM
    var sumSql = @"SELECT SUM(barei) FROM arima_kinen";
    var sum = connection.ExecuteScalar(sumSql);
    Console.WriteLine($"\r\n2022年の有馬記念出走馬の馬齢合計は{sum}です。");
}

2022年の有馬記念は計16頭が出走しました。
馬齢の合計は72です。

戻り値をDbDataReaderで受け取りたい場合

以下のメソッドが使用できます。

メソッド名 説明 戻り値
ExecuteReader クエリを実行 System.Data.IDataReader

非同期の場合、メソッド名の末尾に Async が付きます。

おまけ：SQLite のセットアップとテーブル作成

サンプルに使用するSQLiteのセットアップとテーブル作成についてのメモです。
起動すればOKというラインのため、詳しい内容は公式ドキュメント等を参考にしてください。

ダウンロード後解凍し、ファイルを適当なフォルダに移動します。移動先のフォルダでバージョン確認ができたらセットアップ完了です。

E:\...\SQLite>sqlite3 --version
3.40.0 2022-11-16 12:10:08 89...
  • 新規データベース作成
    sqlite3 [テーブル名].db で作成します。以降、既存のデータベースを開くときも同様のコマンドを使用します。
sqlite3.exe と同じ場所にあるデータベースを起動（新規作成）する場合の例

E:\...\SQLite>sqlite3 advent.db
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
sqlite>

エクスプローラー上でもデータベースファイルが確認できます。
image.png
データベースファイルは sqlite3.exe と同じ場所になくてもOKです。データベースファイルは作成後、別の場所に移動できます。
sqlite3.exe と別の場所にあるデータベースを起動したい場合 sqlite3 [フォルダパス\テーブル名].db で実行可能です。

sqlite3.exe と別の場所にあるデータベースを起動する場合の例

E:\...\SQLite>sqlite3 E:\...\advent.db
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
sqlite>

データベースを終了するコマンドは .exit です。ctrl+C でも終了可能です。

sqlite> .exit
  • 新規テーブル作成
    データベースを開き、作成したCREATE文を貼り付けます。正しく実行できた場合、テーブルが作成されます。
sqlite> CREATE TABLE arima_kinen(... ;

作成したテーブルを確認するコマンドは .tables です。データベースに存在する全てのテーブルが確認できます。

sqlite> .tables
arima_kinen

テーブルの定義を確認するコマンドは .schema [テーブル名] です。

sqlite> .schema arima_kinen
CREATE TABLE arima_kinen(...
  • 新規データ追加
    作成したテーブルに対し、幾つかデータを追加します。
sqlite> INSERT INTO arima_kinen... ;

初期設定のままSELECT文を実行すると、listモードとなるため見辛いです。ヘッダーを表示してcolumnモードに切り替えます。
columnモード時の列幅を変更したい場合 .width コマンドで任意の値を設定してください。

sqlite> 
1|1|アカイイト|牝|5|55|幸|中竹|2022-12-24 23:51:42|2022-12-24 23:51:42
2|1|イズジョーノキセキ|牝|5|55|岩田康|石坂|2022-12-24 23:51:42|2022-12-24 23:51:42
...
...
sqlite> .header on
sqlite> .mode column
sqlite> .width 6 7 18
sqlite> SELECT * FROM arima_kinen;
umaban  wakuban  bamei               seibetu  barei  kinryo  kisyu  kyusya  createdate           updatedate
------  -------  ------------------  -------  -----  ------  -----  ------  -------------------  -------------------
1       1        アカイイト               牝        5      55      幸      中竹      2022-12-24 23:51:42  2022-12-24 23:51:42
2       1        イズジョーノキセキ           牝        5      55      岩田康    石坂      2022-12-24 23:51:42  2022-12-24 23:51:42
...
...

まとめ

記事内で使用したサンプルコードはこちら▶ CSharpDapperAndSQLSample

2024.09　内容を一部修正しました。最後まで見ていただきありがとうございました。

参考URL

13
23
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
13
23

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?