BulkInsertのサブルーチン

 

BulkInsertのサブルーチン　

https://pg-life.net/csharp/sqlbulkcopy/ 

 とても助かりました。

datatableの項目にnullがあるとNGだったので、以下に修正

 

using System.Data;

    public void BulkInsert<T>(List<T> _list) {

        string connectionId = "AppDbConnStr";
        using (var conn = new SqlConnection(_config.GetConnectionString(connectionId))) {
            conn.Open();
            using (var tran = conn.BeginTransaction()) {
                try {
                    var list = _list.ConvertAll(c => (T)c);
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn,
                                                                  SqlBulkCopyOptions.Default,
                                                                  tran)) {
                        //タイムアウト指定（デフォルトで30秒）
                        bulkCopy.BulkCopyTimeout = 60;

                        //テーブル名指定
                        bulkCopy.DestinationTableName = typeof(T).Name;

                        //一括Insert実行
                        bulkCopy.WriteToServer(ToDataTable<T>(list));

                        //コミット
                        tran.Commit();
                    }
                }
                catch (Exception) {
                    //ロールバック
                    tran.Rollback();
                    throw;
                }
            }
        }
    }

    private static DataTable ToDataTable<T>(IEnumerable<T> data) {

        var properties = typeof(T).GetProperties();
        var typeCasts = new Type[properties.Count()];

        for (var i = 0; i < properties.Count(); i++) {
            if (properties[i].PropertyType.IsEnum) {
                typeCasts[i] = Enum.GetUnderlyingType(properties[i].PropertyType);
            }
            else {
                typeCasts[i] = null;
            }
        }

        var dataTable = new DataTable();
        for (var i = 0; i < properties.Count(); i++) {
            // Nullable types are not supported.
            var propertyNonNullType = Nullable.GetUnderlyingType(properties[i].PropertyType) ?? properties[i].PropertyType;
            dataTable.Columns.Add(properties[i].Name, typeCasts[i] == null ? propertyNonNullType : typeCasts[i]);
        }

        foreach (var item in data) {
            var values = new object[properties.Count()];
            for (var i = 0; i < properties.Count(); i++) {
                var value = properties[i].GetValue(item, null);
                values[i] = typeCasts[i] == null ? value : Convert.ChangeType(value, typeCasts[i]);
            }

            dataTable.Rows.Add(values);
        }

        return dataTable;
    }