To create stored procedures requires straight text processing.
To make the process simpler, I used a StringBuilder. Stored procedure
parameters are created by looping through the columns of a table, using the
column name directly. So if a column is dbo.tblEvents.EventID, it will be used directly
and the parameter name will be @EventID. Column names work the same way; they
use only the name of the column, so the "@" sign does not need
appended to the beginning.
Below is the generation of a selection procedure.
GetShortTableName gets the name of the table without the schema prefix, and
RenderColumnList renders a vertical list of columns, with or without the
"@" and with or without the data type.
Listing 10
private void GenerateSelectProc(TreeViewItem item)
{
string tableName = item.Header.ToString();
StringBuilder builder = new StringBuilder();
builder.AppendLine(string.Format("create procedure dbo.Select{0}",
this.GetShortTableName(tableName)));
builder.AppendLine();
builder.AppendLine("as");
builder.AppendLine();
builder.Append("select");
this.RenderColumnList(builder, item, false, false);
builder.AppendLine(string.Format("from\t{0}", tableName));
this.SelectProcedure.Text = builder.ToString();
} 
