Populate TreeView Control with Tables and Columns from MS Access Database

Introduction
Populating TreeViews controls with some data is a common task in various type of applications. One common task is to let an application scan disks for folders and files and then populate a TreeView control with the data in a hierarchical way. In the following example all the tables and their columns from a MS Access database will be added to a Treeview control. To work with TreeView controls can initially be tricky to understand but I hope the case here will make it more understandable. To grab the names for all the tables as well as columns from a MS Access database can also be tricky but again I hope that the following case will clear the mud so to speak.

At present I work with a new coming add-in that will include an advanced report generator and to work with tables and columns in a database plays a critical role in that tool.

In the next blog entry I will show how we can populate a TreeView control with data from a SQL Server database. In the final entry about the TreeView control a more generic approach will be discussed, i.e. how to get tables and columns from a various type of databases.

The Case
First of all we create a new Windows Form project in VB. Add a TreeView control to the Windows Form and also add an ImageList to the project. Next, add three images to the ImageList which will differentiate the node levels from each other. In other words, we will have three node levels; root, tables and columns. For best performance change the ColorDepth for the list of images as the following screen shot shows:

We add two buttons to the Windows Form and manipulate them so they look like the following screen shot:

Now it’s time to add some code enabling us to populate the TreeView control with the wanted data. As the below code shows; we create a node and we add the wanted data to the created node in one go.

Imports System.Data
Imports System.Data.OleDb

Public Class Form1

'The connection string.
Const Connection As String = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Northwind 2007.accdb"

'The name of the database in use which is used as the root name for
'the TreeView.
Const Database As String = "NorthWind 2007"

Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click

Dim Rootnode As TreeNode = Nothing
Dim Mainnode As TreeNode = Nothing
Dim Childnode As TreeNode = Nothing

Dim MainName As String = String.Empty
Dim ChildName As String = String.Empty

'Open the connection to the database.
Dim cn As New OleDbConnection(connectionString:=Connection)
cn.Open()

'Retrieve the tables from the schema.
Dim SchemaTable As DataTable = _
cn.GetOleDbSchemaTable(schema:=Data.OleDb.OleDbSchemaGuid.Tables, _
restrictions:=New Object() {Nothing, Nothing, Nothing, "Table"})

TVDatabase.Nodes.Clear()

'Add the root node.
Rootnode = TVDatabase.Nodes.Add(key:="Root", text:=Database, _
imageIndex:=0, selectedImageIndex:=0)

For CountTables As Integer = 0 To SchemaTable.Rows.Count - 1

'Get the table name.
Dim TableName As String = SchemaTable.Rows(index:=CountTables)! _
TABLE_NAME.ToString

'Check if the table name already exist or not in the node collection.
If MainName <> TableName Then
'OK, it does not exist so add the table name as a main node.
Mainnode = Rootnode.Nodes.Add(key:="Table", text:=TableName, _
imageIndex:=1, selectedImageIndex:=1)
MainName = TableName
End If

'Get the column names in the table.
Dim SchemaColumn = cn.GetOleDbSchemaTable(schema:=OleDbSchemaGuid.Columns, _
restrictions:=New Object() {Nothing, Nothing, TableName, Nothing})

'Add each column as a child node.
For CountColumns As Integer = 0 To SchemaColumn.Rows.Count - 1
ChildName = SchemaColumn.Rows(index:=CountColumns)!COLUMN_NAME.ToString
Childnode = Mainnode.Nodes.Add(key:="Column", text:=ChildName, _
imageIndex:=2, selectedImageIndex:=2)
Next CountColumns

Next CountTables

TVDatabase.Nodes(0).EnsureVisible()

cn.Close()
cn.Dispose()
cn = Nothing

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click
TVDatabase.CollapseAll()
End Sub

End Class

When the above code is executed, i.e. the Windows Form is loaded, we have clicked on the Populate Treeview button and we have clicked on the plus sign next to the root level, we get the following output:

To collapse the TreeView we click on the Collapse Tree button.

The final part of the solution covers how to get the selected node in the TreeView control. Initially we may think that the Click event of the TreeView control will give use the selected node. Unfortunately it’s not the case as that event will only return the previously selected node. To get the selected node we use the AfterSelect event as it’s fired after the newly selected node is placed in the SelectedNode property.

The following code shows how to retrieve the selected node and as noted in the code we can actually either use the variable e.Node or the SelectedNode property of the TreeView control to get the selected node.

Private Sub TVDatabase_AfterSelect(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.TreeViewEventArgs) _
Handles TVDatabase.AfterSelect

Dim SelectedItem As String = TVDatabase.SelectedNode.ToString
SelectedItem = SelectedItem.Replace("TreeNode: ", "")

If (e.Node.Parent IsNot Nothing) Then
If (e.Node.Parent.GetType() Is GetType(TreeNode)) Then
If Not e.Node.Parent.Text = Database Then
'Show both the selected column name and the table's name
'it belongs to.
MessageBox.Show(e.Node.Parent.Text + "." + SelectedItem)
Else
'Show only the selected table name.
MessageBox.Show(SelectedItem)
End If
End If
End If

End Sub

I hope that the above example clarify how we can work with a TreevView control.

As already mentioned, the next blog entry will cover how to retrieve all the tables, all the views and their columns from a SQL Server database. The collected data will then be added to a TreeView control.

Kind regards,
Dennis

The second part of the series is available at the following URL:
Populate TreeView Control with Tables, Views and Columns from a SQL Server Database

The third article is available here: Populate TreeView Controls with Tables from SQL Server & Access Database by using ADOX

 

Advertisement