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

Stored Procedure Generator

, 20 Jun 2007
Rate this:
Please Sign up or sign in to vote.
This utility creates basic Select, Insert, Update and Delete Stored procedure for the selected Tables.

Introduction

If we use dataset as a data access object every time we create a new project, after the database design is complete we wish to create the basic stored procedures for each and every table such as Select, Insert, Update and Delete. If a table has 10 columns, it's a real pain to write annoying stored procedures which do not need brains. I decided to create a utility which can create these stored procedures for us, in a click (for all the tables in the selected database).

Background

My motive for this application was to automate stored procedures, so for browsing server and database, I nicked in code from Michael Potter. You can find his article from this link.

Using the Code

To start with, this utility allows you to browse through available servers:

Screenshot - SelectServer.jpg

After the database is selected, you want to select a valid database, if you know what you want, you can just type in these two. For database, the precondition is: you must provide a valid username and password which is created in that particular server and it should have access to the database you are going to select.

Screenshot - SelectDatabase.jpg

After selecting the database, you can click on the button "Get Tables". This fires an event (Stored Procedure) to get all the table names in the database selected.

CREATE   PROCEDURE DBO.TableNameSelect
(    
  @TableCatalog AS VARCHAR(100)
) AS
SELECT Table_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
and       TABLE_CATALOG = @TableCatalog
and       TABLE_NAME <>   'dtproperties'
GO

This comes up with tables as shown below:

Screenshot - Create_Stored_Procedure.jpg

You have a choice to select the stored procedure you want to create: Select, Insert, Update, and Delete. Once you have selected, click on "Create procedure" button, the system will get all the columns for each table through the following stored procedure.

CREATE PROCEDURE DBO.ColumnNameSelect 
(
@TableName AS VARCHAR(100) 
) AS 
SELECT COLUMN_NAME, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_Name = @TableName 
GO 

P.S.: The system searches for the above stored procedure and if it does not find one, it creates it, so no worries.

The system now has all the data it needs. Now we can create the stored procedure as needed. For example, to create "Select Stored procedure", we can do something like this:

try 
{
  selectBuilder.AppendLine(string.Format("[{0}Select] ( @{1} AS INT) AS ", 
                           tableName, columnTable.Rows[0]["Column_Name"])); 
  selectBuilder.AppendLine("SELECT ");
  foreach (DataRow row in columnTable.Rows) 
  { 
    selectBuilder.Append(string.Format("[{0}],", row["Column_Name"])); 
  }
  selectBuilder = RemoveLastComma(selectBuilder); 
  selectBuilder.AppendLine(string.Format(" FROM [{0}] ", tableName)); 
  selectBuilder.Append(string.Format("WHERE [{0}] = @{0}", 
                                    columnTable.Rows[0]["Column_Name"])); 
  DataAccess.CreateProcedure(selectBuilder.ToString(), connection); 
}
catch (Exception ex) 
{ 
  throw new Exception(ex.Message); 
} 

Points of Interest

While creating this utility, I just had a thought that an Enterprise Object Framework can be created, which can be an extension to this. Well, I will continue doing that.

P.S.: The code creates and audits the stored procedures created.

History

  • 20th June, 2007: Initial post

License

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

Share

About the Author

RepliCrux
Web Developer
Australia Australia
I am working as a Microsoft .Net Analyst\Programmer in Transact (Telecommunication commpany), located in Australian Capital territory. Its been 3 years since I am doing .Net development work. Finally I have decided to jump into "code project" to contribute my learnings over the years.
I am thinking of completing MCPD so wish me luck !! Smile | :)

Comments and Discussions

 
QuestionThanks PinmemberJasRaj Bishnoi14-Nov-13 19:16 
GeneralError PinmemberYasin7528-Jun-07 19:50 
GeneralRe: Error PinmemberRepliCrux28-Jun-07 19:52 
GeneralImprovements Pinmembermokles28-Jun-07 2:36 
Questionuse existing tools? Pinmemberi61825-Jun-07 21:00 
AnswerRe: use existing tools? PinmemberRepliCrux25-Jun-07 21:05 
GeneralRe: use existing tools? PinmemberJasmine250126-Jun-07 8:14 
GeneralWindows Authentication Pinmemberspoodygoon21-Jun-07 0:24 
GeneralRe: Windows Authentication PinmemberRepliCrux21-Jun-07 0:30 
GeneralComments and suggestion PinmemberNickolay Karnaukhov20-Jun-07 22:56 
Nice article about code autogeneration. In common - it's a good way to create some kind of background for database management framework for complex applications. But I have some suggestions regarding application and other common things. Here they are:
1. Application can be more user-friendly in wizard style or more easy in all-in-one style. For example you can put list of servers in combo-box, then if selected server is changed - put databases in second combo box, after database is selected - fill checkboxed-listbox with tables to create procedures for.
2. Connection data can be saved in registry, ini file or xml config file so application can fill it in again.
3. Code can also be generated in same way as procedures.
4. Code can be compiled into assembly after it was generated.
 
May be you have implemented some of suggestions above - honestly - I didn't downloaded an application, so excuse me if I wrong or missed something.
 
Last thing is about your english (my english is not perfect too) - try to check sentances - they're not correct in most cases in your article. So be kind to non-native-english-thinking people here, as long as you're non-native-english-thinking.
 
Great work after all! You've got +4, not +5 because of english, sorry. Smile | :)
 
------------------------------------------------------------
Want to be happy - do what you like!

GeneralRe: Comments and suggestion PinmemberNickolay Karnaukhov20-Jun-07 22:59 
GeneralRe: Comments and suggestion PinmemberRepliCrux21-Jun-07 0:18 

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
Web01 | 2.8.140827.1 | Last Updated 21 Jun 2007
Article Copyright 2007 by RepliCrux
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid