public void ScriptAsDrop(object sender, ExecutedRoutedEventArgs e)
{
var menuItem = sender as MenuItem;
if (menuItem == null) return;
var menuInfo = menuItem.CommandParameter as MenuCommandParameters;
if (menuInfo == null) return;
try
{
using (IRepository repository = Helpers.DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var generator = Helpers.DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
if (menuInfo.MenuItemType == MenuType.FK)
{
generator.GenerateForeignKeyDrop(menuInfo.Name, menuInfo.Description);
}
if (menuInfo.MenuItemType == MenuType.PK)
{
var pk = new PrimaryKey { KeyName = menuInfo.Description };
generator.GeneratePrimaryKeyDrop(pk, menuInfo.Name);
}
OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
Helpers.DataConnectionHelper.LogUsage("KeyScriptAsDrop");
}
}
catch (Exception ex)
{
Helpers.DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
public void GeneratePrimaryKeyDrop(PrimaryKey primaryKey, string tableName)
{
//ALTER TABLE xx DROP CONSTRAINT yy
_sbScript.Append(string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}];{2}", tableName, primaryKey.KeyName, Environment.NewLine));
_sbScript.Append(_sep);
}
internal static List<PrimaryKey> EnsureUniqueNames(List<PrimaryKey> primaryKeys)
{
// Fix for duplicate constraint names (which causes script failure in SQL Server)
// https://connect.microsoft.com/SQLServer/feedback/details/586600/duplicate-constraint-foreign-key-name
var fixedPrimaryKeys = new List<PrimaryKey>();
var uniqueTables = (from c in primaryKeys
select c.TableName).Distinct();
int i = 1;
foreach (string tableName in uniqueTables)
{
{
var uniqueKeys = (from c in primaryKeys
where c.TableName == tableName
select c.KeyName).Distinct();
foreach (string value in uniqueKeys)
{
var pks = primaryKeys.Where(c => c.KeyName.Equals(value, System.StringComparison.Ordinal) && c.TableName == tableName).ToList();
if (pks.Count > 0)
{
var found = primaryKeys.Where(fk => fk.KeyName == pks[0].KeyName && fk.TableName != pks[0].TableName).Any();
string newKeyName = pks[0].KeyName;
if (found)
{
newKeyName = pks[0].KeyName + i.ToString();
i++;
}
foreach (var item in pks)
{
PrimaryKey pk = new PrimaryKey();
pk.ColumnName = item.ColumnName;
pk.TableName = item.TableName;
pk.KeyName = newKeyName;
fixedPrimaryKeys.Add(pk);
}
}
}
}
}
return fixedPrimaryKeys;
}
public PrimaryKeyDisplay(PrimaryKey value)
{
Value = value;
DisplayValue = string.Format("{0} ({1})", value.TableName, value.ColumnName);
}