/// <summary>
/// 宛先テーブルに対して一括でデータ登録を行います。
/// </summary>
/// <typeparam name="T">モデルの型。</typeparam>
/// <param name="connection">コネクション。</param>
/// <param name="destinationTableName">宛先テーブル名。</param>
/// <param name="dataReader">データリーダ。</param>
/// <param name="truncate">宛先テーブルのデータ削有無。 <c>true</c> の場合は登録前に削除を行う。</param>
/// <param name="copyOptions">コピーオプション。</param>
/// <param name="externalTransaction">トランザクション。</param>
public static void BulkCopy <T>(
this SqlConnection connection,
string destinationTableName,
BulkCopyDataReader <T> dataReader,
bool truncate,
SqlBulkCopyOptions copyOptions,
SqlTransaction externalTransaction
)
{
using (var sqlBulkCopy = new SqlBulkCopy(connection, copyOptions, externalTransaction))
{
// 一括登録前にデータを削除
if (truncate)
{
using (var truncateCommand = new SqlCommand($"TRUNCATE TABLE [{destinationTableName}]", connection, externalTransaction))
{
truncateCommand.ExecuteNonQuery();
}
}
// マッピング
dataReader.SetColumnMappings(sqlBulkCopy.ColumnMappings);
// 一括登録実行
sqlBulkCopy.DestinationTableName = destinationTableName;
sqlBulkCopy.WriteToServer(dataReader);
}
}
/// <summary>
/// 宛先テーブルに対して一括でデータ更新を行います。
/// </summary>
/// <typeparam name="T">モデルの型。</typeparam>
/// <param name="connection">コネクション。</param>
/// <param name="destinationTableName">宛先テーブル名。</param>
/// <param name="dataReader">データリーダ。</param>
/// <param name="externalTransaction">トランザクション。</param>
public static void BulkUpdate <T>(
this SqlConnection connection,
string destinationTableName,
BulkCopyDataReader <T> dataReader,
SqlTransaction externalTransaction
)
{
// 一時テーブル作成
var temporaryTableName = $"#_tmp{Guid.NewGuid().ToString("N")}";
CreateTemporaryTable <T>(
connection,
temporaryTableName,
externalTransaction
);
using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, externalTransaction))
{
// マッピング
dataReader.SetColumnMappings(sqlBulkCopy.ColumnMappings);
// 一括登録実行
sqlBulkCopy.DestinationTableName = temporaryTableName;
sqlBulkCopy.WriteToServer(dataReader);
}
// 更新コマンド生成
// モデルに設定された主キーを元にJOINを生成
// 生成されるSQLの例）
//
// UPDATE [T1]
// SET
// [T1].[更新対象1] = [T2].[更新対象1] , [T1].[更新対象2] = [T2].[更新対象2] …
// FROM [宛先テーブル名] AS [T1]
// INNER JOIN [一時テーブル名] AS [T2]
// ON [T1].[主キー1] = [T2].[主キー2] AND [T1].[主キー2] = [T2].[主キー2] …
using (var updateCommand = new SqlCommand($@"
UPDATE [T1]
SET
{dataReader.GetUpdateColumns().Select(column => $"[T1].[{column}] = [T2].[{column}]").Aggregate((a, b) => $"{a} , {b}")}
FROM [{destinationTableName}] AS [T1]
INNER JOIN [{temporaryTableName}] AS [T2]
ON {dataReader.GetPrimaryColumns().Select(column => $"[T1].[{column}] = [T2].[{column}]").Aggregate((a, b) => $"{a} AND {b}")}
", connection, externalTransaction))
{
updateCommand.ExecuteNonQuery();
}
// 一時テーブルは削除
using (var dropCommand = new SqlCommand($"DROP TABLE [{temporaryTableName}]", connection, externalTransaction))
{
dropCommand.ExecuteNonQuery();
}
}
/// <summary>
/// 宛先テーブルに対して一括でデータ登録・更新・削除を行います。
/// </summary>
/// <typeparam name="T">モデルの型。</typeparam>
/// <param name="connection">コネクション。</param>
/// <param name="destinationTableName">宛先テーブル名。</param>
/// <param name="dataReader">データリーダ。</param>
/// <param name="externalTransaction">トランザクション。</param>
/// <param name="notMatchedDelete">宛先テーブルのみに存在するデータの削除要否。<c>true</c> の場合は削除を行う。 <c>false</c>の場合は削除を行わない。</param>
public static void BulkMerge <T>(
this SqlConnection connection,
string destinationTableName,
BulkCopyDataReader <T> dataReader,
SqlTransaction externalTransaction,
bool notMatchedDelete = true
)
{
// 一時テーブル作成
var temporaryTableName = $"#_tmp{Guid.NewGuid().ToString("N")}";
CreateTemporaryTable <T>(
connection,
temporaryTableName,
externalTransaction
);
using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, externalTransaction))
{
// マッピング
dataReader.SetColumnMappings(sqlBulkCopy.ColumnMappings);
// 一括登録実行
sqlBulkCopy.DestinationTableName = temporaryTableName;
sqlBulkCopy.WriteToServer(dataReader);
}
// 更新コマンド生成
// モデルに設定された主キーを元にJOINを生成
// 生成されるSQLの例）
//
// MERGE [宛先テーブル名] AS [T1]
// USING [一時テーブル名] AS [T2]
// ON [T1].[主キー1] = [T2].[主キー2] AND [T1].[主キー2] = [T2].[主キー2] …
// WHEN MATCHED
// THEN UPDATE
// SET
// [T1].[更新対象1] = [T2].[更新対象1] , [T1].[更新対象2] = [T2].[更新対象2] …
// WHEN NOT MATCHED BY TARGET
// THEN INSERT ([項目1] , [項目2] …)
// VALUES ([T2].[項目1], [T2].[項目2] …)
// WHEN NOT MATCHED BY SOURCE
// DELETE
using (var mergeCommand = new SqlCommand($@"
MERGE[{ destinationTableName }] AS[T1]
USING[{ temporaryTableName}] AS[T2]
ON { dataReader.GetPrimaryColumns().Select(column => $"[T1].[{column}] = [T2].[{column}]").Aggregate((a, b) => $"{a} AND {b}")}
WHEN MATCHED
THEN UPDATE
SET
{ dataReader.GetUpdateColumns().Select(column => $"[T1].[{column}] = [T2].[{column}]").Aggregate((a, b) => $"{a} , {b}")}
WHEN NOT MATCHED BY TARGET
THEN INSERT({ dataReader.GetColumns().Select(column => $"[{column}]").Aggregate((a, b) => $"{a} , {b}")})
VALUES({ dataReader.GetColumns().Select(column => $"[T2].[{column}]").Aggregate((a, b) => $"{a} , {b}")})
{ (notMatchedDelete ? $@"
WHEN NOT MATCHED BY SOURCE
THEN DELETE
" : string.Empty)}
;
", connection, externalTransaction))
{
mergeCommand.ExecuteNonQuery();
}
// 一時テーブルは削除
using (var dropCommand = new SqlCommand($"DROP TABLE [{temporaryTableName}]", connection, externalTransaction))
{
dropCommand.ExecuteNonQuery();
}
}
