List<T>を1行でDBにバルクインサートする
はじめに
大量のデータをDBに保存する手段の1つにバルクインサートというものがあります。
具体的に言うと以下のようなSQLのことです。
insert into hoge
values
(1,'text')
, (2,'abc')
1つのSQLでN行をDBに送ることで、高速に登録処理を行うことができます。
本記事は、このバルクインサートクエリを1行で作成する手法について紹介します。
前提条件
C#を使用します。
DBMSは不問です。
ORMは使用しません。
SQLビルダ（Carbunql）を使用します。
SQL実行にDapper、Carbunql.Dapperを使用します。
DBに保存したい内容はList<T>でインスタンスされているものとします。
ライブラリのインストール
PM> NuGet\Install-Package Carbunql
PM> NuGet\Install-Package Carbunql.Dapper
テストに使用するクラス
DBに保存するモデルを以下のように定義するとします。
public class ApiResult
{
public int ResultId { get; set; }
public string? ResultText { get; set; }
}
テストに使用するインスタンス
上記で定義したモデルをList<T>の形式でインスタンスします。
var results = new List<ApiResult>()
{
new (){ ResultId = 1 , ResultText = "a"},
new (){ ResultId = 2 , ResultText = "b"},
new (){ ResultId = 3 , ResultText = null},
};
バルクインサートクエリを作ろう！
var sql = results.ToSelectQuery().ToInsertQuery("table").ToCommand().CommandText;
That's all!
生成されるSQL
このようになります。
パラメータクエリになっていることに注目ください。
INSERT INTO
table (
result_id, result_text
)
SELECT
v.result_id,
v.result_text
FROM
(
VALUES
(:r0c0, :r0c1),
(:r1c0, :r1c1),
(:r2c0, :r2c1)
) AS v (
result_id, result_text
)
なお、パラメータは以下のコードで取得できます。
results.ToSelectQuery().ToInsertQuery("table").ToCommand().Parameters
ただ、実行するだけならクエリ文字列、パラメータを取得する必要はありません。
Carbunql.Dapperを使って以下のように記述するだけです。
IDbConnection cn = CreateOpenConnection(); //TODO : DBコネクションを取得する関数
cn.Execute(results.ToSelectQuery().ToInsertQuery("table"));
SQLを環境に合わせてカスタムしたい
生成されるSQLはデフォルトでは以下のようになっています。
- パラメータ識別子が「:」になっている。（Postgres用）
- 列名はプロパティ名がスネークケースになっている。
環境によってはこのような加工は望ましくない場合があります。この場合は少し手を加えてください。
ToSelectQuery関数の定義
定義はこの様になっています。
public static SelectQuery ToSelectQuery<T>(
this IEnumerable<T> source,
string placeholderIndentifer,
Func<string, string> propertyNameConverter
)
第1引数：string placeholderIndentifer
パラメータ識別子です。
SQLServerなら"@"を指定すればOK。
第2引数：Func<string, string> propertyNameConverter
プロパティ名を列名に変換する関数。
デフォルトはスネークケースに変換する関数がセットされるため、必要に応じて任意の関数をセットしてください。
発展：テンポラリテーブルを作成したい
List<T>のため、テーブル構造と一致しているとは限りません。
この場合、テンポラリテーブルを作ってDBサーバー上で加工する、という手法を取ることがあります。
このようなケースでは以下のようにしてください。
var sql = results.ToSelectQuery().ToCreateTableQuery("table", isTemporary: true).ToCommand().CommandText;
こちらもワンライナーで書くことができます。
まとめ
Carbunqlを使用すると、SQLを簡単に生成、加工できます。
ぜひご利用ください。
補足
本記事で紹介したソースコードの全文はこちらにあります。ご参考ください。
（本記事のコードと全く同じではないですが、ニュアンスはほぼ同じです）
Discussion