Simple POCO N SQL Generator In C#

While starting to code for any new application we are required to write POCO (Plain Old CLR Objects) classes for our database tables as Models (if you are using any ORM for data access) and we also need the stored procedures for our database tables. The process is simple but most of time repetitive and just like other developers I try to avoid it, so I wrote this simple windows forms application which helps me create database tables of POCO/model classes, base repository and related repository classes for the database tables in order to use Dapper ORM framework (which I mostly use in my web applications) and basic stored procedures scripts (like insert, update, delete etc) for the selected database tables. This saves time and helps me to avoid writing repetitive code.

This application is primarily targeting databases designed in SQL Server.

This is how the application looks:



Let's look into the code of the application and how it works. The flow of the application can be understood by the following diagram:



As you can see from the diagram, the first event occurs when the application is started and user clicks on ‘Get Database List’ button after entering the DB server connection string. The code of that event is as follows:

private void btnGetDBList_Click(object sender, EventArgs e)
{
    String conxString = txtConnectionString.Text.Trim();
    using (var sqlConx = new SqlConnection(conxString))
    {
        sqlConx.Open();
        var tblDatabases = sqlConx.GetSchema("Databases");
        sqlConx.Close();
        foreach (DataRow row in tblDatabases.Rows)
        {
            cboDatabases.Items.Add(row["database_name"]);
        }
    }
    cboDatabases.Items.Add("Select Database");
    cboDatabases.SelectedIndex = cboDatabases.Items.Count - 1;
}

As per above source code the application is getting the list of databases using ‘GetSchema(“Databases”)’ method of ‘SqlConnection’ object and then adding each item of the list to ‘Select Database’ dropdown. As per application flow (above diagram) the next user action is to select the database from the ‘Select Database’ drop down.

On the selection of database from dropdown application will fire ‘cboDatabases_SelectedIndexChanged’ event to get the tables list from the database and show it in the application checkbox list like following Image.



The source code of the dropdown selected index change event is as follows:

