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

Stored Procedure Generator

, 31 Jul 2008
Rate this:
Please Sign up or sign in to vote.
This software generates Insert, Update, Delete, and Select stored procedures (whichever you want) for selected tables.

SPGenerator.JPG

Introduction

StoredProcedureGenerator is a powerful tool to generate Insert, Update, Delete, and Select Stored Procedures (whichever you want) for selected tables for a particular database. You can connect to any SQL Server DB server.

Description

Select your DB server from the combo box. If you could not find your desired server, type it yourself. Check the radio-button to specify whether the DB server is on Windows Authentication or SQL Server Authentication. If it is SQL Server authentication, enter the username and password. You will be able to see all the databases on the selected database server. Select the desired database. You can test your connection by clicking Test Connection. Click the OK button. You would be able to see all the tables of the selected database in a grid. Check the tables for which you want to generate Stored Procedures. If you want this for all tables, select the checkbox on the top of the tables name (besides Name). Select the folder path where you want to save the script file. The system will give the name for the script file (StoredProcedures.sql).

Select which type of Stored Procedures you want (Insert, Update etc.). Click the OK button to generate the script file on the selected path. The system will give the names for the Stored Procedures itself according to the following format tablename_type (where type is Insert, Update etc). For example Person_Insert.

Using the Code

The following code generates the script files for creating Stored Procedures:

