Solved

Dynamic TreeView from DB / Database - best logic, VB.net Windows Form

Posted on 2004-10-26
1,400 Views
Last Modified: 2012-08-13
Hello,

I have an application that requires the use of a TreeView control.  I am able to apply the MS TreeView to my windows form and can get it to do the basic stuff.  However, my users need to be able to add new nodes via updating an SQL database (not interactively - via a Stored Procedure, or manual updating), and the new root/parent/child/grandchild ends up being the last record in the table.  I run an SQL statement to sort everything by ParentID, Level and Sequence.  Still, I am having a difficult time in creating the most easiest to use structure, and I am finding that I am having to jump through hoops in my programming logic to get it to work.  I strongly feel that there must be an easier way, but I'm just not able to see it (I think I might be stuck on my last project that did all of this via VB 6 and user controls - that compared to this seemed easy).  I would greatly appreciate any advice on how I might explore other alternatives to the path I am following.

Thank you,
Jan

Table Structure
iID, sName, iParentID, iLevel, iSequence

Example:

iID      sNAME                iPARENTID      iLEVEL      iSEQUENCE
1      Batch Report Group            0      0      1
2      Machine Report Group            0      0      2
3      Management Reports            0      0      3
4      Performance Reports            0      0      4
5      Misc Reports            0      0      5
6      Find                            0      0      6
7      Batch List Pr                      1      1      1
8      Batch Status Pr                      1      1      4
9      Machine Batch Pr                      2      1      1
10      Mach Maintenance Pr                      2      1      4
12      BatchSize Summary                      3      1      1
.
.
.
To build the tree in VB.Net:
This is where I get stuck - and need a breath of fresh air.
- I cannot find a way to sort the table in a top-down structure (Root/Parent/etc.) without the user having to manually redo the sort (a new - and unwanted process/field) each time something new is added to the table.
- I find that I can not do this in a single step programmaticaly
- For my first pass through I am using SQLDataReader - and can add new levels to the root
- I then call another query to obtain the children - but can't get past this point - with the structure I have.  

