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)

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 
I tried to do that, but the problem seems to be with the ODBC32.dll. When I try to get the database names, it does not likes it without username and password.
GeneralComments and suggestion PinmemberNickolay Karnaukhov20-Jun-07 22:56 
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
Web03 | 2.8.140721.1 | Last Updated 21 Jun 2007
Article Copyright 2007 by RepliCrux
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid