個人的に使っているDapper の使うときの小技とか

DataBase にアクセスする処理を使うとき、便利なのがMicro O/R Mapper のDapper です。

普段、あまり複雑な処理はせず、SQL 部分は手書きできる程度の複雑さで、読み込みはクラスにマッピングしてくれればいいと思っているので、Dapper はまさにピッタリで、とても気に入っています。

これは、Dapper を使うときにもう一声と感じる部分を補うためにやっていることのまとめです。

1.Dapper とは？

データベースに格納されている値を読み書きする際に、C# などでデータを格納するクラスのメンバーとのマッピングをしてくれるO/R Mapper の一種です。

.Net でよく使われている、Entity Framework と比較するとSQL を内部で生成するクエリビルダなどの機能が省略されており、マッピングに特化したシンプルな構成のため、オーバーヘッドが少なく高速に動作するのが特徴です。

2.Dapper の使い方

こちらの記事がとても分かりやすかったです。
【C#】Dapper忘備録【基本編】

今回のサンプルでは、データベース側はSQLite を使用しています。

Program.cs

using System.Data.SQLite;
using System.Diagnostics;

namespace DapperSample;

internal static class Program
{
    [STAThread]
    static void Main()
    {
        var 接続文字列 = new SQLiteConnectionStringBuilder() { DataSource = @$"{Application.StartupPath}\Sample.db" };
        using var conn = new CustomDbConnection(new SQLiteConnection(接続文字列.ConnectionString));
        conn.Open();

        プロフィール.DropTable(conn);

        プロフィール.CreateTable(conn);

        プロフィール.Insert(conn);

        Debug.WriteLine($"Count:{プロフィール.Count(conn)}");

        var Items = プロフィール.Select(conn);
        var ローズオリアナ = Items.Where(x => x.名前 == "ローズ・オリアナ").First();
        ローズオリアナ.別名 = "666番";
        プロフィール.Update(conn, new[] { ローズオリアナ });

        プロフィール.Delete(conn, Items.Where(x => x.別名 is null));

        Debug.WriteLine($"Count:{プロフィール.Count(conn)}");
    }
}

プロフィール.cs

using System.Data;
using System.Runtime.CompilerServices;
using Dapper;

namespace DapperSample;

[TableName("プロフィール")]
public class プロフィール
{
    public static void CreateTable(IDbConnection cn)
    {
        var query = "CREATE TABLE IF NOT EXISTS プロフィール(Id INTEGER PRIMARY KEY AUTOINCREMENT , 名前 TEXT NOT NULL, 別名 TEXT NULL, 年齢 INTAGER NOT NULL )";
        cn.Execute(query);
    }

    public static void DropTable(IDbConnection cn)
    {
        var query = "DROP TABLE IF EXISTS プロフィール";
        cn.Execute(query);
    }

    public static IEnumerable<プロフィール> Select(IDbConnection cn)
    {
        var query = $"SELECT * FROM プロフィール";
        return cn.Query<プロフィール>(query);
    }

    public static int Count(IDbConnection cn)
    {
        var query = $"SELECT COUNT(*) FROM プロフィール";
        return cn.ExecuteScalar<int>(query);
    }

    public static void Insert(IDbConnection cn)
    {
        var Items = new List<プロフィール>()
        {
            new プロフィール("シド・カゲノー", "シャドウ", 15),
            new プロフィール("アルファ", 15),
            new プロフィール("ベータ","ナツメ・カフカ",15),
            new プロフィール("デルタ", 15),
            new プロフィール("ゼータ", 15),
            new プロフィール("アレクシア・ミドガル", 15),
            new プロフィール("ヒョロ・ガリ", 15),
            new プロフィール("ジャガ・イモ", 15),
            new プロフィール("イータ", "イータ・ロイド・ライト", 16),
            new プロフィール("ニュー", 16),
            new プロフィール("シェリー・バーネット", 16),
            new プロフィール("ガンマ", "ルーナ", 17),
            new プロフィール("イプシロン", "シロン", 17),
            new プロフィール("クレア・カゲノー", 17),
            new プロフィール("ローズ・オリアナ", 17),
            new プロフィール("アイリス・ミドガル", 20),
        };
        var query = $"INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@{nameof(プロフィール.名前)},@{nameof(プロフィール.別名)}, @{nameof(プロフィール.年齢)})";
        cn.Execute(query, Items);
    }

    public static void Update(IDbConnection cn, IEnumerable<プロフィール> items)
    {
        var query = $"UPDATE プロフィール SET 別名=@{nameof(プロフィール.別名)} WHERE Id=@{nameof(プロフィール.Id)}";
        cn.Execute(query, items);
    }

