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として作成しました。
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
引数は使用頻度が高いです。
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 Cデムーロ 斉藤崇
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を渡し、第二引数以降は任意で設定します。
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 和田竜 五十嵐
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を渡し、第二引数以降は任意で設定します。
各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
が付きます。
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 Cデムーロ 斉藤崇
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)の部分を配列(リスト)で指定することができる
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 Cデムーロ 斉藤崇
5 9 イクイノックス 牡3 55 ルメール 木村
その他のパラメーター
他にもいろいろなパラメーターの作成方法があります。
データを変更する場合【INSERT, UPDATE, DELETE 等】
以下のメソッドが使用できます。
メソッド名 | 説明 | 戻り値 |
---|---|---|
Execute | クエリやストアドプロシージャを実行 | 影響を受けた行(int型) |
影響を受けた行とは、SQL文が実行され正常に処理できた行数のことです。例えば1件のINSERTを実行した場合 Execute
の戻り値が「1」かどうかで成功または失敗を判断することができます。
非同期の場合、メソッド名の末尾に Async
が付きます。
Query
と同じく Execute
の第一引数に string 型のSQLを渡し、第二引数以降は任意で設定します。
SELECT文の実行にも使用できますが、基本的に 結果を返さない(データを取得しない) INSERT, UPDATE, DELETE や ストアドプロシージャの実行 に使用します。
パラメーターが1つの場合
INSERT
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
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
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
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
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を渡し、第二引数以降は任意で設定します。
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というラインのため、詳しい内容は公式ドキュメント等を参考にしてください。
-
ダウンロードからセットアップ
Precompiled Binaries for Windows(Windows 用のプリコンパイル済みバイナリ)から sqlite-tools--.zip をダウンロードします。
ダウンロード後解凍し、ファイルを適当なフォルダに移動します。移動先のフォルダでバージョン確認ができたらセットアップ完了です。
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>
エクスプローラー上でもデータベースファイルが確認できます。
データベースファイルは 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
2024.09 内容を一部修正しました。最後まで見ていただきありがとうございました。