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 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, 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)); } } bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(SqlBulkCopyOptions.KeepIdentity); try { Insert(context, entities, tableInfo, progress); if (keepIdentity && tableInfo.HasIdentity) { context.Database.OpenConnection(); context.Database.ExecuteSqlCommand(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, true)); } 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)); } 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 void Merge <T>(DbContext context, 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, 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, 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, entities, tableInfo, connection, transaction); var typeAccessor = TypeAccessor.Create(typeof(T), 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(); 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); } }