SqlBulkCopy でデータを一括登録する(SQL Server)

# データの一括登録

Dapper を使用してDB処理を実装していたのですが、データ件数が多い場合に INSERT処理 に多くの時間がかかるようになりました。 [^1]
大量データの一括登録処理向けに、SqlBulkCopy を使用する方法を導入したので、簡単にまとめておきます。[^2]
(NULL値を含むデータを登録する時に例外が発生して対応に困ったので、その対処についての覚書を兼ねてます)

[^1]: 100万件のデータ登録に5分以上かかったりしました。
[^2]: 有料版の Dapper Plus では Bulk Insert を使用することができるそうです。

## SqlBulkCopy

`System.Data.SqlClient.SqlBulkCopy` を使用すると SQL Server のテーブルに大量データを高速に一括登録することが出来ます。

今回は `DataTable` を作成していますが、データベース間でのデータ移行では `IDataReader` を渡す方法が便利です。
Qiitaでは以下の記事で Oracle から SQL Server へのデータ移行に `IDataReader` を渡す方法が紹介されています。

https://qiita.com/st450/items/e8453c4a53d1e80b8111

### 注意事項

* .NET 5、 SQL Server 2019 Developer エディション で動作確認しています。
* データベースのテーブルに対応する マッピング用のデータクラス[^3] が必要となります。
* 以下のソースコードは例外処理や細かい制御を省いています。
* パフォーマンス測定を目的とした記事では無いので、所要時間は厳密な測定を行っていません。

[^3]: データベースの テーブルの項目名 と クラスのプロパティ名 が一致する必要があります。

## SqlBulkCopy の使用方法

SqlBulkCopy の簡単な使用例を記載します。
詳しい使用方法はMicrosoftのドキュメントを参照ください。

SqlBulkCopy クラス (System.Data.SqlClient) | Microsoft Docs
https://docs.microsoft.com/ja-jp/dotnet/api/system.data.sqlclient.sqlbulkcopy

```csharp
string connectionString = "****"; // 接続文字列
string tableName = "****"; // 登録先のテーブル名
List<T> data = new List<T> { /* 省略 */ } // 一括登録するデータ(Tはマッピング用のデータクラス)

using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
    connection.Open();

    using (var transaction = connection.BeginTransaction())
    {
        // DataTableの作成(後述)
        System.Data.DataTable dataTable = CreateDataTable(data);

        // SqlBulkCopyインスタンスの作成・利用
        using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(connection, System.Data.SqlClient.SqlBulkCopyOptions.Default, transaction))
        {
            // 登録先のテーブル名を指定
            bulkCopy.DestinationTableName = tableName;

            // データを一括登録
            bulkCopy.WriteToServer(dataTable);
        }

        transaction.Commit(); // コミット
    }
}
```

## DataTable の作成

マッピング用のデータクラスのリストから、`SqlBulkCopy` に受け渡すための `DataTable` を以下のように作成することが出来ます。
データクラスのプロパティ名 を 登録先のテーブルの項目名 として扱う為、プロパティ名 と 項目名 は一致している必要があります。[^4]

[^4]: 一致させられない場合、カスタム属性を用いてマッピングを行う等の工夫が必要になります(この記事では割愛します)。

```csharp
public static System.Data.DataTable CreateDataTable<T>(IEnumerable<T> items)
{
    var table = new System.Data.DataTable();

    var properties = typeof(T).GetProperties();

    // 項目定義の作成
    foreach (var prop in properties)
    {
        // Null許容の値型(Nullable)
        if (prop.PropertyType.IsGenericType
            && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
        {
            table.Columns.Add(new System.Data.DataColumn
            {
                ColumnName = prop.Name,
                DataType = Nullable.GetUnderlyingType(prop.PropertyType)
            });
        }
        // Nullable以外
        else
        {
            table.Columns.Add(new System.Data.DataColumn
            {
                ColumnName = prop.Name,
                DataType = prop.PropertyType
            });
        }
    }

    // データ投入
    foreach (var item in items)
    {
        var row = table.NewRow();

        foreach (var prop in properties)
        {
            row[prop.Name] = prop.GetValue(item) ?? System.DBNull.Value;
        }

        table.Rows.Add(row);
    }

    return table;
}
```

### NULL許容な値型への対応

`DataTable` の 項目定義として `DataColumn` のインスタンスを作成していますが、`DataType` プロパティには NULL許容型(`Nullable<>`)を指定することが出来ません。

`PropertyInfo` のインスタンス `prop` を用いて、基本的には以下のように `DataColumn` のインスタンスを作成できますが、

```csharp
new System.Data.DataColumn
{
    ColumnName = prop.Name,
    DataType = prop.PropertyType
}
```

対象となるプロパティが `int?` や `Nullable<int>` 等の NULL許容の値型 の場合、以下の例外が発生します。

```
System.NotSupportedException
  HResult=0x80131515
  Message=DataSet は System.Nullable<> をサポートしていません。
  Source=System.Data
```

その為、上記 `CreateDataTable`メソッド では、プロパティが `Nullable` の場合はそのベースとなる型(`Nullable<int>` に対する `int`)を使用するように実装しています。

```csharp
if (prop.PropertyType.IsGenericType
    && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
    table.Columns.Add(new System.Data.DataColumn
    {
        ColumnName = prop.Name,
        DataType = Nullable.GetUnderlyingType(prop.PropertyType)
    });
}
```

## SqlBulkCopy を使用した効果

最後に、Dapper を使用したデータ登録[^5] と `SqlBulkCopy` を使用したデータ登録にかかった時間を比較します。
登録したデータは 100万件 で 80MB 程度のデータサイズとなります。

[^5]: INSERT文 を指定して `Dapper.SqlMapper.Execute`メソッド を実行しました。

| 件数 | Dapper | SqlBulkCopy |
|--:|--:|--:|
|  10万件 |  約 40 秒 | 1 秒 未満 |
| 100万件 | 約 420 秒 |  約 10 秒 |

条件により実際にかかる所要時間は変わりますが、多くの場合データの一括登録処理を `SqlBulkCopy` に置き換えることで相当な高速化が期待できると思います。