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.
Public Function BuildRetrieveByIDSP(ByVal sTable _
As String) As String
Dim ds As DataSet
Dim sb As 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)
public string BuildRetrieveByIDSP(string sTable)
System.Text.StringBuilder sb =
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
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.
sb.Append("CREATE PROCEDURE dbo." & spName _
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)
sb.Append("FROM " & sTable & vbCrLf)
sWhere = sID & " = @" & sID
sb.Append("WHERE " & sWhere & vbCrLf & vbCrLf _
sb.Append("CREATE PROCEDURE dbo." + spName
+ " \r\n");
sb.Append("\t \t @" + sID + " \tnchar(5) \r\n");
sb.Append("AS \r\n \r\n");
foreach (DataColumn col in ds.Tables.Columns)
sb.Append(" \t \t " + col.Caption);
if (col.Ordinal <
ds.Tables.Columns.Count - 1)
sb.Append("FROM " + sTable + " \r\n");
sWhere = sID + " = @" + sID;
sb.Append("WHERE " + sWhere + " \r\n \r\n \r\n");
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
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.
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
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.