private async Task MergeAsync(DataTable tableData, CancellationToken cancellationToken, Func <DbCommand, CancellationToken, Task> executeFactory)
{
var isBulk = _mergeDefinition.Mode == DataMergeMode.BulkCopy ||
(_mergeDefinition.Mode == DataMergeMode.Auto && tableData.Rows.Count > 1000);
// Step 1, validate definition
if (!Validate(_mergeDefinition, isBulk))
{
return;
}
try
{
await _dataSession
.EnsureConnectionAsync(cancellationToken)
.ConfigureAwait(false);
var sqlConnection = _dataSession.Connection as SqlConnection;
if (sqlConnection == null)
{
throw new InvalidOperationException(
"Bulk-Copy only supported by SQL Server. Make sure DataSession was create with a valid SqlConnection.");
}
var sqlTransaction = _dataSession.Transaction as SqlTransaction;
string mergeSql;
if (isBulk)
{
// Step 2, create temp table
string tableSql = DataMergeGenerator.BuildTable(_mergeDefinition);
using (var tableCommand = _dataSession.Connection.CreateCommand())
{
tableCommand.CommandText = tableSql;
tableCommand.CommandType = CommandType.Text;
tableCommand.Transaction = sqlTransaction;
await tableCommand
.ExecuteNonQueryAsync(cancellationToken)
.ConfigureAwait(false);
}
// Step 3, bulk copy into temp table
using (var bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction))
{
bulkCopy.DestinationTableName = _mergeDefinition.TemporaryTable;
bulkCopy.BatchSize = 1000;
foreach (var mergeColumn in _mergeDefinition.Columns.Where(c => !c.IsIgnored && c.CanBulkCopy))
{
bulkCopy.ColumnMappings.Add(mergeColumn.SourceColumn, mergeColumn.SourceColumn);
}
await bulkCopy
.WriteToServerAsync(tableData, cancellationToken)
.ConfigureAwait(false);
}
// Step 4, merge sql
mergeSql = DataMergeGenerator.BuildMerge(_mergeDefinition);
}
else
{
// build merge from data
mergeSql = DataMergeGenerator.BuildMerge(_mergeDefinition, tableData);
}
// run merge statement
using (var mergeCommand = _dataSession.Connection.CreateCommand())
{
mergeCommand.CommandText = mergeSql;
mergeCommand.CommandType = CommandType.Text;
mergeCommand.Transaction = sqlTransaction;
// run merge with factory
await executeFactory(mergeCommand, cancellationToken)
.ConfigureAwait(false);
}
}
finally
{
_dataSession.ReleaseConnection();
}
}
private void Merge(DataTable table, Action <IDbCommand> executeFactory)
{
// Step 1, validate definition
if (!Validate(_mergeDefinition))
{
return;
}
try
{
_dataSession.EnsureConnection();
var sqlConnection = _dataSession.Connection as SqlConnection;
if (sqlConnection == null)
{
throw new InvalidOperationException(
"Bulk-Copy only supported by SQL Server. Make sure DataSession was create with a valid SqlConnection.");
}
var sqlTransaction = _dataSession.Transaction as SqlTransaction;
// Step 2, create temp table
string tableSql = DataMergeGenerator.BuildTable(_mergeDefinition);
using (var tableCommand = _dataSession.Connection.CreateCommand())
{
tableCommand.CommandText = tableSql;
tableCommand.CommandType = CommandType.Text;
tableCommand.Transaction = sqlTransaction;
tableCommand.ExecuteNonQuery();
}
// Step 3, bulk copy into temp table
using (var bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction))
{
bulkCopy.DestinationTableName = _mergeDefinition.TemporaryTable;
bulkCopy.BatchSize = 1000;
foreach (var mergeColumn in _mergeDefinition.Columns.Where(c => !c.IsIgnored && c.CanBulkCopy))
{
bulkCopy.ColumnMappings.Add(mergeColumn.SourceColumn, mergeColumn.SourceColumn);
}
bulkCopy.WriteToServer(table);
}
// Step 4, run merge sql
string mergeSql = DataMergeGenerator.BuildMerge(_mergeDefinition);
using (var mergeCommand = _dataSession.Connection.CreateCommand())
{
mergeCommand.CommandText = mergeSql;
mergeCommand.CommandType = CommandType.Text;
mergeCommand.Transaction = sqlTransaction;
// run merge with factory
executeFactory(mergeCommand);
}
}
finally
{
_dataSession.ReleaseConnection();
}
}