public static void Refresh(DB db)
{
//reset
DBObjectManager.BeforeRefresh(db);
//self data
//get schemas
RefreshSchemas(db);
//get database triggers
RefreshTriggers(db);
//get database roles
RefreshRoles(db);
//get database users
RefreshUsers(db);
//properties
DBObjectManager.RefreshProperties(db);
//intact
DBObjectManager.AfterRefresh(db);
}
void DBExtendedProperty_PropertyChanged(object sender, PropertyChangedEventArgs e)
{
if (this.State == DBObjectState.Modified && this.Owner != null && this.Owner.Connection.Project != null)
{
DBObjectManager.CreateExtendedProperty(this.Owner, this.Name, "", this.Value);
}
}
private void refreshToolStripMenuItem_refresh_Click(object sender, EventArgs e)
{
var tag = ((sender as ToolStripItem).Owner as ContextMenuStrip).Tag;
if (tag != null)
{
var tab_page = tag as TabPage;
var control = (tab_page.Tag as DBObjectControl);
DBObjectManager.Refresh(control.DBObject);
control.RefreshDisplay();
}
}
public static void Refresh(DBView v)
{
//reset
DBObjectManager.BeforeRefresh(v);
//self
//columns
DBObjectManager.RefreshColumns(v);
//properties
DBObjectManager.RefreshProperties(v);
//intact
DBObjectManager.AfterRefresh(v);
}
public static void Refresh(DBTable t)
{
//reset state and collections
DBObjectManager.BeforeRefresh(t);
//columns
DBObjectManager.RefreshColumns(t);
//primary key
RefreshPrimaryKey(t);
//triggers
RefreshTriggers(t);
//policies (select, update, delete)
RefreshPolicies(t);
//extensions (views/computed/secure) for extra columns
RefreshExtensions(t);
//table level properties
RefreshTableProperties(t);
//business
RefreshBusinessProcedures(t);
//foreign key relations
RefreshForeignKeys(t);
//check constraints
RefreshCheckConstraints(t);
//default constraints
RefreshDefaultConstraints(t);
//properties
DBObjectManager.RefreshProperties(t);
//state intact
DBObjectManager.AfterRefresh(t);
}
public static void RefreshExtensions(DBTable t)
{
var sql = string.Format(@"
select
SchemaName = s.name,
ViewName = v.name,
Definition = m.definition
from
sys.schemas s
join sys.views v on v.schema_id = s.schema_id
join sys.sql_modules m on m.object_id = v.object_id
where
s.name in ('computed', 'views', 'secure')
and v.name = '{0}_{1}'", t.Schema.Name, t.Name);
var cmd = new DBCommand{ Sql = sql, Owner = t, Description = "Get Table Extensions" };
t.Extensions.Clear();
t.Extensions.AddRange(DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x => new DBView()
{
OnAlreadyExists = DBObject.DBOnExists.PerformActionWithNoExistenceCheck,
Name = x["ViewName"].ToString(),
Schema = t.Schema.Database.Schemas.FirstOrDefault(s => s.Name == x["SchemaName"].ToString()),
DefinitionSQL = x["Definition"].ToString(),
Action = DB.DBAction.Alter,
Connection = t.Connection,
State = EngineManager.DBObject.DBObjectState.Intact
}).ToList());
//objects for views
t.ExtendedView = t.Extensions.FirstOrDefault(x => x.Schema.Name == "views");
t.ComputedView = t.Extensions.FirstOrDefault(x => x.Schema.Name == "computed");
t.SecureView = t.Extensions.FirstOrDefault(x => x.Schema.Name == "secure");
foreach (var e in t.Extensions)
DBObjectManager.RefreshColumns(e);
}
public static void Refresh(DBSchema s)
{
DBObjectManager.BeforeRefresh(s);
//get tables
RefreshTables(s);
//get views
RefreshViews(s);
//get procedures
RefreshProcedures(s);
//table valued functions
//scalar functions
//datatypes
//properties
DBObjectManager.RefreshProperties(s);
DBObjectManager.AfterRefresh(s);
}
private void dataGridView_properties_UserAddedRow(object sender, DataGridViewRowEventArgs e)
{
var prop = (DBExtendedProperty)e.Row.DataBoundItem;
DBObjectManager.CreateExtendedProperty(prop.Owner, prop.Name, "", prop.Value);
}
private List <DBColumn> m_list = new List <DBColumn>(); //stores the list
public override bool GetStandardValuesSupported(ITypeDescriptorContext context)
{
if (context.Instance is DBTable)
{
var table = context.Instance as DBTable;
m_list = table.Columns.ToList();
if (table.SecureView != null)
{
if (table.SecureView.Columns.Count == 0)
{
DBObjectManager.RefreshColumns(table.SecureView);
}
m_list = table.SecureView.Columns.ToList();
}
}
else if (context.Instance is DBView)
{
var view = context.Instance as DBView;
m_list = view.Columns.ToList();
}
else if (context.Instance is DBColumn && (context.Instance as DBObject).Parent is IContainsColumns)
{
var table = (context.Instance as DBObject).Parent as IContainsColumns;
if (table != null)
{
m_list = table.Columns.ToList();
}
}
else if (context.Instance is RowAction && (context.Instance as RowAction).Table is IContainsColumns)
{
var table = (context.Instance as RowAction).Table as IContainsColumns;
if (table != null)
{
m_list = table.Columns.ToList();
}
}
else if (context.Instance is SecurityPolicyQuestion)
{
var question = context.Instance as SecurityPolicyQuestion;
var additional_columns = question.Table.AdditionalRelatedColumns.Select(rc => rc.Column).ToList();
m_list = additional_columns;
}
else if (context.Instance is AdditionalColumn)
{
var db_rel_col = context.Instance as AdditionalColumn;
if (db_rel_col.Constraint == null)
{
m_list = db_rel_col.CurrentTable.Columns.ToList();
}
else if (db_rel_col.Constraint.Parent == db_rel_col.CurrentTable)
{
m_list = (db_rel_col.Constraint.ReferenceColumn.Parent as DBTable).CombinedColumns.ToList();
}
else
{
m_list = (db_rel_col.Constraint.Parent as DBTable).CombinedColumns.ToList();
}
}
else if (context.Instance is Workflow)
{
var wf = context.Instance as Workflow;
if (wf.Table != null && context.PropertyDescriptor.Name == "StateColumn")
{
m_list = wf.Table.ForeignKeys.Select(x => x.Column).Distinct().ToList();
}
else if (wf.Table != null && context.PropertyDescriptor.Name == "StateNameColumn" && wf.StateColumn != null)
{
m_list = (wf.Table.ForeignKeys.Where(x => x.Column == wf.StateColumn).FirstOrDefault().ReferenceColumn.Parent as DBTable).Columns.Distinct().ToList();
}
}
else if (context.Instance is SQLUpdateColumnValuePair)
{
var cvp = context.Instance as SQLUpdateColumnValuePair;
if (cvp.Table != null)
{
m_list = cvp.Table.Columns.ToList();
}
}
else if (context.Instance is SQLInsertColumnValuePair)
{
var cvp = context.Instance as SQLInsertColumnValuePair;
if (cvp.Table != null)
{
if (context.PropertyDescriptor.Name == "SourceColumn")
{
m_list = cvp.Table.Columns.ToList();
}
else if (cvp.TargetTable != null && context.PropertyDescriptor.Name == "TargetColumn")
{
m_list = cvp.TargetTable.Columns.ToList();
}
}
}
else if (context.Instance is SQLStatement)
{
var cvp = context.Instance as SQLStatement;
if (cvp.Table != null)
{
m_list = cvp.Table.Columns.ToList();
}
}
return(true);
}
private void ShowNode(TreeNode node, bool refresh = false, bool open = true)
{
if (node == null)
{
return;
}
if (node.Tag is DBObject)
{
if (((node.Tag as DBObject).State == DBObject.DBObjectState.None || refresh))
{
DBObjectManager.Refresh((node.Tag as DBObject)); //only refresh when needed or forced
var db_obj = (node.Tag as IGenerateTreeNode);
node.Nodes.Clear();
foreach (TreeNode n in db_obj.GetTree().Nodes)
{
node.Nodes.Add(n);
}
IconizeTreeNodes(node);
AttachTreeNodesContextMenus(node);
}
//else
//{
// if(node.Tag is IContainsColumns) //refresh tables if their columns collection is empty
// {
// if ((node.Tag as IContainsColumns).Columns.Count == 0)
// {
// (node.Tag as IRefreshableObject).Refresh();
// }
// }
//}
}
UpdatePropertyGrid(node.Tag);
if (node.Tag is DBObject)
{
if (refresh || open)
{
var childForm = CreateNewForm(node.Tag as DBObject);
//update the details view of the object
if (refresh)
{
//add tab
childForm.RefreshDisplay();
}
}
}
if (node.Tag is IGenerateTreeNode)
{
//reload nodes
/*var db_obj = (node.Tag as IGenerateTreeNode);
*
* node.Nodes.Clear();
* foreach (TreeNode n in db_obj.GetTree().Nodes)
* node.Nodes.Add(n);*/
}
}
private void refresh_grids()
{
if (DBObject != null)
{
//get selected record
DBObjectManager.Refresh(DBObject);
var column_permissions_sql = string.Format(@"
declare @actual table (role_name nvarchar(100), [column] nvarchar(100), [permission_name] nvarchar(100), [permission_state] nvarchar(100))
insert @actual
SELECT
[role_name] = roleprinc.[name] COLLATE SQL_Latin1_General_CP1_CI_AS,
[column] = c.name,
[permission_name] = perm.[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AS,
[permission_state] = perm.[state_desc] COLLATE SQL_Latin1_General_CP1_CI_AS
FROM
sys.database_principals roleprinc
join sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
join sys.objects o on perm.major_id = o.object_id
join sys.schemas s on o.schema_id = s.schema_id
join sys.columns c on c.object_id = o.object_id and perm.minor_id = c.column_id
where
roleprinc.principal_id <> 0 and s.name = N'{0}' and o.name = N'{1}'
and roleprinc.name = '{2}'
select * from
(
select * from @actual
union all
select [role_name] = roleprinc.[name], [column] = c.name, p.permission_name, s.permission_state
from sys.database_principals roleprinc
cross join (values('SELECT'),('UPDATE')) p(permission_name)
cross join (values('REVOKE')) s(permission_state)
cross join (select c.name from sys.columns c where object_schema_name(c.object_id) = N'{0}' and object_name(c.object_id) = N'{1}') c
where roleprinc.type = 'R' and roleprinc.is_fixed_role = 0 and roleprinc.principal_id <> 0
and roleprinc.name + c.name + p.permission_name not in(select role_name + [column] + permission_name from @actual)
and roleprinc.name = '{2}'
) t order by [role_name], [column], [permission_name]
", DBObject.Schema.Name, DBObject.Name, Role.Name);
this.column_permissions = this.DBObject.Connection.GetDataTable(column_permissions_sql).AsEnumerable().Select(x =>
new DBColumnPermission
{
DBColumn = (this.DBObject as IContainsColumns).Columns.Where(c => c.Name == x["column"].ToString()).FirstOrDefault(),
Role = Role, //roles.Where(r => r.Name == x["role_name"].ToString()).FirstOrDefault(),
PermissionName = (DBColumnPermission.PermissionNames)Enum.Parse(typeof(DBColumnPermission.PermissionNames), x["permission_name"].ToString().Replace(" ", "_"), true),
Grant = x["permission_state"].ToString().Equals("GRANT"),
Deny = x["permission_state"].ToString().Equals("DENY"),
State = EngineManager.DBObject.DBObjectState.Intact
}
).ToList();
}
//columns select permissions
dataGridView_columns_select.AutoGenerateColumns = false;
dataGridView_columns_select.DataSource = new BindingSource(column_permissions.Where(p =>
p.Role == listBox_roles.SelectedValue as DBRole &&
p.PermissionName == DBColumnPermission.PermissionNames.Select).ToList(), null);
//columns update permissions
dataGridView_columns_update.AutoGenerateColumns = false;
dataGridView_columns_update.DataSource = new BindingSource(column_permissions.Where(p =>
p.Role == listBox_roles.SelectedValue as DBRole &&
p.PermissionName == DBColumnPermission.PermissionNames.Update).ToList(), null);
}
