Populate TreeView Controls with Tables from SQL Server & Access Databases by using ADOX

This is the third article on how to populate a TreeView Control with Tables, Views and Columns from a SQL Server database respectively from an Access database. The previously blog entries on this subject are:

• Populate TreeView Control with Tables and Columns from MS Access Database
• Populate TreeView Control with Tables, Views and Columns from a SQL Server Database

The last article on the subject will be about how to do it by using LINQ to SQL. I have already used ADOX in previously blog posts, for instance in the article Create SQL Server Compact Edition Database with VBA. And because the library allows us to work with the framework of databases we can, for instance, retrieve all system tables from an Access database. The first code case below shows exactly how to do it. In the second case I demonstrate how to retrieve all tables from a SQL Server database, including views and system tables.

The code to retrieve all system tables in an Access database and to populate a TreeView with the tables:

'A reference to the following COM Library must be made in order
'to establish a connection to the database:
'Microsoft ActiveX Data Objects x.x Library

'To access tables and columns in the database a reference
'must be made to the following COM Library:
'Microsoft ADO Ext. x.x for DDL and Security

'x.x above refers to version like 2.8.

Imports System.Runtime.InteropServices
Imports System.Windows.Forms

Public Class frmMain

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

'Connection string for an Access 2010 Database.
Const Con As String = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=c:\NorthWind 2010.accdb;" & _
"Persist Security Info=False"

Const Database As String = "NorthWind"

Dim ADODBConnection As New ADODB.Connection
Dim ADOXCat As New ADOX.Catalog
Dim ADOXTbl As New ADOX.Table
Dim ADOXCol As New ADOX.Column

'Open the connection to the database.
ADODBConnection.Open(Con)

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

Dim MainName As String = String.Empty

ADOXCat.ActiveConnection = ADODBConnection

TVDatabase.Nodes.Clear()

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

'Populate the TreeView with tables' names and their columns' names.
For Each ADOXTbl In ADOXCat.Tables
'To retrieve only system tables.
If ADOXTbl.Name Like "MSys*" Then
'Add the tables' names.
Mainnode = Rootnode.Nodes.Add(key:="Table", text:=ADOXTbl.Name.ToString, _
imageIndex:=1, selectedImageIndex:=1)
'Add the columns' names.
For Each ADOXCol In ADOXTbl.Columns
Childnode = Mainnode.Nodes.Add(key:="Column", text:=ADOXCol.Name.ToString, _
imageIndex:=2, selectedImageIndex:=2)
Next ADOXCol
End If

Next ADOXTbl

TVDatabase.Nodes(0).EnsureVisible()

'Clean up.
ADODBConnection.Close()
Marshal.ReleaseComObject(ADODBConnection)
ADODBConnection = Nothing
Marshal.ReleaseComObject(ADOXTbl)
ADOXTbl = Nothing
Marshal.ReleaseComObject(ADOXCat)
ADOXCat = Nothing

End Sub

Next, below shows the code to retrieve all tables from a SQL Server Database and to populate the TreeView with the tables:

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

Const Con As String = _
"Provider=SQLOLEDB;Data Source=DENNIS-DATOR;" & _
"Password=street;User ID=DenWal;" & _
"Initial Catalog=ContosoRetailDW"

Const Database As String = "Contoso Retail"

Dim ADODBConnection As New ADODB.Connection
Dim ADOXCat As New ADOX.Catalog
Dim ADOXTbl As New ADOX.Table
Dim ADOXCol As New ADOX.Column

'Open the connection to the database.
ADODBConnection.Open(Con)

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

Dim MainName As String = String.Empty

ADOXCat.ActiveConnection = ADODBConnection

TVSQLServer.Nodes.Clear()

Rootnode = TVSQLServer.Nodes.Add(key:="Root", text:=Database, _
imageIndex:=0, selectedImageIndex:=0)

'Populate the TreeView with tables' names and their columns' names.
For Each ADOXTbl In ADOXCat.Tables
'Add the tables' names.
Mainnode = Rootnode.Nodes.Add(key:="Table", text:=ADOXTbl.Name.ToString, _
imageIndex:=1, selectedImageIndex:=1)
'Add the columns' names.
For Each ADOXCol In ADOXTbl.Columns
Childnode = Mainnode.Nodes.Add(key:="Column", text:=ADOXCol.Name.ToString, _
imageIndex:=2, selectedImageIndex:=2)
Next ADOXCol
Next ADOXTbl

With TVSQLServer
.Sort()
.Nodes(0).EnsureVisible()
End With

'Clean up.
ADODBConnection.Close()
Marshal.ReleaseComObject(ADODBConnection)
ADODBConnection = Nothing
Marshal.ReleaseComObject(ADOXTbl)
ADOXTbl = Nothing
Marshal.ReleaseComObject(ADOXCat)
ADOXCat = Nothing

End Sub

The following screen shot shows the two TreeViews after we have populated them with the tables from the two databases.

That’s all for now. The next article will finalize the series about creating XLLs with Add-in Express.

Kind regards,
Dennis