public static ForeignKeyDbo GetThisForeignKey(string tableName, string foreignKeyName)
{
var srv = GetConnectedServer(SqlServerName, UserName, Password);
if (srv == null)
{
throw new Exception(String.Format("Server '{0}' is not accessible.", SqlServerName));
}
var db = srv.Databases[DatabaseName];
if (db == null)
{
throw new Exception(String.Format("Database '{0}' is not accessible.", DatabaseName));
}
Table table = db.Tables[tableName];
if (table == null)
{
throw new Exception(String.Format("Database '{0}' has no table '{1}'.", DatabaseName, tableName));
}
ForeignKey aKey = table.ForeignKeys[foreignKeyName];
if (aKey == null)
{
throw new Exception(String.Format("Table '{0}' has no key '{1}'.", tableName, foreignKeyName));
}
ForeignKeyDbo foreignKey = CreateForeignKeyDbo(aKey);
foreignKey.AvailableTargetColumns = GetTargetColumns(foreignKey.ReferencedTable);
return(foreignKey);
}
public GetForeignKeysResponse GetForeignKeyDescription(string tableName, string foreignKeyName)
{
try
{
var options = new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.Serializable,
Timeout = new TimeSpan(0, TransactionTimeout, 0)
};
using (var trScope = new TransactionScope(TransactionScopeOption.Required, options))
{
ForeignKeyDbo foreignKey = GetThisForeignKey(tableName, foreignKeyName);
var rzlt = new GetForeignKeysResponse()
{
IsSuccess = true,
Dbo = foreignKey
};
trScope.Complete();
return(rzlt);
}
}
catch (Exception ex)
{
return(new GetForeignKeysResponse()
{
IsSuccess = false, ErrorMessage = ParseErrorMessage(ex)
});
}
}
/// <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);
}
/// <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 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));
}
protected void lstIndexList6_OnDataBound(Object sender, EventArgs e)
{
int selectedIndex = 0;
Func <string, ForeignKeyDbo> currentKey = delegate(string fKeyName)
{
selectedIndex = this.foreignKeys.Count - 1;
while (selectedIndex > 0 && this.foreignKeys[selectedIndex].Name != fKeyName)
{
selectedIndex--;
}
return(this.foreignKeys[selectedIndex]);
};
if (this.foreignKeys.Count > 0)
{
ForeignKeyDbo selectedItem = null;
if (!String.IsNullOrEmpty(this.hdnSelectedForeignKey6.Value))
{
selectedItem = currentKey(this.hdnSelectedForeignKey6.Value);
this.lstForeignKeyList6.SelectedIndex = selectedIndex;
this.hdnSelectedForeignKey6.Value = this.currentForeignKey = selectedItem.Name;
}
else
{
selectedItem = this.foreignKeys[0];
this.lstForeignKeyList6.SelectedIndex = 0;
this.hdnSelectedForeignKey6.Value = this.currentForeignKey = selectedItem.Name;
}
ShowFields(selectedItem);
}
else
{
this.hdnSelectedForeignKey6.Value = this.currentForeignKey = String.Empty;
}
if (String.IsNullOrEmpty(this.currentForeignKey))
{
this.btnRenameFkey6.Attributes["disabled"] = "disabled";
this.btnModifyFkey6.Attributes["disabled"] = "disabled";
this.btnDeleteFkey6.Attributes["disabled"] = "disabled";
}
}
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);
}
public UpdateForeignKeyResponse UpdateForeignKey(UpdateForeignKeyRequest request, bool singleUserMode)
{
try
{
var options = new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.Serializable,
Timeout = new TimeSpan(0, TransactionTimeout, 0)
};
using (var trScope = new TransactionScope(TransactionScopeOption.Required, options))
{
if (singleUserMode)
{
SetSingleMode(DatabaseName);
}
ForeignKeyDbo dbo = null;
string logMsg = null;
switch (request.Operation)
{
case UpdateColumnOperation.Rename:
dbo = RenameTheForeignKey(request.Table, request.OldForeignKeyName, request.ForeignKeyName);
logMsg = String.Format("Table '{0}': foreign key '{1}' was renamed to '{2}'.",
request.Table, request.OldForeignKeyName, request.ForeignKeyName);
break;
case UpdateColumnOperation.Insert:
dbo = CreateForeignKey(request.Table, request.Dbo);
logMsg = String.Format("Table '{0}': foreign key '{1}' was created.", request.Table, request.Dbo.Name);
break;
case UpdateColumnOperation.Delete:
DeleteTheForeignKey(request.Table, request.OldForeignKeyName);
dbo = new ForeignKeyDbo()
{
Name = request.OldForeignKeyName
};
logMsg = String.Format("Table '{0}': foreign key '{1}' was deleted.", request.Table, request.OldForeignKeyName);
break;
case UpdateColumnOperation.Modify:
DeleteTheForeignKey(request.Table, request.OldForeignKeyName);
dbo = CreateForeignKey(request.Table, request.Dbo);
logMsg = String.Format("Table '{0}': foreign key '{1}' was modified.", request.Table, request.Dbo.Name);
break;
}
int recordCount = CountRecords(request.Table);
Guid historyRecordId = LogTableOperation(request.Table, logMsg, request.CFC_DB_Major_Version,
request.CFC_DB_Minor_Version);
trScope.Complete();
return(new UpdateForeignKeyResponse()
{
IsSuccess = true,
Dbo = dbo,
RecordCount = recordCount
});
}
}
catch (Exception ex)
{
return(new UpdateForeignKeyResponse()
{
IsSuccess = false, ErrorMessage = ParseErrorMessage(ex)
});
}
finally
{
if (singleUserMode)
{
SetMultiUserMode(DatabaseName);
}
}
}
