facebook  linkedin  Twitter  skype  Rss googlePlus

Populating a .Net Treeview control from a parent-child relationship db table.

May 03 2004
4717

In this tutorial we'll describe how to populate a treeview control on a
Windows form using VB.Net. This code is useful where you have a parent child relationship contained within the same table. For example say you have the following table structure:

You can create this parent-child relationship in a database table.

 

For example, the following table structure in a database:

 


RecordID

(autonumber)
ParentID DisplayName
1 0 Topic 1
2 0 Topic 2
3 1 RE: Topic 1
4 1 RE: Topic 1
5 2 RE: RE: Topic 1
6 2 RE: RE: Topic 1

You can see from the above table that we have several records, some of
the records have a 0 as a ParentID, meaning this is a top level parent
record, then other records do have a value other than 0 in the ParentID, meaning they are children of the record that has a matching ID in the table.
This structure is very versatile, in that you can have unlimited child
records using this structure, thus allowing you to create as many nests or  branches you wish.

In the following routine we have a recursive sub routine which calls itself until there are no more records. The purpose of this is to populate our treeview control with the parent child records.

First in the Form_Load routine we create a root node for our tree and
pass this root node and parent id of the record, in this example we'll pass  the number 0, which will give us all the parent level nodes in the table.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    TreeView1.Nodes.Clear()
    TreeView1.Nodes.Add(New TreeNode("ASP.NetSearchDNN"))
    Dim tNode As New TreeNode
    tNode = TreeView1.Nodes(0)
    PopulateTreeView(0, tNode)
    tNode.Expand()
End Sub

Then in PopulateTreeView routine we pass the parent id of the record and the
node of the tree that we are currently in. This routine will call itself to
see if there are any child records of the record we're currently in.
   
Private Sub PopulateTreeView(ByVal inParentID As Integer, ByRef inTreeNode As TreeNode)
       Dim DSNASPSearch As DataSet
       Dim CNASPSearch As New SqlClient.SqlConnection("Server=servername;Database=dnn;uid=sa;pwd=;")
       Dim DACategories As New SqlClient.SqlDataAdapter("SELECT CategoryID, CategoryName, ParentID, " _
           "SiteCount FROM ASPSearch_Categories Where ParentID = " & inParentID, CNASPSearch)
       DSNASPSearch = New DataSet
       CNASPSearch.Open()
       DACategories.Fill(DSNASPSearch, "ASPSearch_Categories")
       'Close the connection to the data store; free up the resources
       CNASPSearch.Close()
       Dim parentrow As DataRow
       Dim ParentTable As DataTable
       ParentTable = DSNASPSearch.Tables("ASPSearch_Categories")
       For Each parentrow In ParentTable.Rows
           Dim parentnode As TreeNode
           'we'll provide some text for the tree node.
           Dim strLabel As String = parentrow.Item(1) & " (" & parentrow.Item(3) & ")"
           parentnode = New TreeNode(strLabel)
           inTreeNode.Nodes.Add(parentnode)
           'set the tag property for the current node. This comes in useful if
           'you want to pass the value of a specific record id.
           'since the tag value is not visible, in the TreeView1_AfterSelect event
           'you could pass the value to another sub routine, for example:
 'FillDataGrid(TreeView1.SelectedNode.Tag)
 
           parentnode.Tag = parentrow.Item(0)
 'call the routine again to find childern of this record.
           PopulateTreeView(parentrow.Item(0), parentnode)
       Next parentrow
   End Sub
   

Continue on to Part II - Populating the Grid Control from the Treeview Node Selection.


 

About the Author, Patrick Santry

Patrick Santry, has two decades of experience in enabling businesses to take advantage of the digital landscape. A well rounded experience in technology, and business is what sets me apart from the rest of the pack. When it comes to an overall digital strategy my experience is impressive.

BS in Computer Information Systems. Four time recipient of the Microsoft MVP Award, and author of several books and magazine articles on digital technologies.


blog comments powered by Disqus

 

Thank you for visiting our site, before you leave, please visit some of the areas or information you may have missed.