SqlBulkCopyクラスの使い方を記載します。
大量のデータを登録したい場合に、EFやDapperでは1件ずつInsert文が発行されるのでどうしても時間がかかってしまいます。
SqlBulkCopyでは一括で登録が行えるので、大量のデータを高速にInsertすることが可能です。
【検証環境】.NET 5.0
利用準備
SqlBulkCopyにはDataTableで渡す必要がありますので、ListをDataTableに変換する拡張メソッドを定義します。
https://qiita.com/keidrumfreak/items/f092b3cacfc2961610b6
using System.Data;
public static class IEnumerableExtention
{
public static DataTable ConvertDataTable<T>(this IEnumerable<T> items)
{
var properties = typeof(T).GetProperties();
var result = new DataTable();
// テーブルレイアウトの作成
foreach (var prop in properties)
{
result.Columns.Add(prop.Name, prop.PropertyType);
}
// 値の投げ込み
foreach (var item in items)
{
var row = result.NewRow();
foreach (var prop in properties)
{
var itemValue = prop.GetValue(item, new object[] { });
row[prop.Name] = itemValue;
}
result.Rows.Add(row);
}
return result;
}
}
サンプルコード
下記のメソッドを定義して、任意の型のリストで渡せば一括Insertできます。
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public static void BulkInsert<T>(List<T> _list)
{
//環境に応じて変えてください
var connectionString = "接続文字列";
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var tran = connection.BeginTransaction())
{
try
{
var list = _list.ConvertAll(c => (T)c);
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection,
SqlBulkCopyOptions.Default,
tran))
{
//タイムアウト指定（デフォルトで30秒）
bulkCopy.BulkCopyTimeout = 60;
//テーブル名指定
bulkCopy.DestinationTableName = typeof(T).Name;
//一括Insert実行
bulkCopy.WriteToServer(list.ConvertDataTable<T>());
//コミット
tran.Commit();
}
}
catch (Exception)
{
//ロールバック
tran.Rollback();
throw;
}
}
}
}SqlBulkCopyではテーブル名を指定する必要があります。
このコードの場合、<T>のクラス名がテーブル名である前提で作成してあります。
また、列名とクラスのプロパティ名は一致させる必要があり、定義順も同様に一致させる必要があります。
速度比較
業務で4000行ほどのデータをDelete&Insertする必要があったので、DapperとSqlBulkInsertでそれぞれ試してみたところ下記の様な結果でした。(DeleteはどちらもDapper）
| Dapper | 約120秒 |
| SqlBulkCopy | 約5秒 |
https://www.moonmile.net/blog/archives/9649
https://feeld-uni.com/entry/2019/11/06/212958