Click here to Skip to main content
Click here to Skip to main content

SQL Stored Procedure Generator

, 29 Dec 2009 CPOL
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 3:52 
SuggestionSmall suggestion PinmemberMember 978640120-Oct-13 7:25 
QuestionGreat PinmemberMember 920344324-Nov-12 0:05 
SuggestionFail to save procedure PinmemberMember 900291119-Jul-12 7:02 
GeneralMy vote of 5 PinmemberESST22-Oct-10 19:42 
GeneralNice article Pinmemberjkpieters4-Feb-10 2:26 
GeneralMy vote of 2 PinmemberSimon Hughes6-Jan-10 3:33 
GeneralSmall suggession Pinmemberchvbreddy4-Jan-10 23:21 
RantRe: Small suggession PinmemberGary Noter5-Jan-10 12:34 
GeneralOh NO!!! PinmemberDavid Lean4-Jan-10 17:58 
Your solution seems nice & fits your design spec.
However your design spec is destined to create Database Hell.
Please don't do stored procs this way.
 
Stored Procs work best when they are used to abstract the application from the DB Schema. Its best to create SP's that perform a unit of useful work eg: p_ListCustomerOrders(@CustID) or p_AddNewInvoice()
Try to minimise the number of network roundtrips, perhaps by:-
a. Inserting/Updating multiple related tables from 1 sp,
b. Passing TableValueParameters, to process multiple rows at once, eg new Invoice & all its Invoice items.
c. Using an OUTPUT clause on your DML statements to have it pass back the Defaults & Identity values as part of the inesrt.
d. Use SQL Service Broker Queues instead of Triggers & multiple calls to SP's.
 
Yes I know that creating 4 SP's for each table is a common practise. Even the Microsoft EDM team designed their system expecting 4 SP's like this. Which is why the MS SQL field people told them their design was doomed & the SQL interface needed a rewrite.
 
I have been assisting Microsoft Customers & ISV's tune their SQL Server Systems for the past 20 years. Over that time I've worked closely with the MS SQL Dev Team & many of the worlwide SQL MVP Folks. And yes I can program in C, C#, VB etc not just TSQL.
Trust me whan I say "This is not a good practise".
 
That said. It is better to abstract your access to SQL via Stored Procs (as you are doing)than send T-SQL directly from your app. So if this is where you stop then I guess it is better than nothing.
 
(Note: a part of the issue here is that you'll have hundreds of SP's that someone has to maintain. Any change to a table requires updates to all its SP's many of which your appliaction never actually calls. Your Application will call SPs which return columns that it doesn't actually require, so indexes that could be used, aren't. Your app, calls many SP's when one SP might do. You are likely to find that most of these procs are insufficient so you'll either need to create more with more TSQL smarts OR you'll encourage your developers to retrieve half your database back to the client only to use filters & datasets to manipulate the resultsets in RAM. This This causes the DB, the Network & the Client to do more work.
 
I hope you find this useful, sorry to be such a party pooper.
GeneralRe: Oh NO!!! Pinmemberjkpieters4-Feb-10 2:22 
QuestionRe: Oh NO!!! PinmemberCrshTstDmmy11-Jan-11 8:50 
QuestionSCOPE_IDENTITY??? PinmemberIrasimus30-Dec-09 7:16 
AnswerRe: SCOPE_IDENTITY??? PinmemberMi.Chal.31-Dec-09 2:33 
GeneralRe: SCOPE_IDENTITY??? PinmemberIrasimus6-Jan-10 11:31 
GeneralVery Good PinmemberSyed M Hussain30-Dec-09 3:48 
GeneralGood article PinmemberColinBud30-Dec-09 0: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 | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 30 Dec 2009
Article Copyright 2009 by JayessSoftware
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid