PART 1:
This article expands on recursive subroutines. We will be populating a VB.Net treeview control in a Windows form with records from a database table containing a parent-child relationship.
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
In this particular application the results of the treeview control would
look like this:
