Click here to Skip to main content
Click here to Skip to main content
Go to top

SQL Stored Procedure Generator

, 29 Dec 2009
Rate this:
Please Sign up or sign in to vote.
A simple application to generate Stored Procedures for existing tables in a SQL Server database.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

JayessSoftware
Founder Jayess Software
South Africa South Africa
Jayess Software is a provider of both outsourced development resources, and developed solutions

Comments and Discussions

 
QuestionSP Gen using Tools4SQL.net PinmemberMember 435095014-Feb-14 2:52 
SuggestionSmall suggestion PinmemberMember 978640120-Oct-13 6:25 
QuestionGreat PinmemberMember 920344323-Nov-12 23:05 
SuggestionFail to save procedure PinmemberMember 900291119-Jul-12 6:02 
GeneralMy vote of 5 PinmemberESST22-Oct-10 18:42 
GeneralNice article Pinmemberjkpieters4-Feb-10 1:26 
GeneralMy vote of 2 PinmemberSimon Hughes6-Jan-10 2:33 
GeneralSmall suggession Pinmemberchvbreddy4-Jan-10 22:21 
RantRe: Small suggession PinmemberGary Noter5-Jan-10 11:34 
GeneralOh NO!!! PinmemberDavid Lean4-Jan-10 16:58 
GeneralRe: Oh NO!!! Pinmemberjkpieters4-Feb-10 1:22 
Show us how you would do this...
 
Regards,
 
Jan
QuestionRe: Oh NO!!! PinmemberCrshTstDmmy11-Jan-11 7:50 
QuestionSCOPE_IDENTITY??? PinmemberIrasimus30-Dec-09 6:16 
AnswerRe: SCOPE_IDENTITY??? PinmemberMi.Chal.31-Dec-09 1:33 
GeneralRe: SCOPE_IDENTITY??? PinmemberIrasimus6-Jan-10 10:31 
GeneralVery Good PinmemberSyed M Hussain30-Dec-09 2:48 
GeneralGood article PinmemberColinBud29-Dec-09 23:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140921.1 | Last Updated 30 Dec 2009
Article Copyright 2009 by JayessSoftware
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid