Populate Treeview from Datatable - VB.NET

Question

I have a SQL table (called tblClosure) which has these fields:

1. LocationID
2. LocationDesc
3. ParentID

My question is how can I populate a TreeView control from this DataTable in WinForms VB.NET? I have seen examples in C# but I'm a rookie coder and am having difficulties adapting that code.

Any help would be greatly appreciated.

Thanks in advance

Answer 1

_{To see the C# version of my answer see this post.}

---

To populate a TreeView from a DataTable or any IEnumerable(Of T), you should be able to answer the following questions:

1. What are data source items
2. How to detect if an item in data source is a root item in tree
3. How to find child items of an item in data source
4. How to create tree item from data source item.

By passing answer of above questions as lambda expressions to the following method, it uses a recursive algorithm to create a list of TreeNode which you can add to TreeView. Each TreeNode contains the descendant TreeNode items:

Private Iterator Function GetTreeNodes(Of T)(
    ByVal source As IEnumerable(Of T),
    ByVal isRoot As Func(Of T, Boolean),
    ByVal getChilds As Func(Of T, IEnumerable(Of T), IEnumerable(Of T)),
    ByVal getItem As Func(Of T, TreeNode)) As IEnumerable(Of TreeNode)

    Dim roots As IEnumerable(Of T) = source.Where(Function(x) isRoot(x))
    For Each root As T In roots
        Yield ConvertEntityToTreeNode(root, source, getChilds, getItem)
    Next
End Function

Private Function ConvertEntityToTreeNode(Of T)(
    ByVal entity As T,
    ByVal source As IEnumerable(Of T),
    ByVal getChilds As Func(Of T, IEnumerable(Of T), IEnumerable(Of T)),
    ByVal getItem As Func(Of T, TreeNode)) As TreeNode

    Dim node As TreeNode = getItem(entity)
    Dim childs = getChilds(entity, source)
    For Each child As T In childs
        node.Nodes.Add(ConvertEntityToTreeNode(child, source, getChilds, getItem))
    Next
    Return node
End Function

Example

I assume you have loaded data into the following structure:

Dim dt = New DataTable()
dt.Columns.Add("Id", GetType(Integer))
dt.Columns.Add("Name", GetType(String))
dt.Columns.Add("ParentId", GetType(Integer))
dt.Rows.Add(1, "Menu 1", DBNull.Value)
dt.Rows.Add(11, "Menu 1-1", 1)
dt.Rows.Add(111, "Menu 1-1-1", 11)
dt.Rows.Add(112, "Menu 1-1-2", 11)
dt.Rows.Add(12, "Menu 1-2", 1)
dt.Rows.Add(121, "Menu 1-2-1", 12)
dt.Rows.Add(122, "Menu 1-2-2", 12)
dt.Rows.Add(123, "Menu 1-2-3", 12)
dt.Rows.Add(124, "Menu 1-2-4", 12)
dt.Rows.Add(2, "Menu 2", DBNull.Value)
dt.Rows.Add(21, "Menu 2-1", 2)
dt.Rows.Add(211, "Menu 2-1-1", 21)

Then to convert it nodes of a TreeView, you can use the following code:

Dim source = dt.AsEnumerable()
Dim nodes = GetTreeNodes(
    source,
    Function(r) r.Field(Of Integer?)("ParentId") Is Nothing,
    Function(r, s) s.Where(Function(x) r("Id").Equals(x("ParentId")) ),
    Function(r) New TreeNode With {.Text = r.Field(Of String)("Name")})

TreeView1.Nodes.AddRange(nodes.ToArray())

As a result, you will have the following tree structure:

├─ Menu 1
│  ├─ Menu 1-1
│  │  ├─ Menu 1-1-1
│  │  └─ Menu 1-1-2
│  └─ Menu 1-2
│     ├─ Menu 1-2-1
│     ├─ Menu 1-2-2
│     ├─ Menu 1-2-3
│     └─ Menu 1-2-4
└─ Menu 2
   └─ Menu 2-1
      └─ Menu 2-1-1

Answer 2

If you are having something like 2 levels of hierarchy of data, I would suggest using a gridview control instead and bind it to a datatable that results from query like this

Select 
 Case when ParentID>0 then ParentID else LocationID end as TheParentID
 ,LocationID
 ,LocationDesc
 ,ParentID
 From tblClosure  
 Order by 0, ParentID, LocationDesc

Then based on value of ParentID you style it differently to show if it is a parent or child row.

Check this http://sqlfiddle.com/#!2/b29b8/2/0