【C#】Dapper忘備録【応用編】

Qiita Advent Calendar 4回目の参加です。よろしくお願いします。※遅刻しました。:bow_tone2:
Dapperに関する応用的な内容をまとめました。

2年前に書いたDapper忘備録【基本編】の内容がベースとなっているため、Dapperとはなんぞや？という方はこちらを先にご覧ください。

https://qiita.com/YuMo_tea/items/ee4182260a0e3216237e

## Dapper とは　
.NETプラットフォーム用のマイクロORM（Object-relational mapping）です。
Dapperを使用する場合、SQLとマッピング用のクラスは自力で書く必要があります。SQLを書けることが前提のオブジェクトマッパーです。

:mag:マッピングのイメージ図
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/bf640f9e-c829-0209-d120-32c6333ff312.png)

## 下準備：テーブル作成
下記の定義を持つtokyo_yushun テーブル、dubai_sheema_classic テーブルを**SQLite**で作成しました。
※日本語と英語バラバラですがご容赦ください。

| NO | カラム名 | 日本語名 | 型 |  | 備考 | 
|:-------:|:-------:|:-------:|:-------:|:-------:|:-------:|
|1| kaisu  | 回数 | INTEGER | NOT NULL | UNIQUE制約
|2| wakuban | 枠番() | INTEGER | NOT NULL | UNIQUE制約
|3| umaban  | 馬番 | INTEGER | NOT NULL | UNIQUE制約
|4| bamei   | 馬名 | TEXT    | NOT NULL |
|5| seibetu | 性別 | TEXT    | NOT NULL |
|6| barei   | 馬齢 | INTEGER | NOT NULL |
|7| kinryo  | 斤量 | REAL | NOT NULL | 小数点以下の桁数を指定可能
|8| kisyu   | 騎手 | TEXT    | NOT NULL |
|9| kyusya  | 厩舎 | TEXT    | NOT NULL |
|10| createdate  | 作成日 | TEXT  | NOT NULL |
|11| updatedate  | 更新日 | TEXT | NOT NULL |

## 型変換の重要性
**<font color="DeepPink">※今回使用したデータベースはSQLiteのため、SQLite⇔C#間の変換を行うことを前提とした説明になります。</font>**
データベースとクラスのマッピングでは、**互いの型が変換できるかどうか**が重要となります。取得(SELECT)であれ、追加(INSERT)であれ、更新(UPDATE)であれ、型変換が成功しない場合はデータのやり取りができないからです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/c7f50489-3968-e187-69a6-d9693d9cd113.png)

### しかし、暗黙的型変換ができるのならば、それでいい

#### SQLiteのデータ型
SQLiteのデータ型は下記の4つとなります。※厳密にはNULLが含まれるようなのですが、SQLiteの仕様については解説しないため、詳しくは公式ドキュメントを参照してください。

- INTEGER（符号付き整数）
- REAL（浮動小数点数）
- TEXT（文字列）
- BLOB（入力されたままの形式で格納されるデータの塊）

https://www.sqlite.org/datatype3.html

#### 暗黙的型変換
マッピングクラスのプロパティをプリミティブ型で定義している場合、SQLite⇔C#で暗黙的型変換が実施されます。
※型変換の組み合わせ一覧は下記のリンクにあります。

https://learn.microsoft.com/ja-jp/dotnet/standard/data/sqlite/types

この場合、何も設定せずともDapperを通してデータのやり取りができるため、とても楽です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/dffc4ada-a667-c8af-6cfb-5668decce09e.png)


最初に準備した `tokyo_yushun` テーブルのデータを**暗黙的型変換**でやり取りする場合、C#側は下記の変換ルールに基づいてクラスを定義しておきます。これにより、`Query`でデータを取得したり、`Execute`でデータを追加したりすることができます。

:white_check_mark:変換ルール
| **SQLiteの型** | **C#の型** |
|--------------|------------|
| INTEGER      | int        |
| REAL         | double     |
| TEXT         | string     |

<details><summary>SQLite CREATE文</summary>

```SQL
CREATE TABLE tokyo_yushun
(
	kaisu INTEGER NOT NULL,  
	wakuban INTEGER NOT NULL,
	umaban INTEGER NOT NULL,
	bamei TEXT NOT NULL,
	seibetu TEXT NOT NULL,
	barei INTEGER NOT NULL,
	kinryo REAL NOT NULL, -- REAL型で小数点以下の桁数を指定
	kisyu TEXT NOT NULL,
	kyusya TEXT NOT NULL,
	createdate TEXT NOT NULL,
	updatedate TEXT NOT NULL,
	UNIQUE(kaisu, wakuban, umaban) -- UNIQUE制約で回数、枠番、馬番の組み合わせが一意になるようにする
);
```
</details>

```C#:DTOクラス
/// <summary>
/// 東京優駿（日本ダービー）のデータを格納するクラス（取得のみ使用）
/// </summary>
public class TokyoYushunDTO()
{
    /// <summary>
    /// 回数
    /// </summary>
    public int? Kaisu { get; set; }

    /// <summary>
    /// 枠番
    /// </summary>
    public Wakuban? Wakuban { get; set; }

    /// <summary>
    /// 馬番
    /// </summary>
    public Umaban? 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; }
}

```

## 型変換とカスタム型ハンドラー
暗黙的型変換が使えるようにすべてプリミティブ型で定義してやり取りをしたいところですが、実際はそうもいかないことが多いと思います。プリミティブ型で定義した場合、必要な情報や機能を加えることができないからです。

### カスタム型（自作クラス）を作る
`tokyo_yushun` の `wakuban` を例にして考えます。競馬における「枠番」のことです。
「枠番」が持つ情報や機能として、以下を定義します。
- 内枠、外枠がある
- 枠によって帽子の色が異なる

```C#:枠番クラス
/// <summary>
/// 枠番
/// </summary>
public readonly record struct Wakuban
{
    /// <summary>
    /// 馬番の値
    /// </summary>
    public readonly int Value;

    /// <summary>
    /// 内枠かどうか
    /// </summary>
    public readonly bool IsInnerFrame;

    /// <summary>
    /// 枠番の色
    /// </summary>
    public readonly WakubanColor Color;

    // 上記の「WakubanColor」はEnumです。

    /// <summary>
    /// コンストラクタ
    /// </summary>
    /// <param name="value">数値</param>
    public Wakuban(int value)
    {
        Value = value;
        IsInnerFrame = value <= 4;
        Color = (WakubanColor)value;
    }

    /// <summary>
    /// 枠番に変換します。
    /// </summary>
    /// <param name="value">変換対象の数値</param>
    /// <returns>枠番</returns>
    public static Wakuban Parse(int value)
    {
        return new Wakuban(value);
    }
}
```

さらに `tokyo_yushun` の `umaban` についても考えます。競馬における「馬番」のことです。
- 奇数番、偶数番がある
- 基本は奇数番からゲートインする

```C#:馬番クラス
/// <summary>
/// 馬番
/// </summary>
public readonly record struct Umaban
{
    /// <summary>
    /// 馬番の値
    /// </summary>
    public readonly int Value;

    /// <summary>
    /// 先入れかどうかを示す値 <br />
    /// 奇数番の馬から先にゲートに入ります。
    /// </summary>
    public readonly bool IsFirstInserted;

    /// <summary>
    /// コンストラクタ
    /// </summary>
    /// <param name="value">数値</param>
    public Umaban(int value)
    {
        Value = value;
        IsFirstInserted = value % 2 != 0;
    }

    /// <summary>
    /// 馬番に変換します。
    /// </summary>
    /// <param name="value">変換対象の数値</param>
    /// <returns>馬番</returns>
    public static Umaban Parse(int value)
    {
        return new Umaban(value);
    }
}
```

### カスタム型ハンドラーを作る
カスタム型を作成しただけでは、Dapperを通じてやり取りすることができません。カスタム型の変換ルールを作成してDapperに伝える必要があります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/566811/fc805a27-5f38-b790-112f-9f96a9ed3fa1.png)

変換ルールの作成はDapperの `SqlMapper.TypeHandler<T>` または `SqlMapper.StringTypeHandler<T>` を使用します。`StringTypeHandler<T>` は `TypeHandler<T>` を継承しています。
抽象クラスかつ抽象メソッドを持つため、一部のメソッドはオーバーライドをして実装します。

| クラス名 | 概要 | 実装するメソッド |
|:-------:|:-------:|:-------:|
| SqlMapper.TypeHandler\<T\> | 汎用型ハンドラ | `SetValue(IDbDataParameter parameter, T? value)` と `Parse(object value)` |
| SqlMapper.StringTypeHandler\<T\> | 文字列専用の型ハンドラ | `Parse(string xml)` と `Format(T xml)` | 

https://github.com/DapperLib/Dapper/blob/main/Dapper/SqlMapper.TypeHandler.cs

ここでは `SqlMapper.TypeHandler<T>` を使用します。

| メソッド名 | 概要 | 主な処理 |
|:-------:|:-------:|:-------:|
| Parse | カスタム型の値をデータベースに保存するため、適切なデータベース型に変換する | INSERT
| SetValue | データベースから取得した値をカスタム型に変換する | SELECT

```C#: 型ハンドラー
/// <summary>
/// Wakubanの型ハンドラー
/// </summary>
public class WakubanTypeHandler : SqlMapper.TypeHandler<Wakuban>
{
    /// <summary>
    /// カスタム型をdapperの型に変換します。
    /// </summary>
    /// <param name="parameter">パラメータのDbType</param>
    /// <param name="value">設定する値</param>
    public override void SetValue(IDbDataParameter parameter, Wakuban value)
    {
        parameter.DbType = DbType.Int64;
        parameter.Value = value.Value;
    }

    /// <summary>
    /// dapperの型をカスタム型に変換します。
    /// </summary>
    /// <param name="value">データベースから取得した値</param>
    /// <returns><see cref="Wakuban"/>が戻ります。</returns>
    public override Wakuban Parse(object value)
    {
        /*
            下記は型変換できない場合を考慮していますが、
            適切なDB設計を行うことで、例外処理は不要になります。

            例）
            public override Wakuban Parse(object value)　=> Wakuban.Parse((int)value);
        */

        if (value is int intValue)
        {
            return Wakuban.Parse(intValue);
        }
        else if (value is long longValue)
        {
            return Wakuban.Parse((int)longValue);
        }
        else
        {
            throw new ArgumentException($"対応していない型です: {value?.GetType().FullName}");
        }
    }
}
```

```C#
/// <summary>
/// UmaBanの型ハンドラー
/// </summary>
public class UmabanTypeHandler : SqlMapper.TypeHandler<Umaban>
{
    /// <summary>
    /// カスタム型をdapperの型に変換します。
    /// </summary>
    /// <param name="parameter">パラメータのDbType</param>
    /// <param name="value">設定する値</param>
    public override void SetValue(IDbDataParameter parameter, Umaban value)
    {
        parameter.DbType = DbType.Int32;
        parameter.Value = value.Value;
    }

    /// <summary>
    /// dapperの型をカスタム型に変換します。
    /// </summary>
    /// <param name="value">データベースから取得した値</param>
    /// <returns><see cref="Umaban"/>が戻ります。</returns>
    public override Umaban Parse(object value)
    {
        /*
            下記は型変換できない場合を考慮していますが、
            適切なDB設計を行うことで、例外処理は不要になります。

            例）
            public override Umaban Parse(object value)　=> Umaban.Parse((int)value);
        */

        if (value is int intValue)
        {
            return Umaban.Parse(intValue);
        }
        else if (value is long longValue)
        {
            return Umaban.Parse((int)longValue);
        }
        else
        {
            throw new ArgumentException($"対応していない型です: {value?.GetType().FullName}");
        }
    }
}
```

### カスタム型ハンドラーの呼び出し
Dapperでクエリを実行する前にカスタム型ハンドラーを追加しておく必要があります。
`Program.cs` などの上位で呼び出すようにします。

```C#
/// <summary>
/// Dapperのカスタム型ハンドラーを設定するクラス
/// </summary>
public class TypeHandlerSettings
{
    /// <summary>
    /// Dapperのカスタム型ハンドラーを設定します。
    /// </summary>
    public static void ConfigureDapperTypeHandlers()
    {
        // UmaBanの型ハンドラーを追加
        SqlMapper.AddTypeHandler(new UmabanTypeHandler());

        // Wakubanの型ハンドラーを追加
        SqlMapper.AddTypeHandler(new WakubanTypeHandler());
    }
}
```

```C#
class Program
{
    static void Main(string[] args)
    {
        // 型ハンドラーの設定
        TypeHandlerSettings.ConfigureDapperTypeHandlers();

        // 型ハンドラーを使用した取得、追加
        TokyoYushunRepository.Execute();
    }
}
```

## カスタムマッピング
追記します。


## まとめ

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

最後まで見ていただきありがとうございました。

## 参考URL
- [DapperLib / Dapper - Dapper のGitHubリポジトリ](https://github.com/DapperLib/Dapper)
- [Microsoft.Data.Sqlite 概要 データ型](https://learn.microsoft.com/ja-jp/dotnet/standard/data/sqlite/types)