Save treeview structure in database

[swetham]

I have created a treeview in vb.net. The treeview has the following features,
1) Grows dynamically
2) No limit on child nodes

Treeview has a project name as ROOT and the child nodes grows dynamically (upto n levels). In this case how to create a database to store the project details (tree structure).

Can anyone please help me out (database structure)?

Thnaks,
Swteha

 

[jebenson]

Use one database table. Each record has a field called 'ParentNode' (or something like that). This field will hold the record ID for that node's parent node. The ROOT node's record will have NULL (or blank) for the ParentNode field. So, to get the ROOT node:

"Select * from TreeNodeTable where ParentNode Is Null", or "Select * from TreeNodeTable where ParentNode = '' "

Add the ROOT node to the TreeView, then get all the nodes that have the ROOT node as their parent:

"Select * from TreeNodeTable where ParentNode = '" & ROOT_Node_ID & "'"

(Note: build the SQL as appropriate for your data.)

Loop through these records, and add each node under the ROOT node. Then loop through them again, and for each get its child nodes:

"Select * from TreeNodeTable where ParentNode = '" Current_Node_ID".


Just keep looping through the nested levels until the TreeView is complete.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[MrDataGuy]

Building on Jebenson's answer.
1) Consider adding a "Level" Field. Top level 1: The first branches 2: etc.

2) Consider adding an "Order" field so that the branches are in the order that are wanted/needed.



Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[RiverGuy]

Why add a level field? ParentID will suffice to allow the treeview to be populated. If level is added, then it's another thing that needs to be maintained. Level can be derived easily. The TreeNode object even has a .Level property.

 

[MrDataGuy]

Why add a level field"
Pro: When browsing the data easy to understand. An easy way to 'compare' information on the same level even thou the information is on different branches.

Cons: "another thing that needs to be maintained" Agreed. And of course what should happen if the level and the data relationships do not match.

In my defense I did say "consider" But Riverguy is correct (IMO) having level data is not critical, and if it is not helpful in your situation, can be a real negative. (And even the order is not critical but might be helpful.)

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[swetham]

Hi Jebenson,

For example i have a treeview as follows,

Project
Division1
SubDivision1
Type1
Division2
Subdivision2
Division3

Now, can you please explain me the database structure basing on the above example and how the data gets stored in the above case?

Thankyou,
Swetha.

 

[jebenson]

Ok, assuming that the Project node is the root node of the TreeView, here's how I'd do it. (Note: this is mostly pseudocode, just to show the logic.)

The thing about treeviews (or any hierarchical data structure) is that they can be processed most easily (IMHO) using recursion. So, I'm going to make a sub that 1)process the node passed to it and 2)checks that node for children and if they are found, passes the first child node to the sub itself.

I'm not sure if I'd just update the existing data, or clear out the table each time and insert the treeview node data "fresh" on each save. I'd probably go with clearing out the table before each save, so:

Delete from TreeNodeData

Now that the table is empty, save the Project node to the database.

Here's the Sub to save the node data:

    Private Sub SaveTreeviewData(ByVal StartNode As TreeNode)



        If StartNode.Nodes.Count > 0 Then
            For Each n As TreeNode In StartNode.Nodes
                SaveTreeviewData(n)
            Next
        Else
            'Save tree node data to database

            'First, get the IDNum of the current node' parent

            Dim ParentNodeID As String

            If StartNode.Parent IsNot Nothing Then
                'node has a parent node, so get that ID
                'assuming that the node's ID is stored in the .Tag property
                ParentNodeID = StartNode.Parent.Tag
            Else
                'no parent node, so this is a root node
                ParentNodeID = ""
            End If

            'Use ParentNodeID to populate the ParentNode field

            'Insert Into TreeNodeTable (IDNum, ParentNode, Field1, Field2, ...) VALUES (@IDNum, @ParentNode, @Field1, @Field2, ...)
        End If
    End Sub

To call this sub, do this:

    For Each n As TreeNode In TreeView1.Nodes
        SaveTreeviewData(n)
    Next

So, this code goes through each root node and passes it to SaveTreeviewData. That sub checks if the node passed in has children. If it does, the sub loops through the children, calling SaveTreeviewData and passing each child node.

Using recursion like this, it doesn't matter how deep the nodes are nested.

To get the data out of the database and back into the treeview, use something similar. First, get all root nodes (i.e., all records in the database that have a blank value for ParentNode). Using a recursive procedure call, you can loop through all the root records and fill out the nodes in the treeview.

I'll leave it to you to write the code to read the database and populate the treeview (hint: it's very similar to the code to save the treeview data).

If you run into any problems or questions, please post here and we'll help.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[swetham]

Thanks a lot, you have explained it very clearly.. I will try it and post here if any problem comes further.