(untagged)

Create and Populate Treeviews Dynamically

Nagasai Mattegunta

0.00/5 (No votes)

31 Mar 2006

An article on Database Driven Dynamic multiple Treeviews

Download source files - 36 Kb

Scorecard

Introduction

In my recent project, a part of requirement is, to generate KPI score card. The KPI score card is a chart showing the KPI in tree like structures. All the KPIs are categorised and each category represents a tree. The tree hirarchy is defiend in database. This basically requires Treeviews to be dynamically generated based on the details in database.

I have been trying for resources on Treeview over Web. I was not able to find an exact sample of such kind. I was able to find articles on Treeviews added to web form at design time, and adding controls dynamically to web form seperately, But no example was illustrating dynamic treeview controls. Because of this I thought of uploading this article, which illustrates creating treeviews dynamically and databinding.

I am pretty sure that there is nothing new (which is not known) in this. This article is just a union of two kinds of articles found on web (Treeview and Adding Controls dynamically), Which may be a ready made solution for some people who my customise it for their use.

For this article I used VS 2003, SQL Server Database. This is a sample project in which i used Microsoft Treeview Control, whereas In my actual project I used a third party treeview control for more flexibility and better aesthetic view.

Database Preparation

The database for this project contains two tables 1. SC_Master 2. SC_Detail

The table structure and data are as follows. (Script for generating tables is included in Database folder of source code.)

SC_Master

SC_Master Table

SC_Detail

SC_Detail Table

Project Structure

Using the code

The core of the project is an User control "Scorecard.ascx" which creates and populates the treeviews. The main page which uses the user control is "ScorecardPage.aspx". First let us discuss about the code in User control

Function to retrive data from database:

#Region " Retrieve DataSet "

    Public Function Retrieve_DataSet(ByVal sSQL As String) As DataSet
        Dim cmdCommand As New OleDbCommand()
        Try
            Dim daAdapter As New OleDbDataAdapter()
            Dim dsDataSet As New DataSet()
            'Connection String should be changed suitably.

            Dim cnConnection As New OleDbConnection("Provider=SQLOLEDB;" & _ 
                 "Password=MyPwd;User ID=MyId;" & _ 
                 "Data Source=MyServer;Initial Catalog=SCORECARD;")

            cnConnection.Open()

            With cmdCommand
                .Connection = cnConnection
                .CommandType = CommandType.Text
                .CommandText = sSQL
            End With

            daAdapter.SelectCommand = cmdCommand
            dsDataSet.EnforceConstraints = False
            daAdapter.Fill(dsDataSet)
            dsDataSet.EnforceConstraints = True

            Return dsDataSet.Copy

        Catch err As Exception
            'Throw err

            Response.Write(err.StackTrace)
        Finally
            cmdCommand.Connection.Close()
        End Try
    End Function

#End Region

Code to Create and Populate Treeviews:

#Region " Generate Treeviews "

    Private Sub CreateRootnodes()

        Dim dbRow As System.Data.DataRow
        Dim ds As New System.Data.DataSet()
        Dim strSqlMain As String = "select m.SC_ID_NO as SC_ID_NO, " & _ 
            "m.SC_SHORT_DESC as SC_SHORT_DESC,m.SC_PARENT as SC_PARENT,"
        strSqlMain = strSqlMain & " sum(d.SC_ACTUAL) as SC_ACTUAL,"
        strSqlMain = strSqlMain & " sum(d.SC_PLAN) as SC_PLAN,"
        strSqlMain = strSqlMain & " sum(d.SC_ACTUAL - d.SC_PLAN) as SC_Better"
        strSqlMain = strSqlMain & " from SC_Master m, SC_Detail d"
        strSqlMain = strSqlMain & _
                     " where d.SC_ID = m.SC_ID_NO and m.SC_PARENT is NULL"
        strSqlMain = strSqlMain & " group by SC_ID_NO,SC_SHORT_DESC,SC_PARENT"
        strSqlMain = strSqlMain & " order by SC_ID_NO"

        ds = Retrieve_DataSet(strSqlMain)

        Dim i As Int32
        Dim tbl As New Table()
        Dim tblrow As New TableRow()

        For Each dbRow In ds.Tables(0).Rows
            Dim newTreeview As New TreeView()
            Dim tblcel As New TableCell()

            newTreeview.ShowLines = True

            Dim strSqlSub As String = "select m.SC_ID_NO as SC_ID_NO, " & _ 
                "m.SC_SHORT_DESC as SC_SHORT_DESC,m.SC_PARENT as SC_PARENT,"
            strSqlSub = strSqlSub & " sum(d.SC_ACTUAL) as SC_ACTUAL,"
            strSqlSub = strSqlSub & " sum(d.SC_PLAN) as SC_PLAN,"
            strSqlSub = strSqlSub & " sum(d.SC_ACTUAL - d.SC_PLAN) as SC_Better"
            strSqlSub = strSqlSub & " from SC_Master m, SC_Detail d"
            strSqlSub = strSqlSub & " where d.SC_ID = m.SC_ID_NO and (m.SC_PARENT='"
            strSqlSub = strSqlSub & dbRow("SC_ID_NO").ToString() & "' or m.SC_ID_NO='"
            strSqlSub = strSqlSub & dbRow("SC_ID_NO").ToString() & "')"
            strSqlSub = strSqlSub & " group by SC_ID_NO,SC_SHORT_DESC,SC_PARENT"
            strSqlSub = strSqlSub & " order by SC_ID_NO"

            buildTree(newTreeview, strSqlSub)
            tblcel.ID = dbRow("SC_ID_NO").ToString()
            tblcel.Controls.Add(newTreeview)
            tblcel.VerticalAlign = VerticalAlign.Top
            tblrow.Cells.Add(tblcel)
        Next dbRow
        tbl.Rows.Add(tblrow)
        tbl.ID = "TreeTable"
        Me.Controls.Add(tbl)
    End Sub

    Private Sub buildTree(ByRef treeview As TreeView, ByVal sql As String)

        Dim dbSubTreeRow As System.Data.DataRow
        Dim dsSubtree As New System.Data.DataSet()
        dsSubtree = Retrieve_DataSet(sql)
        dsSubtree.Relations.Add("NodeRelation", _
        dsSubtree.Tables(0).Columns("SC_ID_NO"), _
        dsSubtree.Tables(0).Columns("SC_PARENT"))

        Dim strNodeText As String

        For Each dbSubTreeRow In dsSubtree.Tables(0).Rows
            If (dbSubTreeRow.IsNull("SC_PARENT")) Then
                Dim newNode As TreeNode
                strNodeText = Trim(dbSubTreeRow("SC_SHORT_DESC").ToString())

                Dim j As Int32
                Dim addstr As String = ""

                For j = 1 To 40
                    addstr = addstr & " "
                Next
                strNodeText = addstr & "

" & strNodeText & "
" & Format(dbSubTreeRow("SC_Better"), Me.dfDecimal)

                newNode = CreateNode(strNodeText, "", True)
            'greenBackground,yellowBackground,redBackground

            'are strings which contain the style sheet.

                If dbSubTreeRow("SC_Better") > 0 Then
                    newNode.SelectedStyle = _
                      CssCollection.FromString(greenBackground)
                    newNode.DefaultStyle = _
                      CssCollection.FromString(greenBackground)
                    newNode.HoverStyle = _
                      CssCollection.FromString(greenBackground)

                ElseIf dbSubTreeRow("SC_Better") = 0 Then
                    newNode.SelectedStyle = CssCollection.FromString(yellowBackground)
                    newNode.DefaultStyle = CssCollection.FromString(yellowBackground)
                    newNode.HoverStyle = CssCollection.FromString(yellowBackground)

                Else
                    newNode.SelectedStyle = CssCollection.FromString(redBackground)
                    newNode.DefaultStyle = CssCollection.FromString(redBackground)
                    newNode.HoverStyle = CssCollection.FromString(redBackground)

                End If

                newNode.Expanded = True
                newNode.Expandable = ExpandableValue.Always
                treeview.Nodes.Add(newNode)
                PopulateSubTree(dbSubTreeRow, newNode)
            End If
        Next dbSubTreeRow
    End Sub

    Private Sub PopulateSubTree _
    (ByVal dbRow As System.Data.DataRow, _
    ByVal node As TreeNode)
        Dim childRow As System.Data.DataRow
        Dim strNodeText As String

        Dim j As Int32
        Dim addstr As String = ""
        For j = 1 To 40
            addstr = addstr & " "
        Next

        For Each childRow In dbRow.GetChildRows("NodeRelation")
            strNodeText = Trim(childRow("SC_SHORT_DESC").ToString())
            strNodeText = addstr & "

" & strNodeText & "
" & Format(childRow("SC_Better"), Me.dfDecimal)

            Dim childNode As TreeNode = _
            CreateNode(strNodeText, "", True)

            If childRow("SC_Better") > 0 Then
                childNode.SelectedStyle = CssCollection.FromString(greenBackground)
                childNode.DefaultStyle = CssCollection.FromString(greenBackground)
                childNode.HoverStyle = CssCollection.FromString(greenBackground)

            ElseIf childRow("SC_Better") = 0 Then
                childNode.SelectedStyle = CssCollection.FromString(yellowBackground)
                childNode.DefaultStyle = CssCollection.FromString(yellowBackground)
                childNode.HoverStyle = CssCollection.FromString(yellowBackground)

            Else
                childNode.SelectedStyle = CssCollection.FromString(redBackground)
                childNode.DefaultStyle = CssCollection.FromString(redBackground)
                childNode.HoverStyle = CssCollection.FromString(redBackground)

            End If

            node.Expanded = True
            node.Expandable = ExpandableValue.Always

            node.Nodes.Add(childNode)
            PopulateSubTree(childRow, childNode)
        Next childRow
    End Sub

    Private Function CreateNode _
    (ByVal text As String, ByVal _
    imageurl As String, ByVal expanded As Boolean) _
    As TreeNode
        Dim node As New TreeNode()
        node.Text = text
        node.ImageUrl = imageurl
        node.Expanded = expanded

        Return node
    End Function

#End Region

Code to initialize display of user control.

   Public Sub InitDisplay()
        Try
            CreateRootnodes()
        Catch err As Exception
            Response.Write(err.Message)
        End Try
    End Sub

Code to display User control on "ScorecardPage.aspx"

#Region " Show Controls "
    Public Sub ShowControls()
        Dim ucScoreCard As Scorecard = _
            Page.LoadControl("..\Components\Scorecard.ascx")
        ucScoreCard.ID = "ucScoreCard"
        Me.PlaceHolder1.Controls.Clear()
        PlaceHolder1.Controls.Add(ucScoreCard)
        ucScoreCard.InitDisplay()
    End Sub
#End Region

Call "ShowControls" method in the pageload event of "ScorecardPage.aspx".

Points of Interest

There are lot of things that can be done with treeview properties, which i did not touch in this article. I used style sheets to get the required backgrounds, which highlight the value of the element (negative, positive, and zero).

Comments

Please take the time to vote for this article and/or to comment about it.

History

03/31/2006 - Initial Version

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here