    public static void Delete(IDbConnection cn, IEnumerable<プロフィール> items)
    {
        var query = $"DELETE FROM プロフィール WHERE ID=@{nameof(プロフィール.Id)}";
        cn.Execute(query, items);
    }

    public プロフィール()
    {
    }

    public プロフィール(string 名前, int 年齢)
    {
        this.名前 = 名前;
        this.年齢 = 年齢;
    }

    public プロフィール(string 名前, string? 別名, int 年齢)
    {
        this.名前 = 名前;
        this.別名 = 別名;
        this.年齢 = 年齢;
    }

    public int? Id { get; set; }
    public string 名前 { get; set; } = string.Empty;
    public string? 別名 { get; set; }
    public int 年齢 { get; set; }}

3.クラス名とカラム名のマッピング

Dapper では、カラム名と同じ名前のプロパティに値をマッピングしてくれますが、カラム名をそのままプロパティ名にしたくない場合や、そもそもできない場合があります。

3.1 規則性のあるマッピング

例えば、カラムはスネークケース(snake_case) で、格納するプロパティはパスカルケース(PascalCase)だったりすることがよくあります。

そういうケースでは、このようにSetTypeMap にCustomPropertyTypeMapを追加することでプロパティ名とカラム名を変換して対応付けることができます。
Micro-ORMとC#(とDapperカスタマイズ)

DapperMap.cs

static void SetSnakeToPascal<T>()
{
    var mapper = new CustomPropertyTypeMap(typeof(T), (type, columnName) =>
    {
        //snake_caseをPascalCaseに変換
        var propName = Regex.Replace(columnName, @"^(.)|_(\w)", x => x.Groups[1].Value.ToUpper() + x.Groups[2].Value.ToUpper());
        return type.GetProperty(propName);
    });

    SqlMapper.SetTypeMap(typeof(T), mapper);
}

Program.cs

static void Main()
{
    // MyClassをカラム名snake_cake → プロパティ名PascalCaseにマッピングするようセット
    SetSnakeToPascal<Person>();

    using var conn = new SQLiteConnection("接続文字列");
    conn.Open();
    var result = conn.Query<Person>("SELECT * FROM people");
}

3.2.属性でカラム名を持たせる

このようにプロパティに属性でカラム名を定義すると、変換ルールで対応できないような場合でも対応できます。

カラム名にスペースや記号など、プロパティ名に使用できない文字が含まれている場合などに有効です。

下の例では、テーブル名を静的プロパティで持たせる、リフレクションを使ってプロパティの属性を取得する(かつ、モジュール初期化子で初回にキャッシュを作る)という方法を使って、SQL 内でテーブル名やカラム名をベタ書きしなくて済むようにしています。

C#リフレクションTIPS 55連発

プロフィール.cs

using Dapper;
using System.Data;
using System.Runtime.CompilerServices;
using static DapperSample.DapperMap;

namespace DapperSample;

public interface ITable
{
    public static abstract string TableName { get; }
}

public class プロフィール : ITable
{
    public static string TableName => "プロフィール";
    public static string GetColumnName(string columnName) => columnName.GetColumnName<プロフィール>();

    public static void CreateTable(IDbConnection cn)
    {
        var query = $"CREATE TABLE IF NOT EXISTS {TableName}({GetColumnName(nameof(Id))} INTEGER PRIMARY KEY AUTOINCREMENT , {GetColumnName(nameof(名前))} TEXT NOT NULL, {GetColumnName(nameof(別名))} TEXT NULL, {GetColumnName(nameof(年齢))} INTAGER NOT NULL )";
        cn.Execute(query);
    }

    public static void DropTable(IDbConnection cn)
    {
        var query = "DROP TABLE IF EXISTS {TableName}";
        cn.Execute(query);
    }

    public static IEnumerable<T> Select<T>(IDbConnection cn) where T : class
    {
        var query = $"SELECT * FROM {TableName}";
        return cn.Query<T>(query);
    }

    public static int Count(IDbConnection cn)
    {
        var query = $"SELECT COUNT(*) FROM {TableName}";
        return cn.ExecuteScalar<int>(query);
    }

    public static void Insert(IDbConnection cn)
    {
        var Items = new List<プロフィール>()
        {
            new プロフィール("シド・カゲノー", "シャドウ", 15),
            new プロフィール("アルファ", 15),
            new プロフィール("ベータ","ナツメ・カフカ",15),
            new プロフィール("デルタ", 15),
            new プロフィール("ゼータ", 15),
            new プロフィール("アレクシア・ミドガル", 15),
            new プロフィール("ヒョロ・ガリ", 15),
            new プロフィール("ジャガ・イモ", 15),
            new プロフィール("イータ", "イータ・ロイド・ライト", 16),
            new プロフィール("ニュー", 16),
            new プロフィール("シェリー・バーネット", 16),
            new プロフィール("ガンマ", "ルーナ", 17),
            new プロフィール("イプシロン", "シロン", 17),
            new プロフィール("クレア・カゲノー", 17),
            new プロフィール("ローズ・オリアナ", 17),
            new プロフィール("アイリス・ミドガル", 20),
        };
        var query = $"INSERT INTO {TableName} ({GetColumnName(nameof(名前))}, {GetColumnName(nameof(別名))}, {GetColumnName(nameof(年齢))}) VALUES (@{nameof(名前)},@{nameof(別名)}, @{nameof(年齢)})";
        cn.Execute(query, Items);
    }

    public static void Update(IDbConnection cn, IEnumerable<プロフィール> items)
    {
        var query = $"UPDATE {TableName} SET {GetColumnName(nameof(別名))}=@{nameof(別名)} WHERE {GetColumnName(nameof(Id))}=@{nameof(Id)}";
        cn.Execute(query, items);
    }

    public static void Delete(IDbConnection cn, IEnumerable<プロフィール> items)
    {
        var query = $"DELETE FROM {TableName} WHERE {GetColumnName(nameof(Id))}=@{nameof(Id)}";
        cn.Execute(query, items);
    }

    [ModuleInitializer]
    public static void Init()
    {
        SetSQLMap<プロフィール>();
    }

    public プロフィール()
    {
    }

    public プロフィール(string 名前, int 年齢)
    {
        this.名前 = 名前;
        this.年齢 = 年齢;
    }

    public プロフィール(string 名前, string? 別名, int 年齢)
    {
        this.名前 = 名前;
        this.別名 = 別名;
        this.年齢 = 年齢;
    }

    [ColumnName("Id")]
    public int? Id { get; set; }

    [ColumnName("名前")]
    public string 名前 { get; set; } = string.Empty;

    [ColumnName("別名")]
    public string? 別名 { get; set; }

    [ColumnName("年齢")]
    public int 年齢 { get; set; }

}

public class Profile : ITable
{
    public static string TableName => "プロフィール";

    [ModuleInitializer]
    public static void Init()
    {
        SetSQLMap<Profile>();
    }

    public Profile()
    {
    }

    public Profile(string name, int age)
    {
        Name = name;
        Age = age;
    }

    public Profile(string name, string? alias, int age)
    {
        Name = name;
        Alias = alias;
        Age = age;
    }

    [ColumnName("Id")]
    public int? Id { get; set; }

    [ColumnName("名前")]
    public string Name { get; set; } = string.Empty;

    [ColumnName("別名")]
    public string? Alias { get; set; }

    [ColumnName("年齢")]
    public int Age { get; set; }
}

DapperMap.cs

using Dapper;
using System.Data;
using System.Reflection;

namespace DapperSample;

public static class DapperMap
{
    private static readonly Dictionary<Type, Dictionary<string, string>> ColumnNameCache = new();

    /// <summary>
    /// キャッシュからカラム名を返します。
    /// </summary>
    public static string GetColumnName<T>(this object propertyName) where T : ITable => ColumnNameCache[typeof(T)][propertyName?.ToString() ?? string.Empty] ?? string.Empty;

    /// <summary>
    /// プロパティ名とカラム名を取得します。
    /// </summary>
    private static Dictionary<string, string> GetProperties<T>()
    {
        var type = typeof(T);
        var result = type.GetProperties(BindingFlags.Instance | BindingFlags.Public)
            .Select(x => new KeyValuePair<string, string>(x.Name, x.GetCustomAttribute<ColumnNameAttribute>()?.ColumnName ?? x.Name))
            .ToDictionary(x => x.Key, x => x.Value);

        ColumnNameCache.Add(type, result);
        return result;
    }

    /// <summary>
    /// カスタムSQLMapをセットします。
    /// </summary>
    public static void SetSQLMap<T>() where T : ITable
    {
        var type = typeof(T);
        var properties = GetProperties<T>();
        var mapper = new CustomPropertyTypeMap(type, (type, columnName) => type.GetProperty(properties.First(x => x.Value == columnName).Key));
        SqlMapper.SetTypeMap(type, mapper);
    }
}

Program.cs

using System.Data.SQLite;
using System.Diagnostics;

namespace DapperSample;

internal static class Program
{
    [STAThread]
    static void Main()
    {
        var 接続文字列 = new SQLiteConnectionStringBuilder() { DataSource = @$"{Application.StartupPath}\Sample.db" };
        using var conn = new CustomDbConnection(new SQLiteConnection(接続文字列.ConnectionString));
        conn.Open();

        //プロフィールテーブルの中身が、Profileクラスに格納される
        var Items2 = プロフィール.Select<Profile>(conn);
        Debug.WriteLine(string.Join("\n", Items2.Select(x => $"{x.Name}({x.Age})")));
    }
}

4.発行されるSQL を確認する

Dapper を使っていると、どんなSQL が発行されているのか確かめたり、ログに出力したくなるときがあります。
Dapper - Get information about the executed query statement

そういうときは、IDbConnection と、IDbCommand のWrap を作り、DBConnection の間に挟むことで、発行されるSQL を確認することができます。

using Dapper;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Text.RegularExpressions;

#nullable disable
public class CustomDbConnection : IDbConnection
{
    private readonly IDbConnection _conn;
    private bool disposedValue;

    public CustomDbConnection(IDbConnection connection) => _conn = connection;
    public string ConnectionString { get => _conn.ConnectionString; set => _conn.ConnectionString = value; }
    public int ConnectionTimeout => _conn.ConnectionTimeout;
    public string Database => _conn.Database;
    public ConnectionState State => _conn.State;
    public IDbTransaction BeginTransaction() => _conn.BeginTransaction();
    public IDbTransaction BeginTransaction(IsolationLevel il) => _conn.BeginTransaction(il);
    public void ChangeDatabase(string databaseName) => _conn.ChangeDatabase(databaseName);
    public IDbCommand CreateCommand() => new CustomDbCommand(_conn.CreateCommand());
    public void Open() => _conn.Open();
    public void Close() => _conn.Close();
    protected virtual void Dispose(bool disposing)
    {
        if (!disposedValue)
        {
            if (disposing) { }
            disposedValue = true;
        }
    }
    public void Dispose()
    {
        Dispose(disposing: true);
        GC.SuppressFinalize(this);
    }
}

public class CustomDbCommand : IDbCommand
{
    private readonly IDbCommand _cmd;
    private bool disposedValue;

    public CustomDbCommand(IDbCommand command) => _cmd = command;
    public string CommandText { get => _cmd.CommandText; set => _cmd.CommandText = value; }
    public int CommandTimeout { get => _cmd.CommandTimeout; set => _cmd.CommandTimeout = value; }
    public CommandType CommandType { get => _cmd.CommandType; set => _cmd.CommandType = value; }
    public IDbConnection Connection { get => _cmd.Connection; set => _cmd.Connection = value; }
    public IDataParameterCollection Parameters => _cmd.Parameters;
    public IDbTransaction Transaction { get => _cmd.Transaction; set => _cmd.Transaction = value; }
    public UpdateRowSource UpdatedRowSource { get => _cmd.UpdatedRowSource; set => _cmd.UpdatedRowSource = value; }
    public void Cancel() => _cmd.Cancel();
    public IDbDataParameter CreateParameter() => _cmd.CreateParameter();
    public void Prepare() => _cmd.Prepare();

    public int ExecuteNonQuery()
    {
        WriteLog("ExecuteNonQuery", _cmd.CommandText, _cmd.Parameters);
        return _cmd.ExecuteNonQuery();
    }

    public IDataReader ExecuteReader()
    {
        WriteLog("ExecuteReader", _cmd.CommandText, _cmd.Parameters);
        return _cmd.ExecuteReader();
    }

    public IDataReader ExecuteReader(CommandBehavior behavior)
    {
        WriteLog("ExecuteReader", _cmd.CommandText, _cmd.Parameters);
        return _cmd.ExecuteReader(behavior);
    }

    public object ExecuteScalar()
    {
        WriteLog("ExecuteScalar", _cmd.CommandText, _cmd.Parameters);
        return _cmd.ExecuteScalar();
    }

    private static void WriteLog(string method, string command, IDataParameterCollection parameters)
        => Debug.WriteLine($"[{method}]\t{command}; {string.Join(',', parameters.Cast<DbParameter>().Select(x => $"{x.ParameterName}={(x.Value == DBNull.Value ? "(null)" : x.Value)}"))}");

    protected virtual void Dispose(bool disposing)
    {
        if (!disposedValue)
        {
            if (disposing) { }
            disposedValue = true;
        }
    }

    public void Dispose()
    {
        Dispose(disposing: true);
        GC.SuppressFinalize(this);
    }
}

使い方

Program.cs

static void Main()
{
        var 接続文字列 = new SQLiteConnectionStringBuilder() { DataSource = @$"{Application.StartupPath}\Sample.db" };
        //このように使いたいコネクションの間に挟むだけです。
        using var conn = new CustomDbConnection(new SQLiteConnection(接続文字列.ConnectionString));
        conn.Open();
        プロフィール.DropTable(conn);
        //[ExecuteNonQuery]	DROP TABLE IF EXISTS プロフィール; 

        プロフィール.CreateTable(conn);
        //[ExecuteNonQuery]	CREATE TABLE IF NOT EXISTS プロフィール(Id INTEGER PRIMARY KEY AUTOINCREMENT , 名前 TEXT NOT NULL, 別名 TEXT NULL, 年齢 INTAGER NOT NULL );

        プロフィール.Insert(conn);
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=シャドウ,名前=シド・カゲノー,年齢=15
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=(null),名前=アルファ,年齢=15
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=ナツメ・カフカ,名前=ベータ,年齢=15
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=(null),名前=デルタ,年齢=15
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=(null),名前=ゼータ,年齢=15
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=(null),名前=アレクシア・ミドガル,年齢=15
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=(null),名前=ヒョロ・ガリ,年齢=15
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=(null),名前=ジャガ・イモ,年齢=15
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=イータ・ロイド・ライト,名前=イータ,年齢=16
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=(null),名前=ニュー,年齢=16
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=(null),名前=シェリー・バーネット,年齢=16
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=ルーナ,名前=ガンマ,年齢=17
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=シロン,名前=イプシロン,年齢=17
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=(null),名前=クレア・カゲノー,年齢=17
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=(null),名前=ローズ・オリアナ,年齢=17
        //[ExecuteNonQuery]	INSERT INTO プロフィール (名前, 別名, 年齢) VALUES (@名前,@別名, @年齢); 別名=(null),名前=アイリス・ミドガル,年齢=20

        Debug.WriteLine($"Count:{プロフィール.Count(conn)}");
        //[ExecuteScalar]	SELECT COUNT(*) FROM プロフィール; 
        //Count:16

        var Items = プロフィール.Select<プロフィール>(conn);
        //[ExecuteReader]	SELECT * FROM プロフィール;

        var ローズオリアナ = Items.Where(x => x.名前 == "ローズ・オリアナ").First();
        ローズオリアナ.別名 = "666番";
        プロフィール.Update(conn, new[] { ローズオリアナ });
        //[ExecuteNonQuery]	UPDATE プロフィール SET 別名=@別名 WHERE Id=@Id; Id=15,別名=666番

        プロフィール.Delete(conn, Items.Where(x => x.別名 is null));
        //[ExecuteNonQuery]	DELETE FROM プロフィール WHERE Id=@Id; Id=2
        //[ExecuteNonQuery]	DELETE FROM プロフィール WHERE Id=@Id; Id=4
        //[ExecuteNonQuery]	DELETE FROM プロフィール WHERE Id=@Id; Id=5
        //[ExecuteNonQuery]	DELETE FROM プロフィール WHERE Id=@Id; Id=6
        //[ExecuteNonQuery]	DELETE FROM プロフィール WHERE Id=@Id; Id=7
        //[ExecuteNonQuery]	DELETE FROM プロフィール WHERE Id=@Id; Id=8
        //[ExecuteNonQuery]	DELETE FROM プロフィール WHERE Id=@Id; Id=10
        //[ExecuteNonQuery]	DELETE FROM プロフィール WHERE Id=@Id; Id=11
        //[ExecuteNonQuery]	DELETE FROM プロフィール WHERE Id=@Id; Id=14
        //[ExecuteNonQuery]	DELETE FROM プロフィール WHERE Id=@Id; Id=16

        Debug.WriteLine($"Count:{プロフィール.Count(conn)}");
        //[ExecuteScalar]	SELECT COUNT(*) FROM プロフィール; 
        //Count:6

        var Items2 = プロフィール.Select<Profile>(conn);
        Debug.WriteLine(string.Join("\n", Items2.Select(x => $"{x.Name}({x.Age})")));
        //[ExecuteReader]	SELECT * FROM プロフィール; 
        //シド・カゲノー(15)
        //ベータ(15)
        //イータ(16)
        //ガンマ(17)
        //イプシロン(17)
        //ローズ・オリアナ(17)
}

7.まとめ

他にも便利なテクニックなどがあれば、記事化していくつもりです。