public static async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
tableInfo.InsertToTempTable = true;
await tableInfo.CheckHasIdentityAsync(context).ConfigureAwait(false);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo)).ConfigureAwait(false);
if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo)).ConfigureAwait(false);
}
try
{
await InsertAsync(context, entities, tableInfo, progress).ConfigureAwait(false);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType)).ConfigureAwait(false);
if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity)
{
try
{
await tableInfo.UpdateOutputIdentityAsync(context, entities).ConfigureAwait(false);
}
finally
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
}
}
}
finally
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)).ConfigureAwait(false);
}
}
public static void Merge <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
tableInfo.InsertToTempTable = true;
if (tableInfo.BulkConfig.UpdateByProperties == null || tableInfo.BulkConfig.UpdateByProperties.Count() == 0)
{
tableInfo.CheckHasIdentity(context);
}
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));
if (tableInfo.BulkConfig.SetOutputIdentity)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo));
}
try
{
Insert(context, entities, tableInfo, progress);
context.Database.ExecuteSqlCommand(SqlQueryBuilder.MergeTable(tableInfo, operationType));
if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasSinglePrimaryKey)
{
try
{
tableInfo.UpdateOutputIdentity(context, entities);
}
finally
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
}
}
}
finally
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
}
}
public static void Merge <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
tableInfo.InsertToTempTable = true;
tableInfo.CheckHasIdentity(context);
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName));
if (tableInfo.BulkConfig.SetOutputIdentity)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName));
}
try
{
SqlBulkOperation.Insert <T>(context, entities, tableInfo, progress);
context.Database.ExecuteSqlCommand(SqlQueryBuilder.MergeTable(tableInfo, operationType));
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
if (tableInfo.BulkConfig.SetOutputIdentity)
{
tableInfo.UpdateOutputIdentity(context, entities);
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
}
}
catch (Exception ex)
{
if (tableInfo.BulkConfig.SetOutputIdentity)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
}
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
throw ex;
}
}
public static async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
tableInfo.InsertToTempTable = true;
await tableInfo.CheckHasIdentityAsync(context).ConfigureAwait(false);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo)).ConfigureAwait(false);
if (tableInfo.CreatedOutputTable)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true)).ConfigureAwait(false);
if (tableInfo.TimeStampColumnName != null)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType));
}
}
bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(SqlBulkCopyOptions.KeepIdentity);
try
{
await InsertAsync(context, entities, tableInfo, progress).ConfigureAwait(false);
if (keepIdentity && tableInfo.HasIdentity)
{
context.Database.OpenConnection();
context.Database.ExecuteSqlCommand(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, true));
}
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType)).ConfigureAwait(false);
if (tableInfo.CreatedOutputTable)
{
try
{
await tableInfo.LoadOutputDataAsync(context, entities).ConfigureAwait(false);
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
}
}
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)).ConfigureAwait(false);
}
if (keepIdentity && tableInfo.HasIdentity)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, false));
context.Database.CloseConnection();
}
}
}
public static void Merge <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType) where T : class
{
tableInfo.InsertToTempTable = true;
tableInfo.CheckHasIdentity(context);
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName));
if (tableInfo.BulkConfig.SetOutputIdentity)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName));
}
try
{
SqlBulkOperation.Insert <T>(context, entities, tableInfo);
context.Database.ExecuteSqlCommand(SqlQueryBuilder.MergeTable(tableInfo, operationType));
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
if (tableInfo.BulkConfig.SetOutputIdentity)
{
var entitiesWithOutputIdentity = context.Set <T>().FromSql(SqlQueryBuilder.SelectFromTable(tableInfo.FullTempOutputTableName, tableInfo.PrimaryKeyFormated)).ToList();
if (tableInfo.BulkConfig.PreserveInsertOrder) // Updates PK in entityList
{
Type type = typeof(T);
var accessor = TypeAccessor.Create(type);
for (int i = 0; i < tableInfo.NumberOfEntities; i++)
{
accessor[entities[i], tableInfo.PrimaryKey] = accessor[entitiesWithOutputIdentity[i], tableInfo.PrimaryKey];
}
}
else // Clears entityList and then refill it with loaded entites from Db
{
entities.Clear();
((List <T>)entities).AddRange(entitiesWithOutputIdentity);
}
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
}
}
catch (Exception ex)
{
if (tableInfo.BulkConfig.SetOutputIdentity)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
}
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
throw ex;
}
}
public static void Merge <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
tableInfo.InsertToTempTable = true;
if (tableInfo.BulkConfig.UpdateByProperties == null || tableInfo.BulkConfig.UpdateByProperties.Count() == 0)
{
tableInfo.CheckHasIdentity(context);
}
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));
if (tableInfo.CreatedOutputTable)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true));
if (tableInfo.TimeStampColumnName != null)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType));
}
}
try
{
Insert(context, entities, tableInfo, progress);
context.Database.ExecuteSqlCommand(SqlQueryBuilder.MergeTable(tableInfo, operationType));
if (tableInfo.CreatedOutputTable)
{
try
{
tableInfo.LoadOutputData(context, entities);
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
}
}
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
}
}
}
public static async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken) where T : class
{
string providerName = context.Database.ProviderName;
// -- SQL Server --
if (providerName.EndsWith(DbServer.SqlServer.ToString()))
{
tableInfo.InsertToTempTable = true;
await tableInfo.CheckHasIdentityAsync(context, cancellationToken).ConfigureAwait(false);
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);
if (tableInfo.CreatedOutputTable)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true), cancellationToken).ConfigureAwait(false);
if (tableInfo.TimeStampColumnName != null)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType), cancellationToken).ConfigureAwait(false);
}
}
bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(SqlBulkCopyOptions.KeepIdentity);
try
{
await InsertAsync(context, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
if (keepIdentity && tableInfo.HasIdentity)
{
await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false);
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, true), cancellationToken).ConfigureAwait(false);
}
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType), cancellationToken).ConfigureAwait(false);
if (tableInfo.CreatedOutputTable)
{
await tableInfo.LoadOutputDataAsync(context, entities, cancellationToken).ConfigureAwait(false);
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
if (tableInfo.CreatedOutputTable)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName), cancellationToken).ConfigureAwait(false);
}
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName), cancellationToken).ConfigureAwait(false);
}
if (keepIdentity && tableInfo.HasIdentity)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, false), cancellationToken).ConfigureAwait(false);
context.Database.CloseConnection();
}
}
}
// -- SQLite --
else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
{
var connection = await OpenAndGetSqliteConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false);
var transaction = tableInfo.BulkConfig.SqliteTransaction ?? connection.BeginTransaction();
try
{
var command = GetSqliteCommand(context, entities, tableInfo, connection, transaction);
var typeAccessor = TypeAccessor.Create(typeof(T), true);
int rowsCopied = 0;
foreach (var item in entities)
{
LoadSqliteValues(tableInfo, typeAccessor, item, command);
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
}
if (operationType != OperationType.Delete && tableInfo.BulkConfig.SetOutputIdentity && tableInfo.IdentityColumnName != null)
{
command.CommandText = SqlQueryBuilderSqlite.SelectLastInsertRowId();
long lastRowIdScalar = (long)await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);
int lastRowId = (int)lastRowIdScalar;
var accessor = TypeAccessor.Create(typeof(T), true);
string identityPropertyName = tableInfo.PropertyColumnNamesDict.SingleOrDefault(a => a.Value == tableInfo.IdentityColumnName).Key;
for (int i = entities.Count - 1; i >= 0; i--)
{
accessor[entities[i], identityPropertyName] = lastRowId;
lastRowId--;
}
}
}
finally
{
if (tableInfo.BulkConfig.SqliteTransaction == null)
{
transaction.Commit();
transaction.Dispose();
}
if (tableInfo.BulkConfig.SqliteConnection == null)
{
connection.Close();
}
}
}
else
{
throw new SqlProviderNotSupportedException(providerName);
}
}
private static void Merge <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
string providerName = context.Database.ProviderName;
// -- SQL Server --
if (providerName.EndsWith(DbServer.SqlServer.ToString()))
{
tableInfo.InsertToTempTable = true;
tableInfo.CheckHasIdentity(context);
context.Database.ExecuteSqlRaw(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));
if (tableInfo.CreatedOutputTable)
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true));
if (tableInfo.TimeStampColumnName != null)
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType));
}
}
bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(SqlBulkCopyOptions.KeepIdentity);
try
{
Insert(context, type, entities, tableInfo, progress);
if (keepIdentity && tableInfo.HasIdentity)
{
context.Database.OpenConnection();
context.Database.ExecuteSqlRaw(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, true));
}
context.Database.ExecuteSqlRaw(SqlQueryBuilder.MergeTable(tableInfo, operationType));
if (tableInfo.CreatedOutputTable)
{
tableInfo.LoadOutputData(context, type, entities);
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
if (tableInfo.CreatedOutputTable)
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
}
context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
}
if (keepIdentity && tableInfo.HasIdentity)
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, false));
context.Database.CloseConnection();
}
}
}
// -- SQLite --
else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
{
var connection = OpenAndGetSqliteConnection(context, tableInfo.BulkConfig);
var transaction = tableInfo.BulkConfig.SqliteTransaction ?? connection.BeginTransaction();
try
{
var command = GetSqliteCommand(context, type, entities, tableInfo, connection, transaction);
var typeAccessor = TypeAccessor.Create(type, true);
int rowsCopied = 0;
foreach (var item in entities)
{
LoadSqliteValues(tableInfo, typeAccessor, item, command);
command.ExecuteNonQuery();
SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
}
if (operationType != OperationType.Delete && tableInfo.BulkConfig.SetOutputIdentity && tableInfo.IdentityColumnName != null)
{
command.CommandText = SqlQueryBuilderSqlite.SelectLastInsertRowId();
long lastRowIdScalar = (long)command.ExecuteScalar();
var identityPropertyInteger = false;
var accessor = TypeAccessor.Create(type, true);
string identityPropertyName = tableInfo.PropertyColumnNamesDict.SingleOrDefault(a => a.Value == tableInfo.IdentityColumnName).Key;
if (accessor.GetMembers().FirstOrDefault(x => x.Name == identityPropertyName)?.Type == typeof(int))
{
identityPropertyInteger = true;
}
for (int i = entities.Count - 1; i >= 0; i--)
{
if (identityPropertyInteger)
{
accessor[entities[i], identityPropertyName] = (int)lastRowIdScalar;
}
else
{
accessor[entities[i], identityPropertyName] = lastRowIdScalar;
}
lastRowIdScalar--;
}
}
}
finally
{
if (tableInfo.BulkConfig.SqliteTransaction == null)
{
transaction.Commit();
transaction.Dispose();
}
if (tableInfo.BulkConfig.SqliteConnection == null)
{
connection.Close();
}
}
}
else
{
throw new SqlProviderNotSupportedException(providerName);
}
}