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();
}