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!
|
This demonstration gives you an overview of IBM® Rational® Build Forge Express Edition, a global offering that provides a framework to automate and execute software processes. Rational Build Forge provides a software assembly line that can support all of your tools, technologies, and platforms so you can achieve a repeatable, reliable, and traceable build and release process.
FREE! Go There Now!
| | | |
As businesses grow increasingly dependent upon Web applications to provide services to customers, employees and partners, these complex applications become more difficult to secure. Although traditional security solutions protect Internet infrastructure layers, they do not guard against HTTP and HTML attacks. Many organizations that conduct security testing still deploy applications that allow attackers to manipulate their logic and wreak havoc on their business. To mitigate this risk, development and delivery teams must address Web application security throughout the lifecycle, addressing the many layers detailed in this paper.
FREE! Go There Now!
| | | |
This whitepaper presents the benefits of successfully introducing static analysis into your organization using IBM Rational Software Analyzer. Additionally, it identifies some common pitfalls that can hinder the effective use of static analysis tooling as well as presents 10 simple strategies designed to help you quickly realize the value of static analysis using Rational Software Analyzer.
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!
| | | |
Learn how you can extend modern application lifecycle management to IBM System z through the IBM Rational Software Delivery Platform (SDP). The Did you say mainframe? e-kit includes podcasts, webcasts, tutorials, white and red papers, demos, and articles designed to help ease the challenges of modernizing your enterprise. This complimentary kit for mainframe developers is a practical, how-to guide for making the most of an existing development environment, including the skills and infrastructure already in place at an established enterprise.
FREE! Go There Now!
| | | |
Join us for this web seminar to learn how you can defend your web applications from attack. Learn about the 3 most common web application attacks, including how they occur and what can be done to prevent them. We’ll also discuss manual versus automated approaches for scanning and identifying web application vulnerabilities and how IBM Rational AppScan, an automated vulnerability scanner, can help you automate more of what you are doing manually today.
FREE! Go There Now!
| | | |
IBM Enterprise Modernization solutions help organizations evolve core IT systems towards modern architectures and technologies—reducing the burden of maintenance and freeing up resources to develop new business requirements and capabilities. With the IBM Enterprise Modernization Sandbox for System z you can evaluate IBM Enterprise Modernization solutions focused on five key areas: Assets, Architectures, Skills, Processes and Infrastructures, and Investment. Each solution is based upon real customer experiences and offers a proven path to get you started with your modernization projects.
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!
| | | |
Discover how Rational tools and best practices for testing can make your job easier. The new Rational Testing eKits provide you with valuable resources – including demos, webcasts, tutorials, and articles – that help you address your specific testing needs across the software lifecycle. Five new eKits are available covering the topics of Requirements and Test Management, Functional Testing, Performance Testing, Code Quality and Embedded Systems, and SOA and Web Services Testing.
FREE! Go There Now!
| | | |
Join this webcast to discover the key requirements for successful change and release management. Learn how to extend your .NET environment to improve productivity and collaboration, and address core problems afflicting team development. In this webcast, we’ll review typical challenges faced by customers and how to resolve them with the IBM Rational Change and Release Management solution, including Rational ClearCase, Rational ClearQuest and Rational Build Forge. Replay is available for 9 months.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
