I'm assuming WinForms; if you have a form with a
TreeView
called
TreeView1
on it then the following code reads and populates the tree view (I haven't got mysql here so I've just gone for normal sql readers but you should be able to change that);
Imports System.Data.SqlClient
Public Class Form1
Private Class ProductCategory
Public Property Id As Int32
Public Property CustomerId As Int32
Public Property ParentId As Int32
Public Property Description As String
Public Sub New(ByVal id As Int32, ByVal customerId As Int16, ByVal parentId As Int32, ByVal description As String)
Me.Id = id
Me.CustomerId = customerId
Me.ParentId = parentId
Me.Description = description
End Sub
End Class
Private Function FindParent(ByVal pc As ProductCategory, ByVal node As TreeNode) As TreeNode
Dim tag As ProductCategory = CType(node.Tag, ProductCategory)
If tag.Id = pc.ParentId Then Return node
For Each child As TreeNode In node.Nodes
Dim parent As TreeNode = FindParent(pc, child)
If Not IsNothing(parent) Then Return parent
Next
Return Nothing
End Function
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
Using connection As SqlConnection = New SqlConnection("some super secret connection string")
connection.Open()
Using command As SqlCommand = New SqlCommand("select * from tbl_productcategories where customer_id=@customerId", connection)
command.Parameters.Add(New SqlParameter("customerId", 1))
Dim items As IList(Of ProductCategory) = New List(Of ProductCategory)
Using reader As SqlDataReader = command.ExecuteReader()
While reader.Read()
Dim id As Int32 = CType(reader("idtbl_productcategories"), Int32)
Dim customerId As Int32 = CType(reader("customer_id"), Int32)
Dim parentId As Int32 = 0
If Not IsDBNull(reader("parent_id")) Then
parentId = CType(reader("parent_id"), Int32)
End If
Dim description As String = CType(reader("category_description"), String)
items.Add(New ProductCategory(id, customerId, parentId, description))
End While
End Using
Dim rootNode As TreeNode = New TreeNode("Products")
rootNode.Tag = New ProductCategory(0, 0, 0, "Products")
For Each product As ProductCategory In items.OrderBy(Function(pc) pc.ParentId)
Dim node As TreeNode = New TreeNode(product.Description)
node.Tag = product
Dim parentNode As TreeNode = FindParent(product, rootNode)
parentNode.Nodes.Add(node)
Next
TreeView1.Nodes.Add(rootNode)
End Using
End Using
End Sub
End Class
Hope this helps,
Fredrik