CODEWITHSUNDEEP
mysqlvb.nettreeview
James Stafford
James Stafford

Reputation: 1054

hierarchically populating treeview from mysql content

I am trying trying to build a treeview that populates using data from a mysql database. normally I would say this would be no problem except I am allowing user controlled root, parent, and child elements (the quantity may vary and naming can be erratic)

Example: assuming the database displays employees - but the user owns multiple companies with multiple departments (which he can add or remove) Like the following

Company ABC
  Accounting
    Employee A
    Employee B
  Marketing
    Employee D
    Employee F
  Operations
    Employee Z
  Human Resources
    Employee N
Company 123
  Road Crew
    Employee 1
    Employee 2
    Employee 3
  Dispatcher
    Employee 5

The method I came up for this is to do a running for statement and determine what category the item is (root, parent, or child). Then run a statement to add it to the treeview, rootitem, or parentitem appropriately.

Dim con As New MySqlConnection()
Dim adptr As New MySqlDataAdapter
Dim pagers As New DataTable
Dim Roots() As TreeNode
Dim Parents() As TreeNode
Dim Children() As TreeNode



        con.ConnectionString = "server=localhost;" _
& "user id=*****;" _
& "password=****;" _
& "database=****"
    adptr = New MySqlDataAdapter("SELECT * FROM pagers", con)
    Try
        adptr.Fill(pagers)
    Catch err As Exception
        Dim strError As String = "Exception: & err.ToString()"
    End Try
    If pagers.Rows.Count > 0 Then
        For pop As Integer = 0 To pagers.Rows.Count - 1
            If pendrun.Rows(pop)("type") = "root" Then
                Dim curid As Integer = pendrun.Rows(pop)("id")
                Dim roots(curid) As TreeNode = TreeView1.Nodes.Add(pendrun.Rows(pop)("name"))
            End If
            If pendrun.Rows(pop)("type") = "parent" Then
                Dim curid As Integer = pendrun.Rows(pop)("id")
                Dim rootid As Integer = pendrun.Rows(pop)("rootid")
                Dim Parents(curid) As TreeNode = Roots(rootid).Nodes.Add(pendrun.Rows(pop)("name"))
            End If
            If pendrun.Rows(pop)("type") = "child" Then
                Dim curid As Integer = pendrun.Rows(pop)("id")
                Dim parentid As Integer = pendrun.Rows(pop)("parentid")
                Dim Children(curid) As TreeNode = Parents(parentid).Nodes.Add(pendrun.Rows(pop)("name"))
            End If
        Next
    End If

there are two apparent problems with this

problem #1 is that it is possible the software could reach a child entry before it's parent has been created which could create an error

Possible Solution #1 - I can call the mysql database 3 separate times and use While type = "root" then = "parent" then ="child" which really isn't ideal.

problem #2 is that VB.net will not allow me to create an array of treenode at least not when assigning the way I did.

Possible Solution #2 - I have no idea?

I am sure this is a problem which has been encountered before - does any one know a more appropriate way to solve this problem

Table Example:

 id |    name    | type | rootid | parentid
 1   Company ABC   root    Null      Null
 2   Accounting   parent    1        Null
 3   Employee A   child     1         2
 4   Employee B   child     1         2
 5    Marketing   parent    1        Null
 6   Employee D   child     1         5
 7   Employee F   child     1         5
 8   Marketing    parent    1        Null
 ...

if you want I will continue building more table

Retrieval code:

    con.ConnectionString = "server=****;" _
& "user id=****;" _
& "password=****;" _
& "database=***"
    adptr = New MySqlDataAdapter("SELECT * FROM pagers", con)
    Try
        adptr.Fill(pagers)
        pagers.PrimaryKey = New DataColumn() {pagers.Columns("id")}
    Catch err As Exception
        Dim strError As String = "Exception: & err.ToString()"
    End Try
    If pagers.Rows.Count > 0 Then
        For pop As Integer = 0 To pagers.Rows.Count - 1
            If pagers.Rows(pop)("type") = "root" Then
                LoadRoot(Nothing, pagers.Rows(pop)("id"))
            End If

        Next
    End If

Upvotes: 0

Views: 1252

Answers (1)

Lauren
Lauren

Reputation: 333

I can't fit this in a comment. I filled the table a little differently to use the parentid column to control the layout of the treeview:

  • 1, "Company ABC", "root", Null, Null
  • 2, "Accounting", "parent", 1, 1
  • 3, "Employee A", "child", 1, 2
  • 4, "Employee B", "child", 1, 2
  • 5, "Marketing", "parent", 1, 1
  • 6, "Employee D", "child", 1, 5
  • 7, "Employee F", "child", 1, 5

  • 8, "Marketing", "parent", 1, 1

  • 9, "Company 123", "root", Null, Null

  • 10, "Road Crew", "parent", 9, 9
  • 11, "Employee 1", "child", 9, 10
  • 12, "Employee 2", "child", 9, 10
  • 13, "Employee 3", "child", 9, 10
  • 14, "Dispatcher", "parent", 9, 9
  • 15, "Employee 5", "child", 9, 14

Then pass the id of the desired Root to this function:

Private Sub LoadRoot(ByVal Par As TreeNode, ByVal Id As Integer) Dim oNode As TreeNode Dim oRow As DataRow

    oRow = mTbl.Rows.Find(New Object() {Id})
    If Not oRow Is Nothing Then
        If Par Is Nothing Then
            oNode = tv.Nodes.Add(oRow.Item("name").ToString)
        Else
            oNode = Par.Nodes.Add(oRow.Item("name").ToString)
        End If
        For Each oRow In mTbl.Select("parentid = " & Id.ToString)
            LoadRoot(oNode, CInt(oRow.Item("id")))
        Next
    End If

End Sub

Upvotes: 1

Related Questions