Stored procedures provide the ability to perform some action against a database and have some advantages to querying data from an application. First and foremost, it reduces the amount of redundancy and has a performance gain when multiple calls are executed. In addition, it does not have any hindrances when used with the .NET 2.0 Framework, as stored procedures can be used directly in the ASP.NET 2.0 data source controls.

But coding those stored procedures can be a tedious task, especially when many of the stored procedures work the same way. They read, insert, delete, and update rows in the database using the same or similar structure. When reading data, usually all of the fields are read from, and so no specialization is usually needed. When inserting data, usually all of the fields are inserted and all of the same fields, except for the key value, are updated in an update stored procedure.  Deletions usually occur by specifying the key field only, but that is not necessarily true.  Sometimes the create/update stored procedure is lumped into one. This is true in business-object applications, but may be less true in data-driven applications.

This article will show you how a generator for stored procedures could be created using the new Windows Presentation Framework (WPF) features of .NET 3.0.

SQL Server has a series of system views that can be used to query tables, columns, table relationships, stored procedures, and other information about a SQL Server database. Through these views it is possible to get the list of a table's columns and their data types, and generate a stored procedure from this information. This would assume that the parameter name for a stored procedure would be the same as the column name with the "@" sign preceding it. To get the list of tables from SQL Server 2000/2005, use the following query.

Listing 1

select * from INFORMATION_SCHEMA.Tables where Table_Type = 'BASE TABLE'

The query above uses the INFORMATION_SCHEMA views to query information about the table only. The above query only works in the context of the database it is querying from, so you have to change the database name to collect information from another database. Use the "use" statement or selecting the database in Query Analyzer or Management Studio drop down will do this.  Note that the important fields that come from this query are the TABLE_NAME and TABLE_SCHEMA columns, and the possible table type values are "BASE TABLE" and "VIEW."

To query the columns, the following query can be used:

Listing 2

select * from INFORMATION_SCHEMA.Columns

This will query information about columns, but there are two small complications. First, the TABLE_TYPE field is stored only in the Tables view, meaning an inner join is necessary when limiting the query to tables only. Secondly, the data type length is broken up into several fields.  The first field is the CHARACTER_MAXIMUM_LENGTH field, which represents the maximum length of text data. The second field is the NUMERIC_PRECISION, which is the total number of digits for the field, and the associated NUMERIC_SCALE for the total number of digits to the right of the decimal point. The other situation is with dates; dates have their own precision, which does not match their actual precision, but is a precision relative to dates.  For instance, SQL Server datetime data type is 8 bytes of storage with a precision of 23, but is represented as a precision of 3 when queried from the view.

To get the primary key information, I assume the order of the table has the primary key field first; however, in reality this information can come from the following query which matches the primary or foreign key name to the table, schema, and column that is the primary or foreign key.

Listing 3

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

In the example application I use Windows Presentation Foundation (WPF) to create a stored procedure generator, one that has the ability to create a select, update, delete, and insert procedure.  There are some variants that would be important to have, as well as the ability to generate dynamic templates, but in this version of the code, I did not do that.

In addition, it would have been nice to use a builder approach, but for the first go around I decided to build the SQL directly in the code-behind file. It was easier to see what the needs of the application were, and made the code more easily understandable.

At the top of the application is a grid which lays out the application horizontally. At the top is a Menu control, which contains the menu item to connect to the database. Clicking the connect button pops up a popup window, which takes the name of a database and a server (using windows authentication only).

Listing 4

<MenuItem Header="File">
      <MenuItem Header="Connect" x:Name="ConnectMenuItem"
        Click="OnConnectMenuItemClick"/>
      <MenuItem Command="ApplicationCommands.Close" Header="Exit"/>
</MenuItem>

Upon clicking the menu item, the following code runs:

Listing 5

this.ConnectPopup.IsOpen = true;
this.ConnectPopup.StaysOpen = false;

This opens the following popup, but ensures it does not stay open permanently.

Listing 6

<Popup x:Name="ConnectPopup" Grid.Row="0" PopupAnimation="Fade" 
  PlacementTarget="{Binding ElementName=TabLayout}" Placement="Bottom" 
  PlacementRectangle="20,25,40,30">
      <Grid Width="200" Height="100" Background="LightYellow">
            <Grid.RowDefinitions>
                  <RowDefinition Height="Auto" />
                  <RowDefinition Height="Auto" />
                  <RowDefinition Height="Auto" />
            </Grid.RowDefinitions>
            <Grid.ColumnDefinitions>
                  <ColumnDefinition Width="75" />
                  <ColumnDefinition Width="Auto" />
            </Grid.ColumnDefinitions>
                        
            <Label Grid.Row="0" Grid.Column="0" Width="50">Server</Label>
            <TextBox Name="Server" Grid.Row="0" Grid.Column="1" Width="50" />
            <Label Grid.Row="1" Grid.Column="0" Width="50">Database Name</Label>
            <TextBox Name="Database" Grid.Row="1" Grid.Column="1" Width="50" />
                        
            <Button Name="ConnectButton" Content="Connect" Grid.ColumnSpan="2" 
               Grid.Row="2" Click="OnConnectButtonClick" />
      </Grid>
