Getting a Database Tables in VB.NET
OleDb data provider's connection class (OleDbConnection) provides a method GetOleDbSchemaTable that returns a database schema. The code listed in Listing 1 shows you to call this method. I am using Access 2000 Northwind database.
OleDb data provider's connection class (OleDbConnection) provides a method GetOleDbSchemaTable that returns a database schema. The code listed in Listing 1 shows you to call this method. I am using Access 2000 Northwind database.
To test this application, create a Windows application and add a DataGrid control to the form and write code listed in Listing 1 on the form load.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim str As String = "Provider=Microsoft.jet.oledb.4.0;Data Source=c:\\northwind.mdb"
Dim conn As New OleDb.OleDbConnection
conn.ConnectionString = str
' Open a connection
conn.Open()
'Call GetOleDbSchemaTable
Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
' Attach data row to the grid and close the connection
DataGrid1.DataSource = schemaTable
conn.Close()
End Sub
Listing 1: Getting a Database Schema
The output of Listing 1 looks like Figure 1. As you can see from Figure 1 you get table names. types, description, creation date and so on.
Figure 1. Viewing a database schema.
Now if you want only database table names, you can extract the data table returned by the GetOleDbSchemaTable. The code listed in Listing 2 shows how to do so.
Dim str As String = "Provider=Microsoft.jet.oledb.4.0;Data Source=c:\\northwind.mdb"
Dim conn As New OleDb.OleDbConnectionconn.ConnectionString = str
' Open a connection
conn.Open()
Dim ca As Object
'Call GetOleDbSchemaTable
Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _New Object() {Nothing, Nothing, Nothing, "TABLE"})
Dim tableList As New DataTable("Table")
Dim rowvals(0) As Object
Dim newdc As New DataColumn("Col")
tableList.Columns.Add(newdc)
Dim rowcoll As DataRowCollection = tableList.Rows
Dim counter As Integer
For counter = 0 To schemaTable.Rows.Count - 1
Dim rd As DataRow = schemaTable.Rows(counter)
If rd("TABLE_TYPE").ToString = "TABLE" Then
rowvals(0) = rd("TABLE_NAME").ToString
rowcoll.Add(rowvals)
End If
Next
' Attach data row to the grid and close the connection
DataGrid1.DataSource = tableList
conn.Close()
Listing 2. Extracting a DataTable to get only table names.
The output of Listing 2 looks like Figure 2.
