Creating basic data access stored procedures is time consuming and boring work.
Relieve the tedium by writing code that writes these stored procedures for you.
We all know that the most efficient way to access data from a database is to use stored procedures. For most applications, these stored procedures follow the same basic design whereby you list every field that you need to retrieve or save. For large tables or a large number of tables, writing these stored procedures can be very cumbersome and prone to typographical errors.
A stored procedure generator is basically an application that, when run, creates a stored procedure script.
This article demonstrates how you can build your own stored procedure generator. You can then modify and enhance this stored procedure generator to tailor the results to your stored procedure style.
Define Your Stored Procedure Structure
Stored procedures that perform complex and unique operations are best done manually. But most stored procedures perform basic database operations such as retrieve, insert, update, and delete. Often, these operations are accomplished with stored procedures that look identical except for the actual table and field names. These types of stored procedures are prime candidates for a stored procedure generator.
A stored procedure generator is basically an application that, when run, creates a stored procedure script. You can then save the script within a Database project and run it against your database to create the stored procedure. Details on using Database projects are provided later in this article.
The first step in building a stored procedure generator is to define the basic structure for your most common types of stored procedures.
Let's use the Customers table from the Northwind database as an example. A script that defines a stored procedure to retrieve all of the fields within a table given a unique ID might look like this:
CREATE PROCEDURE dbo.CustomersRetrieveByID_sp
@CustomerID nchar(5)
AS
SELECT
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
FROM Customers
WHERE CustomerID = @CustomerID
This example uses a five-character string for the primary key, but best practices often dictates using a unique, meaningless, numeric key (such as that defined with an Identity column in SQL Server). If your tables use a numeric key, just change the data type of the stored procedure parameter.
Another common retrieve stored procedure is one in which you collect all of the key values for all of the rows in the table. You can then bind the result of this stored procedure to a drop down list or other user-interface element for user selection. For example, to define a stored procedure that returns a list of key fields for all customers, your stored procedure script might look like this:
CREATE PROCEDURE dbo.CustomersRetrieveList_sp
AS
SELECT
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address
FROM Customers WITH (NOLOCK)
There is no parameter in this case because all rows are to be retrieved and included in the list. If instead of deleting rows you add a status field to your table to mark rows as active or deleted, then you will need to add a WHERE clause to only retrieve those rows that have an active status.
This article contains the code to generate both of these types of retrieve stored procedures. You may want to add more types, such as insert, update, and delete stored procedures. If so, you should also define the structure for those types of procedures. Common practice is to create one stored procedure that supports insert, update, and delete operations passing in the ADO RowState as a parameter to determine which operation to perform.
Retrieve the Column Data
After you have defined the basic structure of the stored procedures that you want your stored procedure generator to generate, it is time to write some code.
A Database project allows you to create a project in your solution that manages your stored procedure scripts and other database script files.
Begin by creating a new solution. Add a Windows Forms project to the solution. Update the form for the desired user interface for your generator. Figure 1 shows an example interface.
Since the stored procedure generator will need to obtain the name of each column in the table, it will need to have code to access the database. The easiest way to write this code is to not write this code at all. Instead, use the pre-existing and pre-tested Microsoft Data Access Blocks that are part of the Enterprise Library (www.microsoft.com/practices). Simply download and install the Data Access Application Block (I used the 2.0 version, but you can pick any version). Then select to add an existing project to your solution and add the Data Access Application Block project.
**Note ** If you select the 2.0 version, you will have the option to include a VB or C# project into your solution.
The next step is to define a stored procedure generator class (SPGenerator) that will include all of the code that generates the stored procedure. You can add this class as a separate component in your solution, or just add the class to the Windows Forms project that you already created.
Before you begin to create the code in this SPGenerator class, create a reference to the Data Access Block. Then add a directive for this block and the other .NET Framework components that the class will need as follows.
In VB:
Imports Microsoft.ApplicationBlocks.Data
Imports System.Configuration
In C#:
using System;
using System.Data;
using Microsoft.ApplicationBlocks.Data;
using System.Configuration;
Notice that the C# version has more directives. This is because System and System.Data namespaces are already defined for the VB project. (See Project Properties, Common Properties, Imports)
The Data Access Application blocks methods require a connection string. One of the most common ways to manage a connection string is to define the string in the application configuration file (App.config) as follows.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="CONNECTION" value="data source=
(local);initial catalog=Northwind;
user ID=sa;password=" />
</appSettings>
</configuration>
**Note ** For improved security in your application, you should store the user ID and password as encrypted values or store them somewhere else. They are included here to make it easier for you to access the sample Northwind database.
Define a property in the SPGenerator class that retrieves the connection string from the configuration file. This ConnectionString property uses the ConfigurationSettings class to retrieve the connection string.
In VB:
Private ReadOnly Property ConnectionString() _
As String
Get
Dim sConnectionString As String
sConnectionString =
ConfigurationSettings.AppSettings("CONNECTION")
Return sConnectionString
End Get
End Property
In C#:
private string ConnectionString
{
get
{
string sConnectionString;
sConnectionString =
ConfigurationSettings.AppSettings["CONNECTION"];
return sConnectionString;
}
}
Now that the connection string is defined, a method in the SPGenerator class can call a method in the Data Access Blocks to retrieve the data needed to generate the stored procedure.
In VB:
Private Function Retrieve(ByVal sTable As String)_
As DataSet
Dim ds As DataSet
Dim sWhere As String
Try
sWhere = sTable.TrimEnd("s"c) & "ID = ''"
ds=SqlHelper.ExecuteDataset(ConnectionString, _
CommandType.Text, "SELECT * FROM " & sTable _
& " WHERE " & sWhere)
Catch ex As Exception
Throw New ArgumentNullException("The table: " _
& sTable & _
" either does not exist or cannot be accessed.")
End Try
Return ds
End Function
In C#:
private DataSet Retrieve(string sTable)
{
DataSet ds;
string sWhere;
try
{
char cRemove = 's';
sWhere = sTable.TrimEnd(cRemove) + "ID = ''";
ds = SqlHelper.ExecuteDataset(ConnectionString,
CommandType.Text, "SELECT * FROM " + sTable +
" WHERE " + sWhere);
}
catch
{
throw new ArgumentNullException("The table: " +
sTable +
" either does not exist or cannot be accessed.");
}
return ds;
}
The Retrieve method retrieves a DataSet with all of the columns and no rows. It does not need any specific data from the table, only the column definitions. To retrieve the columns but no rows, the SELECT statement uses a WHERE clause with a blank primary key, assuming it is not valid to have a row with a blank primary key.
**Note ** If you use a numeric key, the WHERE clause could use a primary key value of 0, assuming 0 is not a valid primary key value.
The code uses the TrimEnd method of the String class to trim any “s” from the end of the table name and then concatenates “ID” to the table name to define the primary key name. This makes the assumption that the primary key has the singular form of the table name plus the string “ID”. In the Northwind example, the table name is “Customers” and the primary key is “CustomerID,” the table name is “Employees” and the primary key is “EmployeeID,” and so on.
Most database naming standards define the table name as singular and then the primary key as the exact table name with “ID” appended to it. If this is the style that you use, you can remove the TrimEnd method call. If your naming conventions differ, you may need to modify this code further.
Generate the Script
In looking at the structure of a stored procedure script, it is obvious to see that it is just text. So a stored procedure generator simply needs to output text.
Add a BuildRetrieveByIDSP method to the SPGenerator class to build the script for the stored procedure that retrieves one row for a particular primary key value. This method begins by calling the Retrieve method to retrieve the column data.
In VB:
Public Function BuildRetrieveByIDSP(ByVal sTable _
As String) As String
Dim ds As DataSet
Dim sb As System.Text.StringBuilder = _
New System.Text.StringBuilder
Dim sID As String
Dim sWhere As String
Dim spName As String
spName = sTable & "RetrieveByID_sp"
sID = sTable.TrimEnd("s"c) & "ID"
ds = Retrieve(sTable)
In C#:
public string BuildRetrieveByIDSP(string sTable)
{
DataSet ds;
System.Text.StringBuilder sb =
new System.Text.StringBuilder();
string sID;
string sWhere;
string spName;
spName = sTable + "RetrieveByID_sp";
char cRemove = 's';
sID = sTable.TrimEnd(cRemove) + "ID";
ds = Retrieve(sTable);
This method defines the stored procedure name by concatenating the table name with the string “RetrieveByID_sp”. This creates a name of the form CustomersRetrieveByID_sp. By using the table name as the prefix of the stored procedure name, all of the stored procedures for a table will be alphabetically sorted together in the Server Explorer. This makes it easier to find all of the stored procedures associated with a specific table.
The code then uses the same TrimEnd method of the string to again trim an “s” from the table name to create the primary key name. Note that you could create a property to define the primary key name so that this code would not need to be repeated here and in the Retrieve method.
The next part of the BuildRetrieveByIDSP method uses the StringBuilder class to build a large string containing the stored procedure script. Using the StringBuilder is much more efficient than concatenating a large number of individual strings.
In VB:
sb.Append("CREATE PROCEDURE dbo." & spName _
& vbCrLf)
sb.Append(vbTab & vbTab & "@" & sID & vbTab _
& "nchar(5)" & vbCrLf)
sb.Append("AS" & vbCrLf & vbCrLf)
sb.Append("SELECT " & vbCrLf)
For Each col As DataColumn In _
ds.Tables(0).Columns
sb.Append(vbTab & vbTab & col.Caption)
If col.Ordinal < _
ds.Tables(0).Columns.Count - 1 Then
sb.Append(", " & vbCrLf)
Else
sb.Append(vbCrLf)
End If
Next
sb.Append("FROM " & sTable & vbCrLf)
sWhere = sID & " = @" & sID
sb.Append("WHERE " & sWhere & vbCrLf & vbCrLf _
& vbCrLf)
Return sb.ToString
End Function
In C#:
sb.Append("CREATE PROCEDURE dbo." + spName
+ " \r\n");
sb.Append("\t \t @" + sID + " \tnchar(5) \r\n");
sb.Append("AS \r\n \r\n");
sb.Append("SELECT \r\n");
foreach (DataColumn col in ds.Tables[0].Columns)
{
sb.Append(" \t \t " + col.Caption);
if (col.Ordinal <
ds.Tables[0].Columns.Count - 1)
{
sb.Append(", \r\n");
}
else
{
sb.Append(" \r\n");
}
}
sb.Append("FROM " + sTable + " \r\n");
sWhere = sID + " = @" + sID;
sb.Append("WHERE " + sWhere + " \r\n \r\n \r\n");
return sb.ToString();
}
The first generated line of the script is the CREATE PROCEDURE statement. The second line is the ID parameter, followed by the AS statement and the SELECT statement.
Each column in the table is then processed and the column name is added to the script. The If statement ensures that each column name, except for the last one, is followed by a comma.
The FROM and WHERE clauses are then added to the script and the resulting string is returned from this method.
Add a BuildRetrieveListSP method to the SPGenerator class to build the script for the stored procedure that retrieves key data for all rows in a table. The code for this method is similar to that in BuildRetrieveByIDSP and is provided in Listing 1 (for VB) and Listing 2 (for C#).
Note that the BuildRetrieveListSP code makes the assumption that the first four fields are the most important and only retrieves the first four fields. If this assumption is not correct for your tables, you can adjust the code as needed. The code also has no WHERE clause because it retrieves all rows.
Finish the User Interface
Now that you've generated the script, you need to output it to somewhere. You could create an actual file containing the stored procedure script. Or you could choose to display the script and allow the user to decide what to do with it. This stored procedure generator uses the second option, as shown in Figure 1.
To finish the user interface of the stored procedure generator, declare a module-level variable and create an instance of the SPGenerator class.
In VB:
Private m_oSPGen As SPGenerator
Private Sub StartupWin_Load(ByVal sender _
As Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
m_oSPGen = New SPGenerator
End Sub
In C#:
private SPGenerator m_oSPGen;
private void StartupWin_Load(object sender,
System.EventArgs e)
{
m_oSPGen = new SPGenerator();
}
Add event procedures for the two stored procedure buttons. These procedures call the appropriate method and display the result in the multi-line textbox. You can see the event procedure that calls BuildRetrieveByIDSP below. The event procedure that calls BuildRetrieveListSP (not shown) is similar.
In VB:
Private Sub btnRetrieveByID_Click(ByVal sender As_
System.Object, ByVal e As System.EventArgs) _
Handles btnRetrieveByID.Click
Try
txtSPScript.Text = _
m_oSPGen.BuildRetrieveByIDSP(txtTableName.Text)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
In C#:
private void btnRetrieveByID_Click(object sender,
System.EventArgs e)
{
try
{
txtSPScript.Text =
m_oSPGen.BuildRetrieveByIDSP(txtTableName.Text);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
The code for the Copy to Clipboard button uses the Clipboard class to copy the information from the script to the clipboard.
In VB:
Private Sub btnCopy_Click(ByVal sender As _
System.Object, ByVal e As System.EventArgs) _
Handles btnCopy.Click
Clipboard.SetDataObject(txtSPScript.Text)
End Sub
In C#:
private void btnCopy_Click(object sender,
System.EventArgs e)
{
Clipboard.SetDataObject(txtSPScript.Text);
}
That's it. You can run the project, type in a table name (such as Customers) and click on one of the buttons to generate the script. Then click on the Copy to Clipboard button to copy the script to the Clipboard.
But, now that you have the script on the Clipboard, what do you do with it? The best answer is to paste it into a Database project script.
A Database project allows you to create a project in your solution that manages your stored procedure scripts and other database script files.
Add a Database project to your solution as follows:
**CAUTION ** The location of the database project cannot be changed, so ensure that it is correct before clicking OK in the Add New Project dialog box.
The new Database project is then added to your solution and appears in the Solution Explorer with all of your other projects. Under the Database project is a set of folders for managing your scripts, queries, and database references.
Create new stored procedures in the Create Scripts folder of the Database project by right-clicking Create Scripts and selecting Add SQL Script. The Add New Item dialog box is then displayed. To create a new stored procedure, select Stored Procedure Script. You should now see a template for a stored procedure script displayed as a tab in the Visual Studio editor.
Select where in the script you wish to paste the stored procedure script text that you had copied to the clipboard with the stored procedure generator and paste it in.
Test the resulting stored procedure script by right-clicking it and choosing Run Selection. Apply it to the database by right-clicking in the stored procedure script file and choosing Run.
Repeat this process for every table and type of stored procedure that you wish to generate.
The Database project is a convenient place to manage your database script files. They are all saved as part of the solution, and if the solution is managed by a source code control product, the script files will be under source code control as well.
Conclusion
Developing basic retrieve stored procedures is often a required, but repetitive process and prone to typographical errors. Developing a stored procedure generator to generate these types of stored procedures minimizes the amount of time needed to create and debug your stored procedures.
Now that you have the basics of a stored procedure generator, you can enhance it as you need. Modify it to include a standard comment header block. Add code to support generation of insert, update, and delete stored procedures. Add standard concurrency processing. Add code to loop through all of your tables and generate all of your scripts instead of doing one at a time. Make the stored procedure generator work for you.
Listing 1: The BuildRetrieveListSP method shown in VB
Public Function BuildRetrieveListSP(ByVal sTable As String) _
As String
Dim ds As DataSet
Dim sb As System.Text.StringBuilder = _
New System.Text.StringBuilder
Dim spName As String
spName = sTable & "RetrieveList_sp"
ds = Retrieve(sTable)
sb.Append("CREATE PROCEDURE dbo." & spName & vbCrLf & vbCrLf)
sb.Append("AS" & vbCrLf & vbCrLf)
sb.Append("SELECT " & vbCrLf)
For Each col As DataColumn In ds.Tables(0).Columns
sb.Append(vbTab & vbTab & col.Caption)
If col.Ordinal < 4 Then
sb.Append(", " & vbCrLf)
Else
sb.Append(vbCrLf)
Exit For
End If
Next
sb.Append("FROM " & sTable & " WITH (NOLOCK)" & vbCrLf)
Return sb.ToString
End Function
Listing 2: The BuildRetrieveListSP method shown in C#
public string BuildRetrieveListSP(string sTable)
{
DataSet ds;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
string spName;
spName = sTable + "RetrieveList_sp";
ds = Retrieve(sTable);
sb.Append("CREATE PROCEDURE dbo." + spName + " \r\n \r\n");
sb.Append("AS \r\n \r\n");
sb.Append("SELECT \r\n");
foreach (DataColumn col in ds.Tables[0].Columns)
{
sb.Append("\t\t" + col.Caption);
if (col.Ordinal < 4)
{
sb.Append(", \r\n");
}
else
{
sb.Append(" \r\n");
break;
}
}
sb.Append("FROM " + sTable + " WITH (NOLOCK) \r\n");
return sb.ToString();
}
