Building a Stored Procedure Generator : Page 3

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

Previous Page

Next Page