VB.NET 取得資料庫內容 [Getting a Database Tables]

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.

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()