It's frustrating because if I had a table that I could just read through this would be easy - but that's not going to happen.  Need a new perspective here - I appreciate your help and experience.  If you would llike to see my code attempts (some of my commented out code came close - but didn't do the the job), I'll include it.  But I am thinking I really do need a fresh approach.

Jan



0
Question by:janmarini
    4 Comments
     

    Expert Comment

    by:sukhdev
    Hi Jan,

    Try adding all your tree nodes first and then do this

    myTreeControl.sorted=True

    regards,
    sukhdev.


    0
     
    LVL 1

    Expert Comment

    by:Shaztronics
    Did you ORDER BY fieldname the Database? Or is this just a Treeview problem.

    Regards
    Shaztronics
    0
     
    LVL 1

    Accepted Solution

    by:

    hi there, I do something similar and have pasted the code in case it helps...

    Basically I fill a dataset with the required information - and when it is filled, I fire an event, which then delegates to the updateTreeView sub you can see below...

    ...then using the dataset that contains the data, it adds nodes to the tree, checking what the parentid is - if a node has a parent, and its parent has not been added to the route node as of yet - then it uses the addParentAndChild sub - which is a recursive sub that will add parents before adding children...

    using this method you can pass a dataset that contains the tree node contents in one column, and the parentid in another column, and it will add everything correctly, and will be sorted alphabetically...

    hope that helps (theres probably a lot of redundant code in there, because I am doing other things that you do not need to do, such as filling hashtables and the like - but hopefully you can extract what you need).

    james kearney

    code follows

    --8<--

            Private Sub updatetreeview()
                Try
                    updateGroupTabCheckedList()

                    TreeView_GroupsTree.BeginUpdate()
                    TreeView_GroupsTree.Sorted = True

                    TreeView_GroupsTree.Nodes.Clear()
                    If Not TreeView_GroupsTree.TopNode Is Nothing Then
                        TreeView_GroupsTree.TopNode.Remove()
                    End If

                    Dim rootNode = New TreeNode("Groups")
                    TreeView_GroupsTree.Nodes.Add(rootNode)
                    'Dim theDelegate As delegate_treeViewAdd = New delegate_treeViewAdd(AddressOf TreeView_GroupsTree.Nodes.Add)
                    'Dim objects() As Object = {rootNode}
                    'TreeView_GroupsTree.Invoke(theDelegate, objects)

                    Dim therows As DataRowCollection = SqlDataSet_GroupTree.Tables.Item(0).Rows

                    fillTreeFromRows(therows)
                    'For index As Integer = 0 To therows.Count - 1
                    '    Dim row As DataRow = therows.Item(index)
                    '    recursiveChildFiller(row)
                    'Next

                    TreeView_GroupsTree.ExpandAll()

                    'set the node that was previously selected as selected (if one was at all - if not, then set the root node as selected).
                    If groupsShowing Then
                        Try
                            If Not lastGroupTreeNodeGroupID = -1 AndAlso groupTreeNodeIDHash.ContainsValue(lastGroupTreeNodeGroupID) Then
                                Dim node As TreeNode = groupTreeNodeIDHash(lastGroupTreeNodeGroupID)

                                TreeView_GroupsTree.SelectedNode = node
                                TreeView_GroupsTree.HotTracking = True
                                TreeView_GroupsTree.CheckBoxes = False
                                If groupsShowing Then TreeView_GroupsTree.SelectedNode.EnsureVisible()

                                LastGroupTreeNode = node
                                lastGroupTreeName = node.Text

                            ElseIf Not lastGroupTreeName Is Nothing And Not lastGroupTreeName.Trim.Equals("") And _
                                    Not lastGroupTreeName.Trim.ToUpper.Equals("GROUPS") And _
                                    HashGroupNameID.Contains(lastGroupTreeName) And _
                                    groupTreeNodeIDHash.Contains(CType(HashGroupNameID(lastGroupTreeName), Integer)) Then

                                Dim id As Integer = HashGroupNameID(lastGroupTreeName)
                                Dim node As TreeNode = groupTreeNodeIDHash(id)

                                TreeView_GroupsTree.SelectedNode = node
                                TreeView_GroupsTree.HotTracking = True
                                TreeView_GroupsTree.CheckBoxes = False
                                If groupsShowing Then TreeView_GroupsTree.SelectedNode.EnsureVisible()

                                LastGroupTreeNode = node
                                lastGroupTreeName = lastGroupTreeName
                                lastGroupTreeNodeGroupID = id
                            Else
                                TreeView_GroupsTree.SelectedNode = TreeView_GroupsTree.Nodes(0)
                                TreeView_GroupsTree.HotTracking = True
                                TreeView_GroupsTree.CheckBoxes = False
                                If groupsShowing Then TreeView_GroupsTree.SelectedNode.EnsureVisible()

                                LastGroupTreeNode = TreeView_GroupsTree.Nodes(0)
                                lastGroupTreeNodeGroupID = 0
                                lastGroupTreeName = ""
                            End If
                        Catch ex As Exception
                            TreeView_GroupsTree.SelectedNode = TreeView_GroupsTree.Nodes(0)
                            TreeView_GroupsTree.HotTracking = True
                            TreeView_GroupsTree.CheckBoxes = False
                            If groupsShowing Then TreeView_GroupsTree.SelectedNode.EnsureVisible()

                            LastGroupTreeNode = TreeView_GroupsTree.Nodes(0)
                            lastGroupTreeNodeGroupID = 0
                            lastGroupTreeName = ""
                        End Try

                        If Not currentContactListGroupID = lastGroupTreeNodeGroupID Then
                            sql_getContactsByGroup(lastGroupTreeNodeGroupID)
                        End If
                    End If
                    TreeView_GroupsTree.EndUpdate()
                Catch ex As Exception
                    MessageBox.Show(ex.Message & vbCr & vbCr & ex.StackTrace)
                End Try
                TreeView_GroupsTree.ExpandAll()
            End Sub




            ' fill the groups tree with the contents of the dataset, handle infinite children
            Private Sub fillTreeFromRows(ByVal rows As Data.DataRowCollection)
                groupTreeNodeIDHash.Clear()

                For Each row As DataRow In rows
                    Dim node As TreeNode = New TreeNode(CType(row.Item("GroupName"), String))
                    Dim objects() As Object = {node}

                    If row.Item("ParentID").GetType.ToString.ToUpper.Trim.Equals("SYSTEM.DBNULL") Then
                        ' only add it if it is not already added
                        If Not groupTreeNodeIDHash.Contains(CType(row.Item("ID"), Integer)) Then
                            Dim theDelegate As delegate_treeViewAdd = New delegate_treeViewAdd(AddressOf TreeView_GroupsTree.Nodes(0).Nodes.Add)
                            TreeView_GroupsTree.Invoke(theDelegate, objects)
                            groupTreeNodeIDHash(CType(row.Item("ID"), Integer)) = node
                        End If
                    Else
                        Dim parentID As Integer = CType(row.Item("ParentID"), Integer)
                        If groupTreeNodeIDHash.Contains(parentID) Then
                            ' the parent is already added - so just add the child to it (if it hasnt been added in the recursive function)
                            If Not groupTreeNodeIDHash.Contains(CType(row.Item("ID"), Integer)) Then
                                Dim parentnode As TreeNode = groupTreeNodeIDHash.Item(parentID)
                                Dim theDelegate As delegate_treeViewAdd = New delegate_treeViewAdd(AddressOf parentnode.Nodes.Add)
                                TreeView_GroupsTree.Invoke(thedelegate, objects)
                                groupTreeNodeIDHash(CType(row.Item("ID"), Integer)) = node
                            End If
                        Else
                            addParentAndChild(rows, row)
                        End If
                    End If
                Next
            End Sub




            ' recursive funtion used by fillTreeFromRows above to add a parent before adding a child, when a child needs to be added
            ' but its parent has not yet been added
            Private Sub addParentAndChild(ByVal rows As DataRowCollection, ByVal childrow As DataRow)
                Dim parentid As Integer = CType(childrow.Item("ParentID"), Integer)
                For Each therow As DataRow In rows
                    Dim id As Integer = CType(therow.Item("ID"), Integer)
                    If id = parentid Then

                        ' check if this parent has a parent - if it has - add the parent with this set to the child
                        If therow.Item("ParentID").GetType.ToString.ToUpper.Trim.Equals("SYSTEM.DBNULL") Then
                            'add the parent if it hasnt already been added - otherwise just add the child to the parent
                            Dim node As TreeNode
                            If Not groupTreeNodeIDHash.Contains(CType(therow.Item("id"), Integer)) Then
                                node = New TreeNode(CType(therow.Item("GroupName"), String))
                                Dim thedelegate As delegate_treeViewAdd = New delegate_treeViewAdd(AddressOf TreeView_GroupsTree.Nodes(0).Nodes.Add)
                                Dim objects() As Object = {node}
                                TreeView_GroupsTree.Invoke(thedelegate, objects)
                                groupTreeNodeIDHash(CType(therow.Item("ID"), Integer)) = node
                            Else
                                node = groupTreeNodeIDHash.Item(CType(therow.Item("id"), Integer))
                            End If
                            ' add the child
                            If Not groupTreeNodeIDHash.Contains(CType(childrow.Item("id"), Integer)) Then
                                Dim childnode As TreeNode = New TreeNode(CType(childrow.Item("GroupName"), String))
                                Dim newadddel As delegate_treeViewAdd = New delegate_treeViewAdd(AddressOf node.Nodes.Add)
                                Dim childobjects() = {childnode}
                                TreeView_GroupsTree.Invoke(newadddel, childobjects)
                                groupTreeNodeIDHash(CType(childrow.Item("ID"), Integer)) = childnode
                            End If
                        Else
                            ' found the parent - but it has a parent as well, so add the parent as a child first - and then add the child of this
                            addParentAndChild(rows, therow)
                            ' now that the parent has been added (with its parents) - we can add the child node to it
                            If Not groupTreeNodeIDHash.Contains(CType(childrow.Item("id"), Integer)) Then
                                Dim childnode As TreeNode = New TreeNode(CType(childrow.Item("GroupName"), String))
                                Dim parentnode As TreeNode = groupTreeNodeIDHash.Item(parentid)

                                Dim newadddel As delegate_treeViewAdd = New delegate_treeViewAdd(AddressOf parentnode.Nodes.Add)
                                Dim childobjects() = {childnode}
                                TreeView_GroupsTree.Invoke(newadddel, childobjects)
                                groupTreeNodeIDHash(CType(childrow.Item("ID"), Integer)) = childnode
                            End If
                        End If
                        Exit For
                    End If
                Next
            End Sub
    0
     
    LVL 7

    Author Comment

    by:janmarini
    Thank you all for your answers, I really appreciated them.  James, I was able to study your solution and use it as an excellent foundation to finally getting it all to work exactly how I wanted.  It will take me a little work to document what my end result looks like - a little different from yours, but I hope that both will help others facing the same challenge.  Your sample was the perfect mentor.  Thanks!

    Now my boss wants this (LOL) - OK - no complaints...OK - after reflecting on this for a moment...HELP!!:

    "I like this.  It has a clean appearance and is functional.  The only thing I might suggest we add is if we could right click and add to a favorites list and or on the selection and list the 5 most recent selections like:

    Most Recent
    Report 1
    Report 2
    Report 3

    My Favorites
    Report 1
    Report 2
    Report 3"

    If there's a will, there's a way :-)
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT Security CISA, CISSP & CISM Certification

    Master the advanced techniques required to protect network resources from external threats with the IT Cyber Security bundle. Built around industry best-practice guidelines, the IT Cyber Security bundle consists of three in-depth courses.

    This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
    Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
    This video Micro Tutorial is the second in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles a…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now