利用OleDb的GetOLEDBSchemaTable方法得到数据库架构信息
日期：2012-05-08 浏览次数：20514 次
我们可以利用OleDb的GetOLEDBSchemaTable方法得到数据库的所有视图，表，存储过程等信息。
GetDataBaseSchema.aspx
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="GetDataBaseSchema.aspx.vb"
Inherits="aspxWeb.GetDataBaseSchema"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>GetDataBaseSchama</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid><br>
<asp:DataGrid id="DataGrid2" runat="server"></asp:DataGrid><br>
<asp:DataGrid id="DataGrid3" runat="server"></asp:DataGrid><br>
<asp:DataGrid id="DataGrid4" runat="server"></asp:DataGrid><br>
<asp:DataGrid id="Datagrid5" runat="server"></asp:DataGrid><br>
<asp:DataGrid id="Datagrid6" runat="server"></asp:DataGrid>
</form>
</body>
</HTML>
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 "
<System.Diagnostics.DebuggerStepThrough()> 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"})
GetDataBaseSchema.aspx
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="GetDataBaseSchema.aspx.vb"
Inherits="aspxWeb.GetDataBaseSchema"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>GetDataBaseSchama</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid><br>
<asp:DataGrid id="DataGrid2" runat="server"></asp:DataGrid><br>
<asp:DataGrid id="DataGrid3" runat="server"></asp:DataGrid><br>
<asp:DataGrid id="DataGrid4" runat="server"></asp:DataGrid><br>
<asp:DataGrid id="Datagrid5" runat="server"></asp:DataGrid><br>
<asp:DataGrid id="Datagrid6" runat="server"></asp:DataGrid>
</form>
</body>
</HTML>
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 "
<System.Diagnostics.DebuggerStepThrough()> 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"})
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
