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