Description: It is often helpful to be able to query SQL Server to find out the schema for a certain database, including tables, information about columns in a particular table, the text of a stored procedure and so forth. This comes in handy when building applications ...
It is often helpful to be able to query SQL Server to find out the schema for a certain database, including tables, information about columns in a particular table, the text of a stored procedure and so forth. This comes in handy when building applications like code generators or query builders.
The SQL-DMO Object Library was the main way to interface with SQL Server prior to the .NET Framework. This is still quite a useful COM object and can be used quite successfully from .NET using Interop. In fact, I built quite a nice code-generator using SQL-DMO to build my data access layer in ADO.NET.
The .NET Framework itself provides some other ways to get at database schema. If using the SqlClient, you can query the INFORMATION_SCHEMA tables or use other methods like "sp_helptext". If using System.Data.OleDb, there is an interesting little class called OleDBSchemaGuid that lets us look at all sorts of interesting schema information. Not just in SQL Server either, but I'm going to focus on that since that's what I use.
To illustrate, here is a very simple comparison using SQL-DMO and OleDBSchemaGuid to return a list of user tables from a database. The code is pretty simple, so I'll just show you the examples and you can run with it from there.
I am using the Microsoft SQLDMO Object Library 8.0, which is the version that comes with SQL Server 2000. I have also used SQLDMO 7.0 to work with SQL Server 7 databases. This code uses the release version of the .NET Framework and Visual Studio .NET.
Function Using SQLDMO
Private Function LoadUserTables_DMO(ByVal strServer As String, _ ByVal strUser As String, _ ByVal strPwd As String, _ ByVal strDatabase As String) As ArrayList
Dim slTables As ArrayList = New ArrayList() Dim srv As SQLDMO._SQLServer srv = New SQLDMO.SQLServer() Try srv.Connect(strServer, strUser, strPwd) Dim db As SQLDMO.Database = CType(srv.Databases.Item(strDatabase), SQLDMO.Database) Dim tbl As SQLDMO.Table For Each tbl In db.Tables If Not tbl.SystemObject Then slTables.Add(tbl.Name) End If Next srv.DisConnect() srv = Nothing Catch x As Exception lblMsg.Text = x.Message slTables = Nothing End Try
Return slTables
End Function
Function Using OleDBSchemaGuid
Private Function LoadUserTables_Schema(ByVal strServer As String, _ ByVal strUser As String, _ ByVal strPwd As String, _ ByVal strDatabase As String) As ArrayList
Dim cnString As String Dim slTables As ArrayList = New ArrayList()
Dim cn As OleDbConnection = New OleDbConnection(cnString) Try cn.Open() Dim schemaTable As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _ New Object() {Nothing, Nothing, Nothing, "TABLE"}) cn.Close() Dim dr As DataRow
For Each dr In schemaTable.Rows slTables.Add(dr("TABLE_NAME")) Next Catch x As OleDbException lblMsg.Text = x.Message slTables = Nothing End Try
Return slTables
End Function
I put together a little ASPX page to test. It is very simple and should be easy to follow.
ASPX page
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="TableList.aspx.vb" Inherits="Sandbox.TableList"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <title>TableList</title> <LINK href="Stylesheets/Common.css" type="text/css" rel="stylesheet"> <meta content="Microsoft Visual Studio.NET 7.0" name="GENERATOR"> <meta content="Visual Basic 7.0" name="CODE_LANGUAGE"> <meta content="JavaScript" name="vs_defaultClientScript"> <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema"> </HEAD> <body> <form id="Form1" method="post" runat="server"> <asp:Label id="lblMsg" runat="server"></asp:Label> <p><asp:validationsummary id="valSum" runat="server" displaymode="BulletList" showsummary="True" showmessagebox="False" headertext="The following errors were found."></asp:validationsummary></p> <p><asp:textbox id="DBServer" runat="server"></asp:textbox>SQL Server to target (ComputerName or IP Address) </p> <p><asp:textbox id="UserName" runat="server"></asp:textbox>UserName </p> <p><asp:textbox id="Password" runat="server"></asp:textbox>Password </p> <p><asp:textbox id="Database" runat="server"></asp:textbox>Database </p> <asp:Panel id="PanDropdowns" runat="server" visible="False"> <P> <asp:dropdownlist id="DdlTables_DMO" runat="server"></asp:dropdownlist>List of tables using DMO (Interop) </P> <P> <asp:dropdownlist id="DdlTables_Schema" runat="server"></asp:dropdownlist>List of tables using OleDBSchemaGuid Class </P> </asp:Panel> <P><asp:button id="BtnLoad" onclick="BtnLoad_Click" runat="server" text="Get Table List"></asp:button></P> <asp:requiredfieldvalidator display="None" id="ValR_Server" runat="server" controltovalidate="DBServer" errormessage="Server is a required field."></asp:requiredfieldvalidator> <asp:requiredfieldvalidator display="None" id="ValR_Database" runat="server" controltovalidate="Database" errormessage="Database is a required field."></asp:requiredfieldvalidator> <asp:requiredfieldvalidator display="None" id="ValR_UserName" runat="server" controltovalidate="UserName" errormessage="UserName is a required field."></asp:requiredfieldvalidator> <asp:requiredfieldvalidator display="None" id="ValR_Password" runat="server" controltovalidate="Password" errormessage="Password is a required field."></asp:requiredfieldvalidator></form> </body> </HTML>
Code Behind for page
Option Strict On Option Explicit On
Imports System Imports SQLDMO Imports System.Data Imports System.Data.OleDb
Public Class TableList Inherits System.Web.UI.Page Protected WithEvents DBServer As System.Web.UI.WebControls.TextBox Protected WithEvents UserName As System.Web.UI.WebControls.TextBox Protected WithEvents Password As System.Web.UI.WebControls.TextBox Protected WithEvents Database As System.Web.UI.WebControls.TextBox Protected WithEvents DdlTables_DMO As System.Web.UI.WebControls.DropDownList Protected WithEvents DdlTables_Schema As System.Web.UI.WebControls.DropDownList Protected WithEvents valSum As System.Web.UI.WebControls.ValidationSummary Protected WithEvents ValR_Server As System.Web.UI.WebControls.RequiredFieldValidator Protected WithEvents ValR_Database As System.Web.UI.WebControls.RequiredFieldValidator Protected WithEvents ValR_UserName As System.Web.UI.WebControls.RequiredFieldValidator Protected WithEvents ValR_Password As System.Web.UI.WebControls.RequiredFieldValidator Protected WithEvents PanDropdowns As System.Web.UI.WebControls.Panel Protected WithEvents lblMsg As System.Web.UI.WebControls.Label Protected WithEvents BtnLoad As System.Web.UI.WebControls.Button
Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here
End Sub
Protected Sub btnLoad_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim blnSuccess As Boolean
Dim slTables As ArrayList = LoadUserTables_DMO(DBServer.Text, UserName.Text, _ Password.Text, Database.Text) If Not (slTables Is Nothing) Then DdlTables_DMO.DataSource = slTables DdlTables_DMO.DataBind() blnSuccess = True End If
Dim slTables2 As ArrayList = LoadUserTables_Schema(DBServer.Text, _ UserName.Text, Password.Text, Database.Text) If Not (slTables2 Is Nothing) Then DdlTables_Schema.DataSource = slTables2 DdlTables_Schema.DataBind() blnSuccess = True End If
If blnSuccess Then PanDropdowns.Visible = True Else PanDropdowns.Visible = False End If
End Sub
Private Function LoadUserTables_DMO(ByVal strServer As String, _ ByVal strUser As String, _ ByVal strPwd As String, _ ByVal strDatabase As String) As ArrayList
Dim slTables As ArrayList = New ArrayList() Dim srv As SQLDMO._SQLServer srv = New SQLDMO.SQLServer() Try srv.Connect(strServer, strUser, strPwd) Dim db As SQLDMO.Database = CType(srv.Databases.Item(strDatabase), SQLDMO.Database) Dim tbl As SQLDMO.Table For Each tbl In db.Tables If Not tbl.SystemObject Then slTables.Add(tbl.Name) End If Next srv.DisConnect() srv = Nothing Catch x As Exception lblMsg.Text = x.Message slTables = Nothing End Try
Return slTables
End Function
Private Function LoadUserTables_Schema(ByVal strServer As String, _ ByVal strUser As String, _ ByVal strPwd As String, _ ByVal strDatabase As String) As ArrayList
Dim cnString As String Dim slTables As ArrayList = New ArrayList()
Dim cn As OleDbConnection = New OleDbConnection(cnString) Try cn.Open() Dim schemaTable As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _ New Object() {Nothing, Nothing, Nothing, "TABLE"}) cn.Close() Dim dr As DataRow
For Each dr In schemaTable.Rows slTables.Add(dr("TABLE_NAME")) Next Catch x As OleDbException lblMsg.Text = x.Message slTables = Nothing End Try
Database Code 
/ 38 
Using SQLDMO object and .NET by Curtis Swartzentruber
