Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-26
4
Medium Priority
?
1,441 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
Comment
Question by:janmarini
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 

Expert Comment

by:sukhdev
ID: 12418694
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
ID: 12419028
Did you ORDER BY fieldname the Database? Or is this just a Treeview problem.

Regards
Shaztronics
0
 
LVL 1

Accepted Solution

by:
jameskearney earned 2000 total points
ID: 12419866

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
ID: 12600910
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

636 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