Getting A List of Tables From SQL Server: 2 MethodsDescription:
It is often helpful to be able to query SQL Server to find out theschema for a certain database, including tables, information aboutcolumns in a particular table, the text of a stored procedure and soforth. This comes in handy when building applications like codegenerators or query builders.
The SQL-DMO Object Library was the main way to interface with SQLServer prior to the .NET Framework. This is still quite a useful COMobject and can be used quite successfully from .NET using Interop. Infact, I built quite a nice code-generator using SQL-DMO to build mydata access layer in ADO.NET.
The .NET Framework itself provides some other ways to get at databaseschema. If using the SqlClient, you can query the INFORMATION_SCHEMAtables or use other methods like "sp_helptext". If usingSystem.Data.OleDb, there is an interesting little class calledOleDBSchemaGuid that lets us look at all sorts of interesting schemainformation. Not just in SQL Server either, but I'm going to focus onthat since that's what I use.
To illustrate, here is a very simple comparison using SQL-DMO andOleDBSchemaGuid to return a list of user tables from a database. Thecode is pretty simple, so I'll just show you the examples and you canrun with it from there.
I am using the Microsoft SQLDMO Object Library 8.0, which is theversion that comes with SQL Server 2000. I have also used SQLDMO 7.0 towork with SQL Server 7 databases. This code uses the release version ofthe .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()
cnString = "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial " & _
"Catalog=" & strDatabase & ";" & _
"User ID=" & strUser & ";" & _
"Password=" & strPwd & ";"
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()
cnString = "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial " & _
"Catalog=" & strDatabase & ";" & _
"User ID=" & strUser & ";" & _
"Password=" & strPwd & ";"
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
End Class
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More ASP.NET Articles
More By aspfree
 developerWorks - FREE Tools!
|
Join this Rational Talks to You teleconference, featuring Paul Boustany and Mark Krasovich, to speak to the experts about becoming a Rational ClearCase power user. Get a chance to ask your questions and learn tips and tricks for using Rational ClearCase in Agile development
FREE! Go There Now!
|
|
|
|
David Barnes, Lead Evangelist for IBM Emerging Internet Technologies will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve.
FREE! Go There Now!
|
|
|
|
Learn to enable users to both rate existing animations and to combine existing animations into new snippets. This is the third in a series of three tutorials that chronicle the building of a site that enables collaborative discussion and animation building using Domino and OpenLaszlo.
FREE! Go There Now!
|
|
|
|
Download a free trial version of IBM Rational Developer for System i V7.1, which provides a complete development environment for traditional i5/OS application development. IBM Rational Developer for System i is a new eclipse-based workstation offering for i5/OS application development that provides a comprehensive Integrated Development Environment for edit/compile/debug of traditional RPG/COBOL/C/C++ i5/OS applications.
FREE! Go There Now!
|
|
|
|
Analysts, architects, and developers who have existing COBOL or PL/I skills and want to extend those skills to deploy new workloads on the mainframe can use the IBM Enterprise Modernization Sandbox for System z to find hands-on walkthroughs of common real world scenarios. The scenarios provide examples of how to rapidly design, create, assemble, test, and deploy high-quality Web, Web services, portal, and SOA applications for IBM CICS, IBM IMS, and IBM WebSphere Application Server.
FREE! Go There Now!
|
|
|
|
Portfolio Management is about effectively managing portfolio value by aligning portfolio investments with business goals. This complimentary e-kit provides a collection of materials that can help you understand how IBM Rational enables and automates best practices for improved governance and clear visibility into portfolio and project performance across the entire IT project lifecycle.
FREE! Go There Now!
|
|
|
|
Get a free trial download of the latest version of IBM Rational Functional Tester V7.0.1. Rational Functional Tester is an automated functional and regression testing solution for QA teams concerned with the quality of their Java, Microsoft Visual Studio .NET, and Web-based applications.
FREE! Go There Now!
|
|
|
|
Get a free trial download of the latest version of IBM Rational Performance Tester V7.0.1, a load and performance testing solution for teams concerned about the scalability of their Web-based applications. Combining multiple ease-of-use features with granular detail, Rational Performance Tester simplifies the test-creation, load-generation and data-collection processes that help teams ensure the ability of their applications to accommodate required user loads.
FREE! Go There Now!
|
|
|
|
In this webcast, IBM Rational will discuss the importance of Web application security and will share techniques and best practices to introduce application security testing into current QA processes including: understanding common security vulnerabilities and techniques to integrate security testing with defect tracking and remediation systems in an effort to safeguard sensitive online information.
FREE! Go There Now!
|
|
|
|
Join this webcast to learn how IBM Rational's Functional Testing solution enables you to implement automation your way, at your pace, with your existing staff. In this webcast, you’ll learn how you can eliminate redundancy of manual test scripts, reduce errors, and increase test coverage through test automation. After this presentation you will understand how IBM Rational Functional Testing solution can streamline your manual testing and make test automation easily attainable.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
