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.

How to Get Multiple Rows from Database using Comma Separated IDs (Primary Keys in Delimited String)

Level: Intermediate

Knowledge Required:

• T-SQL
• SQL Server Stored Procedure
• SQL Server User Defined Table Functions

Description:
Sometimes it is required that we need to send more than 1 primary keys to the Database and get the Rows from Table. For example, we want to get all the Rows in which Primary Key = 1, 23, 66 and 99. For this purpose we can create a Dynamic SQL Query in our Application and then execute it as,

SELECT * FROM tbl_SomeTable WHERE PrimaryKey IN (1, 23, 66, 99)

But if we have done all our work using Stored Procedures then we need to execute the same stored procedure 4 times.

CREATE PROCEDURE dbo.GetSomeTableRow
    @PrimaryKey int
AS
Begin
    SET NOCOUNT ON;

    SELECT *
    FROM tbl_SomeTable
    WHERE PrimaryKey = @PrimaryKey;
End

Now we want to send the IDs to this Stored Procedure in one go. We can achieve this by creating a Delimited String, e.g.: Dim sIDs As String sIDs = "1, 22, 33, 99" Then we will create a stored procedure which will accept this Delimited String and return rows. But before creating this Stored Procedure we will first create a User Defined Table Function which will accept the Delimited String and Return the Table having 1 int Field/Column. This function will extract out each integer value from String and add it in a Table then return that Table.

Following is the Script of this function:

CREATE FUNCTION [dbo].GetIntTableFromDelimitedString
( 
    @DelimitedString varchar(max), 
    @Delimiter varchar(10)
)
RETURNS
@ReturnTable TABLE(
    IntValue int
)
AS
Begin
    DECLARE @EachItem varchar(255);
    DECLARE @DelimiterPos int;
    DECLARE @DelimiterPosPrv int;

    SET @DelimitedString = @DelimitedString + ',';
    SET @DelimiterPosPrv = 1;
    SET @DelimiterPos = CHARINDEX(@Delimiter, @DelimitedString, 1);

    WHILE @DelimiterPos > 0
    BEGIN
        SET @EachItem = LTRIM(RTRIM(SUBSTRING(@DelimitedString, @DelimiterPosPrv, @DelimiterPos - @DelimiterPosPrv)));
        IF @EachItem <> ''
            INSERT INTO @ReturnTable(IntValue)
                VALUES(CAST(@EachItem AS int));  
        SET @DelimiterPosPrv = @DelimiterPos + 1;
        SET @DelimiterPos = CHARINDEX(@Delimiter, @DelimitedString, @DelimiterPosPrv);
    END

    Return;
End

Now we can change our Stored Procedure as

CREATE PROCEDURE dbo.GetSomeTableRows
    @PrimaryKeys varchar(max)
AS
Begin
    SET NOCOUNT ON;

    SELECT *
    FROM tbl_SomeTable
    WHERE PrimaryKey IN (
                        SELECT IntValue 
                        FROM dbo.GetIntTableFromDelimitedString(@PrimaryKeys, ',')
                        );
End

How To Increase Performance of Loading Large Data from Database (Part-1)

Issue: While loading Large amount of Data from Database:

• Connection Timeout occurs
• Application gets Hang/Stuck
• Application takes too much time to load data

Level: Advanced

Knowledge Required:
Part-1:
SQL Server 2005
Stored Procedures

Description:
First of all this is NOT a good practice to load ALL THE DATA from a Table into memory. A Human being cannot process/analyse all this huge data in a glance, instead we usually interested in a short information. For example we have a Table that contains all the Contact Numbers living in the City along with there Addresses and Names. Now we usually want to extract out one particular number or one particular name, NOT all the names and numbers.

But still we (developers) are forced to create such applications which give the user to access all the data by SCROLLING up or down.

So in this Article I will show you how to increase this performance by the following ways:

1. Implement Paging in SQL Server 2005 (Database Level)
2. Implement Paging using DataAdapter (Application Level)
3. Use the Virtual Mode of DataGridView Control

In this part I will discuss the 1st one.
1) Paging in SQL Server 2005
Paging means we divide our huge data into number of small chunks. We will display 1 page at a time and will provide next previous buttons so user can navigate forward and backword. I think this is the fastest way to load data. We will send the Page Number and Number of Records Per Page to Stored Procedure, which will return only that part of data. For example: I have created a Table tbl_LargeData in which there are 3 Fields:

• Row_ID [primary key, int, Identity Column]
• SomeData [varchar(255)]
• InsertDateTime [DateTime, Default = GetDate()]

In this table I have put some fake data with Total 1,000,000 Rows.

Now this is a bit Large Data (NOT that much Large). Now to test, I created a project in VB and simply loaded the Data (in a DataTable) by executing the Query

SELECT * FROM tbl_LargeData

I have 1 GB RAM and AMD Athlon 64 Processor 3500+.

Unbound DataTable: It took 28 Seconds to fill the DataTable.
Bound DataTable: I bind that DataTable to BindingSource, it took 51 seconds, almost double.

BindingSource also increases time to Fill a DataTable, becuase BindingSource itself keeps another Cache of Data for Sorting and Filtering Purpose.

The Loading of Data can significantly increase Time, if:

• System has low RAM and Processing Speed
• Other Applications are also running on Client PC
• Database Server is NOT on same machine, it is somewhere on the LAN
• Client PC is connected to server using low Band Width

Therefore to Implement Paging I have created 2 Stored Procedures in Database:

1. GetLargeDataPageInfo
2. GetLargeDataWithPaging

The GetLargeDataPageInfo stored procedure has a Parameter @PageSize int. User has to provide the Page Size (i.e. Number of Records Per Page) then this stored procedure will return

Total Records	Total Pages
1000000	50000

GetLargeDataWithPaging stored procedure is the main stored procedure which Returns the Particular Page of Data. The script is:

CREATE PROCEDURE [dbo].[GetLargeDataWithPaging]
    @PageNumber int,
    @PageSize int
AS
BEGIN
    SET NOCOUNT ON;

    -- For Paging we have used the ROW_NUMBER() function
    -- which operates on Ordering of Column

    DECLARE @RowStart int;
    DECLARE @RowEnd int;

    -- Calculate the first row's Index
    -- and Last Row's Index
    SET @RowStart = ((@PageNumber - 1) * @PageSize) + 1;
    SET @RowEnd = @RowStart + @PageSize - 1;
 
    SELECT Row_ID, SomeData, InsertDateTime
    FROM (
        SELECT    ROW_NUMBER()
                OVER (
                    ORDER BY Row_ID
                    ) AS Row_Num,
                *
        FROM    tbl_LargeData
        ) AS DerivedTable
    WHERE Row_Num Between @RowStart AND @RowEnd;
END

Source Code:
LargeDataWithSQLPaging.rar
Database:
TemporaryDB.rar
Note: Execute the ScriptToInsertBulkData.sql file (ziped inside the TemporaryDB.rar) to add the Fake Data

Update (12-May-2008): Point #2 changed to "Implement Paging using DataAdapter" which was "Implement Paging using DataReader"