public TreeNode[] GetObjectHierarchy()
{
TreeNode[] top = new TreeNode[]
{
new TreeNode("User Tables"),
new TreeNode("User Views"),
};
DataSet ds = dbClient.ExecuteOnWorker("select TABLE_NAME from USER_TABLES order by TABLE_NAME", timeout);
if (ds == null || ds.Tables.Count == 0)
{
return(null);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
OracleNode node = new OracleNode(row[0].ToString());
node.type = "T";
node.dragText = node.Text;
top[0].Nodes.Add(node);
// Add a dummy sub-node to user tables and views so they'll have a clickable expand sign
// allowing us to have GetSubObjectHierarchy called so the user can view the columns
node.Nodes.Add(new TreeNode());
}
ds = dbClient.ExecuteOnWorker("select VIEW_NAME from USER_VIEWS order by VIEW_NAME", timeout);
if (ds == null || ds.Tables.Count == 0)
{
return(top);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
OracleNode node = new OracleNode(row[0].ToString());
node.type = "V";
node.dragText = node.Text;
top[1].Nodes.Add(node);
// Add a dummy sub-node to user tables and views so they'll have a clickable expand sign
// allowing us to have GetSubObjectHierarchy called so the user can view the columns
node.Nodes.Add(new TreeNode());
}
return(top);
}
public TreeNode[] GetObjectHierarchy()
{
TreeNode[] top = new TreeNode[]
{
new TreeNode("User Tables"),
new TreeNode("System Tables"),
new TreeNode("Views"),
new TreeNode("User Stored Procs"),
new TreeNode("MS Stored Procs"),
new TreeNode("Functions")
};
string Query;
//Query = "select type, ObjectProperty (id, N'IsMSShipped') shipped, object_name(id) object, user_name(uid) owner from sysobjects where type in (N'U', N'S', N'V', N'P', N'FN') order by object, owner";
Query = "select table_type as type, table_name as object, table_schema as [schema] from INFORMATION_SCHEMA.TABLES"
+ " UNION"
+ " select routine_type, routine_name, routine_schema from INFORMATION_SCHEMA.ROUTINES";
DataSet ds = dbClient.ExecuteOnWorker(Query, timeout);
if (ds == null || ds.Tables.Count == 0)
{
return(null);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
string type = row["type"].ToString();
int position;
switch (type)
{
case "BASE TABLE":
type = "U"; position = 0; break;
case "VIEW":
type = "V"; position = 2; break;
case "PROCEDURE":
type = "P"; position = 3; break;
case "FUNCTION":
type = "FN"; position = 5; break;
default:
type = "S"; position = 1; break;
}
// if (type == "U") position = 0; // user table - U
// else if (type == "S") position = 1; // system table - S
// else if (type == "V") position = 2; // view - V
// else if (type == "FN") position = 5; // function - FN
// else if ((int)row["shipped"] == 0) position = 3; // user stored proc
// else position = 4; // MS stored proc
string prefix = row["schema"].ToString(); // == "dbo" ? "" : row["owner"].ToString() + ".";
SqlNode node = new SqlNode(row["object"].ToString() + " (" + prefix + ")"); // new SqlNode(prefix + row["object"].ToString());
node.type = type;
node.name = row["object"].ToString();
node.owner = row["schema"].ToString();
// If the object name contains a space, wrap the "safe name" in square brackets.
if (node.owner.IndexOf(' ') >= 0 || node.name.IndexOf(' ') >= 0)
{
node.safeName = "[" + node.name + "]";
node.dragText = "[" + node.owner + "].[" + node.name + "]";
}
else
{
node.safeName = node.name;
node.dragText = node.owner + "." + node.name;
}
top[position].Nodes.Add(node);
// Add a dummy sub-node to user tables and views so they'll have a clickable expand sign
// allowing us to have GetSubObjectHierarchy called so the user can view the columns
if (type == "U" || type == "V")
{
node.Nodes.Add(new TreeNode());
}
}
return(top);
}