Saturday, December 02, 2017

Putting Up The Tree

From the subject title, I bet you thought this was going to be a post about Christmas trees (seeing as it's the start of the Holiday Season). But no, this is still a geeky blog.  ;0)
A friend of mine recently asked me for help with TreeViews on Windows Forms. I used to do a lot of WinForm work back in the early days of working with .NET, but not very much recently. In fact, I don't do much UI stuff anymore, except for a little test application I keep adding to which is used to help with answering questions on the Forums (and writing some of my blog posts) ... but there was nothing in my test app with a TreeView. So, consequently, I had to really dig around to be able to help him.
I remembered the concept I wanted to tell him about easily enough ... it deals with constructing a tree with data that uses self-referencing keys/ids. So, you only need one database table, and it would contain the data for multiple TreeViews. Forget about multiple tables (parent, child, grandchild ... where does it end?) ... one table will handle it all and it's not all that complicated.  The part I couldn't remember was the code to actually fill a TreeView with that data, but it didn't take too long to come up with it. Bear in mind that this concept of self-referencing columns is a concept that can be used for anything that is hierarchical in nature, not just for a TreeView on a Windows Form!!
First, let's look at the database table structure.  It should be something like this:

PK    Description       ID            IsRoot    ParentID        RootID

It's probably best to use self-referencing IDs rather than keys ... unless your ID's are not unique. For example, if your data is for an inventory system, then most likely all your Part IDs would be unique across all of your inventory. In that case, use those Part IDs for self-referencing. 

PK    Description       ID            IsRoot    ParentID        RootID
0     Display Assembly  101-A045      1         NULL            101-A045
1     Camera            P101-0024     0         101-A045        101-A045
2     Tape              P100-0004     0         P101-0024       101-A045
3     3M Tape           P100-0124     0         P101-0024       101-A045
4     Display Panel     P101-A045     0         P101-0024       101-A045
5     LCD Cable         P101-0046     0         P101-A045       101-A045
6     X Tape            P100-0084     0         P101-A045       101-A045
7     Graphic Board     P101-A023     0         P101-0024       101-A045
8     Screws 4          P100-0110     0         P101-A023       101-A045

The tree would look like this (you can display the ID if you wish, this particular example shows the ID) :

+Display Assembly [101-A045]
    +---Camera [P101-0024]
        |---Tape [P100-0004]
        |---3M Tape [P100-0124]
        +---Display Panel [P101-A045]
        |    |---LCD Cable [P101-0046]
        |    |---X Tape [P100-0084]
        +---Graphic Board [P101-A023]
        |    |---Screws 4 [P100-0110]

But, if your data is of the type code/description, where code is not unique because different *kinds* of codes might utilize the same number/symbols for the code, then you'll have to use the keys instead. I'm thinking of something like a system that integrates information from several different organizations, each organization having its own set of codes and descriptions. Those codes could easily be similar amongst the organizations. Note that in the following example, I'm going to show data for two different Organizations (the Organization is not something stored in this particular data, but depending on your application, you *could* add another column for an Organization name to your database table, or just use the Root's Description for that).

These are codes for imaginary Fire and Police departments. Note that each department has its own Root, so that each department can display its own hierarchy of codes. Note also that there *are* some codes that are the same in both Departments:

PK    Description        ID            IsRoot    ParentID        RootID
0     MyTown Fire Dept   FIRE          1         NULL             0
1     Resource Type      RSRC          0         0                0
2     Battalion          BAT           0         1                0
3     BLS Ambulance      BA            0         1                0
4     Command            CMD           0         1                0
5     Engine             ENG           0         1                0
6     Helicopter         COPT          0         1                0
7     Truck              TRK           0         1                0
8     Call Type          CALL          0         0                0
9     Brush Fire         BRUSH         0         8                0
10    EMS                EMS           0         8                0
11    River Rescue       RR            0         8                0
12    Structure Fire     STRUCT        0         8                0
13    Traffic Accident   TA            0         8                0
14    MyTown Police Dept POLICE        1         NULL              14
15    Unit Type          UNIT          0         14                14
16    Ambulance          MEDIC         0         15                14
17    Bicycle            BIKE          0         15                14
18    Chief              CHIEF         0         15                14
19    Helicopter         COPT          0         15                14
20    Mobile Command     CMD           0         15                14
21    Patrol Car         CRUISER       0         15                14
22    Swat Team          SWAT          0         15                14
23    Incident Type      CALL          0         14                14
24    Break & Enter      B&E           0         23                14
25    Burglary           BURG          0         23                14
26    EMS                EMS           0         23                14
27    Homicide           HOM           0         23                14
28    Robbery            ROBR          0         23                14
29    Traffic Accident   TRAFFIC       0         23                14

Now, because we have data for more than one organization, we could start out getting data from the database like this:

SELECT * MyCodesTable WHERE IsRoot = 1

That will show the organizations that we have data for.
Then, if we're displaying data for the Fire Department, we do:

SELECT * MyCodesTable WHERE RootID = 0

and for the Police Department:

SELECT * MyCodesTable WHERE RootID = 14

Here is how our two trees would look: 

+MyTown Fire Dept [FIRE]
    +---Resource Type [RSRC]
        |---Battalion [BAT]
        |---BLS Ambulance [BA]
        |---Command [CMD]
        |---Engine [ENG]
        |---Helicopter [COPT]
        |---Truck [TRK]
    +---Call Type [CALL]
        |---Brush Fire [BRUSH ]
        |---EMS [EMS]   
        |---River Rescue [RR]    
        |---Structure Fire [STRUCT]
        |---Traffic Accident [TA]

+MyTown Police Dept [POLICE]    
    +---Unit Type [UNIT]
        |---Ambulance [MEDIC]
        |---Bicycle [BIKE]
        |---Chief [CHIEF]  
        |---Helicopter [COPT]
        |---Mobile Command [CMD]  
        |---Patrol Car [CRUISER]
        |---Swat Team [SWAT]
    +---Incident Type [CALL]
        |---Break & Enter [B&E]
        |---Burglary [BURG]   
        |---EMS [EMS]  
        |---Homicide [HOM]   
        |---Robbery [ROBR]   
        |---Traffic Accident [TRAFFIC]

Now on to the last bit: how to code this sucker! First, we'll need some data. Here is some XML you can use (copy/paste, save as an XML file, Tree.xml).

<?xml version="1.0" standalone="yes"?>
    <Description>Display Assembly</Description>
    <Description>Camera Chassis</Description>
    <Description>Tape VHB</Description>
    <Description>3M Foam Tape</Description>
    <Description>Display Touch Panel Assembly</Description>
    <Description>LCD Display Ribbon Cable</Description>
    <Description>S-11730 Kapton Tape</Description>
    <Description>G5 Board</Description>
    <Description>Screw 4-24x0.375</Description>

The code is pretty straightforward. Assuming that you know the RootID already, call the FillTheTree() method passing it the instance of the TreeView on your Form along with the RootID:

this.FillTheTree(this.oTree, "101-A045");

And now, the two FillTheTree() methods. The second method calls itself recursively:

private void FillTheTree(TreeView tree, string RootID)
    DataSet dsTree = new DataSet();
    // Here's where you'll actually retrieve the data from the database with this:
    // SELECT * FROM MyCodesTable WHERE RootID = @RootID
    DataView dvTree = new DataView(dsTree.Tables[0]);
    dvTree.RowFilter = string.Format("ID = '{0}'", RootID);
    this.FillTheTree(tree, dvTree, RootID);
// Calls itself recursively
private void FillTheTree(TreeView tree, DataView dvTree, string parentNodeID, TreeNode ParentNode = null)
    string nodeText, ID;
    TreeNode ChildNode = null;
    foreach (DataRowView dvRow in dvTree)
        ID = dvRow["ID"].ToString();
        nodeText = string.Format("{0}[{1}]", dvRow["Description"], ID);
        if (ParentNode != null)
            ChildNode = ParentNode.Nodes.Add(nodeText);
            ChildNode.Tag = ID;
            // The ParentNode will only initially be NULL when starting, meaning we're adding the root
            ParentNode = tree.Nodes.Add(nodeText);
            ParentNode.Tag = ID;
    // Now, loop through the child nodes of parent node, if there are any
    // Calling recursively to continue traversing the tree.
    if (ParentNode.Nodes.Count > 0)
        foreach (TreeNode node in ParentNode.Nodes)
            dvTree.RowFilter = string.Format("ParentID = '{0}'", node.Tag.ToString());
            this.FillTheTree(tree, dvTree, node.Tag.ToString(), node);
    else if (ParentNode.NextNode == null && ParentNode.Parent == null)
        dvTree.RowFilter = string.Format("ParentID = '{0}'", parentNodeID);
        this.FillTheTree(tree, dvTree, parentNodeID, ParentNode);

That's all there is to it!  Happy coding!  =0)