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 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); } }