How to create a tree view from MySQL as database using VB.NET treeview

Question

I been try to fix this problem, in code but until now i don't know how to get the correct result this is my table

+--------+-----------+----------+
| id     | name      | node_id  |
+--------+-----------+-----------
|      1 | Color     |     NULL |
|      2 | Black     |        1 |
|      3 | Red       |        1 |
|      4 | White     |        1 |
|      5 | Animal    |     NULL |
|      6 | Dog       |        5 |
|      7 | Cat       |        5 |
+--------+-----------+----------+

I want to display this in my Form in Tree view like this, but it was messed up as I expected. The Color, Animal is the Parent node, and Black , White, Dog, Cat is the child node

Color
   |-Black
   |-Red
Animal
   |-Dog
   |-Cat

This is the code for the tree view using VB.NET:

'select the min Id as the starting of node
Dim SQL As String = "select min(node_id), name from mytable;"
Dim cmd As MySqlCommand = New MySqlCommand(SQL, cn)
cmd.ExecuteNonQuery()
Dim reader As MySqlDataReader = cmd.ExecuteReader()
Dim id As Integer
Dim name As String
Dim node1, node2 As TreeNode

While reader.Read
    id = reader.GetString(0)
    name = reader.GetString(1)
End While

reader.Close()
cmd.CommandText = "select id, name from mytable"
cmd.ExecuteNonQuery()
Dim ds As New DataSet
Dim da As New MySqlDataAdapter
'add the first find of min id = name
node1 = TreeView1.Nodes.Add(id, name)
If node1 is Nothing Then
    node2 = TreeView1.Nodes.Add(id, name)
Else
    node2 = node1.Nodes.Add(id, name)
End If
Dim dr = cmd.ExecuteReader

Do While dr.Read()
    TreeView1.Nodes.Add(dr("id"), dr("name"))
    TreeView1.Nodes(node2.Level).Nodes.Add(dr("id"), dr("name"))
Loop
dr.Close()

Answer 1

Rather than rows referencing other rows in the same table to identify the parent and then multiple queries to unravel it, your db ought to have 2 tables since there are 2 actors: parent nodes/items and the child item/nodes related to each of them. However, SO is more about handing out fish, than fishing rods and since we can join the table to itself for the same result, this should work.

Dim SQL = <sql>SELECT p.Name As Parent, tvnode.Name
                FROM tvnode p
                LEFT JOIN tvnode 
                ON tvnode.ParentId = p.Id
                WHERE p.ParentId Is null  
                ORDER BY p.Name;
          </sql>.Value

Using dbcon As New MySqlConnection(MySQLConnStr)
    Using cmd As New MySqlCommand(SQL, dbcon)

        dbcon.Open()
        Using rdr As MySqlDataReader = cmd.ExecuteReader
            Dim parentName As String = ""
            While rdr.Read
                Dim nodeName = rdr.GetString("Parent")
                ' add new parent
                If nodeName <> parentName Then
                    tv1.Nodes.Add(nodeName, nodeName)
                    parentName = nodeName
                End If
                ' add non null child names
                If rdr.IsDBNull(rdr.GetOrdinal("Name")) = False Then
                    Dim thisname = rdr.GetString("Name")
                    tv1.Nodes(parentName).Nodes.Add(thisname, thisname)
                End If
            End While
        End Using

    End Using
End Using

I used an XML literal for the SQL just to avoid scroll, dont let that throw you. The SQL joins tvnodes to itself so it can provide Parent name (Animal, Color) using the p alias; and provide the child names for each of those in one query.

The SQL orders the results by ParentName so that whenever that value changes, you know you need to add a new root/parent node. So, there is no need to search, check or run a new query; no need for a DataSet, DataAdapter or DataGridView. By putting the data in order, you can just loop thru the reader.

This particular JOIN will provide parent names even when there are no children ("Snack"), but this requires that the code check that each child name is not DbNull. The code doesn't need the Ids so they are left out.

+-----------------------
| Parent | Name
| Animal | Cat  
| Animal | Dog  
| ...
| Color  | Red  
| ...  
| Color  | Purple  
| Fish   | Perch  
| Fish   | Bass  
|...  
| Snack  | NULL  

Results:

To learn more about SQL this SQL Tutorial is very good.