public override void CopyTableData(string src, string dst, bool ignoreMissingColumn = false, bool whatIf = false)
{
var srcColumns = DescribeTable(src, false, out _);
var dstColumns = DescribeTable(dst, false, out _);
var diff = DynamicTableHelper.CompareDefinitions(srcColumns, dstColumns);
List <string> columns = new List <string>();
foreach (var item in diff)
{
if (item.Table2Def == null && !ignoreMissingColumn)
{
if (!whatIf)
{
throw new InvalidOperationException("CopyJob with possible loss of data is not supported!");
}
else
{
LogEnvironment.LogEvent("CopyJob would fail due to data-loss!", LogSeverity.Error, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter");
}
}
if (item.Table1Def != null && item.Table2Def != null)
{
columns.Add($"[{item.ColumnName}]");
if (!item.Table1Def.DataType.Equals(item.Table2Def.DataType, StringComparison.OrdinalIgnoreCase))
{
if (!whatIf)
{
throw new InvalidOperationException("Changing the DataType is not supported!");
}
else
{
LogEnvironment.LogEvent("CopyJob would fail due to non-equal Data-Types between source and destination-table!", LogSeverity.Error, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter");
}
}
}
}
var joinedColumns = string.Join(", ", columns);
var rawQuery = $"insert into [{dst}] ({joinedColumns}) select {joinedColumns} from [{src}]";
if (!whatIf)
{
using (Facade.UseConnection(out DbCommand cmd))
{
cmd.CommandText = rawQuery;
cmd.ExecuteNonQuery();
}
}
else
{
LogEnvironment.LogEvent($"CopyJob would execute the following SQL-Command: {{{rawQuery}}}", LogSeverity.Error, "ITVComponents.EFRepo.SqlServer.SqlDynamicDataAdapter");
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
_list.Add("row " + _list.Count);
RowEntity row = new RowEntity();
row.List = _list;
row.Operation = RowEnum.Add;
TableLayoutPanel temp = (TableLayoutPanel)pnlTableHolder.Controls[0];
DynamicTableHelper control = new DynamicTableHelper(temp);
temp = control.AddOrRemoveRows(row);
pnlTableHolder.Controls.Remove(tblMain);
pnlTableHolder.Controls.Add(temp);
//we will get the no. of rows directly from the table
//to ascertain that rows have been added correctly
lblRowCount.Text = "No. of rows " + temp.RowCount;
}
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;
}
}
