Generate Code to Call Stored Procedures

asp:Feature

LANGUAGES: VB .NET | C#

TECHNOLOGIES: SQL | SQL Server | T-SQL

Generate Code to Call Stored Procedures

Save code-writing time with this cool code-generationtool.

By Dan Fergus

As a tools and desktop application developer, I never hadmuch need for a SQL Server database. Microsoft Access did just about everythingI needed. And for the things Access couldn't do, I simply would code my ownworkaround. But over the last year, I've become more of a Web applicationdeveloper through the use of the .NET Framework SDK and Web Services. I'm nowwriting applications that require the enterprise capabilities of SQL Server. AsI made this transition, I found many things to like about SQL Server, and a fewthings not to like. Sometimes an item can take on both Jekyll and Hydeattributes.

Take stored procedures, for instance. For accessing adatabase over the Web, stored procedures are the only way to go. From thesecurity aspect, you can restrict user access to stored procedures. If amalicious user gets hold of the connect string, they won't be able to anythingother than run the stored procedures. With no direct access to the tables, theycan do only a limited amount of damage. If the login you use to connect hasfull database privileges, the user could wreak havoc by doing a DROP TABLEcommand on the database. Other attacks on your Web site can come from SQLInjection, where a user enters T-SQL code in the login boxes that can run andact on your system data. The parameter checking done in a stored procedureprevents this type of attack.

Although stored procedures are good, you need to write alot of code to get them working. Figure 1 shows the code required to make a SQLstored procedure call that requires four parameters.

public void AssignGame(string umpid, int gameid,

 int position, stringumpName) {

  string connectString =_ConnectString;

  SqlConnection cn = newSqlConnection(connectString);

  SqlCommand sqlcmd =

   newSqlCommand("AssignGame", cn);

  sqlcmd.CommandType =CommandType.StoredProcedure;

  sqlParameter sp =sqlcmd.Parameters.Add("@umpid",

   SqlDbType.VarChar);

  sp.Value = umpid;

  sp =sqlcmd.Parameters.Add("@gameid",

   SqlDbType.Int);

  sp.Value = gameid;

  sp =sqlcmd.Parameters.Add("@position",

   SqlDbType.Int);

  sp.Value = position;

  sp =sqlcmd.Parameters.Add("@umpname",

  SqlDbType.VarChar);

  sp.Value = umpName;

  cn.Open();

  try {

    sqlcmd.ExecuteNonQuery();

  }

  finally {

    cn.Close();

  }

  return;

}

Figure 1. Even a call to a relatively simple storedprocedure can involve writing a lot of code. Connections must be created,opened, and closed, and you must create a SqlParameter object and assign avalue to it for each parameter the stored procedure requires. The code shouldalso contain appropriate exception handling to protect your application. Andthis is all pretty much cookie-cutter type of code.

This type of boilerplate code lends itself well to acode-generation tool, and that's what I'll do in this article: I'll show youhow to build a code generator for stored procedure calls.

A Little Design

I wanted to make this application portable andconfigurable. At the top of my list was the requirement to display all thestored procedures in the database and allow the user to choose the one forwhich they want to generate. Second, I wanted the ability to generate code ineither Visual Basic .NET or C#. The code I'll discuss in this article iswritten in VB .NET, but the application generates either language as output(this article's code download contains both VB .NET and C# code; see theDownload box for details). You can extend this application to generate code forany of the other languages the .NET runtime supports. Finally, I wanted theability to select the return type of the code to be generated. This implementationrequires you to know the data type the stored procedure returns. On your ownyou can use a more advanced technique to analyze the stored procedure codeitself to see what the return type should be.

After a short expedition into SQL Server's system tables,you can see how to access the list of stored procedures (see Figure 2).

Dim cmd As String = "SELECTname, id FROM sysobjects " + _

 "WHERE (type = 'P')AND (Category = 0)"

Dim cn As SqlConnection = _

 NewSqlConnection(txtConnectString.Text)

Dim sqlcmd As SqlCommand = New SqlCommand(cmd, cn)

sqlcmd.CommandType = CommandType.Text

listBox1.Items.Clear()

Try

  Dim ods As DataSet = NewDataSet()

  Dim oda AsSqlDataAdapter = _

   NewSqlDataAdapter(sqlcmd)

  oda.Fill(ods)

  Dim dr As DataRow

  For Each dr In ods.Tables(0).Rows

    Dim obj As spObject =New spObject( _

     dr.ItemArray(0).ToString(), _

     Convert.ToInt32(dr.ItemArray(1)))

    listBox1.Items.Add(obj)

    Next

  Catch

  Finally

End Try

Figure 2. You can mine a considerable amount of datafrom SQL Server's system tables. To get a list of the stored procedures in aparticular database, you look in the sysobjects table. The information neededfor this application is the stored procedure's name and ID. The name is usedfor display and the ID is used later to get details about the stored procedure.

The code in Figure 2 looks in the sysobjects table to geteach stored procedure's name and ID. Then you store this information in acustom object, spObject, and add it to the list box. A little magichappens here. By default, if you call the ToString method on a class,the method returns the name of the class - in this case, spObject. Toget this to work, however, spObject's ToString method must beoverridden to return the name of the stored procedure:

Public Class spObject

  Dim _Name As String

  Dim _id As Int32

  Public Overrides Function ToString() As String

    Return _Name

  End Function

    . . .

End Class

When you add an object - instead of a simple string - to alist box, the Framework calls the object's ToString method to use astring representation of the object in the list.

Set Up the Foundation

In your UI, the user selects the procedure for which thecalling code is to be generated. The ID of the stored procedure is retrievedfrom the spObject selected in the list box and is used in the SQLstatement's where clause to retrieve the data needed to build the code.The syscolumns system table holds all the information needed to generate thecode. The code in Figure 3 shows how to use the ID obtained earlier to pull theinformation about the stored procedure needed to set up the parameters in thecode that's about to be generated.

Dim sp As spObject = CType(listBox1.SelectedItem, spObject)

Dim cn As SqlConnection

Dim sqlcmd As SqlCommand

Try

  ' Call into thesyscolumns table and find the

  ' columns that relate tothe requested sproc.

  Dim cmd As String ="SELECT name, xtype, length," + _

   " isoutparam FROMsyscolumns " + _

   " WHERE (id="+ sp.ID.ToString() + ")"

  cn = New SqlConnection(txtConnectString.Text)

  sqlcmd = NewSqlCommand(cmd, cn)

  sqlcmd.CommandType =CommandType.Text

Catch ex As Exception

  Throw ex

End Try

Try

  Dim ods As DataSet = NewDataSet

  Dim oda AsSqlDataAdapter = New SqlDataAdapter(sqlcmd)

  oda.Fill(ods)

  ' We will collect information about each paramter

  ' and store it in here.

  Dim spa As ArrayList =New ArrayList

  Dim dr As DataRow

  For Each dr Inods.Tables(0).Rows

    Dim NewParam AsspParam = _

     NewspParam(dr.ItemArray(0).ToString(), _

      Convert.ToInt32(dr.ItemArray(1)))

    Select CaseConvert.ToInt32(dr.ItemArray(1))

      Case 36

        ' uniqueidentifier

        NewParam.SpType ="SqlDbType.UniqueIdentifier"

        NewParam.NetType ="Guid"

        Exit Select

      Case 61

         ' DateTime

        NewParam.SpType ="SqlDbType.DateTime"

        NewParam.NetType ="DateTime"

        Exit Select

      Case 56

        ' int

        NewParam.SpType ="SqlDbType.Int"

        NewParam.NetType ="Int32"

        Exit Select

      Case 167

        ' varchar

        NewParam.SpType ="SqlDbType.VarChar"

        NewParam.NetType ="String"

        Exit Select

      Case 231

        ' nvarchar

        NewParam.SpType ="SqlDbType.NVarChar"

        NewParam.NetType ="String"

        Exit Select

    End Select

    If Convert.ToBoolean(dr.ItemArray(3)) Then

      NewParam.Direction =ParameterDirection.Output

    Else

      NewParam.Direction =ParameterDirection.Input

    End If

    spa.Add(NewParam)

  Next

  . . .   more code . . .

Finally

  cn.Close()

End Try

Figure 3. SQL Server's syscolumns table providesenough information to build the code. The name field is the variable name, thextype tells you which type of Framework Class Library (FCL) variable you need,and the isoutparam gives the information needed to build the method call,specifying ByRef or ByVal. Once the DataSet is filled, the code iteratesthrough the rows and builds a collection of spParam objects.

With the required information returned in a DataSet,the procedure sets up some language-independent groundwork. An ArrayListis built that contains as many spParam objects as there are parametersto the stored procedure call. The spParam object contains informationabout each parameter including the direction of the data, the SQL data type,the .NET data type, the name, and the ID of the procedure.

Next, both the connectstring and the return type are determined. The connect string can be either of two choices. You can enter avariable name in the In Code Constant text box and use that value in thegenerated code as the connectionstring. If this box is left blank, however, the connection string used to retrieve the stored procedures is usedin the code. Based on which option button is selected, either a VB or a C#code-generating class is instantiated and the GenerateCode method iscalled.

Build the Code

Both classes, CSharpCodeBuilderand VBCodeBuilder, implement aninterface named ICodeBuilder. This interface contains only threemethods:

Public Interface ICodeBuilder

  FunctionBuildEntryPoint() As String

  FunctionCreateIndividualParamterCode() As String

  Function GenerateCode()As String

End Interface

The entry point into each class is the GenerateCodemethod; all the others are implemented as Private and therefore cannot becalled outside the class. The generated code is a complete Sub or Function withentry and exit points. As such, the first code generated is the method entrypoint, produced by walking the ArrayList that contains the spParamobjects and generating code for each parameter in the collection:

Public FunctionCall_DeleteEvent(itemid As Int32) _

 As DataSet

This example shows what the BuildEntryPoint methodbuilds for a simple procedure from IBuySpy. The function has the same name asthe stored procedure with "Call_" preceding it. This method also uses thereturn type passed to the class to define the method as a Function returning a DataSet.For anyone with experience in database programming - even Access developers -the next step is obvious: Build a connection to the database:

' Write the connection code for a SQLServer client

strTemp += "Dim cn As SqlConnection = " + _

 "new SqlConnection(" + _connectString + " )" + vbCrLf

