Stored procedures generator

• Download source - 12.2 Kb

Introduction

The project presented serves for generating stored procedures for Insert, Update and Delete in a specified table.

Description

The job we most often have to do during writing queries is writing the basic stored procedures for manipulating data on the existing tables in a database. It is perhaps the step causing the most number of syntax errors. The application in this article helps avoid such errors. You just have to fill the required fields and your stored procedures are ready. Of course, the generated queries with some modifications can be used further in your code or server.

How to use

1. Download the project to your computer.
2. Compile/Run it.
3. Enter the table name and number of columns.
4. Press the button Reload.
5. Fill the fields for primary keys, column names, data types, sizes and Allow Nulls.
6. Before pressing the button Generate Queries, check: if there is at least one column with primary key and all columns aren't primary keys. This is important for the Update and Delete query to be generated.
7. Press the Generate Queries button.

Example

Fig.1. The window appears after running the project.

After filling the textboxes for table name and number of columns, click on Reload button:

Fig.2. Fields for table columns are generated.

Next, after filling the column table data and clicking on Generate Queries button:

Fig.3. Queries are created.

You can now copy them and use in your application simply by clicking on the Copy to Clipboard button, below each textbox.

Commenting the code

Let's see first the code executed on clicking the Reload button.

try
{
  for(int i = 0; i<no; i++) colList[i].Dispose();
    no = Convert.ToInt32(txtColumnNo.Text);
  colList = new ucColumn[no];
  for(int i = 0; i<no; i++)
  {
    colList[i] = new ucColumn();
    colList[i].Location = new System.Drawing.Point(8, 90+i*28);
    colList[i].Size = new System.Drawing.Size(432, 21);
    this.Controls.Add(colList[i]);
  }
}
catch(Exception ex)
{
  MessageBox.Show("Please type valid integer for number of columns.", 
                  "Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}

At the beginning, the number typed for No. of columns is converted to an integer, which is used for declaring and creating the array of controls dynamically. The value for control distance step is carefully selected - 90+i*28 -corresponding to its dimension.

The code to for query generation follows, e.g. for Insert query:

if(checkvalidity()=="")
{
  if((no > 0) && (txtTableName.Text != ""))
  {

Above code checks if the sizes for column types, number of columns and table name are given properly.

txtInsertQuery.Text = 
    "CREATE PROCEDURE dbo.sp_Insert_" + txtTableName.Text;
txtInsertQuery.Text += "\r\n(";
if(no>1)
{
  for(int i=0;i<no-1;i++)
    txtInsertQuery.Text += "\r\n @"+colList[i].txtColumn.Text + 
         " "+colList[i].cmbType.Text+
         createtype(colList[i].txtSize.Text)+ 
         allownull(colList[i].cmbNull.Text)+",";
    txtInsertQuery.Text += "\r\n @"+colList[no-1].txtColumn.Text + 
         " "+colList[no-1].cmbType.Text + 
         createtype(colList[no-1].txtSize.Text)+
         allownull(colList[no-1].cmbNull.Text);
}
else
{
  txtInsertQuery.Text += "\r\n @"+colList[0].txtColumn.Text+ 
         " "+colList[0].cmbType.Text+
         createtype(colList[0].txtSize.Text)+
         allownull(colList[0].cmbNull.Text);
}
txtInsertQuery.Text += "\r\n)";

Then comes creation of the header of the procedure and the declaration of variables.

txtInsertQuery.Text += 
  "\r\nAS\r\nINSERT INTO "+txtTableName.Text+"(";
if(no>1)
{
  for(int i=0;i<no-1;i++)
    txtInsertQuery.Text += colList[i].txtColumn.Text+", ";
  txtInsertQuery.Text += colList[no-1].txtColumn.Text+")";
}
else
{
  txtInsertQuery.Text += colList[0].txtColumn.Text+")";
}
txtInsertQuery.Text += "\r\nVALUES(";

if(no>1)
{
  for(int i=0;i<no-1;i++)
    txtInsertQuery.Text += "@"+colList[i].txtColumn.Text+", ";
  txtInsertQuery.Text += "@"+colList[no-1].txtColumn.Text+")";
}
else
{
  txtInsertQuery.Text += "@"+colList[0].txtColumn.Text+")";
}
txtInsertQuery.Text += "\r\nGO";

And the query for inserting is thus created.

The code for Update and Delete is similar with some additional requirements.

Happy querying!