// Publish Async and NonAsync are merged into single operation flow with protected method using arg: bool isAsync (keeps code DRY)
// https://docs.microsoft.com/en-us/archive/msdn-magazine/2015/july/async-programming-brownfield-async-development#the-flag-argument-hack
protected async Task InsertAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync)
{
tableInfo.CheckToSetIdentityForPreserveOrder(entities);
if (isAsync)
{
await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.OpenConnection();
}
var connection = context.GetUnderlyingConnection(tableInfo.BulkConfig);
try
{
var transaction = context.Database.CurrentTransaction;
using var sqlBulkCopy = GetSqlBulkCopy((SqlConnection)connection, transaction, tableInfo.BulkConfig);
bool setColumnMapping = false;
tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
try
{
var dataTable = GetDataTable(context, type, entities, sqlBulkCopy, tableInfo);
if (isAsync)
{
await sqlBulkCopy.WriteToServerAsync(dataTable, cancellationToken).ConfigureAwait(false);
}
else
{
sqlBulkCopy.WriteToServer(dataTable);
}
}
catch (InvalidOperationException ex)
{
if (ex.Message.Contains(BulkExceptionMessage.ColumnMappingNotMatch))
{
bool tableExist = isAsync ? await tableInfo.CheckTableExistAsync(context, tableInfo, cancellationToken, isAsync : true).ConfigureAwait(false)
: tableInfo.CheckTableExistAsync(context, tableInfo, cancellationToken, isAsync: false).GetAwaiter().GetResult();
if (!tableExist)
{
var sqlCreateTableCopy = SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo);
var sqlDropTable = SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlCreateTableCopy, cancellationToken).ConfigureAwait(false);
await context.Database.ExecuteSqlRawAsync(sqlDropTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlCreateTableCopy);
context.Database.ExecuteSqlRaw(sqlDropTable);
}
}
}
throw;
}
}
finally
{
if (isAsync)
{
await context.Database.CloseConnectionAsync().ConfigureAwait(false);
}
else
{
context.Database.CloseConnection();
}
}
if (!tableInfo.CreatedOutputTable)
{
tableInfo.CheckToSetIdentityForPreserveOrder(entities, reset: true);
}
}
public async Task InsertAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken)
{
tableInfo.CheckToSetIdentityForPreserveOrder(entities);
var connection = await OpenAndGetSqlConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false);
try
{
var transaction = context.Database.CurrentTransaction;
// separate logic for System.Data.SqlClient and Microsoft.Data.SqlClient
if (SqlClientHelper.IsSystemConnection(connection))
{
using (var sqlBulkCopy = GetSqlBulkCopy((System.Data.SqlClient.SqlConnection)connection, transaction, tableInfo.BulkConfig))
{
bool setColumnMapping = false;
tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
try
{
var dataTable = GetDataTable(context, type, entities, sqlBulkCopy, tableInfo);
await sqlBulkCopy.WriteToServerAsync(dataTable, cancellationToken).ConfigureAwait(false);
}
catch (InvalidOperationException ex)
{
if (ex.Message.Contains(ColumnMappingExceptionMessage))
{
if (!await tableInfo.CheckTableExistAsync(context, tableInfo, cancellationToken).ConfigureAwait(false))
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false);
}
}
throw;
}
}
}
else
{
using (var sqlBulkCopy = GetSqlBulkCopy((Microsoft.Data.SqlClient.SqlConnection)connection, transaction, tableInfo.BulkConfig))
{
bool setColumnMapping = false;
tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
try
{
var dataTable = GetDataTable(context, type, entities, sqlBulkCopy, tableInfo);
await sqlBulkCopy.WriteToServerAsync(dataTable, cancellationToken).ConfigureAwait(false);
}
catch (InvalidOperationException ex)
{
if (ex.Message.Contains(ColumnMappingExceptionMessage))
{
if (!await tableInfo.CheckTableExistAsync(context, tableInfo, cancellationToken).ConfigureAwait(false))
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false);
}
}
throw;
}
}
}
}
finally
{
await context.Database.CloseConnectionAsync().ConfigureAwait(false);
}
if (!tableInfo.CreatedOutputTable)
{
tableInfo.CheckToSetIdentityForPreserveOrder(entities, reset: true);
}
}