public TreeNode[] GetSubObjectHierarchy(TreeNode node) { // Show the column breakdown for the selected table if (node is SqlNode) { SqlNode sn = (SqlNode)node; this.DbClient.Database = sn.database; if (sn.type == "U" || sn.type == "V") // break down columns for user tables and views { DataSet ds = dbClient.Execute("select COLUMN_NAME name, DATA_TYPE type, CHARACTER_MAXIMUM_LENGTH clength, NUMERIC_PRECISION nprecision, NUMERIC_SCALE nscale, IS_NULLABLE nullable from INFORMATION_SCHEMA.COLUMNS where TABLE_CATALOG = db_name() and TABLE_SCHEMA = '" + sn.owner + "' and TABLE_NAME = '" + sn.name + "' order by ORDINAL_POSITION", timeout); if (ds == null || ds.Tables.Count == 0) { return(null); } TreeNode[] tn = new SqlNode [ds.Tables[0].Rows.Count]; int count = 0; foreach (DataRow row in ds.Tables[0].Rows) { string length; if (row ["clength"].ToString() != "") { length = "(" + row ["clength"].ToString() + ")"; } else if (row ["nprecision"].ToString() != "") { length = "(" + row ["nprecision"].ToString() + "," + row ["nscale"].ToString() + ")"; } else { length = ""; } string nullable = row ["nullable"].ToString().StartsWith("Y") ? "null" : "not null"; SqlNode column = new SqlNode(row ["name"].ToString() + " (" + row ["type"].ToString() + length + ", " + nullable + ")"); column.type = "CO"; // column column.dragText = row ["name"].ToString(); if (column.dragText.IndexOf(' ') >= 0) { column.dragText = "[" + column.dragText + "]"; } column.safeName = column.dragText; tn [count++] = column; } return(tn); } } return(null); }
public DataSet GetSubObjectDataSet(TreeNode thenode) { if (thenode is SqlNode) { SqlNode sn = (SqlNode)thenode; this.DbClient.Database = sn.database; if (sn.type == "U" || sn.type == "V") // break down columns for user tables and views { DataSet ds = dbClient.Execute("select COLUMN_NAME name, DATA_TYPE type, CHARACTER_MAXIMUM_LENGTH clength, NUMERIC_PRECISION nprecision, NUMERIC_SCALE nscale, IS_NULLABLE nullable from INFORMATION_SCHEMA.COLUMNS where TABLE_CATALOG = db_name() and TABLE_SCHEMA = '" + sn.owner + "' and TABLE_NAME = '" + sn.name + "' order by ORDINAL_POSITION", timeout); return(ds); } } return(null); }
public StringCollection GetActionList(TreeNode node) { if (!(node is SqlNode)) { return(null); } SqlNode sn = (SqlNode)node; StringCollection output = new StringCollection(); if (sn.type == "U" || sn.type == "S" || sn.type == "V") { output.Add("select * from " + sn.safeName); output.Add("sp_help " + sn.safeName); if (sn.type != "V") { output.Add("sp_helpindex " + sn.safeName); output.Add("sp_helpconstraint " + sn.safeName); output.Add("sp_helptrigger " + sn.safeName); } output.Add("(insert all fields)"); output.Add("(insert all fields, table prefixed)"); } if (sn.type == "V" || sn.type == "P" || sn.type == "FN") { output.Add("View / Modify " + sn.name); } if (sn.type == "CO" && ((SqlNode)sn.Parent).type == "U") { output.Add("Alter column..."); } return(output.Count == 0 ? null : output); }
public string GetActionText(TreeNode node, string action) { if (!(node is SqlNode)) { return(null); } SqlNode sn = (SqlNode)node; if (action.StartsWith("select * from ") || action.StartsWith("sp_")) { return(action); } if (action.StartsWith("(insert all fields")) { StringBuilder sb = new StringBuilder(); // If the table-prefixed option has been selected, add the table name to all the fields string prefix = action == "(insert all fields)" ? "" : sn.safeName + "."; int chars = 0; foreach (TreeNode subNode in GetSubObjectHierarchy(node)) { if (chars > 50) { chars = 0; sb.Append("\r\n"); } string s = (sb.Length == 0 ? "" : ", ") + prefix + ((SqlNode)subNode).dragText; chars += s.Length; sb.Append(s); } return(sb.Length == 0 ? null : sb.ToString()); } if (action.StartsWith("View / Modify ")) { DataSet ds = dbClient.Execute("sp_helptext " + sn.safeName, timeout); if (ds == null || ds.Tables.Count == 0) { return(null); } StringBuilder sb = new StringBuilder(); bool altered = false; foreach (DataRow row in ds.Tables[0].Rows) { string line = row[0].ToString(); if (!altered && line.Trim().ToUpper().StartsWith("CREATE")) { sb.Append("ALTER" + line.Trim().Substring(6, line.Trim().Length - 6) + "\r\n"); altered = true; } else { sb.Append(line); } } return(sb.ToString().Trim()); } if (action == "Alter column...") { return("alter table " + ((SqlNode)sn.Parent).dragText + " alter column " + sn.safeName + " "); } return(null); }
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") }; DataSet ds = dbClient.Execute("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", timeout); if (ds == null || ds.Tables.Count == 0) { return(null); } foreach (DataRow row in ds.Tables[0].Rows) { string type = row ["type"].ToString().Substring(0, 2).Trim(); int position; if (type == "U") { position = 0; // user table } else if (type == "S") { position = 1; // system table } else if (type == "V") { position = 2; // view } else if (type == "FN") { position = 5; // function } else if ((int)row ["shipped"] == 0) { position = 3; // user stored proc } else { position = 4; // MS stored proc } string prefix = row ["owner"].ToString() == "dbo" ? "" : row ["owner"].ToString() + "."; SqlNode node = new SqlNode(prefix + row ["object"].ToString()); node.type = type; node.database = dbClient.Database; node.name = row ["object"].ToString(); node.owner = row ["owner"].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); }