private void toolStripButton_fix_lookup_Click(object sender, EventArgs e)
{
var fk = (propertyGrid1.SelectedObject as DBFKConstraint);
var schema = fk.ReferenceSchemaName;
var table = fk.ReferenceTableName;
var db_schema = fk.Owner.Schema.Database.Schemas.FirstOrDefault(s => s.Name == schema);
DBSchemaManager.Refresh(db_schema);
var referenced_table = db_schema.Tables.FirstOrDefault(t => t.Name == table);
//todo fix
}
public static void Refresh(DBObject o)
{
//redirect to proper manager
if (o is DB)
{
DBManager.Refresh(o as DB);
}
else if (o is DBSchema)
{
DBSchemaManager.Refresh(o as DBSchema);
}
else if (o is DBTable)
{
DBTableManager.Refresh(o as DBTable);
}
else if (o is DBView)
{
DBViewManager.Refresh(o as DBView);
}
}
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);
}