Populating a Tree list from database

Question

I'm looking to populate a treelist with our company structure. In my database i have a table with positions that have the following layout

PositionID - Integer for the position
PositionText - Text for the position
ReportsToID - Position ID that this position reports to.

I would like to load the positions that first report to positionID=0 (Root level) and then cascade load each of the positions from there onwards while populating a Treeview so i can then have somthing like:

-Position 1
  -Position 3
  -position 5
     -Position 7
  -Position 4
     -Position 2

Any suggestions on how to achieve this would be great, even some sudocode would help me on the right track.

EDIT: I am able comfortable with adding nodes to my tree view, but where i am getting a little hung up is how to loop/recursive search so that i can work through my items and add everything. Im thinking somehow i need to add my first node, see if anyone reports to that node. If there are, add them. Then for the next level nodes, see if anyone reports to them and then continue. I'm probably a little stuck on how to do the loop cleanly and efficiently.

Does this help? If not please post a specific question. https://support.microsoft.com/en-us/kb/320755 — Nick.Mc, Dec 18 '15 at 03:52
Thanks, I know how to populate the Treeview, probably should have been more clear with my question. What i would like help with is how to do the looping/cascade to add the items with their parent nodes/child nodes. What is best way to code it so that i can do the cascade and add the required items? — Stephen Pefanis, Dec 18 '15 at 04:37
Sorry I thought the link showed a recursive populate but it doesn't. One way to do this is to first populate the root of the tree, then use this: `SELECT ReportsToID, PositionID, PositionText FROM Table ORDER BY ReportsToID, PositionID`. Then in that result set for each row add the `ReportsToID` (parent) node if it doesn't exist, then add the `PositionID` node to the tree until you've processed every row. That is one algorithm. — Nick.Mc, Dec 18 '15 at 05:55

score 1 · Answer 1 · answered Dec 18 '15 at 06:15

1

Use this recordset:

SELECT ReportsToID, PositionID, PositionText 
FROM Table 
ORDER BY ReportsToID, PositionID

then

Create the root node (0) in your tree
Take the first row (which will need to have ReportsToID=0 in it)
Create the PositionID node under the ReportsToID node Note that ReportsToID has already been created in step 1 or 3
Take the next row and go to step 3

Having the order means that you always create one complete level before going on to the next level.

Just be aware there is a major assumption here, and that is that ID's are incremental. For example ReportsToID=5 is on a lower level than ReportsToID=6

You can avoid this with a more sophisticated query that adds a 'path' column. Then you order on the path. You need a CTE for that.

answered Dec 18 '15 at 06:15

Nick.Mc

18,304
6
61
91

Thanks. Unfortunately my Positions ID's aren't incremental as they may add a new position later on that lower ID's report to. – Stephen Pefanis Dec 20 '15 at 21:31
OK that query needs to be changed to a CTE that provides a 'path' string that can be ordered. This is in line with option 1 in @Shadow answer – Nick.Mc Dec 21 '15 at 00:30
Here's an example of a CTE that turns an iterative table into something that also contains a level column. If you use this and order by the level then you guarantee that you are populating the tree level by level. You do one database call rather than repeated ones: http://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy – Nick.Mc Dec 21 '15 at 00:38
If you state your problem in a SQLFiddle like that one, lazy people like me are more inclined to take a stab at a complete solution. As it is I won't spend time setting up tables and data etc. – Nick.Mc Dec 21 '15 at 00:39
Thanks, That looks pretty good. I'll attempt to get that to work in my treelist. – Stephen Pefanis Dec 21 '15 at 23:07

score 0 · Answer 2 · answered Dec 18 '15 at 10:31

TreeViews are tricky. There are two approaches.

Fill all the tree at once.

Pros: You hit the data base once. Less code.

Cons: If there are a lot of data, treeView will take some serious time to be filled.

Fill only first level, add dummy nodes (to indicate a false cross-sign) and on BeforeExpand query the data base to fill the node.

Pros: Very fast.

Cons: Query the database on the first BeforeExpand of each node. More code.

So, if you have just some nodes to fill go with the first approach. But if there are serious data, second works better.

score 0 · Accepted Answer · answered Dec 20 '15 at 23:44

I was able to work a solution to this which is posted below. This might not be the most elegant but it works quickly and seems to work correctly. Any feedback on this will be great.

 Private Sub LoadTreeList()
    TreeView1.Nodes.Clear()
    Dim dr() As System.Data.DataRow
    dr = Me.PositionsDataSet.Positions.Select("ReportsToPositionID = 0")
    'Populate the First Node
    For Each Row In dr
        TreeView1.Nodes.Add(Row("PositionID").ToString, Row("PositionText").ToString)
        AddSubNodes(TreeView1.Nodes(Row("PositionID").ToString))
    Next

End Sub

Private Sub AddSubNodes(ByVal CurrentNode As TreeNode)
    Dim dr() As System.Data.DataRow
    dr = Me.PositionsDataSet.Positions.Select("ReportsToPositionID = " & CurrentNode.Name)
    For Each Row In dr
        Dim NewNode As TreeNode = CurrentNode.Nodes.Add(Row("PositionID").ToString, Row("PositionText").ToString)
        Dim dr2() As System.Data.DataRow
        dr2 = Me.PositionsDataSet.Positions.Select("ReportsToPositionID = " & Row("PositionID").ToString)
        If dr2.Count > 0 Then
            AddSubNodes(NewNode)
        End If
    Next
End Sub

Good work. This is yet another pattern - populate each node with individual repeated calls to the database. This is probably the slowest method but for small amount of nodes i.e. a couple of hundred) it's probably fine. It may not scale well so if you think the tree is going to grow over time, be aware that performance will reduce over time. — Nick.Mc, Dec 21 '15 at 00:36
Thanks, At this stage there are only 50 or so records and i don't think it will grow much over the next 10 years. — Stephen Pefanis, Dec 21 '15 at 23:00

Populating a Tree list from database

3 Answers3