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); } } }