private void cboDatabases_SelectedIndexChanged(object sender, EventArgs e)
{
    try
    {
        if (cboDatabases.Text.Trim() != "Select Database")
        {
            //if ((cboCustomerName.SelectedValue.ToString().Trim() != "System.Data.DataRowView"))
            mSSqlDatabase = cboDatabases.Text.Trim();
            string strConn = txtConnectionString.Text.Trim() + ";Initial Catalog=" + mSSqlDatabase;
            SqlConnection cbConnection = null;
            try
            {
                DataTable dtSchemaTable = new DataTable("Tables");
                using (cbConnection = new SqlConnection(strConn))
                {
                    SqlCommand cmdCommand = cbConnection.CreateCommand();
                    cmdCommand.CommandText = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE'";
                    cbConnection.Open();
                    dtSchemaTable.Load(cmdCommand.ExecuteReader(CommandBehavior.CloseConnection));
                }
                cblTableList.Items.Clear();
                for (int iCount = 0; iCount < dtSchemaTable.Rows.Count; iCount++)
                {
                    cblTableList.Items.Add(dtSchemaTable.Rows[iCount][0].ToString());
                }
            }
            finally
            {
                // ReSharper disable once PossibleNullReferenceException
                cbConnection.Close();
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error : " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);  }
}

The next action taken by user as per application flow is to either click on ‘Generate SQL’ / ‘Generate Classes’ / ‘Generate Both SQL & Classes’ button. Lets understand the code of ‘Generate SQL’ button click event handler first given as bellow:

private void btnGenSQL_Click(object sender, EventArgs e)
{
    try
    {
        GenerateSQLScripts();
        MessageBox.Show("SQL file(s) created Successfully at path mentioned in 'SQL Query Files'", "Success");
        grpOutPut.Visible = true;
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error : " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

The above written code use the following methods in order to generate SQL scripts of selected tables:

private void GenerateSQLScripts()
{
    string sFolderPath = CreateOutputDir(txtNamespace.Text.Trim() != "" ? txtNamespace.Text.Trim() : mSSqlDatabase);
    var objTableNames = new ArrayList();
    string sConString = txtConnectionString.Text + ";Initial Catalog=" + mSSqlDatabase;
    for (int iTableCount = 0; iTableCount < cblTableList.CheckedItems.Count; iTableCount++)
    {
        objTableNames.Add(cblTableList.CheckedItems[iTableCount].ToString());
    }
    txtQueryFilePath.Text = SqlScriptGenerator.GenerateSQLFiles(sFolderPath, sConString, txtgrantUser.Text.Trim(), txtSPPrefix.Text.Trim(), cbxMultipleFiles.Checked, objTableNames);
}
private string CreateOutputDir(string aSDirName)
{
    string sRootDirPath = Path.GetDirectoryName(Application.ExecutablePath) + "\\" + aSDirName;
    if (!Directory.Exists(sRootDirPath)) Directory.CreateDirectory(sRootDirPath);
    return sRootDirPath;
}

‘GenerateSQLScripts’ method uses ‘CreateOutputDir’ method to first create the folder to store the SQL scripts and then loops through each selected table of the list and generate the SQL files using ‘GenerateSQLFiles’ method of ‘SqlScriptGenerator’ class.

The code of ‘GenerateSQLFiles’ method of ‘SqlScriptGenerator’ is as follows:

     public static string GenerateSQLFiles(string outputDirectory, string connectionString, string grantLoginName, string storedProcedurePrefix, bool createMultipleFiles, ArrayList tableNames)
        {

            string databaseName = "";
            string sqlPath;
            sqlPath = Path.Combine(outputDirectory, "SQL");
            List
<Table> tableList = AppUtility.GetTableList(connectionString, outputDirectory, tableNames, ref databaseName);
            // Generate the necessary SQL for each table
            int count = 0;
            if (tableList.Count > 0)
            {
                // Create the necessary directories
                AppUtility.CreateSubDirectory(sqlPath, true);
                // Create the necessary database logins
                CreateUserQueries(databaseName, grantLoginName, sqlPath, createMultipleFiles);

                // Create the CRUD stored procedures and data access code for each table
                foreach (Table table in tableList)
                {
                    CreateInsertStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
                    CreateUpdateStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
                    CreateDeleteStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
                    CreateDeleteAllByStoredProcedures(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
                    CreateSelectStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
                    CreateSelectAllStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
                    CreateSelectAllByStoredProcedures(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
                    count++;
                }
            }

            return sqlPath;
        }

In above ‘GenerateSQLFiles’ method, the application is first getting the table list of the given database using ‘GetTableList’ and then generating the SQL script for CRUD operations of those tables by looping through each table. Apart from general Insert,Update,Delete stored procedures the application create Select stored procedures based on all Primary and Foreign keys using ‘CreateSelectAllByStoredProcedures’ method similarly it creates stored procedure to delete rows on the basis of all Primary and Foreign keys using ‘CreateDeleteAllByStoredProcedures’ method. The code of all these methods are as follows:

 internal static void CreateInsertStoredProcedure(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)
        {
            // Create the stored procedure name
            string procedureName = storedProcedurePrefix + table.Name + "Insert";
            string fileName;

            // Determine the file name to be used
            if (createMultipleFiles)
            {
                fileName = Path.Combine(path, procedureName + ".sql");
            }
            else
            {
                fileName = Path.Combine(path, "StoredProcedures.sql");
            }

            using (StreamWriter writer = new StreamWriter(fileName, true))
            {
                // Create the seperator
                if (createMultipleFiles == false)
                {
                    writer.WriteLine();
                    writer.WriteLine("/******************************************************************************");
                    writer.WriteLine("******************************************************************************/");
                }

                // Create the drop statment
                writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
                writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");
                writer.WriteLine("GO");
                writer.WriteLine();

                // Create the SQL for the stored procedure
                writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");
                writer.WriteLine("(");

                // Create the parameter list
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    Column column = table.Columns[i];
                    if (column.IsIdentity == false && column.IsRowGuidCol == false)
                    {
                        writer.Write("\t" + AppUtility.CreateParameterString(column, true));
                        if (i < (table.Columns.Count - 1))
                        {
                            writer.Write(",");
                        }
                        writer.WriteLine();
                    }
                }
                writer.WriteLine(")");

                writer.WriteLine();
                writer.WriteLine("AS");
                writer.WriteLine();
                writer.WriteLine("SET NOCOUNT ON");
                writer.WriteLine();

                // Initialize all RowGuidCol columns
                foreach (Column column in table.Columns)
                {
                    if (column.IsRowGuidCol)
                    {
                        writer.WriteLine("SET @" + column.Name + " = NEWID()");
                        writer.WriteLine();
                        break;
                    }
                }

                writer.WriteLine("INSERT INTO [" + table.Name + "]");
                writer.WriteLine("(");

                // Create the parameter list
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    Column column = table.Columns[i];

                    // Ignore any identity columns
                    if (column.IsIdentity == false)
                    {
                        // Append the column name as a parameter of the insert statement
                        if (i < (table.Columns.Count - 1))
                        {
                            writer.WriteLine("\t[" + column.Name + "],");
                        }
                        else
                        {
                            writer.WriteLine("\t[" + column.Name + "]");
                        }
                    }
                }

                writer.WriteLine(")");
                writer.WriteLine("VALUES");
                writer.WriteLine("(");

                // Create the values list
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    Column column = table.Columns[i];

                    // Is the current column an identity column?
                    if (column.IsIdentity == false)
                    {
                        // Append the necessary line breaks and commas
                        if (i < (table.Columns.Count - 1)) { writer.WriteLine("\t@" + column.Name + ","); } else { writer.WriteLine("\t@" + column.Name); } } } writer.WriteLine(")"); // Should we include a line for returning the identity? foreach (Column column in table.Columns) { // Is the current column an identity column? if (column.IsIdentity) { writer.WriteLine(); writer.WriteLine("SELECT SCOPE_IDENTITY()"); break; } if (column.IsRowGuidCol) { writer.WriteLine(); writer.WriteLine("SELECT @" + column.Name); break; } } writer.WriteLine("GO"); // Create the grant statement, if a user was specified if (grantLoginName.Length > 0)
                {
                    writer.WriteLine();
                    writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");
                    writer.WriteLine("GO");
                }
            }
        }

        ///
<summary>
        /// Creates an update stored procedure SQL script for the specified table
        /// </summary>

        /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param>
        /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param>
        /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param>
        /// <param name="path">Path where the stored procedure script should be created.</param>
        /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param>
        internal static void CreateUpdateStoredProcedure(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)
        {
            if (table.PrimaryKeys.Count > 0 && table.Columns.Count != table.PrimaryKeys.Count && table.Columns.Count != table.ForeignKeys.Count)
            {
                // Create the stored procedure name
                string procedureName = storedProcedurePrefix + table.Name + "Update";
                string fileName;

                // Determine the file name to be used
                if (createMultipleFiles)
                {
                    fileName = Path.Combine(path, procedureName + ".sql");
                }
                else
                {
                    fileName = Path.Combine(path, "StoredProcedures.sql");
                }

                using (StreamWriter writer = new StreamWriter(fileName, true))
                {
                    // Create the seperator
                    if (createMultipleFiles == false)
                    {
                        writer.WriteLine();
                        writer.WriteLine("/******************************************************************************");
                        writer.WriteLine("******************************************************************************/");
                    }

                    // Create the drop statment
                    writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
                    writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");
                    writer.WriteLine("GO");
                    writer.WriteLine();

                    // Create the SQL for the stored procedure
                    writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");
                    writer.WriteLine("(");

                    // Create the parameter list
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        Column column = table.Columns[i];

                        if (i == 0)
                        {

                        }
                        if (i < (table.Columns.Count - 1))
                        {
                            writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false) + ",");
                        }
                        else
                        {
                            writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false));
                        }
                    }
                    writer.WriteLine(")");

                    writer.WriteLine();
                    writer.WriteLine("AS");
                    writer.WriteLine();
                    writer.WriteLine("SET NOCOUNT ON");
                    writer.WriteLine();
                    writer.WriteLine("UPDATE [" + table.Name + "]");
                    writer.Write("SET");

                    // Create the set statement
                    bool firstLine = true;
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        var column = table.Columns[i];

                        // Ignore Identity and RowGuidCol columns
                        if (table.PrimaryKeys.Contains(column) == false)
                        {
                            if (firstLine)
                            {
                                writer.Write(" ");
                                firstLine = false;
                            }
                            else
                            {
                                writer.Write("\t");
                            }

                            writer.Write("[" + column.Name + "] = @" + column.Name);

                            if (i < (table.Columns.Count - 1))
                            {
                                writer.Write(",");
                            }

                            writer.WriteLine();
                        }
                    }

                    writer.Write("WHERE");

                    // Create the where clause
                    for (int i = 0; i < table.PrimaryKeys.Count; i++) { Column column = table.PrimaryKeys[i]; if (i == 0) { writer.Write(" [" + column.Name + "] = @" + column.Name); } else { writer.Write("\tAND [" + column.Name + "] = @" + column.Name); } } writer.WriteLine(); writer.WriteLine("GO"); // Create the grant statement, if a user was specified if (grantLoginName.Length > 0)
                    {
                        writer.WriteLine();
                        writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");
                        writer.WriteLine("GO");
                    }
                }
            }
        }

        ///
<summary>
        /// Creates an delete stored procedure SQL script for the specified table
        /// </summary>

        /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param>
        /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param>
        /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param>
        /// <param name="path">Path where the stored procedure script should be created.</param>
        /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param>
        internal static void CreateDeleteStoredProcedure(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)
        {
            if (table.PrimaryKeys.Count > 0)
            {
                // Create the stored procedure name
                string procedureName = storedProcedurePrefix + table.Name + "Delete";
                string fileName;

                // Determine the file name to be used
                if (createMultipleFiles)
                {
                    fileName = Path.Combine(path, procedureName + ".sql");
                }
                else
                {
                    fileName = Path.Combine(path, "StoredProcedures.sql");
                }

                using (StreamWriter writer = new StreamWriter(fileName, true))
                {
                    // Create the seperator
                    if (createMultipleFiles == false)
                    {
                        writer.WriteLine();
                        writer.WriteLine("/******************************************************************************");
                        writer.WriteLine("******************************************************************************/");
                    }

                    // Create the drop statment
                    writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
                    writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");
                    writer.WriteLine("GO");
                    writer.WriteLine();

                    // Create the SQL for the stored procedure
                    writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");
                    writer.WriteLine("(");

                    // Create the parameter list
                    for (int i = 0; i < table.PrimaryKeys.Count; i++)
                    {
                        Column column = table.PrimaryKeys[i];

                        if (i < (table.PrimaryKeys.Count - 1))
                        {
                            writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false) + ",");
                        }
                        else
                        {
                            writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false));
                        }
                    }
                    writer.WriteLine(")");

                    writer.WriteLine();
                    writer.WriteLine("AS");
                    writer.WriteLine();
                    writer.WriteLine("SET NOCOUNT ON");
                    writer.WriteLine();
                    writer.WriteLine("DELETE FROM [" + table.Name + "]");
                    writer.Write("WHERE");

                    // Create the where clause
                    for (int i = 0; i < table.PrimaryKeys.Count; i++) { Column column = table.PrimaryKeys[i]; if (i == 0) { writer.WriteLine(" [" + column.Name + "] = @" + column.Name); } else { writer.WriteLine("\tAND [" + column.Name + "] = @" + column.Name); } } writer.WriteLine("GO"); // Create the grant statement, if a user was specified if (grantLoginName.Length > 0)
                    {
                        writer.WriteLine();
                        writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");
                        writer.WriteLine("GO");
                    }
                }
            }
        }

        ///
<summary>
        /// Creates one or more delete stored procedures SQL script for the specified table and its foreign keys
        /// </summary>

        /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param>
        /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param>
        /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param>
        /// <param name="path">Path where the stored procedure script should be created.</param>
        /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param>
        internal static void CreateDeleteAllByStoredProcedures(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)
        {
            // Create a stored procedure for each foreign key
            foreach (List<Column> compositeKeyList in table.ForeignKeys.Values)
            {
                // Create the stored procedure name
                StringBuilder stringBuilder = new StringBuilder(255);
                stringBuilder.Append(storedProcedurePrefix + table.Name + "DeleteAllBy");

                // Create the parameter list
                for (int i = 0; i < compositeKeyList.Count; i++) { Column column = compositeKeyList[i]; if (i > 0)
                    {
                        stringBuilder.Append("_" + AppUtility.FormatPascal(column.Name));
                    }
                    else
                    {
                        stringBuilder.Append(AppUtility.FormatPascal(column.Name));
                    }
                }

                string procedureName = stringBuilder.ToString();
                string fileName;

                // Determine the file name to be used
                if (createMultipleFiles)
                {
                    fileName = Path.Combine(path, procedureName + ".sql");
                }
                else
                {
                    fileName = Path.Combine(path, "StoredProcedures.sql");
                }

                using (StreamWriter writer = new StreamWriter(fileName, true))
                {
                    // Create the seperator
                    if (createMultipleFiles == false)
                    {
                        writer.WriteLine();
                        writer.WriteLine("/******************************************************************************");
                        writer.WriteLine("******************************************************************************/");
                    }

                    // Create the drop statment
                    writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
                    writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");
                    writer.WriteLine("GO");
                    writer.WriteLine();

                    // Create the SQL for the stored procedure
                    writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");
                    writer.WriteLine("(");

                    // Create the parameter list
                    for (int i = 0; i < compositeKeyList.Count; i++)
                    {
                        Column column = compositeKeyList[i];

                        if (i < (compositeKeyList.Count - 1))
                        {
                            writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false) + ",");
                        }
                        else
                        {
                            writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false));
                        }
                    }
                    writer.WriteLine(")");

                    writer.WriteLine();
                    writer.WriteLine("AS");
                    writer.WriteLine();
                    writer.WriteLine("SET NOCOUNT ON");
                    writer.WriteLine();
                    writer.WriteLine("DELETE FROM [" + table.Name + "]");
                    writer.Write("WHERE");

                    // Create the where clause
                    for (int i = 0; i < compositeKeyList.Count; i++) { Column column = compositeKeyList[i]; if (i == 0) { writer.WriteLine(" [" + column.Name + "] = @" + column.Name); } else { writer.WriteLine("\tAND [" + column.Name + "] = @" + column.Name); } } writer.WriteLine("GO"); // Create the grant statement, if a user was specified if (grantLoginName.Length > 0)
                    {
                        writer.WriteLine();
                        writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");
                        writer.WriteLine("GO");
                    }
                }
            }
        }

        ///
<summary>
        /// Creates an select stored procedure SQL script for the specified table
        /// </summary>

        /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param>
        /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param>
        /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param>
        /// <param name="path">Path where the stored procedure script should be created.</param>
        /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param>
        internal static void CreateSelectStoredProcedure(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)
        {
            if (table.PrimaryKeys.Count > 0 && table.ForeignKeys.Count != table.Columns.Count)
            {
                // Create the stored procedure name
                string procedureName = storedProcedurePrefix + table.Name + "Select";
                string fileName;

                // Determine the file name to be used
                if (createMultipleFiles)
                {
                    fileName = Path.Combine(path, procedureName + ".sql");
                }
                else
                {
                    fileName = Path.Combine(path, "StoredProcedures.sql");
                }

                using (StreamWriter writer = new StreamWriter(fileName, true))
                {
                    // Create the seperator
                    if (createMultipleFiles == false)
                    {
                        writer.WriteLine();
                        writer.WriteLine("/******************************************************************************");
                        writer.WriteLine("******************************************************************************/");
                    }

                    // Create the drop statment
                    writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
                    writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");
                    writer.WriteLine("GO");
                    writer.WriteLine();

                    // Create the SQL for the stored procedure
                    writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");
                    writer.WriteLine("(");

                    // Create the parameter list
                    for (int i = 0; i < table.PrimaryKeys.Count; i++)
                    {
                        Column column = table.PrimaryKeys[i];

                        if (i == (table.PrimaryKeys.Count - 1))
                        {
                            writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false));
                        }
                        else
                        {
                            writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false) + ",");
                        }
                    }

                    writer.WriteLine(")");

                    writer.WriteLine();
                    writer.WriteLine("AS");
                    writer.WriteLine();
                    writer.WriteLine("SET NOCOUNT ON");
                    writer.WriteLine();
                    writer.Write("SELECT");

                    // Create the list of columns
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        Column column = table.Columns[i];

                        if (i == 0)
                        {
                            writer.Write(" ");
                        }
                        else
                        {
                            writer.Write("\t");
                        }

                        writer.Write("[" + column.Name + "]");

                        if (i < (table.Columns.Count - 1))
                        {
                            writer.Write(",");
                        }

                        writer.WriteLine();
                    }

                    writer.WriteLine("FROM [" + table.Name + "]");
                    writer.Write("WHERE");

                    // Create the where clause
                    for (int i = 0; i < table.PrimaryKeys.Count; i++) { Column column = table.PrimaryKeys[i]; if (i == 0) { writer.WriteLine(" [" + column.Name + "] = @" + column.Name); } else { writer.WriteLine("\tAND [" + column.Name + "] = @" + column.Name); } } writer.WriteLine("GO"); // Create the grant statement, if a user was specified if (grantLoginName.Length > 0)
                    {
                        writer.WriteLine();
                        writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");
                        writer.WriteLine("GO");
                    }
                }
            }
        }

        ///
<summary>
        /// Creates an select all stored procedure SQL script for the specified table
        /// </summary>

        /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param>
        /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param>
        /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param>
        /// <param name="path">Path where the stored procedure script should be created.</param>
        /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param>
        internal static void CreateSelectAllStoredProcedure(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)
        {
            if (table.PrimaryKeys.Count > 0 && table.ForeignKeys.Count != table.Columns.Count)
            {
                // Create the stored procedure name
                string procedureName = storedProcedurePrefix + table.Name + "SelectAll";
                string fileName;

                // Determine the file name to be used
                if (createMultipleFiles)
                {
                    fileName = Path.Combine(path, procedureName + ".sql");
                }
                else
                {
                    fileName = Path.Combine(path, "StoredProcedures.sql");
                }

                using (StreamWriter writer = new StreamWriter(fileName, true))
                {
                    // Create the seperator
                    if (createMultipleFiles == false)
                    {
                        writer.WriteLine();
                        writer.WriteLine("/******************************************************************************");
                        writer.WriteLine("******************************************************************************/");
                    }

                    // Create the drop statment
                    writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
                    writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");
                    writer.WriteLine("GO");
                    writer.WriteLine();

                    // Create the SQL for the stored procedure
                    writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");
                    writer.WriteLine();
                    writer.WriteLine("AS");
                    writer.WriteLine();
                    writer.WriteLine("SET NOCOUNT ON");
                    writer.WriteLine();
                    writer.Write("SELECT");

                    // Create the list of columns
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        Column column = table.Columns[i];

                        if (i == 0)
                        {
                            writer.Write(" ");
                        }
                        else
                        {
                            writer.Write("\t");
                        }

                        writer.Write("[" + column.Name + "]");

                        if (i < (table.Columns.Count - 1)) { writer.Write(","); } writer.WriteLine(); } writer.WriteLine("FROM [" + table.Name + "]"); writer.WriteLine("GO"); // Create the grant statement, if a user was specified if (grantLoginName.Length > 0)
                    {
                        writer.WriteLine();
                        writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");
                        writer.WriteLine("GO");
                    }
                }
            }
        }

        ///
