SQL Stored Procedure Generator

• Download SQLGen binaries - 40.3 KB
• Download SQLGen source - 86.8 KB

Introduction

The beginning of any new project/application mostly starts with a database, some classes, and then the UI. One of the most boring/tedious elements to this is generating the Stored Procedures needed for simple CRUD operations, as well as the class that references the database with all its methods etc.

Background

I searched pretty far and wide for a simple and decent application that would take an existing table from a database and generate the most common Stored Procedures for me, as well as script out the C# code to call these procs. There weren't too many useful applications that worked for me (or actually worked!).

Using the Code

By iterating through a selected table's columns, we can generate the script as need be. By treating some columns differently (i.e., making them appear in a Where clause, or knowing they can't be updated), the scripting can become fairly intelligent. (See example in code samples below.)

Here, a picture is worth quite a few words. The picture above shows the operations of the application.

Shown below is some code which repeats itself in various ways throughout the app. It's a mixture of SqlClient and SqlSMO operations to interrogate a database and its tables/columns etc.

//Code to iterate tables             
private ServerConnection serverConnection = null;

private void btnGo_Click(object sender, EventArgs e)
{
    this.Cursor = Cursors.WaitCursor;
    lstTables.Items.Clear();
    SqlConnection objCn = new SqlConnection(sqlControl1.ConnectionString);
    serverConnection = new ServerConnection(objCn);
    Server server = new Server(serverConnection);
    TableCollection objTables = 
      server.Databases[sqlControl1.DatabaseName].Tables;

    foreach (Table objTable in objTables)
    {
        lstTables.Items.Add(objTable.Name);
    }
    this.Cursor = Cursors.Default;
}

//Snippet to show column iteration and generation of script.
if (chkSelect.Checked)
{
    strSQL = "CREATE PROCEDURE [GetAll" + 
                  strTableName + "] " + Environment.NewLine;
    strSQL += " As Select ";

    foreach (Column item in server.Databases[
               sqlControl1.DatabaseName].Tables[strTableName].Columns)
    {
        keyCount++;
        strSQL += "[" + item.Name.ToString() + "]";
        if (keyCount < server.Databases[
           sqlControl1.DatabaseName].Tables[strTableName].Columns.Count) 
           strSQL += "," + Environment.NewLine;
    }

    strSQL +=" from [" + strTableName + "]";
    txtSQL.Text += strSQL + Environment.NewLine + Environment.NewLine;
}

Below is a screenshot of a sample table, and the subsequent generated scripts.

CREATE PROCEDURE [GetAllVariables] As Select [ID], 
  [VarName], [VarDesc], [VarType] from [Variables]
CREATE PROCEDURE [DeleteVariables] (@ID int) As Delete from [Variables] Where ID = @ID
CREATE PROCEDURE [AddVariables] (@VarName varchar, @VarDesc varchar, @VarType varchar) 
  As Insert Into [Variables] ( VarName, VarDesc, VarType) 
  Values ( @VarName, @VarDesc, @VarType) select SCOPE_IDENTITY()
CREATE PROCEDURE [UpdateVariables] (@ID int, @VarName varchar, 
  @VarDesc varchar, @VarType varchar) As Update [Variables] set 
  VarName = @VarName, VarDesc = @VarDesc, VarType = @VarType Where ID = @ID
CREATE PROCEDURE [AddUpdateVariables] (@ID int, @VarName varchar, @VarDesc varchar, 
  @VarType varchar) As Begin If (Select ID from [Variables] 
  Where ID = @ID) <> 0 Begin Update [Variables] set VarName = @VarName, 
  VarDesc = @VarDesc, VarType = @VarType Where ID = @ID End Else Begin Insert 
  Into [Variables] ( VarName, VarDesc, VarType) 
  Values ( @VarName, @VarDesc, @VarType) select SCOPE_IDENTITY() End End 

Points of Interest

Nothing too special here about this project, just some string manipulation and working with the SQL SMO objects.

Note: The "Save To File" button will save each script individually. Also, I include "select SCOPE_IDENTITY()" in my insert procedures, because that's something I use quite often.

History

This is the first iteration. The next one will be adding the ability to generate the classes and interfaces for a table.