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型の変数から直接メソッドを呼び出すことができます。
var result = connection.Query<ArimaKinenDTO>(sql);
下準備：テーブルの作成
以下のようなテーブルを作成しました。
※日本語と英語バラバラですがご容赦ください……
| 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 |
下準備：マッピングするクラスの作成
ざっくり言うと、DBのテーブルと対になるクラスです。今回はカラム名とプロパティ名が完全一致している前提で作成しました。
ですが、必ずしも一致している必要はなく、Dapperの機能を使って紐づけることも可能です。
※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; }
}
データを取得する場合【SELECT】
複数行のデータを取得する場合
以下のメソッドが使用できます。
Query<T>を使用すると、戻り値が指定した型となります。非同期の場合、メソッド名にAsyncが付きます。
| メソッド名 | SELECTした結果 | 戻り値 |
|---|---|---|
| Query | 1つ以上の行 | 列挙可能なdynamic型を返す |
Query
public void Query()
{
// DataSource 以外のプロパティを設定しないため SqliteConnectionStringBuilder を経由しません。
// SqliteConnection にそのまま DataSource を渡します。
using var connection = new SqliteConnection(DataSource);
using (var command = connection.CreateCommand())
{
connection.Open();
var sql1 = @"SELECT * FROM arima_kinen;";
// 複数行のデータを取得して、すべてのレコードを返します。
var result = connection.Query<ArimaKinenDTO>(sql1);
foreach (var item in result)
{
Console.WriteLine($"{item.Wakuban} {item.Umaban} {item.Bamei} {item.Seibetu}{item.Barei} {item.Kinryo} {item.Kisyu} {item.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 川田 大久保
単一のデータを取得する場合
以下のメソッドが使用できます。
XXX<T>を使用すると、戻り値が指定した型となります。非同期の場合、それぞれのメソッド名にAsyncが付きます。
| メソッド名 | SELECTした結果 | 戻り値 |
|---|---|---|
| QuerySingle | 1行のみ | dynamic型を返す |
| QuerySingleOrDefault | 0行または1行 | dynamic型または null を返す |
| QueryFirst | 1つ以上の行 | 最初の行をdynamic型で返す |
| QueryFirstOrDefault | 1つ以上の行 | 最初の行をdynamic型で返す、または結果が返らない場合は null を返す |
QuerySingleOrDefault
public void Query()
{
// DataSource 以外のプロパティを設定しないため SqliteConnectionStringBuilder を経由しません。
// SqliteConnection にそのまま DataSource を渡します。
using var connection = new SqliteConnection(DataSource);
using (var command = connection.CreateCommand())
{
connection.Open();
// QuerySingleOrDefault(QuerySingle)の場合、複数行を取得するSELECT文を投げるとエラーになります。かならず単一行を返すSELECT文を作成してください。
var sql2 = @"SELECT * FROM arima_kinen WHERE umaban = 8;";
// 1行を取得し、そのレコードを返します。
var result2 = connection.QuerySingleOrDefault<ArimaKinenDTO>(sql2);
Console.WriteLine($"{result2.Wakuban} {result2.Umaban} {result2.Bamei} {result2.Seibetu}{result2.Barei} {result2.Kinryo} {result2.Kisyu} {result2.Kyusya}");
}
}
4 8 ウインマイティー 牝5 55 和田竜 五十嵐
QueryFirstOrDefault
public void Query()
{
// DataSource 以外のプロパティを設定しないため SqliteConnectionStringBuilder を経由しません。
// SqliteConnection にそのまま DataSource を渡します。
using var connection = new SqliteConnection(DataSource);
using (var command = connection.CreateCommand())
{
connection.Open();
var sql3 = @"SELECT * FROM arima_kinen;";
// 複数行のデータを取得し、最初のレコードを返します。
var result3 = connection.QueryFirstOrDefault<ArimaKinenDTO>(sql3);
Console.WriteLine($"{result3.Wakuban} {result3.Umaban} {result3.Bamei} {result3.Seibetu}{result3.Barei} {result3.Kinryo} {result3.Kisyu} {result3.Kyusya}");
}
}
1 1 アカイイト 牝5 55 幸 中竹
複数のSELECT文を実行し、データを取得する場合
以下のメソッドが使用できます。非同期の場合、メソッド名にAsyncが付きます。
- 売上情報＋売上明細や、請求情報＋請求明細など、関連するテーブルを一括で取得したい場合に重宝します。
| メソッド名 | 説明 |
|---|---|
| QueryMultiple | 複数のSELECT文を実行できる |
複数のSELECT文を実行した場合 QueryMultiple の結果から、さらにメソッドを呼び出し結果を取得します。
XXX<T>を使用すると、戻り値が指定した型となります。非同期の場合、それぞれのメソッド名にAsyncが付きます。
| メソッド名 | 取得対象の行数 | SELECTした結果 | 戻り値 |
|---|---|---|---|
| Read | 複数行 | 1つ以上の行 | 列挙可能なdynamic型を返す |
| ReadSingle | 単一行 | 1行のみ | dynamic型を返す |
| ReadSingleOrDefault | 単一行 | 0行または1行 | dynamic型または null を返す |
| ReadFirst | 単一行 | 1つ以上の行 | 最初の行をdynamic型で返す |
| ReadFirstOrDefault | 単一行 | 1つ以上の行 | 最初の行をdynamic型で返す、または結果が返らない場合は null を返す |
QueryMultiple
public void Query()
{
// DataSource 以外のプロパティを設定しないため SqliteConnectionStringBuilder を経由しません。
// SqliteConnection にそのまま DataSource を渡します。
using var connection = new SqliteConnection(DataSource);
using (var command = connection.CreateCommand())
{
connection.Open();
var sql3 = @"
SELECT * FROM arima_kinen;
SELECT * FROM arima_kinen WHERE umaban = 7;
SELECT COUNT(*) FROM arima_kinen;
";
// SELECT文をまとめて実行します。
var multi = connection.QueryMultiple(sql3);
// 戻り値の型を指定して1番目のSELECT文の取得結果を受け取ります。
var all = multi.Read<ArimaKinenDTO>();
// 戻り値の型を指定して2番目のSELECT文の取得結果を受け取ります。
var first = multi.ReadFirstOrDefault<ArimaKinenDTO>();
// 戻り値の型を指定して3番目のSELECT文の取得結果を受け取ります。
var count = multi.ReadFirstOrDefault<int>();
foreach (var item in all)
{
Console.WriteLine($"{item.Wakuban} {item.Umaban} {item.Bamei} {item.Seibetu}{item.Barei} {item.Kinryo} {item.Kisyu} {item.Kyusya} ");
}
Console.WriteLine($"\r\n{first.Wakuban} {first.Umaban} {first.Bamei} {first.Seibetu}{first.Barei} {first.Kinryo} {first.Kisyu} {first.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頭が出走しました。
WHERE句のパラメーターを設定する
実際のSQLではパラメーターを固定で書くことは少なく、ユーザーが指定する値等が割り当てられることが多いです。
そのような場合、パラメーター部分を @xxx と記述することがあります。
SELECT * FROM arima_kinen WHERE umaban = @umaban;
Dapperでは、以下のようにパラメーターを設定できます。
var result = connection.QueryFirstOrDefault<ArimaKinenDTO>(sql, xxx); // 第2引数にパラメーターを渡す
Anonymous ParameterとDynamic Parameters
-
匿名パラメーター(Anonymous Parameter)：
new {xxx = XXX}の形で値を設定する
Dapper - Anonymous Parameter -
動的パラメーター(Dynamic Parameters)：DynamicParameters型の変数を渡す
Dapper - Dynamic Parameter
パラメーターの指定
public void Query()
{
// DataSource 以外のプロパティを設定しないため SqliteConnectionStringBuilder を経由しません。
// SqliteConnection にそのまま DataSource を渡します。
using var connection = new SqliteConnection(DataSource);
using (var command = connection.CreateCommand())
{
connection.Open();
var sql5 = @"SELECT * FROM arima_kinen WHERE umaban = @umaban;";
// Anonymous Parameterの場合
// new {[カラム名] = [値]} でパラメーターを作成します。
var param = 3;
var apResult = connection.QueryFirstOrDefault<ArimaKinenDTO>(sql5, new { umaban = param });
Console.WriteLine($"{apResult.Wakuban} {apResult.Umaban} {apResult.Bamei} {apResult.Seibetu}{result4.Barei} {apResult.Kinryo} {apResult.Kisyu} {apResult.Kyusya}");
// Dynamic Parametersの場合
// Dynamic Parametersを作成し、Addメソッドでパラメーターを作成します。
var parameters = new DynamicParameters();
var number2 = "4";
parameters.Add("@umaban", number2, DbType.Int32); // 第3引数はパラメーターの型を指定します。
var dpResult = connection.QueryFirstOrDefault<ArimaKinenDTO>(sql5, 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 パラメーター
-
new {xxx = XXX}の形で配列（リストでも可）を設定する
Dapper - List Parameter
SELECT * FROM arima_kinen WHERE umaban in (1, 5, 9);のようなIN句のパラメーターを設定することができます。
IN句のパラメーター
public void Query()
{
// DataSource 以外のプロパティを設定しないため SqliteConnectionStringBuilder を経由しません。
// SqliteConnection にそのまま DataSource を渡します。
using var connection = new SqliteConnection(DataSource);
using (var command = connection.CreateCommand())
{
connection.Open();
var sql6 = @"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 result6 = connection.Query<ArimaKinenDTO>(sql6, new { umaban = numbers });
foreach (var item in result6)
{
Console.WriteLine($"{item.Wakuban} {item.Umaban} {item.Bamei} {item.Seibetu}{item.Barei} {item.Kinryo} {item.Kisyu} {item.Kyusya} ");
}
}
}
1 1 アカイイト 牝5 55 幸 中竹
3 5 ジェラルディーナ 牝4 55 Ｃデムーロ 斉藤崇
5 9 イクイノックス 牡3 55 ルメール 木村
その他のパラメーター
他にもいろいろなパラメーターの作成方法があります。
データを変更する場合【INSERT, UPDATE, DELETE 等】
クエリやストアドプロシージャを実行する場合
以下のメソッドが使用できます。非同期の場合、メソッド名にAsyncが付きます。
| メソッド名 | 説明 | 戻り値 |
|---|---|---|
| Execute | クエリやストアドプロシージャを実行 | 影響を受けた行（int型） |
SELECT文の実行にも使用できますが、基本は INSERT, UPDATE, DELETE や ストアドプロシージャ の実行に使用します。
Execute
public void Execute()
{
// DataSource 以外のプロパティを設定しないため SqliteConnectionStringBuilder を経由しません。
// SqliteConnection にそのまま DataSource を渡します。
using var connection = new SqliteConnection(DataSource);
using (var command = connection.CreateCommand())
{
connection.Open();
// 別のテーブル「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 でパラメーターを作成します。
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($"{insertResult}件追加しました。");
var targetNumber = 1;
// 更新
var updateSql = @"UPDATE takarazuka_kinen SET barei = 5 WHERE umaban = @umaban";
var updateResult = connection.Execute(updateSql, new { umaban = targetNo });
Console.WriteLine($"{updateResult}件更新しました。");
// 削除
var deleteSql = @"DELETE FROM takarazuka_kinen WHERE umaban = @umaban";
var updateResult = connection.Execute(deleteSql, new { umaban = targetNo });
Console.WriteLine($"{updateResult}件削除しました。");
}
}
1件追加しました
1件更新しました
1件削除しました
大量のデータ処理
大量のデータを処理する場合は一括処理（BulkInsertなど）を検討する必要があります。Dapperで一括処理を実施する場合、Dapper Plusライブラリ（有料）が必要となります。
SQLServerにもBULK INSAERTの機能があります。
データを計算する場合【Sum, Count 等】
単一の値を受け取りたい場合
以下のメソッドが使用できます。ExecuteScalar<T>を使用すると、戻り値が指定した型となります。
非同期の場合、メソッド名にAsyncが付きます。
- 単一の値を返す
Count(*)やSum()など、集計関数の結果を受け取る場合に使用することが多いです。 - クエリによって複数行が返された場合でも、最初の行の最初の列以外の値は返しません。
| メソッド名 | 説明 | 戻り値 |
|---|---|---|
| ExecuteScalar | クエリやストアドプロシージャを実行 | 最初の行の最初の列の値を返す、または空の場合 null を返す |
ExecuteScalar
public void Execute()
{
// DataSource 以外のプロパティを設定しないため SqliteConnectionStringBuilder を経由しません。
// SqliteConnection にそのまま DataSource を渡します。
using var connection = new SqliteConnection(DataSource);
using (var command = connection.CreateCommand())
{
connection.Open();
// COUNT
var countSql = @"SELECT COUNT(*) FROM arima_kinen";
var count = connection.ExecuteScalar(countSql);
Console.WriteLine($"2022年の有馬記念は計{count}頭が出走しました。");
// SUM
var sumSql = @"SELECT SUM(barei) FROM arima_kinen";
var sum = connection.ExecuteScalar(sumSql);
Console.WriteLine($"馬齢の合計は{sum}です。");
}
}
2022年の有馬記念は計16頭が出走しました。
馬齢の合計は72です。
戻り値をDbDataReaderで受け取りたい場合
以下のメソッドが使用できます。非同期の場合、メソッド名にAsyncが付きます。
| メソッド名 | 説明 | 戻り値 |
|---|---|---|
| ExecuteReader | クエリを実行 | System.Data.IDataReader |
おまけ：SQLite のセットアップとテーブル作成
サンプルに使用するSQLiteのセットアップとテーブル作成についてのメモです。
起動すればOKというラインのため、詳しい内容は公式ドキュメント等を参考にしてください。
-
ダウンロードからセットアップ
Precompiled Binaries for Windows（Windows 用のプリコンパイル済みバイナリ）から sqlite-tools--.zip をダウンロードします。
ダウンロード後解凍し、ファイルを適当なフォルダに移動します。移動先のフォルダでバージョン確認ができたらセットアップ完了です。
E:\...\SQLite>sqlite3 --version
3.40.0 2022-11-16 12:10:08 89...
-
新規データベース作成
sqlite3 [テーブル名].dbで作成します。以降、既存のデータベースを開くときも同様のコマンドを使用します。
E:\...\SQLite>sqlite3 advent.db
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
sqlite>
エクスプローラー上でもデータベースファイルが確認できます。
データベースファイルは sqlite3.exe と同じ場所になくてもOKです。データベースファイルは作成後、別の場所に移動できます。
sqlite3.exe と別の場所にあるデータベースを起動したい場合 sqlite3 [フォルダパス\テーブル名].db で実行可能です。
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
まとめ
記事内で使用したサンプルコードはこちら▶ CSharpDapperSample
2024.04 追記しました。最後まで見ていただきありがとうございました。