private TreeNode AddGroupingFolder(TreeNode parentNode, string name, int nodeType, string dbName, bool addFakeChild, int parentType)
{
if ((parentNode == null) || (nodeType != DBObjectType.GroupingFolderB && nodeType != DBObjectType.GroupingFolderY))
{
return(null);
}
NodeData parentData = NodeDataFactory.GetNodeData(parentNode.Tag);
if (parentData == null)
{
return(AddErrorNode(parentNode.Nodes, name, "Parent data is empty"));
}
TreeNode node = AddNode(parentNode.Nodes, name, nodeType);
if (addFakeChild)
{
node.Nodes.Add("...");
}
NodeData data = NodeDataFactory.Create(parentData.ConnParams, name, nodeType, dbName);
data.ConnParams = ((NodeData)parentNode.Tag).ConnParams;
data.ParentType = parentType;
data.ID = parentData.ID;
node.Tag = data;
return(node);
}
private TreeNode AddServerNode(ConnectionParams cp)
{
if (cp == null)
{
return(null);
}
TreeNode node = AddNode(null, cp.Name, DBObjectType.Server);
NodeData data = NodeDataFactory.Create(cp, cp.Name, DBObjectType.Server, String.Empty);
node.Tag = data;
return(node);
}
private void PopulateUsers(ConnectionParams cp, TreeNode parentNode)
{
if (cp == null || parentNode == null)
{
return;
}
if (!_connections.Keys.Contains(cp.Name))
{
return;
}
SqlConnection conn = _connections[cp.Name];
if (conn.State != ConnectionState.Open)
{
throw new InvalidConnectionState("Can not populate users!");
}
TreeNode usersNode = AddNode(parentNode.Nodes, FolderType.Users, DBObjectType.UsersGroup);
usersNode.Nodes.Clear();
NodeData parentData = NodeDataFactory.GetNodeData(parentNode.Tag);
NodeData usersNodeData = NodeDataFactory.Create(parentData.ConnParams, FolderType.Users, DBObjectType.UsersGroup, parentData.DBName);
usersNodeData.Populated = true;
usersNode.Tag = usersNodeData;
DataTable users = conn.GetSchema("Users");
users.DefaultView.Sort = "user_name";
users = users.DefaultView.ToTable();
foreach (DataRow row in users.Rows)
{
string userName = (string)row["user_name"];
TreeNode node = AddNode(usersNode.Nodes, userName, DBObjectType.User);
NodeData userNodeData = NodeDataFactory.Create(parentData.ConnParams, userName, DBObjectType.User, parentData.DBName);
userNodeData.Populated = false;
node.Tag = userNodeData;
}
}
private TreeNode TryToAddTriggerGroupingFolder(TreeNode parentNode)
{
if (parentNode == null)
{
return(null);
}
NodeData parentData = NodeDataFactory.GetNodeData(parentNode.Tag);
if (parentData == null)
{
return(null);
}
if (!((parentData.Type == DBObjectType.UserTable) ||
(parentData.Type == DBObjectType.SystemTable) ||
(parentData.Type == DBObjectType.View))
)
{
return(null);
}
TreeNode node = AddGroupingFolderY(parentNode, FolderType.Triggers, parentData.DBName, false, parentData.Type);
NodeData nodeData = NodeDataFactory.Create(parentData.ConnParams, FolderType.Triggers, DBObjectType.GroupingFolderY, parentData.DBName);
nodeData.ID = parentData.ID;
node.Tag = nodeData;
if (!PopulateTriggers(node))
{
parentNode.Nodes.Remove(node);
return(null);
}
else
{
return(node);
}
}
private bool PopulateTriggers(TreeNode parentNode)
{
if (parentNode == null)
{
return(false);
}
NodeData parentData = NodeDataFactory.GetNodeData(parentNode.Tag);
if (parentData == null)
{
AddErrorNode(parentNode.Nodes, String.Empty, "Parent data is null.");
return(false);
}
if (!_connections.Keys.Contains(parentData.ConnParams.Name))
{
return(false);
}
SqlConnection conn = _connections[parentData.ConnParams.Name];
if (conn.State != ConnectionState.Open)
{
throw new InvalidConnectionState("Can not populate table-valued function!");
}
string script = global::PragmaSQL.Properties.Resources.Script_GetTriggers;
if (conn.Database.ToLowerInvariant() != parentData.DBName.ToLowerInvariant())
{
try
{
conn.ChangeDatabase(parentData.DBName);
}
catch (Exception ex)
{
AddErrorNode(parentNode.Nodes, String.Empty, ex.Message);
return(true);
}
}
SqlCommand cmd = new SqlCommand(script, conn);
SqlParameter param = cmd.Parameters.Add("@parentid", SqlDbType.Int);
param.Value = parentData.ID;
SqlDataReader reader = cmd.ExecuteReader();
int objCount = 0;
while (reader.Read())
{
string objName = (string)reader["name"];
TreeNode node = AddNode(parentNode.Nodes, objName, DBObjectType.Trigger);
NodeData nodeData = NodeDataFactory.Create(parentData.ConnParams, objName, DBObjectType.Trigger, parentData.DBName);
nodeData.ID = (int)reader["id"];
node.Tag = nodeData;
objCount++;
}
reader.Close();
parentData.Populated = true;
parentNode.Text = parentData.Name + " (" + objCount.ToString() + ")";
if (objCount == 0)
{
return(false);
}
else
{
return(true);
}
}
private void PopulateParamsAndColumns(TreeNode parentNode, int nodeType)
{
if (parentNode == null)
{
return;
}
NodeData parentData = NodeDataFactory.GetNodeData(parentNode.Tag);
if (parentData == null)
{
AddErrorNode(parentNode.Nodes, String.Empty, "Parent data is null.");
return;
}
if (!_connections.Keys.Contains(parentData.ConnParams.Name))
{
return;
}
SqlConnection conn = _connections[parentData.ConnParams.Name];
if (conn.State != ConnectionState.Open)
{
throw new InvalidConnectionState("Can not populate table-valued function!");
}
string script = global::PragmaSQL.Properties.Resources.Script_GetColsAndParams;
if (conn.Database.ToLowerInvariant() != parentData.DBName.ToLowerInvariant())
{
try
{
conn.ChangeDatabase(parentData.DBName);
}
catch (Exception ex)
{
AddErrorNode(parentNode.Nodes, String.Empty, ex.Message);
return;
}
}
SqlCommand cmd = new SqlCommand(script, conn);
SqlParameter param = cmd.Parameters.Add("@id", SqlDbType.Int);
param.Value = parentData.ID;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string pName = (string)reader["name"];
pName = pName + " [ " + (string)reader["typename"] + " (" + ((short)reader["length"]).ToString() + ") " + "]";
int type = -1;
if ((int)reader["isoutparam"] == 1)
{
type = DBObjectType.ParameterOut;
}
else if (nodeType == DBObjectType.StoredProc || nodeType == DBObjectType.ScalarValuedFunction || nodeType == DBObjectType.TableValuedFunction)
{
if (nodeType == DBObjectType.TableValuedFunction)
{
if (parentData.Name == FolderType.Columns)
{
if (!pName.StartsWith("@"))
{
type = DBObjectType.ParameterOut;
}
else
{
continue;
}
}
else if (parentData.Name == FolderType.Parameters)
{
if (pName.StartsWith("@"))
{
type = DBObjectType.ParameterOut;
}
else
{
continue;
}
}
else
{
continue;
}
}
else
{
if (pName.StartsWith("@"))
{
type = DBObjectType.ParameterIn;
}
else
{
type = DBObjectType.ParameterOut;
}
}
}
else if (nodeType == DBObjectType.UserTable || nodeType == DBObjectType.SystemTable || nodeType == DBObjectType.View)
{
byte status = (byte)reader["status"];
if ((status & 128) == 128)
{
type = DBObjectType.IdentityCol;
}
else
{
type = DBObjectType.Column;
}
}
TreeNode node = AddNode(parentNode.Nodes, pName, type);
NodeData nodeData = NodeDataFactory.Create(parentData.ConnParams, pName, type, parentData.DBName);
nodeData.ID = parentData.ID;
node.Tag = nodeData;
}
reader.Close();
parentData.Populated = true;
}
private void PopulateObjects(TreeNode parentNode, int nodeType)
{
if (parentNode == null)
{
return;
}
NodeData parentData = NodeDataFactory.GetNodeData(parentNode.Tag);
if (parentData == null)
{
AddErrorNode(parentNode.Nodes, String.Empty, "Parent data is null.");
return;
}
if (!_connections.Keys.Contains(parentData.ConnParams.Name))
{
return;
}
SqlConnection conn = _connections[parentData.ConnParams.Name];
if (conn.State != ConnectionState.Open)
{
throw new InvalidConnectionState("Can not populate table-valued function!");
}
string script = String.Empty;
switch (nodeType)
{
case DBObjectType.UserTable:
script = global::PragmaSQL.Properties.Resources.Script_GetUserTables;
break;
case DBObjectType.SystemTable:
script = global::PragmaSQL.Properties.Resources.Script_GetSystemTables;
break;
case DBObjectType.View:
script = global::PragmaSQL.Properties.Resources.Script_GetViews;
break;
case DBObjectType.StoredProc:
script = global::PragmaSQL.Properties.Resources.Script_GetStoredProcedures;
break;
case DBObjectType.TableValuedFunction:
script = global::PragmaSQL.Properties.Resources.Script_GetTableValuedFunctions;
break;
case DBObjectType.ScalarValuedFunction:
script = global::PragmaSQL.Properties.Resources.Script_GetScalarValuedFunctions;
break;
default:
AddErrorNode(parentNode.Nodes, String.Empty, String.Format("Invalid object type. \"{0}\" ", nodeType));
return;
}
if (conn.Database.ToLowerInvariant() != parentData.DBName.ToLowerInvariant())
{
try
{
conn.ChangeDatabase(parentData.DBName);
}
catch (Exception ex)
{
AddErrorNode(parentNode.Nodes, String.Empty, ex.Message);
return;
}
}
SqlCommand cmd = new SqlCommand(script, conn);
SqlDataReader reader = cmd.ExecuteReader();
int objCount = 0;
while (reader.Read())
{
string objName = (string)reader["name"];
TreeNode node = AddNode(parentNode.Nodes, objName, nodeType);
NodeData nodeData = NodeDataFactory.Create(parentData.ConnParams, objName, nodeType, parentData.DBName);
nodeData.ID = (int)reader["id"];
nodeData.HasParamsOrCols = true;
node.Tag = nodeData;
objCount++;
AddNode(node.Nodes, "...", -1);
}
reader.Close();
parentData.Populated = true;
parentNode.Text = parentData.Name + " (" + objCount.ToString() + ")";
}
private void PopulateDatabases(ConnectionParams cp, TreeNode parentNode)
{
if (cp == null || parentNode == null)
{
return;
}
if (!_connections.Keys.Contains(cp.Name))
{
return;
}
SqlConnection conn = _connections[cp.Name];
if (conn.State != ConnectionState.Open)
{
throw new InvalidConnectionState("Can not populate databases!");
}
NodeData parentData = NodeDataFactory.GetNodeData(parentNode);
TreeNode sysDbGroupNode = AddGroupingFolder(parentNode, "System Databases", DBObjectType.GroupingFolderB, true, DBObjectType.Server);
sysDbGroupNode.Nodes.Clear();
DataTable dbs = conn.GetSchema("Databases");
dbs.DefaultView.Sort = "database_name";
dbs = dbs.DefaultView.ToTable();
foreach (DataRow row in dbs.Rows)
{
string dbName = (string)row["database_name"];
TreeNode node = null;
if (DBConstants.SystemDbNames.Contains(dbName.ToLowerInvariant()))
{
node = AddNode(sysDbGroupNode.Nodes, dbName, DBObjectType.Database);
}
else
{
node = AddNode(parentNode.Nodes, dbName, DBObjectType.Database);
}
NodeData data = NodeDataFactory.Create(cp, dbName, DBObjectType.Database, dbName);
data.Populated = true;
node.Tag = data;
TreeNode tblsNode = AddGroupingFolderY(node, FolderType.Tables, dbName, true, DBObjectType.Database);
tblsNode.Nodes.Clear();
AddGroupingFolderY(tblsNode, FolderType.SystemTables, dbName, true, DBObjectType.Database);
AddGroupingFolderY(tblsNode, FolderType.UserTables, dbName, true, DBObjectType.Database);
AddGroupingFolderY(node, FolderType.Views, dbName, true, DBObjectType.Database);
AddGroupingFolderY(node, FolderType.Procedures, dbName, true, DBObjectType.Database);
TreeNode fnNode = AddGroupingFolderY(node, FolderType.Functions, dbName, true, DBObjectType.Database);
fnNode.Nodes.Clear();
AddGroupingFolderY(fnNode, FolderType.TableValuedFunctions, dbName, true, DBObjectType.Database);
AddGroupingFolderY(fnNode, FolderType.ScalarValuedFunctions, dbName, true, DBObjectType.Database);
if (cp.InitialCatalog.ToLowerInvariant() == dbName.ToLowerInvariant())
{
node.Expand();
tv.SelectedNode = node;
}
}
}