C#

【C#】SqlBulkCopyで大量のデータを一括Insert

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秒

エンジニアの転職ならこれ!

【第二新卒向け】マイナビジョブ20's

マイナビジョブ20'sは、20代・第二新卒・既卒向けの転職エージェントです。

▼こんな方におすすめ
・はじめて転職しようと思っている
・転職できるだけのスキルが自分にあるか不安
・手厚いサポートを受けたい

【フリーランス向け】安心保障と豊富な案件紹介 Midworks

Midworksは豊富な案件と「フリーランス」と「正社員」の良いとこ取りをした働き方を実現する手厚い保障が特徴です。

▼こんな方におすすめ
・現在正社員でフリーランスになろうか悩んでいる
・フリーランスとして働いているが、先行きが不安がある  (安定的な案件確保や保障など)
・自分の市場価値を知りたい、見合った案件で参画したい
・今後のキャリアビジョンを踏まえて案件を選びたい

【未経験向け】自宅で現役エンジニアから学べる TechAcademy

テックアカデミーは、現役エンジニアから学べるオンラインに特化したプログラミングスクールです。
講師は全員、通過率10%の選考に合格した現役エンジニア。
確かなスキルをもとに受講生をマンツーマンサポートします。


▼こんな方におすすめ
・自宅にいながらオンライン完結で勉強できる
・受講生に1人ずつ現役エンジニアのパーソナルメンターが専属でつく
・チャットで質問すればすぐに回答が返ってくる
・オリジナルサービスやオリジナルアプリなどの開発までサポート