Stored Procedure Generator [Advance]

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.

1. SPGen starts up and lists all locally registered SQL Servers in the top left drop down list
2. Select, or type in, the SQL Server you want to connect to
3. Enter in the User Name and Password for the SQL Server. If there is no Password needed then just leave the Password field untouched
4. Click the Connect button
5. SPGen will now attempt to connect to the specified SQL Server and list all the Databases
6. Once the Databases are listed, expand the Database you wish to work with
7. SPGen will now list all the Tables within the expanded Database
8. Now expand the Table you wish to generate an SP for
9. There will be two options;

   UPDATE
   

   or INSERT. Click the one you want
10. SPGen will now attempt to retrieve the columns for the Table (but not display them) and generate the specified SP type
11. 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:

• StoredProcedureTypes 
                          sptypeGenerate

  
  An enum indicating the type of Stored Procedure to generate. StoredProcedureTypes.INSERT and StoredProcedureTypes.UPDATE are currently valid choices
• SQLDMO.Columns colsFields
  The Columns collection to use in the generation of the Stored Procedure
• string sTableName
  The name of the Table the INSERT or UPDATE will affect

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.