<summary>
        /// Creates one or more select stored procedures SQL script for the specified table and its foreign keys
        /// </summary>

        /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param>
        /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param>
        /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param>
        /// <param name="path">Path where the stored procedure script should be created.</param>
        /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param>
        internal static void CreateSelectAllByStoredProcedures(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)
        {
            // Create a stored procedure for each foreign key
            foreach (List<Column> compositeKeyList in table.ForeignKeys.Values)
            {
                // Create the stored procedure name
                StringBuilder stringBuilder = new StringBuilder(255);
                stringBuilder.Append(storedProcedurePrefix + table.Name + "SelectAllBy");

                // Create the parameter list
                for (int i = 0; i < compositeKeyList.Count; i++) { Column column = compositeKeyList[i]; if (i > 0)
                    {
                        stringBuilder.Append("_" + AppUtility.FormatPascal(column.Name));
                    }
                    else
                    {
                        stringBuilder.Append(AppUtility.FormatPascal(column.Name));
                    }
                }

                string procedureName = stringBuilder.ToString();
                string fileName;

                // Determine the file name to be used
                if (createMultipleFiles)
                {
                    fileName = Path.Combine(path, procedureName + ".sql");
                }
                else
                {
                    fileName = Path.Combine(path, "StoredProcedures.sql");
                }

                using (StreamWriter writer = new StreamWriter(fileName, true))
                {
                    // Create the seperator
                    if (createMultipleFiles == false)
                    {
                        writer.WriteLine();
                        writer.WriteLine("/******************************************************************************");
                        writer.WriteLine("******************************************************************************/");
                    }

                    // Create the drop statment
                    writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
                    writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");
                    writer.WriteLine("GO");
                    writer.WriteLine();

                    // Create the SQL for the stored procedure
                    writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");
                    writer.WriteLine("(");

                    // Create the parameter list
                    for (int i = 0; i < compositeKeyList.Count; i++)
                    {
                        Column column = compositeKeyList[i];

                        if (i < (compositeKeyList.Count - 1))
                        {
                            writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false) + ",");
                        }
                        else
                        {
                            writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false));
                        }
                    }
                    writer.WriteLine(")");

                    writer.WriteLine();
                    writer.WriteLine("AS");
                    writer.WriteLine();
                    writer.WriteLine("SET NOCOUNT ON");
                    writer.WriteLine();
                    writer.Write("SELECT");

                    // Create the list of columns
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        Column column = table.Columns[i];

                        if (i == 0)
                        {
                            writer.Write(" ");
                        }
                        else
                        {
                            writer.Write("\t");
                        }

                        writer.Write("[" + column.Name + "]");

                        if (i < (table.Columns.Count - 1))
                        {
                            writer.Write(",");
                        }

                        writer.WriteLine();
                    }

                    writer.WriteLine("FROM [" + table.Name + "]");
                    writer.Write("WHERE");

                    // Create the where clause
                    for (int i = 0; i < compositeKeyList.Count; i++) { Column column = compositeKeyList[i]; if (i == 0) { writer.WriteLine(" [" + column.Name + "] = @" + column.Name); } else { writer.WriteLine("\tAND [" + column.Name + "] = @" + column.Name); } } writer.WriteLine("GO"); // Create the grant statement, if a user was specified if (grantLoginName.Length > 0)
                    {
                        writer.WriteLine();
                        writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");
                        writer.WriteLine("GO");
                    }
                }
            }
        }

Now lets understand the code of ‘Generate Classes’ button click event handler given as below:

private void btnGenClasses_Click(object sender, EventArgs e)
{
    try
    {
        GenerateCSharpClasses();
        MessageBox.Show("Class file(s) created Successfully at path mentioned in 'Class Files Path'", "Success");
        grpOutPut.Visible = true;
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error : " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

The above written code uses following methods in order to generate C# POCO classes and Dapper Repo classes of selected tables:

private void GenerateCSharpClasses()
{
    string sFolderPath, sNameSpace;
    if (txtNamespace.Text.Trim() != "")
    {
        sFolderPath = CreateOutputDir(txtNamespace.Text.Trim());
        sNameSpace = txtNamespace.Text.Trim();
    }
    else
    {
        sFolderPath = CreateOutputDir(mSSqlDatabase);
        sNameSpace = mSSqlDatabase;
    }
        CreateBaseRepoClass(sFolderPath + "\\BaseRepository.cs", sNameSpace);
        var objTableNames = new ArrayList();
        string sConString = "";
        sConString = txtConnectionString.Text + ";Initial Catalog=" + mSSqlDatabase;

        for (int iTableCount = 0; iTableCount < cblTableList.CheckedItems.Count; iTableCount++)
        {
            objTableNames.Add(cblTableList.CheckedItems[iTableCount].ToString());
        }
        txtFilesPath.Text = CSharpCodeGenerator.GenerateClassFiles(sFolderPath, sConString, txtSPPrefix.Text.Trim(), sNameSpace, "", objTableNames);
    CSharpCodeGenerator.GenerateRepoFiles(sFolderPath, sConString, txtSPPrefix.Text.Trim(), sNameSpace, "", objTableNames);

    }
private void CreateBaseRepoClass(string aSFilePath, string targetNamespace)
{
    using (var streamWriter = new StreamWriter(aSFilePath))
    {
        #region Add Referances

        streamWriter.WriteLine("using System;");
        streamWriter.WriteLine("using System.Data;");
        streamWriter.WriteLine("using System.Data.SqlClient;");
        streamWriter.WriteLine("using System.Linq;");
        streamWriter.WriteLine("using System.Web.Configuration;");
        streamWriter.WriteLine();
        streamWriter.WriteLine("namespace " + targetNamespace);
        streamWriter.WriteLine("{");

        #endregion

        #region Create Base Repository Class

        streamWriter.WriteLine("\t public abstract class BaseRepository ");
        streamWriter.WriteLine("\t\t {");
        streamWriter.WriteLine(
            "\t\t\t protected static void SetIdentity<T>(IDbConnection connection, Action<T> setId) ");
        streamWriter.WriteLine("\t\t\t {");
        streamWriter.WriteLine(
            "\t\t\t dynamic identity = connection.Query(\"SELECT @@IDENTITY AS Id\").Single(); ");
        streamWriter.WriteLine("\t\t\t T newId = (T)identity.Id; ");
        streamWriter.WriteLine("\t\t\t setId(newId); ");
        streamWriter.WriteLine("\t\t\t }");

        streamWriter.WriteLine(
            "\t\t\t protected static IDbConnection OpenConnection() ");
        streamWriter.WriteLine("\t\t\t {");
        streamWriter.WriteLine(
            "\t\t\t IDbConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings[\"DBConString\"].ConnectionString); ");
        streamWriter.WriteLine("\t\t\t connection.Open(); ");
        streamWriter.WriteLine("\t\t\t return connection; ");
        streamWriter.WriteLine("\t\t\t }");
        streamWriter.WriteLine("\t\t }");

        #endregion
    }
}

‘GenerateCSharpClasses’ method first creates the folder to save the output files using ” method, then it creates the BaseRepository class using ‘CreateBaseRepoClass’ method (this is used as base class for all the Dapper Repo Classes) and then it loops through each selected table of the list and generate the C# POCO class files using ‘GenerateClassFiles’ and Dapper Repo files using ‘GenerateRepoFiles’ method of ‘CSharpCodeGenerator’ class.
The code of ‘GenerateClassFiles’ method of ‘CSharpCodeGenerator’ is as follows:  

public static string GenerateClassFiles(string outputDirectory, string connectionString, string storedProcedurePrefix, string targetNamespace, string daoSuffix, ArrayList tableNames)
        {
            string databaseName = "";
            string csPath;
            csPath = Path.Combine(outputDirectory, "CS");
            List
<Table> tableList = AppUtility.GetTableList(connectionString, outputDirectory, tableNames, ref databaseName);
            // Generate the necessary SQL and C# code for each table
            if (tableList.Count <= 0) return csPath;
            // Create the necessary directories
            AppUtility.CreateSubDirectory(csPath, true);
            foreach (Table table in tableList)
            {
                CreateModelClass(databaseName, table, targetNamespace, storedProcedurePrefix, csPath);
            }
            return csPath;
        }

In above ‘GenerateClassFiles’ method, the application is first getting the table list of the given database using ‘GetTableList’ method and then it generates the C# POCO class definition files of all the tables present in tables list by looping through each table and using ‘CreateModelClass’ method. The code of ‘CreateModelClass’ method is as follows:

       internal static void CreateModelClass(string databaseName, Table table, string targetNamespace, string storedProcedurePrefix, string path)
        {
            var className = AppUtility.FormatClassName(table.Name);
            using (var streamWriter = new StreamWriter(Path.Combine(path, className + ".cs")))
            {
                #region Create the header for the class
                streamWriter.WriteLine("using System;");
                streamWriter.WriteLine();
                streamWriter.WriteLine("namespace " + targetNamespace);
                streamWriter.WriteLine("{");

                streamWriter.WriteLine("\tpublic class " + className);
                streamWriter.WriteLine("\t{");
                #endregion

                #region  Append the public properties
                streamWriter.WriteLine("\t\t#region Properties");
                for (var i = 0; i < table.Columns.Count; i++)
                {
                    var column = table.Columns[i];
                    var parameter = AppUtility.CreateMethodParameter(column);
                    var type = parameter.Split(' ')[0];
                    var name = parameter.Split(' ')[1];
                    streamWriter.WriteLine("\t\t///
<summary>");
                    streamWriter.WriteLine("\t\t/// Gets or sets the " + AppUtility.FormatPascal(name) + " value.");
                    streamWriter.WriteLine("\t\t/// </summary>

");
                    streamWriter.WriteLine("\t\tpublic " + type + " " + AppUtility.FormatPascal(name));
                    streamWriter.WriteLine("\t\t{ get; set; }");
                    if (i < (table.Columns.Count - 1))
                    {
                        streamWriter.WriteLine();
                    }
                }

                streamWriter.WriteLine();
                streamWriter.WriteLine("\t\t#endregion");
                #endregion
                // Close out the class and namespace
                streamWriter.WriteLine("\t}");
                streamWriter.WriteLine("}");
            }
        }

The code of ‘GenerateRepoFiles’ method of ‘CSharpCodeGenerator’ is as follows:

public static string GenerateRepoFiles(string outputDirectory, string connectionString, string storedProcedurePrefix, string targetNamespace, string daoSuffix, ArrayList tableNames)
        {
            string databaseName = "";
            string csPath = Path.Combine(outputDirectory, "Repo");
            List
<Table> tableList = AppUtility.GetTableList(connectionString, outputDirectory, tableNames, ref databaseName);
            // Generate the necessary SQL and C# code for each table
            if (tableList.Count <= 0) return csPath;
            // Create the necessary directories
            AppUtility.CreateSubDirectory(csPath, true);
            // Create the CRUD stored procedures and data access code for each table
            foreach (Table table in tableList)
            {
                CreateRepoClass(databaseName, table, targetNamespace, storedProcedurePrefix, csPath);
            }
            return csPath;
        }

In above ‘GenerateRepoFiles’ method, the application is first getting the tables list of the given database using ‘AppUtility.GetTableList’ method and then it loops through each table present in tables list to generate Dapper Repo files containing CRUD operation methods using ‘CreateRepoClass’ method. The code of ‘CreateRepoClass’ method is as follows:

internal static void CreateRepoClass(string databaseName, Table table, string targetNamespace, string storedProcedurePrefix, string path)
 {
     var className = AppUtility.FormatClassName(table.Name);
     using (var streamWriter = new StreamWriter(Path.Combine(path, className + ".cs")))
     {
         #region Add References & Declare Class
         streamWriter.WriteLine("using System.Collections.Generic;");
         streamWriter.WriteLine("using System.Data;");
         streamWriter.WriteLine("using System.Linq;");
         streamWriter.WriteLine("using Dapper;");
         streamWriter.WriteLine();
         streamWriter.WriteLine("namespace " + targetNamespace);
         streamWriter.WriteLine("{");
         streamWriter.WriteLine("\t public class " + className + "Repo : BaseRepository");
         streamWriter.WriteLine("\t\t {");
         #endregion

         #region Append the access methods
         streamWriter.WriteLine("\t\t#region Methods");
         streamWriter.WriteLine();
         CreateInsertMethod(table, streamWriter);
         CreateUpdateMethod(table, streamWriter);
         CreateSelectMethod(table, streamWriter);
         CreateSelectAllMethod(table, streamWriter);
         CreateSelectAllByMethods(table, storedProcedurePrefix, streamWriter);
         #endregion

         streamWriter.WriteLine();
         streamWriter.WriteLine("\t\t#endregion");

         // Close out the class and namespace
         streamWriter.WriteLine("\t\t}");
         streamWriter.WriteLine("}");
     }
 }

In above ‘CreateRepoClass’ the application generates a class which is named as ‘Repo’ containing methods for Insert,Update,Select & Select All operations for the given table. The code of methods used in ‘CreateRepoClass’ method are as follows:

/// <summary>
 /// Creates a string that represents the insert functionality of the data access class.
 /// </summary>
 /// <param name="table">The Table instance that this method will be created for.</param>
 /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
 private static void CreateInsertMethod(Table table, TextWriter streamWriter)
 {
     var className = AppUtility.FormatClassName(table.Name);
     var variableName = "a" + className;

     // Append the method header
     streamWriter.WriteLine("\t\t/// <summary>");
     streamWriter.WriteLine("\t\t/// Saves a record to the " + table.Name + " table.");
     streamWriter.WriteLine("\t\t/// returns True if value saved successfullyelse false");
     streamWriter.WriteLine("\t\t/// Throw exception with message value 'EXISTS' if the data is duplicate");
     streamWriter.WriteLine("\t\t/// </summary>");
     streamWriter.WriteLine("\t\tpublic bool Insert(" + className + " " + variableName + ")");
     streamWriter.WriteLine("\t\t{");
     streamWriter.WriteLine("\t\t var blResult = false;");
     streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");
     streamWriter.WriteLine("\t\t\t\t {");
     streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();");
     foreach (var column in table.Columns)
     { streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + column.Name + "\"," + variableName + "." + AppUtility.FormatPascal(column.Name) + ");"); }
     streamWriter.WriteLine("\t\t\t\t\t int iResult = vConn.Execute(\"" + table.Name + "Insert\", vParams, commandType: CommandType.StoredProcedure);");
     streamWriter.WriteLine("\t\t\t if (iResult == -1) blResult = true;");
     streamWriter.WriteLine("\t\t\t }");
     streamWriter.WriteLine("\t\t\t return blResult;");
     streamWriter.WriteLine("\t\t}");
     streamWriter.WriteLine();
 }

 /// <summary>
 /// Creates a string that represents the update functionality of the data access class.
 /// </summary>
 /// <param name="table">The Table instance that this method will be created for.</param>
 /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
 private static void CreateUpdateMethod(Table table, TextWriter streamWriter)
 {
     if (table.PrimaryKeys.Count <= 0 || table.Columns.Count == table.PrimaryKeys.Count ||
         table.Columns.Count == table.ForeignKeys.Count) return;
     var className = AppUtility.FormatClassName(table.Name);
     var variableName = "a" + className;

     // Append the method header
     streamWriter.WriteLine("\t\t/// <summary>");
     streamWriter.WriteLine("\t\t/// Updates record to the " + table.Name + " table.");
     streamWriter.WriteLine("\t\t/// returns True if value saved successfullyelse false");
     streamWriter.WriteLine("\t\t/// Throw exception with message value 'EXISTS' if the data is duplicate");
     streamWriter.WriteLine("\t\t/// </summary>");
     streamWriter.WriteLine("\t\tpublic bool Update(" + className + " " + variableName + ")");
     streamWriter.WriteLine("\t\t{");
     streamWriter.WriteLine("\t\t var blResult = false;");
     streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");
     streamWriter.WriteLine("\t\t\t\t {");
     streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();");
     foreach (var column in table.Columns)
     { streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + column.Name + "\"," + variableName + "." + AppUtility.FormatPascal(column.Name) + ");"); }
     streamWriter.WriteLine("\t\t\t\t\t int iResult = vConn.Execute(\"" + table.Name + "Update\", vParams, commandType: CommandType.StoredProcedure);");
     streamWriter.WriteLine("\t\t\t\t if (iResult == -1) blResult = true;");
     streamWriter.WriteLine("\t\t\t\t }");
     streamWriter.WriteLine("\t\t\treturn blResult;");
     streamWriter.WriteLine("\t\t}");
     streamWriter.WriteLine();
 }

 /// <summary>
 /// Creates a string that represents the "select" functionality of the data access class.
 /// </summary>
 /// <param name="table">The Table instance that this method will be created for.</param>
  /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
 private static void CreateSelectMethod(Table table, TextWriter streamWriter)
 {
     if (table.PrimaryKeys.Count <= 0 || table.Columns.Count == table.PrimaryKeys.Count ||
         table.Columns.Count == table.ForeignKeys.Count) return;
     var className = AppUtility.FormatClassName(table.Name);
     var variableName = "a" + table.PrimaryKeys[0].Name;

     // Append the method header
     streamWriter.WriteLine("\t\t/// <summary>");
     streamWriter.WriteLine("\t\t/// Selects the Single object of " + table.Name + " table.");
     streamWriter.WriteLine("\t\t/// </summary>");
     streamWriter.WriteLine("\t\tpublic "+ className + " Get"+ className +"(" + AppUtility.GetCsType(table.PrimaryKeys[0]) + " " + variableName + ")");
     streamWriter.WriteLine("\t\t{");
     streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");
     streamWriter.WriteLine("\t\t\t\t {");
     streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();");
     streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + table.PrimaryKeys[0].Name + "\"," + variableName + ");");
     streamWriter.WriteLine("\t\t\t\t\t return vConn.Query<"+ className + ">(\"" + table.Name + "Select\", vParams, commandType: CommandType.StoredProcedure);");
     streamWriter.WriteLine("\t\t\t\t }");
     streamWriter.WriteLine("\t\t}");
     streamWriter.WriteLine();


 }

 /// <summary>
 /// Creates a string that represents the select functionality of the data access class.
 /// </summary>
 /// <param name="table">The Table instance that this method will be created for.</param>
 /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
 private static void CreateSelectAllMethod(Table table, TextWriter streamWriter)
 {
     if (table.Columns.Count == table.PrimaryKeys.Count || table.Columns.Count == table.ForeignKeys.Count)
         return;
     var className = AppUtility.FormatClassName(table.Name);
     // Append the method header
     streamWriter.WriteLine("\t\t/// <summary>");
     streamWriter.WriteLine("\t\t/// Selects all records from the " + table.Name + " table.");
     streamWriter.WriteLine("\t\t/// </summary>");
     streamWriter.WriteLine("\t\t public IEnumerable<" + className + "> SelectAll()");
     streamWriter.WriteLine("\t\t{");
     // Append the stored procedure execution
     streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");
     streamWriter.WriteLine("\t\t\t{");
     streamWriter.WriteLine("\t\t\t\t return vConn.Query<" + className + ">(\"" + table.Name + "SelectAll\", commandType: CommandType.StoredProcedure).ToList();");
     streamWriter.WriteLine("\t\t\t}");
     streamWriter.WriteLine("\t\t}");
 }

 /// <summary>
 /// Creates a string that represents the "select by" functionality of the data access class.
 /// </summary>
 /// <param name="table">The Table instance that this method will be created for.</param>
 /// <param name="storedProcedurePrefix">The prefix that is used on the stored procedure that this method will call.</param>
 /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
 private static void CreateSelectAllByMethods(Table table, string storedProcedurePrefix, TextWriter streamWriter)
 {
     string className = AppUtility.FormatClassName(table.Name);
     string dtoVariableName = AppUtility.FormatCamel(className);

     // Create a stored procedure for each foreign key
     foreach (List<Column> compositeKeyList in table.ForeignKeys.Values)
     {
         // Create the stored procedure name
         StringBuilder stringBuilder = new StringBuilder(255);
         stringBuilder.Append("SelectAllBy");
         for (var i = 0; i < compositeKeyList.Count; i++)
         {
             var column = compositeKeyList[i];

             if (i > 0)
             {
                 stringBuilder.Append("_" + AppUtility.FormatPascal(column.Name));
             }
             else
             {
                 stringBuilder.Append(AppUtility.FormatPascal(column.Name));
             }
         }
         string methodName = stringBuilder.ToString();
         string procedureName = storedProcedurePrefix + table.Name + methodName;

         // Create the select function based on keys
         // Append the method header
         streamWriter.WriteLine("\t\t/// <summary>");
         streamWriter.WriteLine("\t\t/// Selects all records from the " + table.Name + " table by a foreign key.");
         streamWriter.WriteLine("\t\t/// </summary>");

         streamWriter.Write("\t\tpublic List<" + className + "> " + methodName + "(");
         for (int i = 0; i < compositeKeyList.Count; i++)
         {
             Column column = compositeKeyList[i];
             streamWriter.Write(AppUtility.CreateMethodParameter(column));
             if (i < (compositeKeyList.Count - 1))
             {
                 streamWriter.Write(",");
             }
         }
         streamWriter.WriteLine(")");
         streamWriter.WriteLine("\t\t{");

         streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");
         streamWriter.WriteLine("\t\t\t\t {");
         streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();");
         for (var i = 0; i < compositeKeyList.Count; i++)
         {
             var column = compositeKeyList[i];
             streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + column.Name + "\"," + AppUtility.FormatCamel(column.Name) + ");");
         }
         streamWriter.WriteLine("\t\t\t\t return vConn.Query<" + className + ">(\"" + table.Name + "SelectAll\", vParams, commandType: CommandType.StoredProcedure).ToList();");
         streamWriter.WriteLine("\t\t\t\t }");
         streamWriter.WriteLine("\t\t}");
         streamWriter.WriteLine();
     }
 }

The ‘Generate Both SQL & Classes’ button click will execute both ‘GenerateSQLScripts’ & ‘GenerateCSharpClasses’ methods together and show the output files path after success message like given screenshot.

The source code of this application can be downloaded from Github. I always use this application in order to generate POCO classes, DML SQL Scripts and Dapper Repo classes. I hope that it will be as useful for other people as it is to me, let me know if I have missed anything or you have any queries/suggestions. Happy coding!

 

Read more articles on SQL Server:

• Dynamic Data Masking In SQL Server 2016
• Introduction To CDC (Change Data Capture) Of SQL Server - Part Two