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
Leave a Reply