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 ... |
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 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()
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 Database Code Articles
More By Curtis_Swartzentruber  developerWorks - FREE Tools!
|
You'll get answers to many questions and more from David Barnes, Lead Evangelist for IBM Emerging Internet Technologies. David 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!
| | | |
Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, for an overview of Rational’s new software offerings and resources to help modernize and accelerate software innovation on i on Power Systems – while ensuring past application investments are protected and continue to grow. Learn how these solutions are helping customers extend their core i5/OS solutions toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time.
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!
| | | |
Manage, govern, and share services across your organization by using WebSphere Service Registry and Repository. Follow the hands-on exercises to learn how to navigate the Web interface to publish, find, reuse, and update services.
FREE! Go There Now!
| | | |
Learn how to implement a build management system that uses and extends your existing automation technologies. This tutorial shows, step-by-step, how to install and configure IBM Rational Build Forge to manage builds for Jakarta Tomcat from source code.
FREE! Go There Now!
| | | |
Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available.
FREE! Go There Now!
| | | |
Informix Dynamic Server (IDS) Express Edition offers outstanding online transaction processing (OLTP) database performance, while helping to simplify and automate many of the tasks associated with deploying databases for small business applications. IDS 11 further extends the ease of management and applications integration with the Admin API and Scheduler, high availability with Continuous Log Restore for backup server recovery in case of a primary server failure, and column level encryption to protect personal and company private data.
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!
| | | |
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!
| |
/ 36 
