WEBINAR:
On-Demand
Application Security Testing: An Integral Part of DevOps
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.