Browse DevX
Sign up for e-mail newsletters from DevX


Building a Stored Procedure Generator : Page 3

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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date