public static DBView CreateSecureView(DBTable t)
{
var sql = string.Format("exec engine.secure_CreateSecure '{0}', '{1}'", t.Schema.Name, t.Name);
t.Connection.GetDataTable(sql);
DBTableManager.Refresh(t); //refresh extensions -> secure view only!
DBViewManager.Refresh(t.SecureView);
return t.SecureView;
}
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);
}
}
private void NewLookupTableFormClosing(object sender, FormClosingEventArgs e)
{
var form = (sender as InputForm);
if (form.DialogResult == DialogResult.Cancel)
{
return;
}
var lookup_table = form.Object as DBTable;
var sql = lookup_table.GetSQL();
lookup_table.Connection.Project.Commands.Add(new DBCommand {
Sql = sql, Owner = lookup_table, Description = "Create New Lookup Table"
});
//set custom properties
lookup_table.RowIconClass = lookup_table.Columns.Where(c => c.UserDataType == "iconclass").FirstOrDefault();
lookup_table.RowColor = lookup_table.Columns.Where(c => c.UserDataType == "color").FirstOrDefault();
lookup_table.DefaultSort = lookup_table.Columns.Where(c => c.UserDataType == "displayorder").FirstOrDefault();
//pk
var pk_col = lookup_table.Columns.Where(c => c.Name == "id").FirstOrDefault();
pk_col.IsPrimaryKey = true;
//execute the commands..
foreach (var c in lookup_table.Connection.Project.Commands.Where(c => c.Owner == lookup_table).ToList())
{
DBProjectManager.Execute(c);
}
DBTableManager.Refresh(lookup_table);
//show the designer..
CreateNewForm(lookup_table);
}
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);
}