/// <summary>
/// Creates new foreign key
/// </summary>
/// <param name="tableName">Table name</param>
/// <param name="dbo">Foreign key description <see cref="ForeignKeyDbo"/></param>
/// <returns>Description of created foreign key</returns>
public static ForeignKeyDbo CreateForeignKey(string tableName, ForeignKeyDbo dbo)
{
var srv = GetConnectedServer(SqlServerName, UserName, Password);
var db = srv.Databases[DatabaseName];
Table aTable = db.Tables[tableName];
if (aTable == null)
throw new Exception(String.Format("There is no table {0} in the {1} database.", tableName, DatabaseName));
if (aTable.ForeignKeys[dbo.Name] != null)
throw new Exception(String.Format("There is {0} foreign key in the table {1}.", dbo.Name, tableName));
return CreateForeignKey(aTable, dbo);
}
private static ForeignKeyDbo CreateForeignKeyDbo(ForeignKey aKey)
{
var rzlt = new ForeignKeyDbo()
{
Name = aKey.Name,
DeleteAction = aKey.DeleteAction,
UpdateAction = aKey.UpdateAction,
IsChecked = aKey.IsChecked,
ReferencedTable = aKey.ReferencedTable
};
foreach (ForeignKeyColumn clmn in aKey.Columns)
{
rzlt.Columns.Add(new ForeignKeyColumnDbo()
{
Name = clmn.Name,
ReferencedColumn = clmn.ReferencedColumn
});
}
return rzlt;
}
/// <summary>
/// <see cref="http://msdn.microsoft.com/en-us/library/ms219599%28v=sql.90%29.aspx"/>
/// <seealso cref="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.foreignkey.aspx"/>
/// <seealso cref="http://msdn.microsoft.com/en-us/library/ms162566.aspx"/>
/// </summary>
/// <param name="fKeyName"></param>
protected void ShowFields(ForeignKeyDbo selectedItem)
{
foreach (ForeignKeyColumnDbo dbo in selectedItem.Columns)
{
lstSourceColumnList6.Items.Add(new ListItem(dbo.Name, dbo.Name));
lstTargetColumnList6.Items.Add(new ListItem(dbo.ReferencedColumn, dbo.ReferencedColumn));
}
this.TargetFieldLabel6.InnerText = String.Format("Fields ({0})", selectedItem.ReferencedTable);
}
private static ForeignKeyDbo CreateForeignKey(Table aTable, ForeignKeyDbo dbo)
{
ForeignKey fKey = new ForeignKey(aTable, dbo.Name)
{
DeleteAction = dbo.DeleteAction,
UpdateAction = dbo.UpdateAction,
ReferencedTable = dbo.ReferencedTable,
IsChecked = dbo.IsChecked
};
foreach (ForeignKeyColumnDbo clmn in dbo.Columns)
{
ForeignKeyColumn fkColumn = new ForeignKeyColumn(fKey, clmn.Name, clmn.ReferencedColumn);
fKey.Columns.Add(fkColumn);
}
fKey.Create();
return CreateForeignKeyDbo(fKey);
}