Introduction
Writing the basics of Stored Procedures is mind
numbing at best, even for DBAs. Megan Forbes, myself and a
few others got into a heated rant about Microsoft SQL Server
Enterprise Manager and it's extreme lack of SP tools and
management. I decided to write a very simple app which takes
away the drudge of typing in all the base code for an SP.
When you are faced with a table of 50 fields and the need to
create a simple UPDATE
or
INSERT
SP, declaring all those parameters can be akin
to agreeing to be the designated driver for the office
Christmas party, i.e. deadly boring.
Using the application
Extract the downloaded demo zip, or re-compile the
project, and run the executable.
- SPGen starts up and lists all locally
registered SQL Servers in the top left drop down list
- Select, or type in, the SQL Server you want
to connect to
- Enter in the User Name and Password for the
SQL Server. If there is no Password needed then just
leave the Password field untouched
- Click the Connect button
- SPGen will now attempt to connect to the
specified SQL Server and list all the Databases
- Once the Databases are listed, expand the
Database you wish to work with
- SPGen will now list all the Tables within
the expanded Database
- Now expand the Table you wish to generate an
SP for
- There will be two options;
UPDATE
or INSERT
. Click the
one you want
- SPGen will now attempt to retrieve the
columns for the Table (but not display them) and
generate the specified SP type
- Once generated the code is placed in the
text box on the right and you can cut & paste that code
into Microsoft SQL Enterprise Manager, or Microsoft SQL
Server Query Analyzer
That is the extent of SPGen's functionality. You
can generate SPs for other Tables, without having to
re-connect, or you can connect to another SQL Server and
generate SPs for that.
SQLDMOHelper
SQLDMOHelper
is a simple
class which returns basic information about a SQL Server to
the caller. Really it just wraps up the common methods I
needed from SQLDMO into easy to use .NET methods which
return easily usable data. To this end it only returns data
and does not provide any methods to save changes to a SQL
Server, yet.
Using SQLDMO in your .NET app is actually very
simple. All you need to do is add a reference to the
Microsoft SQLDMO Object Library COM object in your project.
You can then utilise SQLDMO methods with the interopped
SQLDMO namespace. All very simple thanks to .NET.
Property:
public
Array RegisteredServers
This property returns a one-dimensional string
array containing the names of all registered SQL Servers in
the local domain.
SQLDMO provides a class called
ApplicationClass
which you can use to gather this
list, like so;
ArrayList aServers = <span class="cs-keyword" nd="31">new ArrayList();
SQLDMO.ApplicationClass acServers = <span class="cs-keyword" nd="32">new SQLDMO.ApplicationClass();
<span class="cs-keyword" nd="33">for (<span class="cs-keyword" nd="34">int iServerGroupCount = <span class="cs-literal" nd="35">1;
iServerGroupCount <= acServers.ServerGroups.Count;
iServerGroupCount++)
<span class="cs-keyword" nd="36">for (<span class="cs-keyword" nd="37">int iServerCount = <span class="cs-literal" nd="38">1;
iServerCount <= acServers.ServerGroups.Item(
iServerGroupCount).RegisteredServers.Count;
iServerCount++)
aServers.Add(acServers.ServerGroups.Item
(iServerGroupCount).RegisteredServers.Item(iServerCount).Name);
<span class="cs-keyword" nd="39">return aServers.ToArray();
Quite simply a new instance of
ApplicationClass
is created. Then a
for
loop runs
through each
ServerGroups
returned and
then in the second
for
loop adds each
RegisteredServer
name to
the
aServers
ArrayList
.
aServers
is then returned to the caller
to be consumed.
ArrayList
really makes
working with un-known length arrays very easy. You can
basically redimension the array on the fly and then once you
are finished use the ToArray
method to
return a valid Array
.
Property:
public
Array Databases
Databases
is a property
which returns, as the name suggest, a one-dimensional string
array of all Databases in a specified SQL Server.
ArrayList aDatabases = <span class="cs-keyword" nd="54">new ArrayList();
<span class="cs-keyword" nd="55">foreach(SQLDMO.Database dbCurrent <span class="cs-keyword" nd="56">in Connection.Databases)
aDatabases.Add(dbCurrent.Name);
<span class="cs-keyword" nd="57">return aDatabases.ToArray();
A simple foreach
loop is run
against the SQLDMO.Databases
collection
which is returned from Connection.Databases
.
Connection
is a property of
SQLDMOHelper
which provides a SQLDMO
Server connection. You need to use the
Connect
method to set the
Connection
property up. Also remember to use the
DisConnect
method to, wait for it, disconnect the
connection.
Databases
then returns the
string array of Database names for your app to use.
Property:
public
Array Tables
Looks familiar, doesn't it? It is. The
Tables
property returns a
one-dimensional string array of all Table names in a
specified Database.
ArrayList aTables = <span class="cs-keyword" nd="73">new ArrayList();
SQLDMO.Database dbCurrent = (SQLDMO.Database)Connection.Databases.Item(
<span class="cs-keyword" nd="74">this.Database, Connection);
<span class="cs-keyword" nd="75">foreach(SQLDMO.Table tblCurrent <span class="cs-keyword" nd="76">in dbCurrent.Tables)
aTables.Add(tblCurrent.Name);
<span class="cs-keyword" nd="77">return aTables.ToArray();
Property:
public
SQLDMO.Columns Fields
The Fields
property however
is a bit different. Instead of returning a one-dimensional
string array it returns a SQLDMO.Columns
collection which provides a full range of details on all
columns (fields) within a table.
The code though is even simpler than before as we
are really just returning what SQLDMO provides and not
translating it at all:
SQLDMO.Database dbCurrent = (SQLDMO.Database)
Connection.Databases.Item(<span class="cs-keyword" nd="83">this.Database, Connection);
SQLDMO.Table tblCurrent = (SQLDMO.Table)
dbCurrent.Tables.Item(<span class="cs-keyword" nd="84">this.Table, Connection);
<span class="cs-keyword" nd="85">return tblCurrent.Columns;
Columns
is a collection of
SQLDMO.Column
objects which contain
various properties and methods for working on a field in a
table. In SPGen only Name
,
DataType
and Length
are used, but there are many more.
Properties: string ServerName
,
UserName
, Password
, DataBase
and Table
These four properties of
SQLDMOHelper
are simply strings which hold what SQL
Server, user name, password, database and table respectively
the methods of
SQLDMOHelper
should work
on. For instance
Databases
requires
just
ServerName
,
UserName
and
Password
to be filled in to work.
To use
Fields
though you also need
Database
and
Table
filled in so that
Fields
knows what to
work on.
StoredProcedure
The StoredProcedure
class
provides just one method at the moment,
Generate
. This, finally, is the heart of SPGen and
provides the functionality for returning valid Stored
Procedure code.
Method:
public
string Generate
Parameters:
The code within Generate
is pretty straight forward and consists largely of a
StringBuilder
being used to construct
the Stored Procedure. On that note I found the
AppendFormat
method of
StrinbBuilder
to be highly effective for this kind of
work.
Take this code for instance:
sParamDeclaration.AppendFormat(<span class="cpp-string" nd="125">"
@{0} {1}", new
string[]{colCurrent.Name, colCurrent.Datatype});
.
Without the
AppendFormat
method one
would have to do the following:
sParamDeclaration += <span class="cpp-string" nd="128">" @"
+ colCurrent.Name + <span class="cpp-string" nd="129">" "
+ colCurrent.Datatype;
This latter way is terrible to
debug and hard to understand when there is a whole page of
similar code. The format functionality of
StringBuilder
(and just
String
itself) makes for much more manageable and understandable
string manipulation.
StringBuilder
also is
faster than using
sSample +=
<span class="cpp-string" nd="135">"Not in kansas, " +
sName + <span class="cpp-string" nd="136">", anymore";
,
especially when performing many string appends. Thanks to
Tom Archer's fantastic
sample chapter on using String
in .NET, I
certainly learnt a lot from it.
One other slight item of interest in the
Generate
method is this:
<span class="cs-keyword" nd="139">if (
colCurrent.Datatype == <span class="cpp-string" nd="141">"binary" ||
colCurrent.Datatype == <span class="cpp-string" nd="142">"char" ||
colCurrent.Datatype == <span class="cpp-string" nd="143">"nchar" ||
colCurrent.Datatype == <span class="cpp-string" nd="144">"nvarchar" ||
colCurrent.Datatype == <span class="cpp-string" nd="145">"varbinary" ||
colCurrent.Datatype == <span class="cpp-string" nd="146">"varchar")
sParamDeclaration.AppendFormat(<span class="cpp-string" nd="147">"({0})", colCurrent.Length);
Basically in TSQL you must only declare the
length of a parameter if it is one of the above data types.
If you for instance try
@NameFirst
int(4)
in TSQL you will get back an error as you may not declare
the length of an
int
data type. At present I know of no way to programmatically
detect which data types must and must not have length
declarations, therefore I have used the cumbersome
if
block you see
above. I was hoping that
SqlDbType
would provide the necessary information, but it does not,
rendering it slightly less useful.
Apart from the the method is as stated mainly a
big string manipulation method which takes in the provided
fields, loops through them and returns a Stored Procedure of
the type specified.
As I find more areas to automate in regards to
Stored Procedures I hope to add new methods and helpers to
this class.