public override void AlterOrCreateTable(string tableName, TableColumnDefinition[] columns, bool forceDeleteColumn = false, bool useTransaction = true, bool whatIf = false) { var lenFx = new Func <TableColumnDefinition, string>(def => { var ln = def.DataLength ?? -1; return($"{(def.Type.LengthRequired ? $"({(ln != -1 ? ln.ToString() : "max")})" : "")}"); }); var pos = 1; foreach (var col in columns) { if (col.Type == null) { col.Type = SyntaxProvider.GetAppropriateType(col, true); } if (col.Position == 0) { col.Position = pos++; } } bool transactionCreated = false; if (useTransaction && (Facade.CurrentTransaction == null)) { if (!whatIf) { Facade.BeginTransaction(); } else { LogEnvironment.LogEvent("Table-Modification would start a new transaction!", LogSeverity.Report, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } transactionCreated = true; } try { List <string> dropIndices = new List <string>(); List <string> addIndices = new List <string>(); List <string> constraintChanges = new List <string>(); var cmd = ""; if (TableExists(tableName)) { var orig = DescribeTable(tableName, false, out var definitionEditable); var differ = DynamicTableHelper.CompareDefinitions(orig, columns); List <string> addCols = new List <string>(); List <string> dropCols = new List <string>(); List <string> alterCols = new List <string>(); foreach (var item in differ) { if (item.Table2Def == null && !forceDeleteColumn) { if (!whatIf) { throw new InvalidOperationException("Column removal must be explicitly forced!"); } else { LogEnvironment.LogEvent("Table-Modification would fail due to a column-removal with forceDeleteColumn=false!", LogSeverity.Error, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } if (item.Table2Def == null && !definitionEditable) { if (!whatIf) { throw new InvalidOperationException("Column removal is not supported on this table, because the index-configuration is not supported!"); } else { LogEnvironment.LogEvent("Table-Modification would fail due to a column-removal on a table with an unsupported index-configuration!", LogSeverity.Error, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } if (item.Table2Def == null) { dropCols.Add($"[{item.ColumnName}]"); } if (item.Table1Def != null && item.Table2Def != null) { if (!item.Table1Def.DataType.Equals(item.Table2Def.DataType, StringComparison.OrdinalIgnoreCase)) { if (!whatIf) { throw new InvalidOperationException("Changing the DataType is not supported!"); } else { LogEnvironment.LogEvent("Table-Modification would fail, because DataTypes must not be altered!", LogSeverity.Error, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } if (item.Table1Def.DataLength != item.Table2Def.DataLength || item.Table1Def.Nullable != item.Table2Def.Nullable) { alterCols.Add($"[{item.ColumnName}] {item.Table2Def.DataType}{lenFx(item.Table2Def)} {(item.Table2Def.Nullable ? "" : "NOT ")}NULL"); } if ((item.Table1Def.HasIndex != item.Table2Def.HasIndex || item.Table1Def.IsUniqueKey != item.Table2Def.IsUniqueKey) && item.Table1Def.IsForeignKey == item.Table2Def.IsForeignKey && !item.Table2Def.IsForeignKey) { if (item.Table1Def.IsPrimaryKey != item.Table2Def.IsPrimaryKey) { if (!whatIf) { throw new InvalidOperationException("Primary keys must not be altered!"); } else { LogEnvironment.LogEvent("Table-Modification would fail, because Primary-Key Columns may not be altered!", LogSeverity.Error, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } if (!definitionEditable) { if (!whatIf) { throw new InvalidOperationException("Index-Changes not supported on this table. Use Management studio to alter table."); } else { LogEnvironment.LogEvent("Table-Modification would fail, because index-configuration of the table is not supported!", LogSeverity.Error, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } if (!item.Table1Def.IsPrimaryKey) { if (item.Table1Def.IsUniqueKey) { dropIndices.Add($"UQ_{tableName}_{item.ColumnName}"); } else if (item.Table1Def.HasIndex) { dropIndices.Add($"CX_{tableName}_{item.ColumnName}"); } if (item.Table2Def.IsUniqueKey) { addIndices.Add($"Create UNIQUE INDEX [UQ_{tableName}_{item.ColumnName}] on [{tableName}] ([{item.ColumnName}] ASC)"); } else if (item.Table2Def.HasIndex) { addIndices.Add($"Create NONCLUSTERED INDEX [CX_{tableName}_{item.ColumnName}] on [{tableName}] ({item.ColumnName})"); } } } if (item.Table2Def.IsForeignKey != item.Table1Def.IsForeignKey) { if (!definitionEditable) { if (!whatIf) { throw new InvalidOperationException("Constraint-Changes not supported on this table. Use Management studio to alter table."); } else { LogEnvironment.LogEvent("Table-Modification would fail, because index-configuration of the table is not supported!", LogSeverity.Error, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } if (item.Table1Def.IsForeignKey) { constraintChanges.Add($"Alter Table [{tableName}] drop constraint [FK_{tableName}_{item.Table1Def.Position}_{item.Table1Def.RefTable}_{item.Table1Def.RefColumn}]"); } else { constraintChanges.Add($@"Alter Table [{tableName}] add constraint [FK_{tableName}_{item.Table2Def.Position}_{item.Table2Def.RefTable}_{item.Table2Def.RefColumn}] Foreign Key ({item.Table2Def.ColumnName}) references [{item.Table2Def.RefTable}] ([{item.Table2Def.RefColumn}])"); } } } else if (item.Table2Def != null) { addCols.Add($"[{item.ColumnName}] {item.Table2Def.DataType}{lenFx(item.Table2Def)} {(item.Table2Def.Nullable ? "" : "NOT ")}NULL"); if (!item.Table2Def.IsPrimaryKey && !item.Table2Def.IsForeignKey && (item.Table2Def.HasIndex || item.Table2Def.IsUniqueKey)) { if (item.Table2Def.IsUniqueKey) { addIndices.Add($"Create UNIQUE INDEX [UQ_{tableName}_{item.ColumnName}] on [{tableName}] ([{item.ColumnName}] ASC)"); } else if (item.Table2Def.HasIndex) { addIndices.Add($"Create NONCLUSTERED INDEX [CX_{tableName}_{item.ColumnName}] on [{tableName}] ({item.ColumnName})"); } } if (item.Table2Def.IsForeignKey) { constraintChanges.Add($@"Alter Table [{tableName}] add constraint [FK_{tableName}_{item.Table2Def.Position}_{item.Table2Def.RefTable}_{item.Table2Def.RefColumn}] Foreign Key ({item.Table2Def.ColumnName}) references [{item.Table2Def.RefTable}] ([{item.Table2Def.RefColumn}])"); } } } if (dropCols.Count != 0) { cmd = $"alter table [{tableName}] drop column {string.Join(", ", dropCols)}"; if (!whatIf) { Facade.ExecuteSqlRaw(cmd); } else { LogEnvironment.LogEvent($"Table-Modification would execute the following SQL-Command: {{{cmd}}}", LogSeverity.Report, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } if (alterCols.Count != 0) { foreach (var ac in alterCols) { cmd = $"alter table [{tableName}] alter column {ac}"; if (!whatIf) { Facade.ExecuteSqlRaw(cmd); } else { LogEnvironment.LogEvent($"Table-Modification would execute the following SQL-Command: {{{cmd}}}", LogSeverity.Report, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } } if (addCols.Count != 0) { cmd = $"alter table [{tableName}] add {string.Join(", ", addCols)}"; if (!whatIf) { Facade.ExecuteSqlRaw(cmd); } else { LogEnvironment.LogEvent($"Table-Modification would execute the following SQL-Command: {{{cmd}}}", LogSeverity.Report, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } } else { cmd = $"CREATE TABLE [{tableName}] ({string.Join(", ", from t in columns select $"[{t.ColumnName}] {t.DataType}{lenFx(t)} {(t.Nullable ? "" : "NOT")} NULL {(t.IsPrimaryKey ? "IDENTITY(1,1) PRIMARY KEY" : "")}")})"; if (!whatIf) { Facade.ExecuteSqlRaw(cmd); } else { LogEnvironment.LogEvent($"Table-Modification would execute the following SQL-Command: {{{cmd}}}", LogSeverity.Report, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } foreach (var item in columns) { if (!item.IsPrimaryKey && (item.HasIndex || item.IsUniqueKey)) { if (item.IsUniqueKey) { addIndices.Add($"Create UNIQUE INDEX [UQ_{tableName}_{item.ColumnName}] on [{tableName}] ([{item.ColumnName}] ASC)"); } else if (item.HasIndex) { addIndices.Add($"Create NONCLUSTERED INDEX [CX_{tableName}_{item.ColumnName}] on [{tableName}] ({item.ColumnName})"); } } if (item.IsForeignKey) { constraintChanges.Add($@"Alter Table [{tableName}] add constraint [FK_{tableName}_{item.Position}_{item.RefTable}_{item.RefColumn}] Foreign Key ({item.ColumnName}) references [{item.RefTable}] ([{item.RefColumn}])"); } } } if (dropIndices.Count != 0) { foreach (var di in dropIndices) { cmd = $"Drop INDEX IF EXISTS [{di}] on [{tableName}]"; if (!whatIf) { Facade.ExecuteSqlRaw(cmd); } else { LogEnvironment.LogEvent($"Table-Modification would execute the following SQL-Command: {{{cmd}}}", LogSeverity.Report, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } } if (addIndices.Count != 0) { foreach (var ai in addIndices) { cmd = ai; if (!whatIf) { Facade.ExecuteSqlRaw(cmd); } else { LogEnvironment.LogEvent($"Table-Modification would execute the following SQL-Command: {{{cmd}}}", LogSeverity.Report, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } } if (constraintChanges.Count != 0) { foreach (var cc in constraintChanges) { cmd = cc; if (!whatIf) { Facade.ExecuteSqlRaw(cmd); } else { LogEnvironment.LogEvent($"Table-Modification would execute the following SQL-Command: {{{cmd}}}", LogSeverity.Report, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } } if (transactionCreated) { if (!whatIf) { Facade.CommitTransaction(); } else { LogEnvironment.LogEvent($"Table-Modification would commit the started transaction.", LogSeverity.Report, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } } catch { if (transactionCreated) { if (!whatIf) { Facade.RollbackTransaction(); } else { LogEnvironment.LogEvent($"Table-Modification would rollback the started transaction.", LogSeverity.Report, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter"); } } throw; } }
public override List <TableColumnDefinition> DescribeTable(string tableName, bool ignoreUnknownTypes, out bool definitionEditable) { var tmp = SqlQuery <TableColumnDefinition>(@"SELECT c.COLUMN_NAME ColumnName, c.DATA_TYPE DataType, c.CHARACTER_MAXIMUM_LENGTH DataLength, convert(bit,case c.IS_NULLABLE when 'Yes' then 1 else 0 end) Nullable, c.ordinal_position Position, convert(bit, case when ic.object_id is not null then 1 else 0 end) IsIdentity, convert(bit, case when rc.column_name is not null then 1 else 0 end) IsForeignKey, rc.table_name RefTable, rc.column_name RefColumn, convert(bit, case when k.column_name is not null then 1 else 0 end) HasIndex, convert(bit, case when pk.type='PK' then 1 else 0 end) IsPrimaryKey, convert(bit, case when pk.type='UQ' then 1 else 0 end) IsUniqueKey, convert(bit, case when count(cc.referenced_column_id) > 0 then 1 else 0 end) HasReferences FROM INFORMATION_SCHEMA.COLUMNS c left outer join information_schema.KEY_COLUMN_USAGE k on k.COLUMN_NAME = c.column_name and k.table_name = c.TABLE_NAME left outer join sys.identity_columns ic on object_Name(ic.object_id) = c.table_name and ic.name = c.COLUMN_NAME left outer join sys.foreign_key_columns fc on object_name(fc.parent_object_id) = c.table_name and fc.parent_column_id = c.ORDINAL_POSITION left outer join sys.key_constraints pk on object_name(pk.object_id) = k.CONSTRAINT_NAME and object_name(pk.parent_object_id) = c.table_name left outer join information_schema.COLUMNS rc on rc.table_name = object_name(fc.referenced_object_id) and rc.ORDINAL_POSITION = fc.referenced_column_id left outer join sys.foreign_key_columns cc on object_name(cc.referenced_object_id) = c.table_name and cc.referenced_column_id = c.ORDINAL_POSITION WHERE c.TABLE_NAME = @p0 group by c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, convert(bit,case c.IS_NULLABLE when 'Yes' then 1 else 0 end) , c.ordinal_position, convert(bit, case when ic.object_id is not null then 1 else 0 end), convert(bit, case when rc.column_name is not null then 1 else 0 end), rc.table_name, rc.column_name, convert(bit,case when k.column_name is not null then 1 else 0 end), convert(bit, case when pk.type='PK' then 1 else 0 end), pk.type ORDER BY c.ORDINAL_POSITION", tableName); var tmp2 = (from t in tmp group t by new { t.ColumnName, t.DataLength, t.DataType, t.Nullable, t.Position, t.RefTable, t.RefColumn, t.HasReferences }) .ToList(); tmp.Clear(); definitionEditable = true; foreach (var t in tmp2) { tmp.Add(new TableColumnDefinition { ColumnName = t.Key.ColumnName, DataLength = t.Key.DataLength, DataType = t.Key.DataType, Nullable = t.Key.Nullable, Position = t.Key.Position, RefTable = t.Key.RefTable, RefColumn = t.Key.RefColumn, HasReferences = t.Key.HasReferences, HasIndex = t.All(n => n.HasIndex), IsForeignKey = t.All(n => n.IsForeignKey), IsIdentity = t.All(n => n.IsIdentity), IsPrimaryKey = t.All(n => n.IsPrimaryKey), IsUniqueKey = t.All(n => n.IsUniqueKey), Type = SyntaxProvider.GetAppropriateType(t.First(), !ignoreUnknownTypes) }); definitionEditable &= t.Count() == 1; } if (ignoreUnknownTypes) { tmp = tmp.Where(n => n.Type != null).ToList(); } return(tmp); }