Stored Procedure Generator

• Download source - 52.03 KB

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:

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.

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:

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

• 20^th June, 2007: Initial post