private void DELETE(DependencyInfo pkrow, DependencyInfo[] fkrows, string locator, StringBuilder builder)
{
if (fkrows.Length == 0)
{
return;
}
foreach (var row in fkrows)
{
string sql = $"[{row.FkColumn}] IN (SELECT [{row.PkColumn}] FROM {pkrow.FkTable.FormalName} WHERE {locator})";
DependencyInfo[] getFkRows = GetFkRows(row.FkTable);
var columnInfo = row.FkTable.GetTableSchema().Columns[row.FkColumn];
if (columnInfo.Nullable)
{
builder.AppendLine(updateTemplate(row, locator));
}
else
{
DELETE(row, getFkRows, sql, builder);
builder.AppendLine(deleteTemplate(row, locator));
}
}
}
private void DROP_TABLE(DependencyInfo pkrow, DependencyInfo[] fkrows, bool ifExists, StringBuilder builder)
{
if (fkrows.Length == 0)
{
return;
}
List <string> completed = new List <string>();
foreach (var row in fkrows)
{
DependencyInfo[] getFkRows = GetFkRows(row.FkTable);
string stamp = $"{row.FkTable}=>{row.PkTable}";
if (completed.IndexOf(stamp) < 0) //don't allow to same fk=>pk many times
{
DROP_TABLE(row, getFkRows, ifExists, builder);
builder.AppendLine(dropTemplate(row.FkTable, ifExists));
completed.Add(stamp);
}
}
return;
}
private string selectTemplate(DependencyInfo pkrow, DependencyInfo fkrow)
{
return($"SELECT [{fkrow.PkColumn}] FROM {fkrow.PkTable.FormalName} WHERE [{fkrow.FkColumn}] = @{pkrow.PkColumn}");
}
private string updateTemplate(DependencyInfo row, string locator)
{
return($"UPDATE {row.FkTable.FormalName} SET {row.FkColumn} = NULL WHERE [{row.FkColumn}] IN (SELECT [{row.PkColumn}] FROM {row.PkTable.FormalName} WHERE {locator})");
}
private string deleteTemplate(DependencyInfo row, string locator)
{
return($"DELETE FROM {row.FkTable.FormalName} WHERE [{row.FkColumn}] IN (SELECT [{row.PkColumn}] FROM {row.PkTable.FormalName} WHERE {locator})");
}
