devxlogo

Building a Stored Procedure Generator

Building a Stored Procedure Generator

e 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.

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. See the sidebar More On Stored Procedures for useful links to background information on stored procedures.

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 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
A stored procedure generator is basically an application that, when run, creates a stored procedure script.

This example uses a five-character string for the primary key, but best practices often dictate 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 data-retrieval stored procedure collects all the key values for all the rows in the table. You can 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)

This case requires no parameter because it retrieves all the rows and includes them 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 retrieve only those rows that have an active status.

This article contains the code to generate both of these common 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 value as a parameter that determines which operation to perform.

Retrieve the Column Data
After defining the basic structure of the stored procedures that your stored procedure generator should generate, it’s time to write some code.

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.

?
Figure 1: This user interface allows the user to define the name of the table for which the stored procedure is to be generated and then click on a button to select the type of Retrieve stored procedure to generate.

Because the stored procedure generator needs to obtain the name of each column in the table, it needs to contain 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.

Author’s Note: You can use the Microsoft Data Access Blocks (part of the Enterprise Library) to retrieve data for any application. See www.microsoft.com/practices for more information. 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) to hold 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 the 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.

                              
Author’s 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;      }   }

After defining the connection string, 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 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.

Author’s 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” character 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 that it’s 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 the stored procedures for a table will be alphabetically sorted together in the Server Explorer. This makes it easier to find all 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 

In C#:

      sb.Append("CREATE PROCEDURE dbo." + spName          + " 
");      sb.Append("	 	 @" + sID + " 	nchar(5) 
");      sb.Append("AS 
 
");      sb.Append("SELECT  
");      foreach (DataColumn col in ds.Tables[0].Columns)      {         sb.Append(" 	 	 " + col.Caption);         if (col.Ordinal 

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) or 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.

A Database project lets you 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:

  1. Right-click the solution in the Solution Explorer and select Add then choose New Project from the context menu, or from the File menu select Add Project then select New Project. You'll see the Add New Project dialog box.
  2. Select Database Projects as the project type from the Other Projects node in the Add New Project dialog box.
  3. Define a project name for the Database project, verify the location, and click OK.
  4. Author's Note: 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.

  5. The Add Database Reference dialog box is displayed. This dialog box lists all the database connections defined in the Server Explorer Data Connections node. Select the database to be used by the Database project or add a new database connection. For this example, add a new database connection to the Northwind database. Then click OK.

The new Database project is then added to your solution and appears in the Solution Explorer with all 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," which displays the Add New Item dialog box. 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 the location in the script where you wish to paste the stored procedure script text that you 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 each 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.

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 your tables and generate all your scripts instead of doing one at a time. Make the stored procedure generator work for you.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist