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-generation tool.
By Dan Fergus
As a tools and desktop application developer, I never had much need for a SQL Server database. Microsoft Access did just about everything I needed. And for the things Access couldn't do, I simply would code my own workaround. But over the last year, I've become more of a Web application developer through the use of the .NET Framework SDK and Web Services. I'm now writing applications that require the enterprise capabilities of SQL Server. As I made this transition, I found many things to like about SQL Server, and a few things not to like. Sometimes an item can take on both Jekyll and Hyde attributes.
Take stored procedures, for instance. For accessing a database over the Web, stored procedures are the only way to go. From the security aspect, you can restrict user access to stored procedures. If a malicious user gets hold of the connect string, they won't be able to anything other than run the stored procedures. With no direct access to the tables, they can do only a limited amount of damage. If the login you use to connect has full database privileges, the user could wreak havoc by doing a DROP TABLE command on the database. Other attacks on your Web site can come from SQL Injection, where a user enters T-SQL code in the login boxes that can run and act on your system data. The parameter checking done in a stored procedure prevents this type of attack.
Although stored procedures are good, you need to write a lot of code to get them working. Figure 1 shows the code required to make a SQL stored procedure call that requires four parameters.
public void AssignGame(string umpid, int gameid,
int position, string umpName) {
string connectString = _ConnectString;
SqlConnection cn = new SqlConnection(connectString);
SqlCommand sqlcmd =
new SqlCommand("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 stored procedure can involve writing a lot of code. Connections must be created, opened, and closed, and you must create a SqlParameter object and assign a value to it for each parameter the stored procedure requires. The code should also contain appropriate exception handling to protect your application. And this is all pretty much cookie-cutter type of code.
This type of boilerplate code lends itself well to a code-generation tool, and that's what I'll do in this article: I'll show you how to build a code generator for stored procedure calls.
A Little Design
I wanted to make this application portable and configurable. At the top of my list was the requirement to display all the stored procedures in the database and allow the user to choose the one for which they want to generate. Second, I wanted the ability to generate code in either Visual Basic .NET or C#. The code I'll discuss in this article is written in VB .NET, but the application generates either language as output (this article's code download contains both VB .NET and C# code; see the Download box for details). You can extend this application to generate code for any of the other languages the .NET runtime supports. Finally, I wanted the ability to select the return type of the code to be generated. This implementation requires you to know the data type the stored procedure returns. On your own you can use a more advanced technique to analyze the stored procedure code itself 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 = "SELECT name, id FROM sysobjects " + _
"WHERE (type = 'P') AND (Category = 0)"
Dim cn As SqlConnection = _
New SqlConnection(txtConnectString.Text)
Dim sqlcmd As SqlCommand = New SqlCommand(cmd, cn)
sqlcmd.CommandType = CommandType.Text
listBox1.Items.Clear()
Try
Dim ods As DataSet = New DataSet()
Dim oda As SqlDataAdapter = _
New SqlDataAdapter(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 data from SQL Server's system tables. To get a list of the stored procedures in a particular database, you look in the sysobjects table. The information needed for this application is the stored procedure's name and ID. The name is used for 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 get each stored procedure's name and ID. Then you store this information in a custom object, spObject, and add it to the list box. A little magic happens here. By default, if you call the ToString method on a class, the method returns the name of the class - in this case, spObject. To get this to work, however, spObject's ToString method must be overridden 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 a list box, the Framework calls the object's ToString method to use a string representation of the object in the list.
Set Up the Foundation
In your UI, the user selects the procedure for which the calling code is to be generated. The ID of the stored procedure is retrieved from the spObject selected in the list box and is used in the SQL statement's where clause to retrieve the data needed to build the code. The syscolumns system table holds all the information needed to generate the code. The code in Figure 3 shows how to use the ID obtained earlier to pull the information about the stored procedure needed to set up the parameters in the code 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 the syscolumns table and find the
' columns that relate to the requested sproc.
Dim cmd As String = "SELECT name, xtype, length," + _
" isoutparam FROM syscolumns " + _
" WHERE (id=" + sp.ID.ToString() + ")"
cn = New SqlConnection(txtConnectString.Text)
sqlcmd = New SqlCommand(cmd, cn)
sqlcmd.CommandType = CommandType.Text
Catch ex As Exception
Throw ex
End Try
Try
Dim ods As DataSet = New DataSet
Dim oda As SqlDataAdapter = 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 In ods.Tables(0).Rows
Dim NewParam As spParam = _
New spParam(dr.ItemArray(0).ToString(), _
Convert.ToInt32(dr.ItemArray(1)))
Select Case Convert.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 provides enough information to build the code. The name field is the variable name, the xtype 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 iterates through 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 ArrayList is built that contains as many spParam objects as there are parameters to the stored procedure call. The spParam object contains information about 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 connect string and the return type are determined. The connect string can be either of two choices. You can enter a variable name in the In Code Constant text box and use that value in the generated code as the connection string. If this box is left blank, however, the connection string used to retrieve the stored procedures is used in the code. Based on which option button is selected, either a VB or a C# code-generating class is instantiated and the GenerateCode method is called.
Build the Code
Both classes, CSharpCodeBuilder and VBCodeBuilder, implement an interface named ICodeBuilder. This interface contains only three methods:
Public Interface ICodeBuilder
Function BuildEntryPoint() As String
Function CreateIndividualParamterCode() As String
Function GenerateCode() As String
End Interface
The entry point into each class is the GenerateCode method; all the others are implemented as Private and therefore cannot be called outside the class. The generated code is a complete Sub or Function with entry and exit points. As such, the first code generated is the method entry point, produced by walking the ArrayList that contains the spParam objects and generating code for each parameter in the collection:
Public Function Call_DeleteEvent(itemid As Int32) _
As DataSet
This example shows what the BuildEntryPoint method builds for a simple procedure from IBuySpy. The function has the same name as the stored procedure with "Call_" preceding it. This method also uses the return 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 SQL Server client
strTemp += "Dim cn As SqlConnection = " + _
"new SqlConnection( " + _connectString + " )" + vbCrLf
strTemp += "Dim sqlcmd As SqlCommand = " + _
"new SqlCommand(""" + _sprocName + """, cn)" + vbCrLf
strTemp += "sqlcmd.CommandType = " + _
"CommandType.StoredProcedure" + vbCrLf
Before you can make the call to execute the stored procedure, you need to build the list of SqlParameters required by the stored procedure (see Figure 4).
Private Function CreateIndividualParamterCode() _
As String Implements
CodeBuilder.CreateIndividualParamterCode
Dim localTemp As String = ""
localTemp = "Dim sp As SqlParameter" + vbCrLf + vbCrLf
' walk through the collection and build the code
Dim q As spParam
For Each q In _spa
' If parameter is an output parameter adjust a little
If q.Direction = ParameterDirection.Output Then
Dim parameterName As String = "ret" + q.ArgName
' Give it a unique Parameter 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 needs to know whether the direction of the parameter is in or out. Input parameters take no special action, but the output parameters do. The output parameters need to have individual names so that after the procedure is executed, their values can be passed back to the caller. Note that the SqlParameter names are the same as the stored procedure parameter names. The name might be a reserved word in Visual Basic .NET.
As you can see in the code, it is straightforward to do an input parameter, but more work is required for an output parameter. All the input parameters use a single SqlParameter variable. Some code samples create a new variable for every SqlParameter. This seems like overkill and is not my approach. For output parameters, however, you must create new variables for each parameter, and the code in this sample uses the parameter names for the variable name.
So far, the code consists of an entry point, the connection code, and the list of parameters. The next step is to build the calls that actually execute our stored procedure. The code required to make these calls is basic. The complexity comes from the fact that you can have four different return types along with the choice of including exception handling or not:
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: Open the connection, execute the query, and close the connection. This code shows how the code is generated for returning a DataSet, without exception handling. The variable names are generic and you can change them to something more 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 = " + _
"New SqlDataAdapter(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 that the close method is called by placing the cn.Close call in the Finally block as shown.
The final job of the code generator is to close the method with either an End Sub or End Function statement. Once you do this, the code is inserted into the text box on the application GUI. In this box, you can make any edits you wish or simply copy the code to the clipboard as is.
Use the Code Generator
You can see in Figure 5 how the application looks after generating 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 the clipboard, or you can highlight the code and press Ctrl+C. As written, the code generator does not insert tabs or spaces in a line of code. I assumed the code would be pasted into the Visual Studio IDE, which does an excellent job of formatting the code for you. As always, because the source code is included with the article, you can make any changes you need to generate the output you want.
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 fit it into your development process. Many Web applications these days, and desktop applications too, use a customer interface, a business layer, and a data layer. Here, the code generator defines the business logic in the Web service method and then calls the data layer, which makes the call to the stored procedure. To accomplish this, you create the Web method and the data interface call with the proper variables or structures required to pass the data:
[WebMethod (Description= _
"Gets a string of Roles that the user belongs to")]
public String GetRoles(String firstname, String lastname) {
try {
DataInterface di = new DataInterface();
return di.GetRolesInternal(firstname, lastname);
}
finally {}
}
DataInterface Code
internal String GetRolesInternal(String firstname, _
String lastname)
{
}
Once the method is defined, you create the stored procedure and any required tables. The best part of using the Visual Studio IDE is you can do all the testing you need on the database through the Server Explorer tool window. Once you've created any tables and have written and tested the stored procedure, you point the code generator at the new procedure and build the code. Copy the generated code into the GetRoles method and you're done.
The tedious and repetitive nature of the database-calling code lends itself well to a code generator. Many enhancements are possible if you want to take this another step. Stored procedures should be an integral part of any Web application that talks to a data store. If you look at the benefits to the security of your application and add to that the new ease of writing code to talk to the database, stored procedures will become a normal part of your development day.
The sample code in this article is available for download.
Dan Fergus is the chief architect at Forest Software Group, providing custom software and Web Services. He is a well-known lecturer who travels the world speaking at developer conferences, and he works as a consultant and instructor for Wintellect, teaching Windows CE, .NET Compact Framework, and ASP.NET courses. E-mail Dan at mailto:[email protected].
Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.
