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!
|
Building a software-as-a-service solution requires addressing a few key technical challenges. In this webcast, we'll focus on the role of IBM Tivoli Directory Server and WebSphere Portlet Factory in creating a Software as a Service solution. We will demonstrate how to use Tivoli Directory Server to prevent the user population of one tenant from accessing the virtual portal and portlet components of another tenant. We will also use the dynamic profile capability of WebSphere Portlet Factory to create multiple highly customized applications from one code base.
FREE! Go There Now!
|
|
|
|
You probably have thousands of lines of COBOL code loaded with business intelligence and being used to run your business, along with an army of developers maintaining these applications. Learn how to prepare your applications and developers so you can keep that competitive edge and move to a service-oriented architecture with the IBM Rational Enterprise Modernization solutions. Replay is available for 9 months.
FREE! Go There Now!
|
|
|
|
Poor Requirements Management capabilities in an Enterprise have been linked to excessive project failures, escalating IT costs, and failure to deliver competitive advantage into the marketplace. Join Brianna M Smith from IBM Rational and learn about how successful organizations align IT and Business stakeholders through collaborative processes and tools for effective requirements management, and how an integrated approach across the IT lifecycle can provide unparalleled visibility and traceability to ensure that project teams are delivering on the business vision by "doing the right things" and "doing things right."
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!
|
|
|
|
This webcast outlines the best practices that must be instituted to gain the maximum benefit from SOA while maintaining high quality of service. Whether you are deploying new applications or managing and monitoring your existing infrastructure, learn how you can ensure high quality of services with SOA based solutions from IBM. All registrants who attend this live Web Seminar will receive complimentary access to a white paper titled “Maintaining QoS in an SOA Environment”.
FREE! Go There Now!
|
|
|
|
Rational Build Forge Express Edition is an automation framework that packages the latest enterprise-grade technologies into a reliable, flexible and robust configuration designed and priced specifically for small to midsize businesses. The new Rational Build Forge Express eKit provides you with valuable resources – including a case study, podcast, demo, and articles – to help you increase staff productivity, compress development cycles and deliver better software, fast.
FREE! Go There Now!
|
|
|
|
Learn the basics of the IBM Customer Information Control System (CICS). With a hands-on exercise, learn how to get your first CICS application up and running on your desktop using TXSeries V6.1 for Windows. The tutorial shows you how to download and install a free trial version of TXSeries V6.1.
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!
|
|
|
|
With IBM Rational Systems Development Solution, you can deliver products faster with higher quality. Within this kit, Read the “Model Driven Systems Development” white paper to see how to improve product quality and communication. Then check out the rest of the e-Kit to learn more about important topics that can affect the success of any software project through customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems. From start to finish, at every stage in your projects, Rational Systems Development Solution can help your company reach its full potential.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
