Populate three level treeview by SQL Server table VB.NET code

I coded successfully to populate two level treeview object as below:

Dim query As String = "SELECT GrandLedgers, Ledgers FROM TrvBook WHERE ID = @OwnerID"

Using con = New SqlConnection(ConString)
    Using cmd = New SqlCommand(query, con)
        cmd.Parameters.Add(New SqlParameter("@OwnerID", My.Settings.OwnerID))

        con.Open()

        Dim dt As SqlDataReader = cmd.ExecuteReader
        Dim gl, l As String
        Dim name1 As String = ""

        While dt.Read
            Dim node1 As New TreeNode
            gl = dt.Item(0).ToString
            l = dt.Item(1).ToString

            If name1 = gl Then
                TreeViewBook.SelectedNode.Nodes.Add(0)
            Else
                name1 = gl
                node1 = TreeViewBook.Nodes.Add(gl)
                TreeViewBook.SelectedNode = node1
                TreeViewBook.SelectedNode.Nodes.Add(0)
            End If
        End While
    End Using
End Using

TreeViewBook.ExpandAll()

I tried to add third level to this treeview with the added column Persons as shown in this query:

Dim query As String = "SELECT GrandLedgers, Ledgers, Persons FROM TrvBook WHERE ID = @OwnerID"

but I didn't succeed.

I post the origin code for amendment.

Please let me have your amended code to create a three level treeview.

Solution

There are a number of things you should change. Firstly, as suggested in the comments, you should be sorting your results. That way, you know that all values that are the same in a particular column will be grouped. You should also not be adding the nodes to the tree as you create them. Any time you are creating multiple items for a control like this, your first option should be to create the items first and then add them all in one batch at the end. Here's one option for doing what you want:

Dim query = "SELECT Column1, Column2, Column3
             FROM Table1 Where Id = @Id
             ORDER BY Columnn1, Column2"
Dim nodes As New List(Of TreeNode)

Using connection As New SqlConnection(connectionString),
      command As New SqlCommand(query, connection)
    command.Parameters.Add("@Id", SqlDbType.Int).Value = id

    Dim parentNode As TreeNode = Nothing
    Dim childNode As TreeNode = Nothing

    Using reader = command.ExecuteReader()
        While reader.Read()
            Dim parent = reader.GetString(reader.GetOrdinal("Column1"))
            Dim child = reader.GetString(reader.GetOrdinal("Column2"))
            Dim grandchild = reader.GetString(reader.GetOrdinal("Column3"))

            If parent <> parentNode?.Text Then
                'Either there is no parent node or the parent value has changed so create a new parent node.
                parentNode = New TreeNode(parent)
                nodes.Add(parentNode)
                childNode = Nothing
            End If

            If child <> childNode?.Text Then
                'Either there is no child node or the child value has changed so create a new child node.
                childNode = New TreeNode(child)
                parentNode.Nodes.Add(childNode)
            End If

            childNode.Nodes.Add(New TreeNode(grandchild))
        End While
    End Using
End Using

'Add all nodes to the tree in a single batch.
TreeView1.Nodes.AddRange(nodes.ToArray())

Another small point is the way that you're adding your query parameter. This:

cmd.Parameters.Add(New SqlParameter("@OwnerID", My.Settings.OwnerID))

could be more succinctly written like this:

cmd.Parameters.AddWithValue("@OwnerID", My.Settings.OwnerID)

and that's bad. It will probably work in this case but you're relying on the system inferring the correct data type from the value and that will not work in all cases. Do you know which cases it won't work in? If you don't do it that way then you don't have to care. Do it the way I have in my example above, i.e. explicitly state the data type, and you can't go wrong. For variable length data types you should specify the length too, e.g.

command.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = name