Stored Procedure Generator

• Download source code - 31.2 KB

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.