private void GenerateStoredProcedures()
{
    string sp = "";
    if (tablesDataGridView.Rows[0].Cells["Catalog"] != null)
    {
        sp = "USE " + tablesDataGridView.Rows[0].Cells["Catalog"].Value.ToString() +
            "\n";
        sp += "GO \n";
    }
    if (connection.State == ConnectionState.Closed)
    {
        connection.Open();
    }
    foreach (string item in spCheckedListBox.CheckedItems)
    {
        switch (item)
        {
        case "Insert":
            sp+=GenerateInsertStoredProcedure();
            break;
        case "Update":
            sp+=GenerateUpdateStoredProcedure();
            break;
        case "Delete":
            sp+=GenerateDeleteStoredProcedure();
            break;
        case "Select":
            sp+=GenerateSelectStoredProcedure();
            break;
        }
    }

    connection.Close();
    StreamWriter writer = new StreamWriter(filePathTextBox.Text +
        "\\StoredProcedures.sql");
    writer.Write(sp);
    writer.Close();
}
private string GenerateInsertStoredProcedure()
{ 
    string sp = ""; 
    foreach (DataGridViewRow tableRow in tablesDataGridView.Rows)
    {
        if (tableRow.Cells["IsSelected"].Value != null)
        {
            if ((bool)tableRow.Cells["IsSelected"].Value == true)
            {
                SqlCommand command = new SqlCommand("sp_columns", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@table_name", SqlDbType.NVarChar).Value =
                    tableRow.Cells["TableName"].Value.ToString();
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(ds);
                string spName = "[" + tableRow.Cells["Schema"].Value.ToString() + "].[" +
                    tableRow.Cells["TableName"].Value.ToString() + "_Insert]";
                sp = sp + "if exists (select * from dbo.sysobjects where id =
                     object_id(N'" + spName + "') and OBJECTPROPERTY(id,
                     N'IsProcedure') = 1) DROP PROCEDURE " + spName + "\n";
                sp = sp + "GO \n";
                sp = sp + "CREATE PROCEDURE " + spName + "\n";
                string columnNames = "";
                string parameters = "";
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    if (row["TYPE_NAME"].ToString() != "int identity")
                    {
                        string nullable = "";
                        if (row["NULLABLE"].ToString() == "1")
                        {
                            nullable = "=null";
                        }
                        sp = sp + "@" + row["COLUMN_NAME"].ToString() + " " +
                            row["TYPE_NAME"].ToString() + nullable + ","; 
                        columnNames = columnNames + "[" + row["COLUMN_NAME"].ToString() +
                            "],"; 
                        parameters = parameters + "@" +
                            row["COLUMN_NAME"].ToString() + ",";

                    }
                }
                sp = sp.Substring(0, sp.Length - 1);
                columnNames = columnNames.Substring(0, columnNames.Length - 1);
                parameters = parameters.Substring(0, parameters.Length - 1);
                sp = sp + "\n AS \n INSERT INTO " +
                    tableRow.Cells["TableName"].Value.ToString() +
                    "(" + columnNames + ") VALUES(" + parameters + ")\n";
                sp = sp + "GO \n";

            }
        }
    } 
    return sp;

}
private string GenerateUpdateStoredProcedure()
{

    string sp = ""; 
    foreach (DataGridViewRow tableRow in tablesDataGridView.Rows)
    {
        if (tableRow.Cells["IsSelected"].Value != null)
        {
            if ((bool)tableRow.Cells["IsSelected"].Value == true)
            {
                SqlCommand command = new SqlCommand("sp_columns", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@table_name", SqlDbType.NVarChar).Value =
                     tableRow.Cells["TableName"].Value.ToString();
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(ds);
                string spName = "[" + tableRow.Cells["Schema"].Value.ToString() + "].[" +
                    tableRow.Cells["TableName"].Value.ToString() + "_UpdateById]";
                sp = sp + "if exists (select * from dbo.sysobjects where id =
                    object_id(N'" + spName + "') and OBJECTPROPERTY(id,
                    N'IsProcedure') = 1) DROP PROCEDURE " + spName + "\n";
                sp = sp + "GO \n";
                sp = sp + "CREATE PROCEDURE " + spName + "\n";
                string columnNames = ""; 
                string identityColumn = "";
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    string dataType = row["TYPE_NAME"].ToString();
                    if (dataType == "int identity")
                    {
                        identityColumn = row["COLUMN_NAME"].ToString();
                        dataType = "int";
                    }
                    else
                    {
                        columnNames = columnNames + "[" + row["COLUMN_NAME"].ToString() +
                            "]=@" + row["COLUMN_NAME"].ToString() + ",";
                    }
                    string nullable = "";
                    if (row["NULLABLE"].ToString() == "1")
                    {
                        nullable = "=null";
                    }
                    sp = sp + "@" + row["COLUMN_NAME"].ToString() + " " + dataType +
                        nullable + ","; 

                }
                sp = sp.Substring(0, sp.Length - 1);
                columnNames = columnNames.Substring(0, columnNames.Length - 1); 
                sp = sp + "\n AS \n UPDATE " +
                    tableRow.Cells["TableName"].Value.ToString() + " SET " +
                    columnNames + " WHERE [" + identityColumn + "]=@" +
                    identityColumn + "\n";
                sp = sp + "GO \n";
            }
        }
    }

    return sp;
}
private string GenerateDeleteStoredProcedure()
{
    string sp = "";
    foreach (DataGridViewRow tableRow in tablesDataGridView.Rows)
    {
        if (tableRow.Cells["IsSelected"].Value != null)
        {
            if ((bool)tableRow.Cells["IsSelected"].Value == true)
            {
                SqlCommand command = new SqlCommand("sp_columns", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@table_name", SqlDbType.NVarChar).Value =
                    tableRow.Cells["TableName"].Value.ToString();
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(ds);
                string spName = "[" + tableRow.Cells["Schema"].Value.ToString() + "].[" +
                     tableRow.Cells["TableName"].Value.ToString() + "_DeleteById]";
                sp = sp + "if exists (select * from dbo.sysobjects where id =
                    object_id(N'" + spName + "') and OBJECTPROPERTY(id,
                    N'IsProcedure') = 1) DROP PROCEDURE " + spName + "\n";
                sp = sp + "GO \n";
                sp = sp + "CREATE PROCEDURE " + spName + "\n";

                string identityColumn = "";
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    string dataType = row["TYPE_NAME"].ToString();
                    if (dataType == "int identity")
                    {
                        identityColumn = row["COLUMN_NAME"].ToString();
                        dataType = "int";
                        sp += "@" + identityColumn + " " + dataType + "\n";
                    } 
                } 
                sp = sp + "\n AS \n DELETE FROM " +
                    tableRow.Cells["TableName"].Value.ToString() + " WHERE [" +
                    identityColumn + "]=@" + identityColumn + "\n";
                sp = sp + "GO \n";
            }
        }
    }

    return sp;
}
private string GenerateSelectStoredProcedure()
{
    string sp = "";
    foreach (DataGridViewRow tableRow in tablesDataGridView.Rows)
    {
        if (tableRow.Cells["IsSelected"].Value != null)
        {
            if ((bool)tableRow.Cells["IsSelected"].Value == true)
            {
                SqlCommand command = new SqlCommand("sp_columns", connection);

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@table_name", SqlDbType.NVarChar).Value =
                     tableRow.Cells["TableName"].Value.ToString();
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(ds);
                string spName = "[" + tableRow.Cells["Schema"].Value.ToString() + "].[" +
                     tableRow.Cells["TableName"].Value.ToString() + "_SelectAll]";
                sp = sp + "if exists (select * from dbo.sysobjects where id =
                    object_id(N'" + spName + "') and OBJECTPROPERTY(id,
                    N'IsProcedure') = 1) DROP PROCEDURE " + spName + "\n";
                sp = sp + "GO \n";
                sp = sp + "CREATE PROCEDURE " + spName + "\n";
                sp += "AS \n SELECT * FROM " +
                    tableRow.Cells["TableName"].Value.ToString() + "\n";
                sp += "GO \n";
                spName = "[" + tableRow.Cells["Schema"].Value.ToString() + "].[" +
                    tableRow.Cells["TableName"].Value.ToString() + "_SelectById]";
                sp = sp + "if exists (select * from dbo.sysobjects where id =
                    object_id(N'" + spName + "') and OBJECTPROPERTY(id,
                    N'IsProcedure') = 1) DROP PROCEDURE " + spName + "\n";
                sp = sp + "GO \n";
                sp = sp + "CREATE PROCEDURE " + spName + "\n";
                string identityColumn = "";
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    string dataType = row["TYPE_NAME"].ToString();
                    if (dataType == "int identity")
                    {
                        identityColumn = row["COLUMN_NAME"].ToString();
                        dataType = "int";
                        sp += "@" + identityColumn + " " + dataType + "\n";
                    }
                }
                sp = sp + "\n AS \n SELECT * FROM " +
                    tableRow.Cells["TableName"].Value.ToString() +
                    " WHERE [" + identityColumn + "]=@" + identityColumn + "\n";
                sp = sp + "GO \n";
            }
        }
    }

    return sp;
}

Future Enhancements

I have plans to make it more generic so that it can generate Stored Procedures for other databases.

License

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

Share

About the Author

Haider Ali Khan
Architect Myer's Technologies
Pakistan Pakistan
I have more than 8 years of Software development experience. I worked in different software companies. Now a days I am running my own software business as well as Lecturer in Computer Science in Govt. College GT Road Jhelum.

Comments and Discussions

 
QuestionThe best! PinmemberZuper55513-Oct-13 9:04 
GeneralMy vote of 5 PinmvpKanasz Robert25-Sep-12 22:41 
GeneralMaking it Generic Pinmembermarkanthonygohara6-Aug-08 0:59 
GeneralNice Work PinmemberShakeel Iqbal31-Jul-08 19:04 
GeneralGreat Work Done Pinmemberi_islamian31-Jul-08 18:56 
GeneralGreat Job Pinmembernomiikram31-Jul-08 18:54 

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
Web02 | 2.8.140916.1 | Last Updated 31 Jul 2008
Article Copyright 2008 by Haider Ali Khan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid