C# - Populating a TreeView from a DataSet

Hi guys 'n gals,

I need some help in populating a treeView object from a Dataset, here is the structure of my Datatable:

UID, Name, ParentUID, DisplayOrder

UID is the Unique ID for that record
Name is the value I want to display in the TreeView
ParentUID is the UID of the Parent Node
DisplayOrder is the order in which everything will be displayed


If you need more information off of me to be able to solve this issue, please feel free to ask me!!

Cheers!
LVL 4
Cyber-DrugsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dunglaCommented:
1. Query from database with DisplayOrder
SELECT UID, ParentUID, Name, DisplayOrder
FROM yourTable
ORDER BY ParentUID, DisplayOrder

2. Add Parent(s) node

3. Loop thru the parent(s) node, get the parent id then getting the children:
foreach (TreeNode parentNode in treeView.Nodes)
{
// parent id
int intParentUID = Convert.ToInt32(parentNode.Tag); // just for example I store the parent id to Tag of Node)
// now retrieve children from table
DataRow[] drowChildren = childTable.Select("ParentUID = " + intParentUID);
// add child node
foreach (DataRow drowChild in drowChildren)
{
TreeNode childNode = new TreeNode();
childNode.Text = drowChild["Name"].ToString();
childNode.Tag = Convert.ToInt32(drowChild["UID"]);
// add to parent node
parentNode.Nodes.Add(childNode);
}
}
0
Cyber-DrugsAuthor Commented:
Hi dungla,

Unfortunately that won't do the job, as there is an unlimited depth to this structure, rather than one child deep. I have the code I want in PHP, would posting it help all?


Cheers!
0
Cyber-DrugsAuthor Commented:
In case it helps, here is the PHP version of the code:



function catlist()
{
      $db = new DBClass;
      $db->DB();
      $db->Connect();
      $cat = array();
      $totalcats = '1';
      $maxid = '1';
      
      $cat_query = mysql_query("SELECT * FROM tblNavigator ORDER BY displayorder, cat_name ASC");
      
      while ($cattemp = mysql_fetch_array($cat_query))
      {
            $id = $cattemp['UID'];
            if ($maxid < $id) $maxid = $id;
            
            $cat[$totalcats]['id'] = $totalcats;
            $cat[$totalcats]['cat_id'] = $cattemp['UID'];
            $cat[$totalcats]['parent_id'] = $cattemp['ParentUID'];
            $cat[$totalcats]['name'] = $cattemp['cat_name'];
            
            $totalcats++;
      }
      
      if ($totalcats == '1')
      {
            $cat_list_bit = $vbphrase['music_nocat'];
      }
      else
      {
            for ($cid = '1'; $cid <= $maxid; $cid++)
            {
                  if ($cat[$cid]['parent_id'] == '0')
                  {
                        $cat_id = $cat[$cid]['cat_id'];
                        $cat_name = $cat[$cid]['name'];
                        $par_num = '0';
                        $cat_list_sub = subcat($cat_id, $cat, $maxid);
                        
                        $cat_list_bit .= "<img src=\"images/minus.gif\" border='0' style=\"cursor:hand;\" onClick=\"hide_sub_cat(c".$cat_id.", this)\">&nbsp;<a style=\"cursor:hand\" onclick=\"nav_click(".$cat_id.", '".$cat_name."')\"><img src=\"images/text_rich_colored.png\" alt=\"\" width=\"16\" height=\"16\" border=\"0\" />&nbsp;<strong>".$cat_name."</strong></a><br>
                        <span id=\"c$cat_id\" style=\"display:'none'\"><div style=\"margin-left: 15px\">".$cat_list_sub."</div></span>";
                  }
            }
      }
      
      $cat_list = $cat_list_bit;
      
      $db->Disconnect();
      return $cat_list;
}

function subcat($parent_id, $cat, $maxid)
{
      global $vbulletin, $vbphrase;
      
      for ($sub_id = 0; $sub_id <= $maxid; $sub_id++)
      {
            if ($cat[$sub_id]['parent_id'] == $parent_id)
            {
                  $cat_id = $cat[$sub_id]['cat_id'];
                  $cat_name = $cat[$sub_id]['name'];
                  
                  for ($n = '1';$n <= $par_num;$n++) $sub_cats_.='|-';
                  
                  $sub_cats_sub = subcat($cat_id, $cat, $maxid);
                  
                  $child_count = mysql_num_rows(mysql_query("SELECT * FROM tblNavigator WHERE ParentUID='".$cat_id."'"));
                  if ($child_count == 0)
                  {
                        $sub_cats .= $sub_cats_." <img src=\"images/leaf.gif\" border='0' style=\"cursor:hand\" onClick=\"hide_sub_cat(c".$cat_id.", this)\">&nbsp;<a style=\"cursor:hand\" onclick=\"nav_click(".$cat_id.", '".$cat_name."')\"><img src=\"images/text_rich_colored.png\" alt=\"\" width=\"16\" height=\"16\" border=\"0\" />&nbsp;".$cat_name."<a/><br>
                        <span id=\"c".$cat_id."\" style=\"display:'none'\"><div style=\"margin-left: 15px\">".$sub_cats_sub."</div></span>";
                  }
                  else
                  {
                        $sub_cats .= $sub_cats_." <img src=\"images/minus.gif\" border='0' style=\"cursor:hand\" onClick=\"hide_sub_cat(c".$cat_id.", this)\">&nbsp;<a style=\"cursor:hand\" onclick=\"nav_click(".$cat_id.", '".$cat_name."')\"><img src=\"images/text_rich_colored.png\" alt=\"\" width=\"16\" height=\"16\" border=\"0\" />&nbsp;".$cat_name."</a><br>
                        <span id=\"c".$cat_id."\" style=\"display:'none'\"><div style=\"margin-left: 15px\">".$sub_cats_sub."</div></span>";
                  }
            }
      }
      return $sub_cats;
}
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Cyber-DrugsAuthor Commented:
Hey,

I decided to quick try your code to see if maybe it did the same thing as the large PHP function I have. When running it, I got the error:

Error      1      The name 'childTable' does not exist in the current context      C:\Documents and Settings\Justin Nel\My Documents\Visual Studio 2005\Projects\CartoLogix\CartoLogix\Form1.cs      55      42      CartoLogix


What should "childTable" be?
0
dunglaCommented:
childTable is the result of query

"SELECT UID, ParentUID, Name, DisplayOrder
FROM yourTable
ORDER BY ParentUID, DisplayOrder"

If you want to add more child, just make a recursive call to the method.
0
Cyber-DrugsAuthor Commented:
Ok,

Currently it has no errors, but it displays this:

Top Level
- Top Level


Rather than:

Top Level
- Site 1
- etc...


I have added "Top Level" in manually as well as with the code, and it does as displayed above, but when I remove it, nothing is displayed at all. What changes do I need to make?

Cheers!


Code thus far:


        private void PopulateTreeView1()
        {
            MySqlConnection conn = new MySqlConnection(connString);
            DataSet ds = new DataSet();
            conn.Open();
            MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM tblNavigator ORDER BY ParentUID, displayorder", conn);
            adapter.Fill(ds);
            conn.Close();

            TreeNode treeTopLevel;

            DataTable childTable = new DataTable();
            childTable = ds.Tables[0];

            treeTopLevel = treeView1.Nodes.Add("Top Level");

            foreach (TreeNode parentNode in treeView1.Nodes)
            {
                int intParentUID = Convert.ToInt32(parentNode.Tag);
                DataRow[] dRowChildren = childTable.Select("ParentUID = " + intParentUID);
                foreach (DataRow dRowChild in dRowChildren)
                {
                    TreeNode childNode = new TreeNode();
                    childNode.Text = dRowChild["cat_name"].ToString();
                    parentNode.Nodes.Add(childNode);
                }
            }
        }
0
dunglaCommented:
You need to add all parent nodes as my first post already said. So your tree view will look like:

- Parent1
- Parent2
- Parent3

Then now we will make a for loop to add child for each parent node as my code above.

So take a look at your code, I can't see any code to add parent node, just create a Top Level is not enough. You know, we need to look up child by ParentUID retrieve from Tag property of Node.

I think the code will be something like that:

DataTable dtbParent = new DataTable(); // select from database.
foreach (DataRow drowParent in dtbParent.Rows)
{
TreeNode parentNode = new TreeNode();
parentNode.Text = drowParent["Name"].ToString();
parentNode.Tag = drowParent["ParentUID"].ToString();
treeView1.Nodes.Add(parentNode);
}
// now will have added all parent node, continue to add child
DataTable childTable = new DataTable();
childTable = ds.Tables[0];
foreach (TreeNode parentNode in treeView1.Nodes)
{
int intParentUID = Convert.ToInt32(parentNode.Tag);
DataRow[] dRowChildren = childTable.Select("ParentUID = " + intParentUID);
foreach (DataRow dRowChild in dRowChildren)
{
TreeNode childNode = new TreeNode();
childNode.Text = dRowChild["cat_name"].ToString();
parentNode.Nodes.Add(childNode);
}
            }
0
Cyber-DrugsAuthor Commented:
Ok, that's now populating the entire thing with all the data, but it's not all correct...

Currently, it's coming out like this:

Top Level
- Top Level
Site D
- Site D
- Site C
- Site B
- Site A
Site C
- Site D
- Site C
- Site B
- Site A

etc...

Here is an example of the data from the database:


UID       ParentUID       Name       displayorder
1       0       Top Level       0
2       1       Site A       0
3       1       Site B       0
4       2       Building A       0
5       1       Site C       0
6       1       Site D       0
7       5       Building B       0
8       5       Building C       0
9       4       Ground Floor       0
10       4       First Floor       0
11       9       Room A       0
12       11       Server A       0
13       12       Device A       0
14       12       Device B       0
15       13       Port 1       0
16       13       Port 2       0
17       13       Port 3       0
18       13       Port 4       0
19       14       Port 1       0
20       14       Port 2       0
21       14       Port 3       0
22       14       Port 4       0




Here is how the code looks at the moment:



private void PopulateTreeView1()
        {
            MySqlConnection conn = new MySqlConnection(connString);
            DataSet ds = new DataSet();
            conn.Open();
            MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM tblNavigator ORDER BY ParentUID, displayorder", conn);
            adapter.Fill(ds);
            conn.Close();

            TreeNode treeTopLevel;

            DataTable dtbParent = new DataTable();
            dtbParent = ds.Tables[0];
            foreach (DataRow drowParent in dtbParent.Rows)
            {
                TreeNode parentNode = new TreeNode();
                parentNode.Text = drowParent["Name"].ToString();
                parentNode.Tag = drowParent["ParentUID"].ToString();
                treeView1.Nodes.Add(parentNode);
            }

            DataTable childTable = new DataTable();
            childTable = ds.Tables[0];

            //treeTopLevel = treeView1.Nodes.Add("Top Level");

            foreach (TreeNode parentNode in treeView1.Nodes)
            {
                int intParentUID = Convert.ToInt32(parentNode.Tag);
                DataRow[] dRowChildren = childTable.Select("ParentUID = " + intParentUID);
                foreach (DataRow dRowChild in dRowChildren)
                {
                    TreeNode childNode = new TreeNode();
                    childNode.Text = dRowChild["Name"].ToString();
                    parentNode.Nodes.Add(childNode);
                }
            }
        }
0
YttribiumCommented:
Here's a working solution as far as I can see:

            private void PopulateTreeView1()
            {
                  int ParentUID = 0;
                  System.Collections.ArrayList tnodes = new System.Collections.ArrayList();
                  MySqlConnection conn = new MySqlConnection(connString);
                  DataSet ds = new DataSet();
                  conn.Open();
                  MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM tblNavigator ORDER BY ParentUID, displayorder", conn);
                  adapter.Fill(ds);
                  conn.Close();

                  DataTable dtbParent = new DataTable();
                  dtbParent = ds.Tables[0];

                  foreach (DataRow drowParent in dtbParent.Rows)
                  {
                        ParentUID = (int)drowParent["ParentUID"];
                        //If top node
                        if (ParentUID == 0)
                        {
                              TreeNode node = new TreeNode();
                              node.Text = drowParent["cat_name"].ToString();
                              node.Tag = (int)drowParent["UID"];
                              treeView1.Nodes.Add(node);
                              tnodes.Add(node);
                        }
                        else
                        {
                              for (int i = 0; i < tnodes.Count; i++)
                              {
                                    if ((int)((TreeNode)tnodes[i]).Tag == ParentUID)
                                    {
                                          TreeNode node = new TreeNode();
                                          node.Text = drowParent["cat_name"].ToString();
                                          node.Tag = (int)drowParent["UID"];
                                          tnodes.Add(node);
                                          ((TreeNode)tnodes[i]).Nodes.Add(node);
                                          break;
                                    }
                                    //End of tree
                                    
                              }
                        }
                  }
            }
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cyber-DrugsAuthor Commented:
That did the trick perfectly, thank you!!
0
YttribiumCommented:
No probs
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.