VSTO & .NET & Excel

February 23, 2010

Populate TreeView Control with Tables and Columns from MS Access Database

Filed under: .NET & Excel, COM Add-ins, VSTO & Excel — Dennis M Wallentin @ 9:15 pm

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

 

About these ads

9 Comments »

  1. Very nice! I have actually started posting an Excel VSTO tutorial, and my next post will be about how to display workbooks/worksheets in a TreeView control, using WPF. I hesitated doing it in WinForms, because it’s a technology more people are familiar with. Now I can happily go ahead with WPF, and recommend this post for guidance on how to proceed with WinForms!

    Comment by Mathias — February 23, 2010 @ 10:47 pm

  2. Mathias,

    Cool and let me know when You have published Your tutorial for WPF.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — February 24, 2010 @ 2:17 am

  3. I tried to work with this sample, but I keep getting Error “Name ‘TVDatabase’ is not declared.”

    I’m using VS2k8, did something change?

    Comment by Aaron — July 26, 2010 @ 7:50 pm

    • Aaron,
      Are You sure that You have named the Treeview control to TVDatabase? That’s only explanation I can come up with…

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — July 26, 2010 @ 10:25 pm

      • You nailed it.. Dam fat fingers ;o)

        Comment by Aaron — July 27, 2010 @ 4:01 am

      • Aaron,

        Glad You got it sorted out.

        Kind regards,
        Dennis

        Comment by Dennis Wallentin — July 27, 2010 @ 1:54 pm

  4. Good job, Treeview working well.
    now i learned someMore about Treeview (vb)

    Comment by Sergiu — October 9, 2010 @ 4:08 am

  5. This is an excellent tutorial but I have a question. I would like to add a textbox to show the database’s value of a given selection from a selected Treeview node. Suppose that you expanded the Employees Node and
    the first employee is: Davoilio Nancy and her job is Sales Representative and I wanted the TextBox to show here job as opposed to just indicating the node selected (TvData_ After select). Since the Treeview is not
    data bound, how could I accomplish this?

    Comment by John — February 1, 2012 @ 8:34 am

    • First you have to populate database into treeview and textbox then select the rows and Item what will be generated afterselect. hope it will work.
      follow the code:

      Private sub class form1

      Dim inc As Integer
      Dim con As New OleDb.OleDbConnection
      Dim dbProvider As String
      Dim dbSource As String
      Dim ds As New DataSet
      Dim da As OleDb.OleDbDataAdapter
      Dim sql As String
      Dim table As DataTable
      Dim MaxRows As Integer

      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      dbProvider = “PROVIDER=Microsoft.Jet.OLEDB.4.0;”
      dbSource = “Data Source = C:/Address.mdb”
      con.ConnectionString = dbProvider & dbSource
      con.Open()
      sql = “SELECT * FROM tblcontacts”
      da = New OleDb.OleDbDataAdapter(sql, con)
      da.Fill(ds, “AddressBook”)
      con.Close()
      MaxRows = ds.Tables(“AddressBook”).Rows.Count
      inc = 1
      TreeView1.Nodes(0).Nodes.Add(ds.Tables(“addressBook”).Rows(inc).Item(1))

      End Sub

      Private Sub NavigateRecords()
      TextBox2.Text = ds.Tables(“Address”).Rows(inc).Item(2)
      TextBox3.Text = ds.Tables(“Address”).Rows(inc).Item(3)
      End Sub

      Private Sub TreeView1_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView1.AfterSelect

      If inc = Item(1) Then
      inc = inc
      NavigateRecords()
      End If

      End Sub
      End Class

      Comment by Bandhan — March 15, 2012 @ 6:15 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 39 other followers

%d bloggers like this: