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

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

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

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

https://www.learndapper.com/#is-dapper-an-orm

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

https://zzzcode.ai/dapper/chat

## IDbConnection と Dapper
DapperはC#の `IDbConnection` を拡張して作られています。そのため、Connection型の変数から直接メソッドを呼び出すことができます。
```C#:
using var connection = new SqliteConnection(DataSource);
var result = connection.Query<ArimaKinenDTO>("SELECT * FROM arima_kinen;");
```

## 下準備：arima_kinen テーブルの作成
以下のようなテーブルを**SQLite**で作成しました。
※日本語と英語バラバラですがご容赦ください。
| 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  | 斤量 | REAL | 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として作成しました。

:mag:マッピングのイメージ図
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/d02a4e7f-adcd-5fe2-af28-75fed52d7aaf.png)

```C#:DTOクラス
/// <summary>
/// 有馬記念のデータを格納するクラス（取得のみ使用）
/// </summary>
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 double? Kinryo { get; set; }

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

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

    /// <summary>
    /// 作成日付
    /// </summary>
    public string? CreateDate { get; set; }

    /// <summary>
    /// 更新日付
    /// </summary>
    public string? UpdateDate { get; set; }
}
```

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

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

### **<font color="DeepPink">複数行のデータを取得する場合</font>**
以下のメソッドが使用できます。

| メソッド名 | SELECTした結果 | 戻り値 |
|:-------:|:-------:|:-------:|
| [Query](https://www.learndapper.com/dapper-query/selecting-multiple-rows#dapper-query) | 1つ以上の行 | 列挙可能なdynamic型を返す。該当するデータがない場合は空のコレクションを返す。 |

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

https://www.learndapper.com/dapper-query/selecting-multiple-rows

`Query` は、第一引数に `string` 型のSQLを渡します。
第二引数以降は無くても構いません。しかし、後述する「動的なパラメーター」を設定するための `param` 引数や、トランザクションを指定するための `transaction` 引数は使用頻度が高いです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/7cff9203-681a-83cb-68f6-b8704666ea0d.png)

**型指定あり**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/d8f26a1a-c1b8-8b2d-3d68-20f481d7f9b3.png)

**型指定なし**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/ab08877c-ba5a-5967-2afe-3359e769b882.png)

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

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

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

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

### **<font color="DeepPink">単一のデータを取得する場合</font>**
以下のメソッドが使用できます。

| メソッド名 | SELECTした結果 | 戻り値 |
|:-------:|:-------:|:-------:|
| [QuerySingle](https://www.learndapper.com/dapper-query/selecting-single-rows#dapper-querysingle) | 1行のみ | dynamic型を返す。該当するデータがない、または複数行を返す場合は例外がスローされる。 |
| [QuerySingleOrDefault](https://www.learndapper.com/dapper-query/selecting-single-rows#dapper-querysingle) | 0行または1行 | dynamic型を返す。該当するデータがない場合は`null`を返すが、複数行を返す場合は例外がスローされる。 |
| [QueryFirst](https://www.learndapper.com/dapper-query/selecting-single-rows#dapper-queryfirst) | 1つ以上の行 | **最初の行**をdynamic型で返す。該当するデータがない場合は例外がスローされる。 |
| [QueryFirstOrDefault](https://www.learndapper.com/dapper-query/selecting-single-rows#dapper-queryfirst) | 1つ以上の行 | **最初の行**をdynamic型で返す。該当するデータがない場合は`null`を返す。 |

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

https://www.learndapper.com/selecting-single-rows

複数行取得時と同じく `QueryXXX` の第一引数に `string` 型のSQLを渡し、第二引数以降は任意で設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/2787affb-ed81-616e-3d90-b4ee8d85fd30.png)

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

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

```
4 8 ウインマイティー 牝5 55 和田竜 五十嵐 2024-12-25 23:25:46 2024-12-25 23:25:46
```

```C#:QueryFirstOrDefault
private static void QueryFirstRow(SqliteConnection connection)
{   
    // SqliteConnectionは事前に用意し、引数から渡します。
    
    const string SelectSql = @"SELECT * FROM arima_kinen;";
    var firstRow = connection.QueryFirstOrDefault<ArimaKinenDTO>(SelectSql);

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

```
1 1 アカイイト 牝5 55 幸 中竹 2024-12-25 23:25:46 2024-12-25 23:25:46
```

### **<font color="DeepPink">複数のSELECT文を実行し、データを取得する場合</font>**
以下のメソッドが使用できます。

| メソッド名 | 説明 |
|:-------:|:-------:|
| [QueryMultiple](https://www.learndapper.com/dapper-query/selecting-multiple-results#dapper-querymultiple) | 複数のSELECT文を実行できる。SqlMapper.GridReader を返す。 |

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

https://www.learndapper.com/dapper-query/selecting-multiple-results

複数行取得時、単一行取得時と同じく `QueryMultiple` の第一引数に `string` 型のSQLを渡し、第二引数以降は任意で設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/651f38c5-2c6c-0d72-581b-d6dcd2db8e5d.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` が付きます。

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

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

    // 戻り値の型を指定して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} {row.CreateDate} {row.UpdateDate}");
    }

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

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

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

7 13 タイトルホルダー 牡4 57 横山和 栗田 2024-12-25 23:25:46 2024-12-25 23:25:46

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

## 動的なパラメーターを設定する
実際のSQLではSELECTのWHERE句などに固定の値を渡すことは少なく、プレースホルダーを使用することが多いです。
パラメーター部分を `@xxx` と記述し、動的にパラメーターを指定できるようにします。
```SQL
SELECT * FROM arima_kinen WHERE umaban = @umaban;
```
Dapperでは、以下のようにパラメーターを設定できます。
```C#
var result = connection.QueryFirstOrDefault<ArimaKinenDTO>(sql, xxx); // 第2引数にパラメーターを渡す
```
この章では一例としてSELECTのWHERE句にパラメーターを使用する方法を載せていますが、SELECT以外にもINSERTのVALUES、UPDATEのSETなど **<font color="DeepPink">`@xxx` のパラメーターは様々な箇所で利用することができます。</font>**

### Anonymous ParameterとDynamic Parameters
複数あるパラメーターのうち、よく使う2つのパラメーターを紹介します。
どちらが良いというものはなく、実行するSQL文に応じてどちらかを選択しますが、使用頻度が高いのは「匿名パラメーター(Anonymous Parameter)」です。
 -  **匿名パラメーター(Anonymous Parameter)**
各メソッドの第2引数に `new {xxx = XXX}` の匿名型を渡す。
[Dapper - Anonymous Parameter](https://www.learndapper.com/parameters#dapper-anonymous-parameters)

 -  **動的パラメーター(Dynamic Parameters)**
各メソッドの第2引数に `DynamicParameters` 型の変数を渡す。
[Dapper - Dynamic Parameter](https://www.learndapper.com/parameters#dapper-dynamic-parameters)

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

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

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

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

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

```
2 3 ボルドグフーシュ 牡3 55 福永 宮本 2024-12-25 23:25:46 2024-12-25 23:25:46
2 4 アリストテレス 牡5 57 武豊 音無 2024-12-25 23:25:46 2024-12-25 23:25:46
```

### WHERE IN パラメーター
各メソッドの第2引数に `new {xxx = XXX}` の匿名型を渡すとき、配列（リスト）形式の変数を使用する。
[Dapper - List Parameter](https://www.learndapper.com/parameters#dapper-where-in-parameters)

```SQL
SELECT * FROM arima_kinen WHERE umaban in (1, 5, 9); -- (1, 5, 9)の部分を配列（リスト）で指定することができる　
```

```C#:IN句のパラメーター
private static void QueryWithInClause(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。
    
    const string SelectSql = @"SELECT * FROM arima_kinen WHERE umaban in @umaban;";

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

    // 匿名パラメーター(Anonymous Parameter)で配列のパラメータを作成します。
    var targetRows = connection.Query<ArimaKinenDTO>(SelectSql, 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} {row.CreateDate} {row.UpdateDate}");
    }
}
```

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

### その他のパラメーター
他にもいろいろなパラメーターの作成方法があります。
 -  [Dapper String Parameters](https://www.learndapper.com/parameters#dapper-string-parameters)
 -  [Dapper Table-Valued Parameters](https://www.learndapper.com/parameters#dapper-table-valued-parameters)
 -  [Dapper Output Parameter](https://www.learndapper.com/parameters#dapper-output-parameter)


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

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

| メソッド名 | 説明 |戻り値 |
|:-------:|:-------:|:-------:|
| [Execute](https://www.learndapper.com/non-query) | クエリやストアドプロシージャを実行 | 影響を受けた行（int型） |

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

https://www.learndapper.com/non-query

`Query` と同じく `Execute` の第一引数に string 型のSQLを渡し、第二引数以降は任意で設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/594d8c8e-0ee1-17b0-5c43-b76b721a0782.png)

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

### 下準備：takarazuka_kinen テーブルの作成

arima_kinen テーブルと定義が同じtakarazuka_kinen テーブルを**SQLite**で作成しました。
このテーブルに対し、追加・更新・削除を実施します。

| 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  | 斤量 | REAL | NOT NULL |
|7| kisyu   | 騎手 | TEXT    | NOT NULL |
|8| kyusya  | 厩舎 | TEXT    | NOT NULL |
|9| createdate  | 作成日 | TEXT  | NOT NULL |
|10| updatedate  | 更新日 | TEXT | NOT NULL |

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

#### INSERT

```C#:Execute（単一レコードの挿入）
private static void InsertSingleRecord(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。
    
    const string InsertSql = @"
        INSERT INTO takarazuka_kinen
            (
                wakuban,
                bamei,
                seibetu,
                barei,
                kinryo,
                kisyu,
                kyusya,
                createdate,
                updatedate
            )
            VALUES
            (
                @wakuban,
                @bamei,
                @seibetu,
                @barei,
                @kinryo,
                @kisyu,
                @kyusya,
                @createdate,
                @updatedate
            );";

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

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

```
1件追加しました。
```

#### UPDATE・DELETE

```C#:Execute（単一レコードの更新と削除）
private static void UpdateAndDeleteSingleRecord(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。
    
    const int TargetNumber = 1;

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

    const string DeleteSql = @"DELETE FROM takarazuka_kinen WHERE umaban = @umaban;";

    var deleteResult = connection.Execute(DeleteSql, new
    {
        // 匿名パラメーター(Anonymous Parameter) でパラメーターを作成します。  
        umaban = TARGET_NUMBER
    });

    Console.WriteLine($"\r\n{deleteResult}件削除しました。");
}
```
```
1件更新しました。

1件削除しました。
```

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

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

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

```C#:追加したいデータのクラス
/// <summary>
/// 宝塚記念
/// </summary>
public class TakarazukaKinen(
    int wakuban,
    int umaban,
    string bamei,
    string seibetu,
    int barei,
    double 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 double Kinryo { get; } = kinryo;

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

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

#### INSERT

```C#:Execute（複数レコードの挿入）
private static void InsertMultipleRecords(SqliteConnection connection)
{
    const string 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は事前に用意し、引数から渡します。

    // パラメーター用のリストを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, "幸", "池添"),
    };

    // パラメーター用のリストを匿名パラメーター(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) を先にまとめて作成してExecuteを実行するのではなく、
        追加したいデータを foreach で回しながら
        匿名パラメーター(Anonymous Parameter) を作成し、Executeを実行することもできます。
        
        この場合、1つのパラメーターに対し
        ログ出力・エラーハンドリングを詳細に実装することができますが、
        ディスクI/Oの回数が増えるため、パフォーマンスが低下します。
        そのため、パフォーマンスを重視する場合は、まとめてExecuteを実行する方法を推奨します。

        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

```C#:Execute（複数レコードの更新）
private static void UpdateMultipleRecords(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。

    const string UpdateSql1 = @"
        UPDATE takarazuka_kinen 
        SET updatedate = @updatedate
        WHERE umaban = @umaban;";

    var updateDate = DateTime.Now;

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

    var multiUpdateResult = connection.Execute(UpdateSql1, multiUpdateParams);
    Console.WriteLine($"\r\n{multiUpdateResult}件更新しました。");
}
```

```
17件更新しました。
```

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

```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');

-- 以下、続く

```

```SQL: 更新
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

```C#:Execute（複数レコードの更新）
private static void UpdateMultipleRecords(SqliteConnection connection)
{
    // SqliteConnectionは事前に用意し、引数から渡します。

    const string UpdateSql2 = @"
        UPDATE takarazuka_kinen 
        SET updatedate = @updatedate
        WHERE umaban IN @umabanList;";

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

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

```
17件更新しました。
```

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

```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ライブラリ（有料）が必要となります。

https://www.learndapper.com/bulk-operations

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

https://learn.microsoft.com/ja-jp/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver16

## データを計算する場合【Sum, Count 等】
**<font color="DeepPink">単一の値を受け取りたい場合</font>**
以下のメソッドが使用できます。

| メソッド名 | 説明 |戻り値 |
|:-------:|:-------:|:-------:|
| [ExecuteScalar](https://www.learndapper.com/dapper-query/selecting-scalar-values#dapper-executescalar) | クエリやストアドプロシージャを実行 | 最初の行の最初の列の値をobject型で返す。該当するデータがない場合はnullを返す。 |

型指定なしで使用すると、戻り値の型は `object` 型となります。`ExecuteScalar<T>`を使用すると、戻り値が指定した型となります。
非同期の場合、メソッド名の末尾に `Async` が付きます。
 - 単一の値を返す `Count(*)` や `Sum()` など、集計関数の結果を受け取る場合に使用することが多い
 - クエリによって複数行が返された場合でも、最初の行の最初の列以外の値は返さない

https://www.learndapper.com/dapper-query/selecting-scalar-values

`Query` と同じく `ExecuteScalar` の第一引数に string 型のSQLを渡し、第二引数以降は任意で設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/9aead0aa-d5c5-3739-b137-fe15aff2ee36.png)


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

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

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

馬齢の合計は72です。
```

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

| メソッド名 | 説明 |戻り値 |
|:-------:|:-------:|:-------:|
| [ExecuteReader](https://www.learndapper.com/execute-reader#dapper-executereader) | クエリを実行 | System.Data.IDataReader |

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

https://www.learndapper.com/execute-reader

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

- **ダウンロードからセットアップ**
[Precompiled Binaries for Windows（Windows 用のプリコンパイル済みバイナリ）](https://www.sqlite.org/download.html)から sqlite-tools--.zip をダウンロードします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/1e7dcc39-8bc5-369b-f0a6-c98e51cd6a43.png)

ダウンロード後解凍し、ファイルを適当なフォルダに移動します。移動先のフォルダでバージョン確認ができたらセットアップ完了です。
```
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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/b717d8a3-9db8-253c-f2e9-1995071490f0.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
...
...
```
- **必要な nuget パッケージ**
リンク先の .NET CLI をコピーして、作成したプロジェクトに追加します。
https://www.nuget.org/packages/Dapper
https://www.nuget.org/packages/Microsoft.Data.Sqlite

## まとめ

記事内で使用したサンプルコードはこちら▶ [CSharpDapperAndSQLSample](https://github.com/184ym2/til/tree/main/CSharpDapperAndSQLSample)

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

## 参考URL
- [Learn Dapper](https://www.learndapper.com/)
- [Dapper Tutorial](https://dappertutorial.net/)
- [DapperLib / Dapper - Dapper のGitHubリポジトリ](https://github.com/DapperLib/Dapper)
- [SELECT文などの出力モードを変更(.modeコマンド)](https://www.javadrive.jp/sqlite/sqlite_command/index1.html)
- [columnモードの時に列毎の横幅を指定する(.widthコマンド)](https://www.javadrive.jp/sqlite/sqlite_command/index1.html)