strTemp += "Dim sqlcmd As SqlCommand = " + _

 "newSqlCommand(""" + _sprocName + """, cn)" +vbCrLf   

strTemp += "sqlcmd.CommandType = " + _

 "CommandType.StoredProcedure" + vbCrLf

Before you can make the call to execute the storedprocedure, you need to build the list of SqlParameters required by thestored procedure (see Figure 4).

Private FunctionCreateIndividualParamterCode() _

 As String Implements

CodeBuilder.CreateIndividualParamterCode

  Dim localTemp As String= ""

  localTemp = "Dim spAs SqlParameter" + vbCrLf + vbCrLf

  ' walk through thecollection and build the code

  Dim q As spParam

  For Each q In _spa

    ' If parameter is anoutput parameter adjust a little

    If q.Direction =ParameterDirection.Output Then

      Dim parameterName AsString = "ret" + q.ArgName

      ' Give it a uniqueParameter name

      localTemp += vbCrLf+ "Dim " + parameterName

      localTemp += "As SqlParameter " + vbCrLf

      localTemp +=parameterName

      localTemp += "= sqlcmd.Parameters.Add(" + """"

      localTemp +=q.SpName + """" + ", " + q.SpType

      localTemp +=")" + vbCrLf

      localTemp +=parameterName + _

       ".Value =" + q.ArgName + vbCrLf

      localTemp +=parameterName + _

       ".Direction =ParameterDirection.Output" + vbCrLf

    Else

      localTemp +="sp = sqlcmd.Parameters.Add("

      localTemp +="""" + q.SpName + """" + ", "

      localTemp +=q.SpType + ")" + vbCrLf

      localTemp +="sp.Value = " + q.ArgName + vbCrLf

    End If

  Next

  Return localTemp

End Function

Figure 4. The code to generate the SqlParameters needsto know whether the direction of the parameter is in or out. Input parameterstake no special action, but the output parameters do. The output parametersneed to have individual names so that after the procedure is executed, theirvalues can be passed back to the caller. Note that the SqlParameter names arethe same as the stored procedure parameter names. The name might be a reservedword in Visual Basic .NET.

As you can see in the code, it is straightforward to do aninput parameter, but more work is required for an output parameter. All theinput parameters use a single SqlParameter variable. Some code samplescreate a new variable for every SqlParameter. This seems like overkilland is not my approach. For output parameters, however, you must create newvariables for each parameter, and the code in this sample uses the parameternames for the variable name.

So far, the code consists of an entry point, theconnection code, and the list of parameters. The next step is to build thecalls that actually execute our stored procedure. The code required to makethese calls is basic. The complexity comes from the fact that you can have fourdifferent return types along with the choice of including exception handling ornot:

strTemp += vbCrLf + "cn.Open()" + vbCrLf

strTemp += "Dim ods As DataSet = new DataSet()" +vbCrLf

strTemp += "Dim oda As SqlDataAdapter

strTemp += " New SqlDataAdapter(sqlcmd)" + vbCrLf

strTemp += "oda.Fill(ods)" + vbCrLf

strTemp += "cn.Close()" + vbCrLf

strTemp += "return ods" + vbCrLf

To build the calling code, you must take three steps: Openthe connection, execute the query, and close the connection. This code showshow the code is generated for returning a DataSet, without exceptionhandling. The variable names are generic and you can change them to somethingmore to your liking. It's important here to remember to close the connection:

strTemp += "Try" + vbCrLf

strTemp += "cn.Open()" + vbCrLf

strTemp += "Dim ods As DataSet = New DataSet()" +vbCrLf

strTemp += "Dim oda As SqlDataAdapter = " + _

 "NewSqlDataAdapter(sqlcmd)" + vbCrLf

strTemp += "oda.Fill(ods)" + vbCrLf

strTemp += "return ods" + vbCrLf

strTemp += "Catch" + vbCrLf

strTemp += "return null" + vbCrLf

strTemp += "Finally" + vbCrLf

strTemp += "cn.Close()" + vbCrLf

strTemp += "End Try" + vbCrLf

Using .NET exception handling, you can make certain thatthe close method is called by placing the cn.Close call in the Finallyblock as shown.

The final job of the code generator is to close the methodwith either an End Sub or End Function statement. Once you dothis, the code is inserted into the text box on the application GUI. In thisbox, you can make any edits you wish or simply copy the code to the clipboardas is.

Use the Code Generator

You can see in Figure 5 how the application looks aftergenerating a list, then selecting and building the code for a stored procedure.To get the code into your editor, you either can click on the Copy button, which copies the code to theclipboard, or you can highlight the code and press Ctrl+C. As written, the codegenerator does not insert tabs or spaces in a line of code. I assumed the codewould be pasted into the Visual Studio IDE, which does an excellent job offormatting the code for you. As always, because the source code is includedwith the article, you can make any changes you need to generate the output youwant.

Figure 5. SProc provides a list of stored procedures on a SQL Server,then builds calls to the procedure you select automatically.

To make the code generator really useful, you need to fitit into your development process. Many Web applications these days, and desktopapplications too, use a customer interface, a business layer, and a data layer.Here, the code generator defines the business logic in the Web service methodand then calls the data layer, which makes the call to the stored procedure. Toaccomplish this, you create the Web method and the data interface call with theproper variables or structures required to pass the data:

[WebMethod (Description= _

 "Gets a string ofRoles that the user belongs to")]

public String GetRoles(String firstname, String lastname) {

  try {

    DataInterface di = newDataInterface();

    returndi.GetRolesInternal(firstname, lastname);

  }

  finally {}

}

DataInterface Code

internal String GetRolesInternal(String firstname, _

 String lastname)

{

}

Once the method is defined, you create the storedprocedure and any required tables. The best part of using the Visual Studio IDEis you can do all the testing you need on the database through the ServerExplorer tool window. Once you've created any tables and have written andtested the stored procedure, you point the code generator at the new procedureand build the code. Copy the generated code into the GetRoles method andyou're done.

The tedious and repetitive nature of the database-callingcode lends itself well to a code generator. Many enhancements are possible ifyou want to take this another step. Stored procedures should be an integralpart of any Web application that talks to a data store. If you look at the benefitsto the security of your application and add to that the new ease of writingcode to talk to the database, stored procedures will become a normal part ofyour development day.

The sample code in thisarticle is available for download.

Dan Fergus is the chief architect at Forest SoftwareGroup, providing custom software and Web Services. He is a well-known lecturerwho travels the world speaking at developer conferences, and he works as aconsultant and instructor for Wintellect, teaching Windows CE, .NET CompactFramework, and ASP.NET courses. E-mail Dan at mailto:[email protected].

Tell us what you think! Please send any comments aboutthis article to [email protected] include the article title and author.