Wednesday, June 11, 2008

Dynamically Loading Data in TreeView Control

This article explains how to load data dynamically i.e. on runtime in TreeView Control. In this article we will discuss,
  • How to Store custom User Information with each Node of TreeView Control
  • Dynamically adding Nodes on Runtime

Level: Intermediate

Knowledge Required:
  • TreeView Control
  • Table
  • SQL Server Stored Procedure
  • ADO.net
  • DataTable
  • Inheritance

Description:
Sometimes we face a scenario in which we need to display the Data in TreeView control, in such a way that we do NOT load the whole Tree in one go, instead we load only root items first, and when user expands a node, then we load the child items of that particular node and add them underneath.

I am going to divide this solution in 4 phases
  1. Setup Database (an example)
  2. Setup Application to Database Connectivity
  3. Setup Custom TreeNode Class
  4. Putting it altogether
If you can understand the initial 2 phases then you can directly jump to phase 3.

Phase 1: Setup Database (an example)
We will create a table first, for example,

tbl_SearchEngineDirectory
  • Category_ID (primary key)
  • Category_Name
  • CategoryParent_ID (null able, foreign key, linked to Category_ID of this table)

So the above table is to store the Directory Structure of a Search Engine. For the Root entries we will store NULL in CategoryParent_ID column. Now we will create a stored procedure in Database which will return the Categories by supplying its Parent ID,

CREATE PROCEDURE GetCategory
@CategoryParent_ID int = NULL
AS
BEGIN
SET NOCOUNT ON
;

SELECT *
FROM tbl_SearchEngineDirectory
WHERE (CategoryParent_ID = @CategoryParent_ID) OR
(@CategoryParent_ID IS NULL AND CategoryParent_ID IS NULL);
END

Note that in above procedure if we pass NULL then it will return only the Root Categories.

Phase 2: Setup Application to Database Connectivity
Now we will implement this procedure in our Data Access Layer Class and for example have created a Function which will return the DataTable as,

Public Function GetCategory(ByVal CategoryParent_ID As Nullable(Of Integer)) As DataTable

(I am NOT discussing the internal code of above function as it is beyond the scope of this topic.)

Phase 3: Setup Custom TreeNode Class
This is one of the main steps since we will face an issue,

How are we going to Recognize which Node is expanded through which we can load the Children of that particular Node.

For this purpose we will create our own TreeNode class which is actually inherited from the same class but we will expand our properties with it as,

Public Class CategoryNode
Inherits TreeNode

Private _Category_ID As Integer
Private _Category_Name As String
Private _CategoryParent_ID As Nullable(Of Integer)

Public Property Category_ID() As Integer
Get
Return Me._Category_ID
End Get
Set
(ByVal value As Integer)
Me._Category_ID = value
End Set
End Property

Public Property Category_Name() As String
Get
Return Me
._Category_Name
End Get
Set
(ByVal value As String)
Me._Category_Name = value
End Set
End Property

Public Property CategoryParent_ID() As Nullable(Of Integer)
Get
Return Me._CategoryParent_ID
End Get
Set
(ByVal value As Nullable(Of Integer))
Me._CategoryParent_ID = value
End Set
End Property


Public Sub New(ByVal iCategory_ID As Integer, ByVal sCategory_Name As String)
Me._Category_ID = iCategory_ID
Me._Category_Name = sCategory_Name
Me._CategoryParent_ID = Nothing
End Sub

Public Sub New(ByVal iCategory_ID As Integer, ByVal sCategory_Name As String, ByVal iCategoryParent_ID As Integer)
Me.New(iCategory_ID, sCategory_Name)
Me._CategoryParent_ID = iCategoryParent_ID
End Sub

Public Overrides Function ToString() As String
Return Me._Category_Name
End Function
End Class

Phase 4: Putting it altogether
We will load each node in such a way that each Node will have a Temporary Child node for the first time. This is because we do NOT know whether any node has children OR not, and we want to display a Plus Sign with it. If we don’t do this then the node will never be able to expand since it has NO children. So whenever a node is expanded first we will remove the Temporary Node and then will add its Children, and if NO child exists in Database then we do nothing, and since the Temporary Node is already deleted therefore the Plus sign will also be removed.


We know that whenever a Node is expanded in the TreeView control 2 events are triggered,

1) BeforeExpand
2) AfterExpand

We can use both events, here I am going to use the 2nd event. In the handler of this event we will be getting the Node which is expanded. Here is the full code for Form1

Public Class Form1
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Call Me.FillParentCategories()
End Sub

Public Sub FillParentCategories()
Dim tblCat As DataTable
' Get Parent/Root Nodes only
tblCat = CateogryDataAccessClass.GetCategory(Nothing)
' now add them in TreeView
Me.AddCategories(Me.TreeView1.Nodes, tblCat)
' dispose the DataTable
tblCat.Dispose()
End Sub

' Method: AddCategories()
' Description: This method adds the Categories in the given NodeCollection
' Parameters:
' NodeCollection - Collection to be used to add Categories
' Categories - DataTable in which Categories are loaded

Private Sub AddCategories(ByRef NodeCollection As TreeNodeCollection, ByRef Categories As DataTable)
For Each r As DataRow In Categories.Rows
Dim nodCategory As CategoryNode
' if this is the Root element
If r.IsNull("CategoryParent_ID") Then
nodCategory = New CategoryNode(r("Category_ID"), r("Category_Name"))
Else
nodCategory = New CategoryNode(r("Category_ID"), r("Category_Name"), r("CategoryParent_ID"))
End If
' adding a Temporary Node
nodCategory.Nodes.Add("Loading...")
' Now add this node in the given collection
NodeCollection.Add(nodCategory)
Next
End Sub


Private Sub TreeView1_AfterExpand(ByVal sender As Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView1.AfterExpand
Dim nodFirst As TreeNode
nodFirst = e.Node.Nodes(0)
' if First Node is NOT the Category Node then
' it means we haven't filled this Node yet

If Not TypeOf nodFirst Is CategoryNode Then
' nodFirst is the temporary node we will remove it
nodFirst.Remove()
' now we are going to load the children
Dim nodCategory As CategoryNode
' first cast the expanded node into our Category node
' so we can check its ID
nodCategory = CType(e.Node, CategoryNode)
Dim tblCat As DataTable
' load its children
tblCat = CateogryDataAccessClass.GetCategory(nodCategory.Category_ID)
' now add them in TreeView
Me.AddCategories(e.Node.Nodes, tblCat)
' dispose the DataTable
tblCat.Dispose()
End If
End Sub
End Class

Summary:
  • On Load event we have loaded the Parent/Root Nodes and have added a Temporary Node with each node so the Plus Sign should be displayed.
  • Whenever a node is expanded:
    • First we check, have we loaded its children? (see code)
    • If NO then we load the children from database and add them in the same way i.e. also add a Temporary node with each node.

5 comments:

Anonymous said...

Thanks for the post. I'm trying it out and I'm wondering where does the 'CateogryDataAccessClass' come from? Is that a seperate class to create or is this a library you are brining in? Thanks.

Arsalan Tamiz said...

This class is actually the Data Access Layer, which I have NOT mentioned (because it is out of the scope). Simply in this class,

1) Private Connection String variable
2) Public function GetCategory returns the DataTable having Categories loaded from database
3) A constructor (New method) to initialize the Connection String

Internal coding for GetCategory can be as,

1) Create a SQLConnection
2) Create a SQLCommand
3) Create a SQLDataAdapter
4) Create a DataTable
5) Initialize SQLConnection
6) Initialize SQLCommand
7) Initialize SQLDataAdapter
8) Initialize DataTable
9) Open SQLConnection
10) Setup SQLCommand Parameters (to call the Stored Procedure mentioned in this Post)
11) Set SQLCommand in SQLDataAdapter
12) Fill the DataTable using SQLDataAdapter
13) Close SQLConnection
14) Return the DataTable

Anonymous said...

Thanks for your help! I'm getting some errors (I've never tried this with Treeviews so I'm not sure is there is some thing I need to import or a different class to use).

The errors I'm getting are:

System.Windows.Forms.TreeViewEventArgs
Error: Type is not defined

TreeView1.AfterExpand
Error: Cannot be found.

nodFirst.Remove()
Error: Remove is not a member of 'System.Web.UI.WebControls.TreeView'

nodCategory.Nodes.Add("Loading...")
Error: Nodes is not a member of CategoryNode

Arsalan Tamiz said...

This blog focuses the Windows Forms (Windows Application) platform. I think you are trying with ASP.net, which works in a different way.

Arsalan Tamiz said...

See

Dynamically Loading Data in TreeView control ASP.net