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"?> <TreeDataSet> <TreeNode> <PK>1</PK> <Description>Display Assembly</Description> <ID>101-A045</ID> <IsRoot>1</IsRoot> <ParentID></ParentID> <RootID>101-A045</ParentID> </TreeNode> <TreeNode> <PK>2</PK> <Description>Camera Chassis</Description> <ID>P101-0024</ID> <IsRoot>0</IsRoot> <ParentID>101-A045</ParentID> <RootID>101-A045</ParentID> </TreeNode> <TreeNode> <PK>3</PK> <Description>Tape VHB</Description> <ID>P100-0004</ID> <IsRoot>0</IsRoot> <ParentID>P101-0024</ParentID> <RootID>101-A045</ParentID> </TreeNode> <TreeNode> <PK>4</PK> <Description>3M Foam Tape</Description> <ID>P100-0124</ID> <IsRoot>0</IsRoot> <ParentID>P101-0024</ParentID> <RootID>101-A045</ParentID> </TreeNode> <TreeNode> <PK>5</PK> <Description>Display Touch Panel Assembly</Description> <ID>P101-A045</ID> <IsRoot>0</IsRoot> <ParentID>P101-0024</ParentID> <RootID>101-A045</ParentID> </TreeNode> <TreeNode> <PK>6</PK> <Description>LCD Display Ribbon Cable</Description> <ID>P101-0046</ID> <IsRoot>0</IsRoot> <ParentID>P101-A045</ParentID> <RootID>101-A045</ParentID> </TreeNode> <TreeNode> <PK>7</PK> <Description>S-11730 Kapton Tape</Description> <ID>P100-0084</ID> <IsRoot>0</IsRoot> <ParentID>P101-A045</ParentID> <RootID>101-A045</ParentID> </TreeNode> <TreeNode> <PK>8</PK> <Description>G5 Board</Description> <ID>P101-A023</ID> <IsRoot>0</IsRoot> <ParentID>P101-0024</ParentID> <RootID>101-A045</ParentID> </TreeNode> <TreeNode> <PK>9</PK> <Description>Screw 4-24x0.375</Description> <ID>P100-0110</ID> <IsRoot>0</IsRoot> <ParentID>P101-A023</ParentID> <RootID>101-A045</ParentID> </TreeNode> </TreeDataSet>
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 dsTree.ReadXml("Tree.xml"); tree.Nodes.Clear(); 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; } else { // 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); } else return; }
That's all there is to it! Happy coding! =0)