</Popup>

Notice the popup definition above which has certain attributes. I explain about the popup in my blog, but I will reiterate some of the information here. The Popup control can take a container control as its child, which is commonly a grid (but could be another control as well). This grid control, if you are unfamiliar with it, specifies the grid-like structure that a group of controls will adapt to. The grid uses the RowDefinitions/ColumnDefinitions to setup the grid, and uses the Grid.Row or Grid.Column properties to set the child controls to appear in a specific region.

The popup control can specify the location to appear through the Placement, PlacementTarget, and the PlacementRectangle properties.  Upon entering the server/database and clicking connect, the tables are loaded into the sidebar shown below.

Listing 7

<DockPanel>
      <TreeView Name="TablesListing" DockPanel.Dock="Left"
        HorizontalAlignment="Left" Width="150px" Background="#FFF2F0CD" 
        BorderThickness="1,1,1,1" Foreground="#FF6182F5" 
        SelectedItemChanged="OnTablesListingSelectedItemChanged"/>
</DockPanel>

The TreeView tables/columns are loaded, and upon selecting each table, the string builder generates itself inside an expander control, within a text box so the user can copy/paste them into SQL Server. The expander groups control very nicely inside it, and allows the other stored procedures to remain hidden.

Listing 8

<StackPanel Orientation="Vertical" DockPanel.Dock="Top">
      <Expander Header="Create Procedure">
            <TextBox Name="CreateProcedure" Style="{StaticResource SPStyle}" />
      </Expander>
      <Expander Header="Delete Procedure">
            <TextBox Name="DeleteProcedure" Style="{StaticResource SPStyle}" />
      </Expander>
      <Expander Header="Update Procedure">
            <TextBox Name="UpdateProcedure" Style="{StaticResource SPStyle}" />
      </Expander>
      <Expander Header="Select Procedure">
            <TextBox Name="SelectProcedure" Style="{StaticResource SPStyle}" />
      </Expander>
</StackPanel>

To create the tree view, the table's information schema view is queried and the results are parsed. The table name is created in the format of schema.tablename. This node is created as a single string and to create a shorter version of the table name later requires splitting the name at the decimal point.

Columns are created in the format of name, followed by a comma, and then the data type/length combination (such as "EventID,varchar(10)"). To get the column name requires splitting by the comma between the name/data type. That is useful for later.

Listing 9

foreach (DataRow tableRow in _information.Tables["Tables"].Rows)
{
      string tableSchema = tableRow["TABLE_SCHEMA"].ToString();
      string tableName = tableRow["TABLE_NAME"].ToString();
      TreeViewItem tableItem = new TreeViewItem();
      tableItem.Header = string.Format("{0}.{1}", tableSchema, tableName);
      this.TablesListing.Items.Add(tableItem);
 
      DataView columnsView = _information.Tables["Columns"].DefaultView;
      columnsView.RowFilter = string.Format(
          "TABLE_NAME = '{1}' and TABLE_SCHEMA = '{0}'", tableSchema, tableName);
 
      for (int i = 0; i < columnsView.Count; i++)
      {
            DataRow columnRow = columnsView[i].Row;
            string columnName = columnRow["COLUMN_NAME"].ToString();
            string dataType = columnRow["DATA_TYPE"].ToString();
            int precision = (int)(columnRow.IsNull("CHARACTER_MAXIMUM_LENGTH") 
              ? 0 
              : columnRow["CHARACTER_MAXIMUM_LENGTH"]);
            precision = !columnRow.IsNull("NUMERIC_PRECISION") 
               ? (int)columnRow["NUMERIC_PRECISION"]
              : precision;
 
            string text = columnName + ", " + dataType;
            if (precision > 0)
                  text += "(" + precision.ToString() + ")";
 
            TreeViewItem columnItem = new TreeViewItem();
            columnItem.Header = text;
            tableItem.Items.Add(columnItem);
      }
}

All of the information gets pulled from the information schema views and creates the appropriate tree view items. To get the information back, it is collected from these tree view items and parsed appropriately.

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

[Download Sample]

In this article you have seen how to create and generate a stored procedure code with the help of a WPF application.