利用OleDb的GetOLEDBSchemaTable方法得到数据库架构信息

利用OleDb的GetOLEDBSchemaTable方法得到数据库架构信息

作者：孟宪会 出自：【孟宪会之精彩世界】 发布日期：2003年7月2日 9点16分16秒

我们可以利用OleDb的GetOLEDBSchemaTable方法得到数据库的所有视图，表，存储过程等信息。

GetDataBaseSchema.aspx

GetDataBaseSchama

GetDataBaseSchema.aspx.vb

Imports System

Imports System.Data

Imports System.Data.OleDb

Public Class GetDataBaseSchema

Inherits System.Web.UI.Page

Protected WithEvents DataGrid2 As System.Web.UI.WebControls.DataGrid

Protected WithEvents DataGrid3 As System.Web.UI.WebControls.DataGrid

Protected WithEvents DataGrid4 As System.Web.UI.WebControls.DataGrid

Protected WithEvents Datagrid5 As System.Web.UI.WebControls.DataGrid

Protected WithEvents Datagrid6 As System.Web.UI.WebControls.DataGrid

Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

#Region " Web Form Designer Generated Code "

Private Sub InitializeComponent()

End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles MyBase.Init

InitializeComponent()

End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles MyBase.Load

'如何得到数据库中的架构信息？

'GetOLEDBSchemaTable函数有两个方法：

'OLEDBSchemaGUID

'Restrictions

'参数OLEDBSchemaGUID 的成员：Tables, Procedures, Views, Columns, Catlogs 等

'参数restrictions为限制条件，是一个对象数组，原来过虑架构结果信息，

'每一个对象映射到所返回的datacolumn的值。

Dim strCnn As String

strCnn = "Provider=SqlOLEDB; Data Source=.\NetSDK; Initial Catalog=pubs;User ID=sa;Password=;"

Dim dataConn As New OleDbConnection(strCnn)

Try

dataConn.Open()

Dim schemaTable As DataTable

schemaTable = dataConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

'得到全部的表、视图

DataGrid1.DataSource = schemaTable

DataGrid1.DataBind()

schemaTable = dataConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

'得到全部的用户表,用户表类型为Table，进行过虑

DataGrid2.DataSource = schemaTable

DataGrid2.DataBind()

schemaTable = dataConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "VIEW"})

'得到全部的视图

DataGrid3.DataSource = schemaTable

DataGrid3.DataBind()

schemaTable = dataConn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, Nothing)

'得到全部的存储过程

DataGrid4.DataSource = schemaTable

DataGrid4.DataBind()

schemaTable = dataConn.GetOleDbSchemaTable(OleDbSchemaGuid.Provider_Types, Nothing)

'得到全部支持的数据类型

Datagrid5.DataSource = schemaTable

Datagrid5.DataBind()

schemaTable = dataConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, Nothing)

Datagrid6.DataSource = schemaTable

Datagrid6.DataBind()

Catch ex As Exception

Response.Write(ex.Message.ToString())

Finally

dataConn.Close()

End Try

End Sub

End Class