[RESOLVED] Mysql to Treeview

[ssalomon]

I need to have Treeview Regions/Town/contact any advice how it is best to do this

anyone who can help me to get list to treeview like this
Regions
--Town
--contact
I have Table one for Regions and one for Town and the Table contact


to day the database/treeview is like this
company
--contact

 Public Sub getcompany()


        Dim conn As Common.DbConnection

        cnString = String.Format("Server=SERVER;Database=DATABASE;Uid=USER;Pwd=PASSWORD;SslMode=none;")

        Dim sqlQRY As String = "SELECT * FROM  company ORDER BY company Asc"


        conn = New MySqlConnection(cnString)

        Try
            conn.Open()

            'create command 
            Dim cmd As Common.DbCommand = New MySqlCommand(sqlQRY, conn)

            'create data reader
            Dim rdr As MySqlDataReader = cmd.ExecuteReader

            While (rdr.Read)

                TreeView1.Nodes.Add(rdr("ID").ToString, rdr("company").ToString, 1, 2)
                AddOrders(rdr("ID").ToString)

            End While

        Catch ex As Common.DbException
            MsgBox(ex.ToString)
        Finally
            ' Close connection
            conn.Close()
        End Try

    End Sub
	
	
	
	    Private Sub AddOrders(ByVal companyID As String)

        Dim conn2 As Common.DbConnection

        'Create connection
        conn2 = New MySqlConnection(cnString)

        Dim sqlQRY As String = "SELECT * FROM contact WHERE companyID = '" & companyID & "'  ORDER BY contact Asc"

        ' Open connection
        conn2.Open()

        'create command 
        Dim cmd As Common.DbCommand = New MySqlCommand(sqlQRY, conn2)

        'create data reader
        Dim rdrs As MySqlDataReader = cmd.ExecuteReader

        While (rdrs.Read)

            TreeView1.Nodes(companyID).Nodes.Add("", rdrs("contact").ToString, 5, 3)

        End While
    End Sub

[jmcilhinney]

I'd suggest populating a DataSet with three DataTables and appropriate DataRelations between then. You can then use the appropriate DataRelation to get the child rows for each parent to add child nodes. E.g.

Dim connectionString = "connection string here"
Dim data As New DataSet
Dim grandparentAdapter As New SqlDataAdapter("SELECT * FROM Grandparent", connectionString) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
Dim parentAdapter As New SqlDataAdapter("SELECT * FROM Parent", connectionString) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
Dim childAdapter As New SqlDataAdapter("SELECT * FROM Child", connectionString) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
 
grandparentAdapter.Fill(data, "Grandparent")
parentAdapter.Fill(data, "Parent")
childAdapter.Fill(data, "Child")
 
data.Relations.Add("GrandparentParent",
                   data.Tables("Grandparent").Columns("GrandparentId"),
                   data.Tables("Parent").Columns("GrandparentId"))
data.Relations.Add("tParentChild",
                   data.Tables("Parent").Columns("ParentId"),
                   data.Tables("Child").Columns("ParentId"))
 
Dim nodes As New List(Of TreeNode)
 
For Each grandparentRow As DataRow In data.Tables("Grandparent").Rows
    Dim grandparentNode As New TreeNode(CStr(grandparentRow("GrandparentName")))
 
    For Each parentRow As DataRow In grandparentRow.GetChildRows("GrandparentParent")
        Dim parentNode As New TreeNode(CStr(parentRow("ParentName")))
 
        For Each childRow As DataRow In parentRow.GetChildRows("ParentChild")
            parentNode.Nodes.Add(New TreeNode(CStr(childRow("ChildName"))))
        Next
 
        grandparentNode.Nodes.Add(parentNode)
    Next
 
    nodes.Add(grandparentNode)
Next
 
TreeView1.Nodes.AddRange(nodes.ToArray())

[ssalomon]

Thanks jmcilhinney this work well


when I connect Parent(Town) to Child(contact) Each Parent(Town) can have many Child(contact)
same with Child(contact) it can have many Parent(Town)

it it best to get contact information and write to new Table which stores Child(contact_Town)

[jmcilhinney]

That does complicate things slightly and is something that you really should have mentioned to begin with. You could do much as I showed with the child table being the join table between Town and Contact. When you get a TownContact row, you can get the ContactId from that and then get the corresponding Contact row from the Contact table by calling Find on its Rows collection.

[ssalomon]

Thanks jmcilhinney