internal void CreateFK(DBColumn ref_column) { if (this.Parent is DBTable) { var fk = new DBFKConstraint { Action = DB.DBAction.Add, Column = this, ReferenceColumn = ref_column, Connection = this.Parent.Connection, Schema = this.Owner.Schema, Parent = this.Parent, Name = string.Format("FK_{0}_{1}_{2}_ref_{3}_{4}_{5}", this.Owner.Name, this.Parent.Name, this.Name, ref_column.Schema.Name, ref_column.Parent.Name, ref_column.Name), State = DBObject.DBObjectState.New }; var command = new DBCommand { Sql = fk.GetSQL(), Description = "Create FK", Owner = this }; this.Connection.Project.Commands.Add(command); } }
public void CreateLookupTable() { var owner = (Column.Parent as DBTable); var sql = string.Format("if object_id('[{0}].[{1}]') is null select * into [{0}].[{1}] from ({2}) t ", Column.Owner.Schema.Name, LookupTableName, GetPreview()); var cmd_create_table = new DBCommand { Owner = owner, Sql = sql, Description = "Create Lookup Table" }; owner.Connection.Project.Commands.Add(cmd_create_table); DBProjectManager.Execute(cmd_create_table); //refresh so now the schema has the table.. DBSchemaManager.Refresh(Column.Schema); var lookup_table = Column.Schema.Tables.FirstOrDefault(t => t.Name == LookupTableName); DBTableManager.Refresh(lookup_table); lookup_table.Action = DB.DBAction.Alter; var pk_col = lookup_table.Columns.FirstOrDefault(x => x.Name == "id"); pk_col.Action = DB.DBAction.Alter; pk_col.Nullable = false; var cmd_not_null = pk_col.Connection.Project.Commands.FirstOrDefault(c => c.Owner == pk_col); if (cmd_not_null != null) { DBProjectManager.Execute(cmd_not_null); } //create the PK lookup_table.PrimaryKey = new DBPKConstraint { Action = DB.DBAction.Add, Parent = lookup_table, Schema = lookup_table.Schema, Connection = lookup_table.Connection, Name = "PK_" + LookupTableName }; lookup_table.PrimaryKey.Columns.Add(pk_col); var cmd = new DBCommand { Owner = lookup_table, Sql = lookup_table.PrimaryKey.GetSQL(), Description = "Create PK" }; lookup_table.Connection.Project.Commands.Add(cmd); DBProjectManager.Execute(cmd); //table.Refresh(); //reference by FK var fk = new DBFKConstraint { Name = "FK_" + owner.Schema.Name + "_" + owner.Name + "_" + Column.Name + "_ref_" + lookup_table.Schema.Name + "_" + lookup_table.Name + "_id", Action = DB.DBAction.Add, Parent = Column.Parent, Schema = Column.Owner.Schema, Column = Column, Connection = Column.Connection, OnUpdate = DBFKConstraint.CascadeModes.Cascade, ReferenceColumn = pk_col }; owner.ForeignKeys.Add(fk); var cmd_fk = new DBCommand { Owner = owner, Sql = fk.GetSQL(), Description = "Create FK" }; lookup_table.Connection.Project.Commands.Add(cmd_fk); DBProjectManager.Execute(cmd_fk); }