Post
Ask Question

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:

string

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:

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:

  1. private void btnGetDBList_Click(object sender, EventArgs e)  
  2. {  
  3.     String conxString = txtConnectionString.Text.Trim();  
  4.     using (var sqlConx = new SqlConnection(conxString))  
  5.     {  
  6.         sqlConx.Open();  
  7.         var tblDatabases = sqlConx.GetSchema("Databases");  
  8.         sqlConx.Close();  
  9.         foreach (DataRow row in tblDatabases.Rows)  
  10.         {  
  11.             cboDatabases.Items.Add(row["database_name"]);  
  12.         }  
  13.     }  
  14.     cboDatabases.Items.Add("Select Database");  
  15.     cboDatabases.SelectedIndex = cboDatabases.Items.Count - 1;  
  16. }  
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.

database

The source code of the dropdown selected index change event is as follows:
  1. private void cboDatabases_SelectedIndexChanged(object sender, EventArgs e)  
  2. {  
  3.     try  
  4.     {  
  5.         if (cboDatabases.Text.Trim() != "Select Database")  
  6.         {  
  7.             //if ((cboCustomerName.SelectedValue.ToString().Trim() != "System.Data.DataRowView"))  
  8.             mSSqlDatabase = cboDatabases.Text.Trim();  
  9.             string strConn = txtConnectionString.Text.Trim() + ";Initial Catalog=" + mSSqlDatabase;  
  10.             SqlConnection cbConnection = null;  
  11.             try  
  12.             {  
  13.                 DataTable dtSchemaTable = new DataTable("Tables");  
  14.                 using (cbConnection = new SqlConnection(strConn))  
  15.                 {  
  16.                     SqlCommand cmdCommand = cbConnection.CreateCommand();  
  17.                     cmdCommand.CommandText = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE'";  
  18.                     cbConnection.Open();  
  19.                     dtSchemaTable.Load(cmdCommand.ExecuteReader(CommandBehavior.CloseConnection));  
  20.                 }  
  21.                 cblTableList.Items.Clear();  
  22.                 for (int iCount = 0; iCount < dtSchemaTable.Rows.Count; iCount++)  
  23.                 {  
    24. f
  24.                     cblTableList.Items.Add(dtSchemaTable.Rows[iCount][0].ToString());  
  25.                 }  
  26.             }  
  27.             finally  
  28.             {  
  29.                 // ReSharper disable once PossibleNullReferenceException  
  30.                 cbConnection.Close();  
  31.             }  
  32.         }  
  33.     }  
  34.     catch (Exception ex)  
  35.     {  
  36.         MessageBox.Show("Error : " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);  
    }
  37. }     
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:
  1. private void btnGenSQL_Click(object sender, EventArgs e)  
  2. {  
  3.     try  
  4.     {  
  5.         GenerateSQLScripts();  
  6.         MessageBox.Show("SQL file(s) created Successfully at path mentioned in 'SQL Query Files'", "Success");  
  7.         grpOutPut.Visible = true;  
  8.     }  
  9.     catch (Exception ex)  
  10.     {  
  11.         MessageBox.Show("Error : " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);  
  12.     }  
  13. }  
The above written code use the following methods in order to generate SQL scripts of selected tables:
  1. private void GenerateSQLScripts()  
  2. {  
  3.     string sFolderPath = CreateOutputDir(txtNamespace.Text.Trim() != "" ? txtNamespace.Text.Trim() : mSSqlDatabase);  
  4.     var objTableNames = new ArrayList();  
  5.     string sConString = txtConnectionString.Text + ";Initial Catalog=" + mSSqlDatabase;  
  6.     for (int iTableCount = 0; iTableCount < cblTableList.CheckedItems.Count; iTableCount++)  
  7.     {  
  8.         objTableNames.Add(cblTableList.CheckedItems[iTableCount].ToString());  
  9.     }  
  10.     txtQueryFilePath.Text = SqlScriptGenerator.GenerateSQLFiles(sFolderPath, sConString, txtgrantUser.Text.Trim(), txtSPPrefix.Text.Trim(), cbxMultipleFiles.Checked, objTableNames);  
  11. }  
  12. private string CreateOutputDir(string aSDirName)  
  13. {  
  14.     string sRootDirPath = Path.GetDirectoryName(Application.ExecutablePath) + "\\" + aSDirName;  
  15.     if (!Directory.Exists(sRootDirPath)) Directory.CreateDirectory(sRootDirPath);  
  16.     return sRootDirPath;  
  17. }  
‘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:
  1.      public static string GenerateSQLFiles(string outputDirectory, string connectionString, string grantLoginName, string storedProcedurePrefix, bool createMultipleFiles, ArrayList tableNames)  
  2.         {  
  3.    
  4.             string databaseName = "";  
  5.             string sqlPath;  
  6.             sqlPath = Path.Combine(outputDirectory, "SQL");  
  7.             List  
  8. <Table> tableList = AppUtility.GetTableList(connectionString, outputDirectory, tableNames, ref databaseName);  
  9.             // Generate the necessary SQL for each table  
  10.             int count = 0;  
  11.             if (tableList.Count > 0)  
  12.             {  
  13.                 // Create the necessary directories  
  14.                 AppUtility.CreateSubDirectory(sqlPath, true);  
  15.                 // Create the necessary database logins  
  16.                 CreateUserQueries(databaseName, grantLoginName, sqlPath, createMultipleFiles);  
  17.    
  18.                 // Create the CRUD stored procedures and data access code for each table  
  19.                 foreach (Table table in tableList)  
  20.                 {  
  21.                     CreateInsertStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);  
  22.                     CreateUpdateStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);  
  23.                     CreateDeleteStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);  
  24.                     CreateDeleteAllByStoredProcedures(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);  
  25.                     CreateSelectStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);  
  26.                     CreateSelectAllStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);  
  27.                     CreateSelectAllByStoredProcedures(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);  
  28.                     count++;  
  29.                 }  
  30.             }  
  31.    
  32.             return sqlPath;  
  33.         }  
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:
  1.  internal static void CreateInsertStoredProcedure(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)  
  2.         {  
  3.             // Create the stored procedure name  
  4.             string procedureName = storedProcedurePrefix + table.Name + "Insert";  
  5.             string fileName;  
  6.    
  7.             // Determine the file name to be used  
  8.             if (createMultipleFiles)  
  9.             {  
  10.                 fileName = Path.Combine(path, procedureName + ".sql");  
  11.             }  
  12.             else  
  13.             {  
  14.                 fileName = Path.Combine(path, "StoredProcedures.sql");  
  15.             }  
  16.    
  17.             using (StreamWriter writer = new StreamWriter(fileName, true))  
  18.             {  
  19.                 // Create the seperator  
  20.                 if (createMultipleFiles == false)  
  21.                 {  
  22.                     writer.WriteLine();  
  23.                     writer.WriteLine("/******************************************************************************");  
  24.                     writer.WriteLine("******************************************************************************/");  
  25.                 }  
  26.    
  27.                 // Create the drop statment  
  28.                 writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");  
  29.                 writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");  
  30.                 writer.WriteLine("GO");  
  31.                 writer.WriteLine();  
  32.    
  33.                 // Create the SQL for the stored procedure  
  34.                 writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");  
  35.                 writer.WriteLine("(");  
  36.    
  37.                 // Create the parameter list  
  38.                 for (int i = 0; i < table.Columns.Count; i++)  
  39.                 {  
  40.                     Column column = table.Columns[i];  
  41.                     if (column.IsIdentity == false && column.IsRowGuidCol == false)  
  42.                     {  
  43.                         writer.Write("\t" + AppUtility.CreateParameterString(column, true));  
  44.                         if (i < (table.Columns.Count - 1))  
  45.                         {  
  46.                             writer.Write(",");  
  47.                         }  
  48.                         writer.WriteLine();  
  49.                     }  
  50.                 }  
  51.                 writer.WriteLine(")");  
  52.    
  53.                 writer.WriteLine();  
  54.                 writer.WriteLine("AS");  
  55.                 writer.WriteLine();  
  56.                 writer.WriteLine("SET NOCOUNT ON");  
  57.                 writer.WriteLine();  
  58.    
  59.                 // Initialize all RowGuidCol columns  
  60.                 foreach (Column column in table.Columns)  
  61.                 {  
  62.                     if (column.IsRowGuidCol)  
  63.                     {  
  64.                         writer.WriteLine("SET @" + column.Name + " = NEWID()");  
  65.                         writer.WriteLine();  
  66.                         break;  
  67.                     }  
  68.                 }  
  69.    
  70.                 writer.WriteLine("INSERT INTO [" + table.Name + "]");  
  71.                 writer.WriteLine("(");  
  72.    
  73.                 // Create the parameter list  
  74.                 for (int i = 0; i < table.Columns.Count; i++)  
  75.                 {  
  76.                     Column column = table.Columns[i];  
  77.    
  78.                     // Ignore any identity columns  
  79.                     if (column.IsIdentity == false)  
  80.                     {  
  81.                         // Append the column name as a parameter of the insert statement  
  82.                         if (i < (table.Columns.Count - 1))  
  83.                         {  
  84.                             writer.WriteLine("\t[" + column.Name + "],");  
  85.                         }  
  86.                         else  
  87.                         {  
  88.                             writer.WriteLine("\t[" + column.Name + "]");  
  89.                         }  
  90.                     }  
  91.                 }  
  92.    
  93.                 writer.WriteLine(")");  
  94.                 writer.WriteLine("VALUES");  
  95.                 writer.WriteLine("(");  
  96.    
  97.                 // Create the values list  
  98.                 for (int i = 0; i < table.Columns.Count; i++)  
  99.                 {  
  100.                     Column column = table.Columns[i];  
  101.    
  102.                     // Is the current column an identity column?  
  103.                     if (column.IsIdentity == false)  
  104.                     {  
  105.                         // Append the necessary line breaks and commas  
  106.                         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)  
  107.                 {  
  108.                     writer.WriteLine();  
  109.                     writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");  
  110.                     writer.WriteLine("GO");  
  111.                 }  
  112.             }  
  113.         }  
  114.    
  115.         ///  
  116. <summary>  
  117.         /// Creates an update stored procedure SQL script for the specified table  
  118.         /// </summary>  
  119.    
  120.         /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param>  
  121.         /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param>  
  122.         /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param>  
  123.         /// <param name="path">Path where the stored procedure script should be created.</param>  
  124.         /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param>  
  125.         internal static void CreateUpdateStoredProcedure(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)  
  126.         {  
  127.             if (table.PrimaryKeys.Count > 0 && table.Columns.Count != table.PrimaryKeys.Count && table.Columns.Count != table.ForeignKeys.Count)  
  128.             {  
  129.                 // Create the stored procedure name  
  130.                 string procedureName = storedProcedurePrefix + table.Name + "Update";  
  131.                 string fileName;  
  132.    
  133.                 // Determine the file name to be used  
  134.                 if (createMultipleFiles)  
  135.                 {  
  136.                     fileName = Path.Combine(path, procedureName + ".sql");  
  137.                 }  
  138.                 else  
  139.                 {  
  140.                     fileName = Path.Combine(path, "StoredProcedures.sql");  
  141.                 }  
  142.    
  143.                 using (StreamWriter writer = new StreamWriter(fileName, true))  
  144.                 {  
  145.                     // Create the seperator  
  146.                     if (createMultipleFiles == false)  
  147.                     {  
  148.                         writer.WriteLine();  
  149.                         writer.WriteLine("/******************************************************************************");  
  150.                         writer.WriteLine("******************************************************************************/");  
  151.                     }  
  152.    
  153.                     // Create the drop statment  
  154.                     writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");  
  155.                     writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");  
  156.                     writer.WriteLine("GO");  
  157.                     writer.WriteLine();  
  158.    
  159.                     // Create the SQL for the stored procedure  
  160.                     writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");  
  161.                     writer.WriteLine("(");  
  162.    
  163.                     // Create the parameter list  
  164.                     for (int i = 0; i < table.Columns.Count; i++)  
  165.                     {  
  166.                         Column column = table.Columns[i];  
  167.    
  168.                         if (i == 0)  
  169.                         {  
  170.    
  171.                         }  
  172.                         if (i < (table.Columns.Count - 1))  
  173.                         {  
  174.                             writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false) + ",");  
  175.                         }  
  176.                         else  
  177.                         {  
  178.                             writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false));  
  179.                         }  
  180.                     }  
  181.                     writer.WriteLine(")");  
  182.    
  183.                     writer.WriteLine();  
  184.                     writer.WriteLine("AS");  
  185.                     writer.WriteLine();  
  186.                     writer.WriteLine("SET NOCOUNT ON");  
  187.                     writer.WriteLine();  
  188.                     writer.WriteLine("UPDATE [" + table.Name + "]");  
  189.                     writer.Write("SET");  
  190.    
  191.                     // Create the set statement  
  192.                     bool firstLine = true;  
  193.                     for (int i = 0; i < table.Columns.Count; i++)  
  194.                     {  
  195.                         var column = table.Columns[i];  
  196.    
  197.                         // Ignore Identity and RowGuidCol columns  
  198.                         if (table.PrimaryKeys.Contains(column) == false)  
  199.                         {  
  200.                             if (firstLine)  
  201.                             {  
  202.                                 writer.Write(" ");  
  203.                                 firstLine = false;  
  204.                             }  
  205.                             else  
  206.                             {  
  207.                                 writer.Write("\t");  
  208.                             }  
  209.    
  210.                             writer.Write("[" + column.Name + "] = @" + column.Name);  
  211.    
  212.                             if (i < (table.Columns.Count - 1))  
  213.                             {  
  214.                                 writer.Write(",");  
  215.                             }  
  216.    
  217.                             writer.WriteLine();  
  218.                         }  
  219.                     }  
  220.    
  221.                     writer.Write("WHERE");  
  222.    
  223.                     // Create the where clause  
  224.                     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)  
  225.                     {  
  226.                         writer.WriteLine();  
  227.                         writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");  
  228.                         writer.WriteLine("GO");  
  229.                     }  
  230.                 }  
  231.             }  
  232.         }  
  233.    
  234.         ///  
  235. <summary>  
  236.         /// Creates an delete stored procedure SQL script for the specified table  
  237.         /// </summary>  
  238.    
  239.         /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param>  
  240.         /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param>  
  241.         /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param>  
  242.         /// <param name="path">Path where the stored procedure script should be created.</param>  
  243.         /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param>  
  244.         internal static void CreateDeleteStoredProcedure(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)  
  245.         {  
  246.             if (table.PrimaryKeys.Count > 0)  
  247.             {  
  248.                 // Create the stored procedure name  
  249.                 string procedureName = storedProcedurePrefix + table.Name + "Delete";  
  250.                 string fileName;  
  251.    
  252.                 // Determine the file name to be used  
  253.                 if (createMultipleFiles)  
  254.                 {  
  255.                     fileName = Path.Combine(path, procedureName + ".sql");  
  256.                 }  
  257.                 else  
  258.                 {  
  259.                     fileName = Path.Combine(path, "StoredProcedures.sql");  
  260.                 }  
  261.    
  262.                 using (StreamWriter writer = new StreamWriter(fileName, true))  
  263.                 {  
  264.                     // Create the seperator  
  265.                     if (createMultipleFiles == false)  
  266.                     {  
  267.                         writer.WriteLine();  
  268.                         writer.WriteLine("/******************************************************************************");  
  269.                         writer.WriteLine("******************************************************************************/");  
  270.                     }  
  271.    
  272.                     // Create the drop statment  
  273.                     writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");  
  274.                     writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");  
  275.                     writer.WriteLine("GO");  
  276.                     writer.WriteLine();  
  277.    
  278.                     // Create the SQL for the stored procedure  
  279.                     writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");  
  280.                     writer.WriteLine("(");  
  281.    
  282.                     // Create the parameter list  
  283.                     for (int i = 0; i < table.PrimaryKeys.Count; i++)  
  284.                     {  
  285.                         Column column = table.PrimaryKeys[i];  
  286.    
  287.                         if (i < (table.PrimaryKeys.Count - 1))  
  288.                         {  
  289.                             writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false) + ",");  
  290.                         }  
  291.                         else  
  292.                         {  
  293.                             writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false));  
  294.                         }  
  295.                     }  
  296.                     writer.WriteLine(")");  
  297.    
  298.                     writer.WriteLine();  
  299.                     writer.WriteLine("AS");  
  300.                     writer.WriteLine();  
  301.                     writer.WriteLine("SET NOCOUNT ON");  
  302.                     writer.WriteLine();  
  303.                     writer.WriteLine("DELETE FROM [" + table.Name + "]");  
  304.                     writer.Write("WHERE");  
  305.    
  306.                     // Create the where clause  
  307.                     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)  
  308.                     {  
  309.                         writer.WriteLine();  
  310.                         writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");  
  311.                         writer.WriteLine("GO");  
  312.                     }  
  313.                 }  
  314.             }  
  315.         }  
  316.    
  317.         ///  
  318. <summary>  
  319.         /// Creates one or more delete stored procedures SQL script for the specified table and its foreign keys  
  320.         /// </summary>  
  321.    
  322.         /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param>  
  323.         /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param>  
  324.         /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param>  
  325.         /// <param name="path">Path where the stored procedure script should be created.</param>  
  326.         /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param>  
  327.         internal static void CreateDeleteAllByStoredProcedures(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)  
  328.         {  
  329.             // Create a stored procedure for each foreign key  
  330.             foreach (List<Column> compositeKeyList in table.ForeignKeys.Values)  
  331.             {  
  332.                 // Create the stored procedure name  
  333.                 StringBuilder stringBuilder = new StringBuilder(255);  
  334.                 stringBuilder.Append(storedProcedurePrefix + table.Name + "DeleteAllBy");  
  335.    
  336.                 // Create the parameter list  
  337.                 for (int i = 0; i < compositeKeyList.Count; i++) { Column column = compositeKeyList[i]; if (i > 0)  
  338.                     {  
  339.                         stringBuilder.Append("_" + AppUtility.FormatPascal(column.Name));  
  340.                     }  
  341.                     else  
  342.                     {  
  343.                         stringBuilder.Append(AppUtility.FormatPascal(column.Name));  
  344.                     }  
  345.                 }  
  346.    
  347.                 string procedureName = stringBuilder.ToString();  
  348.                 string fileName;  
  349.    
  350.                 // Determine the file name to be used  
  351.                 if (createMultipleFiles)  
  352.                 {  
  353.                     fileName = Path.Combine(path, procedureName + ".sql");  
  354.                 }  
  355.                 else  
  356.                 {  
  357.                     fileName = Path.Combine(path, "StoredProcedures.sql");  
  358.                 }  
  359.    
  360.                 using (StreamWriter writer = new StreamWriter(fileName, true))  
  361.                 {  
  362.                     // Create the seperator  
  363.                     if (createMultipleFiles == false)  
  364.                     {  
  365.                         writer.WriteLine();  
  366.                         writer.WriteLine("/******************************************************************************");  
  367.                         writer.WriteLine("******************************************************************************/");  
  368.                     }  
  369.    
  370.                     // Create the drop statment  
  371.                     writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");  
  372.                     writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");  
  373.                     writer.WriteLine("GO");  
  374.                     writer.WriteLine();  
  375.    
  376.                     // Create the SQL for the stored procedure  
  377.                     writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");  
  378.                     writer.WriteLine("(");  
  379.    
  380.                     // Create the parameter list  
  381.                     for (int i = 0; i < compositeKeyList.Count; i++)  
  382.                     {  
  383.                         Column column = compositeKeyList[i];  
  384.    
  385.                         if (i < (compositeKeyList.Count - 1))  
  386.                         {  
  387.                             writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false) + ",");  
  388.                         }  
  389.                         else  
  390.                         {  
  391.                             writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false));  
  392.                         }  
  393.                     }  
  394.                     writer.WriteLine(")");  
  395.    
  396.                     writer.WriteLine();  
  397.                     writer.WriteLine("AS");  
  398.                     writer.WriteLine();  
  399.                     writer.WriteLine("SET NOCOUNT ON");  
  400.                     writer.WriteLine();  
  401.                     writer.WriteLine("DELETE FROM [" + table.Name + "]");  
  402.                     writer.Write("WHERE");  
  403.    
  404.                     // Create the where clause  
  405.                     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)  
  406.                     {  
  407.                         writer.WriteLine();  
  408.                         writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");  
  409.                         writer.WriteLine("GO");  
  410.                     }  
  411.                 }  
  412.             }  
  413.         }  
  414.    
  415.         ///  
  416. <summary>  
  417.         /// Creates an select stored procedure SQL script for the specified table  
  418.         /// </summary>  
  419.    
  420.         /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param>  
  421.         /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param>  
  422.         /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param>  
  423.         /// <param name="path">Path where the stored procedure script should be created.</param>  
  424.         /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param>  
  425.         internal static void CreateSelectStoredProcedure(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)  
  426.         {  
  427.             if (table.PrimaryKeys.Count > 0 && table.ForeignKeys.Count != table.Columns.Count)  
  428.             {  
  429.                 // Create the stored procedure name  
  430.                 string procedureName = storedProcedurePrefix + table.Name + "Select";  
  431.                 string fileName;  
  432.    
  433.                 // Determine the file name to be used  
  434.                 if (createMultipleFiles)  
  435.                 {  
  436.                     fileName = Path.Combine(path, procedureName + ".sql");  
  437.                 }  
  438.                 else  
  439.                 {  
  440.                     fileName = Path.Combine(path, "StoredProcedures.sql");  
  441.                 }  
  442.    
  443.                 using (StreamWriter writer = new StreamWriter(fileName, true))  
  444.                 {  
  445.                     // Create the seperator  
  446.                     if (createMultipleFiles == false)  
  447.                     {  
  448.                         writer.WriteLine();  
  449.                         writer.WriteLine("/******************************************************************************");  
  450.                         writer.WriteLine("******************************************************************************/");  
  451.                     }  
  452.    
  453.                     // Create the drop statment  
  454.                     writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");  
  455.                     writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");  
  456.                     writer.WriteLine("GO");  
  457.                     writer.WriteLine();  
  458.    
  459.                     // Create the SQL for the stored procedure  
  460.                     writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");  
  461.                     writer.WriteLine("(");  
  462.    
  463.                     // Create the parameter list  
  464.                     for (int i = 0; i < table.PrimaryKeys.Count; i++)  
  465.                     {  
  466.                         Column column = table.PrimaryKeys[i];  
  467.    
  468.                         if (i == (table.PrimaryKeys.Count - 1))  
  469.                         {  
  470.                             writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false));  
  471.                         }  
  472.                         else  
  473.                         {  
  474.                             writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false) + ",");  
  475.                         }  
  476.                     }  
  477.    
  478.                     writer.WriteLine(")");  
  479.    
  480.                     writer.WriteLine();  
  481.                     writer.WriteLine("AS");  
  482.                     writer.WriteLine();  
  483.                     writer.WriteLine("SET NOCOUNT ON");  
  484.                     writer.WriteLine();  
  485.                     writer.Write("SELECT");  
  486.    
  487.                     // Create the list of columns  
  488.                     for (int i = 0; i < table.Columns.Count; i++)  
  489.                     {  
  490.                         Column column = table.Columns[i];  
  491.    
  492.                         if (i == 0)  
  493.                         {  
  494.                             writer.Write(" ");  
  495.                         }  
  496.                         else  
  497.                         {  
  498.                             writer.Write("\t");  
  499.                         }  
  500.    
  501.                         writer.Write("[" + column.Name + "]");  
  502.    
  503.                         if (i < (table.Columns.Count - 1))  
  504.                         {  
  505.                             writer.Write(",");  
  506.                         }  
  507.    
  508.                         writer.WriteLine();  
  509.                     }  
  510.    
  511.                     writer.WriteLine("FROM [" + table.Name + "]");  
  512.                     writer.Write("WHERE");  
  513.    
  514.                     // Create the where clause  
  515.                     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)  
  516.                     {  
  517.                         writer.WriteLine();  
  518.                         writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");  
  519.                         writer.WriteLine("GO");  
  520.                     }  
  521.                 }  
  522.             }  
  523.         }  
  524.    
  525.         ///  
  526. <summary>  
  527.         /// Creates an select all stored procedure SQL script for the specified table  
  528.         /// </summary>  
  529.    
  530.         /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param>  
  531.         /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param>  
  532.         /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param>  
  533.         /// <param name="path">Path where the stored procedure script should be created.</param>  
  534.         /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param>  
  535.         internal static void CreateSelectAllStoredProcedure(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)  
  536.         {  
  537.             if (table.PrimaryKeys.Count > 0 && table.ForeignKeys.Count != table.Columns.Count)  
  538.             {  
  539.                 // Create the stored procedure name  
  540.                 string procedureName = storedProcedurePrefix + table.Name + "SelectAll";  
  541.                 string fileName;  
  542.    
  543.                 // Determine the file name to be used  
  544.                 if (createMultipleFiles)  
  545.                 {  
  546.                     fileName = Path.Combine(path, procedureName + ".sql");  
  547.                 }  
  548.                 else  
  549.                 {  
  550.                     fileName = Path.Combine(path, "StoredProcedures.sql");  
  551.                 }  
  552.    
  553.                 using (StreamWriter writer = new StreamWriter(fileName, true))  
  554.                 {  
  555.                     // Create the seperator  
  556.                     if (createMultipleFiles == false)  
  557.                     {  
  558.                         writer.WriteLine();  
  559.                         writer.WriteLine("/******************************************************************************");  
  560.                         writer.WriteLine("******************************************************************************/");  
  561.                     }  
  562.    
  563.                     // Create the drop statment  
  564.                     writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");  
  565.                     writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");  
  566.                     writer.WriteLine("GO");  
  567.                     writer.WriteLine();  
  568.    
  569.                     // Create the SQL for the stored procedure  
  570.                     writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");  
  571.                     writer.WriteLine();  
  572.                     writer.WriteLine("AS");  
  573.                     writer.WriteLine();  
  574.                     writer.WriteLine("SET NOCOUNT ON");  
  575.                     writer.WriteLine();  
  576.                     writer.Write("SELECT");  
  577.    
  578.                     // Create the list of columns  
  579.                     for (int i = 0; i < table.Columns.Count; i++)  
  580.                     {  
  581.                         Column column = table.Columns[i];  
  582.    
  583.                         if (i == 0)  
  584.                         {  
  585.                             writer.Write(" ");  
  586.                         }  
  587.                         else  
  588.                         {  
  589.                             writer.Write("\t");  
  590.                         }  
  591.    
  592.                         writer.Write("[" + column.Name + "]");  
  593.    
  594.                         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)  
  595.                     {  
  596.                         writer.WriteLine();  
  597.                         writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");  
  598.                         writer.WriteLine("GO");  
  599.                     }  
  600.                 }  
  601.             }  
  602.         }  
  603.    
  604.         ///  
  605. <summary>  
  606.         /// Creates one or more select stored procedures SQL script for the specified table and its foreign keys  
  607.         /// </summary>  
  608.    
  609.         /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param>  
  610.         /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param>  
  611.         /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param>  
  612.         /// <param name="path">Path where the stored procedure script should be created.</param>  
  613.         /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param>  
  614.         internal static void CreateSelectAllByStoredProcedures(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)  
  615.         {  
  616.             // Create a stored procedure for each foreign key  
  617.             foreach (List<Column> compositeKeyList in table.ForeignKeys.Values)  
  618.             {  
  619.                 // Create the stored procedure name  
  620.                 StringBuilder stringBuilder = new StringBuilder(255);  
  621.                 stringBuilder.Append(storedProcedurePrefix + table.Name + "SelectAllBy");  
  622.    
  623.                 // Create the parameter list  
  624.                 for (int i = 0; i < compositeKeyList.Count; i++) { Column column = compositeKeyList[i]; if (i > 0)  
  625.                     {  
  626.                         stringBuilder.Append("_" + AppUtility.FormatPascal(column.Name));  
  627.                     }  
  628.                     else  
  629.                     {  
  630.                         stringBuilder.Append(AppUtility.FormatPascal(column.Name));  
  631.                     }  
  632.                 }  
  633.    
  634.                 string procedureName = stringBuilder.ToString();  
  635.                 string fileName;  
  636.    
  637.                 // Determine the file name to be used  
  638.                 if (createMultipleFiles)  
  639.                 {  
  640.                     fileName = Path.Combine(path, procedureName + ".sql");  
  641.                 }  
  642.                 else  
  643.                 {  
  644.                     fileName = Path.Combine(path, "StoredProcedures.sql");  
  645.                 }  
  646.    
  647.                 using (StreamWriter writer = new StreamWriter(fileName, true))  
  648.                 {  
  649.                     // Create the seperator  
  650.                     if (createMultipleFiles == false)  
  651.                     {  
  652.                         writer.WriteLine();  
  653.                         writer.WriteLine("/******************************************************************************");  
  654.                         writer.WriteLine("******************************************************************************/");  
  655.                     }  
  656.    
  657.                     // Create the drop statment  
  658.                     writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");  
  659.                     writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");  
  660.                     writer.WriteLine("GO");  
  661.                     writer.WriteLine();  
  662.    
  663.                     // Create the SQL for the stored procedure  
  664.                     writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");  
  665.                     writer.WriteLine("(");  
  666.    
  667.                     // Create the parameter list  
  668.                     for (int i = 0; i < compositeKeyList.Count; i++)  
  669.                     {  
  670.                         Column column = compositeKeyList[i];  
  671.    
  672.                         if (i < (compositeKeyList.Count - 1))  
  673.                         {  
  674.                             writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false) + ",");  
  675.                         }  
  676.                         else  
  677.                         {  
  678.                             writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false));  
  679.                         }  
  680.                     }  
  681.                     writer.WriteLine(")");  
  682.    
  683.                     writer.WriteLine();  
  684.                     writer.WriteLine("AS");  
  685.                     writer.WriteLine();  
  686.                     writer.WriteLine("SET NOCOUNT ON");  
  687.                     writer.WriteLine();  
  688.                     writer.Write("SELECT");  
  689.    
  690.                     // Create the list of columns  
  691.                     for (int i = 0; i < table.Columns.Count; i++)  
  692.                     {  
  693.                         Column column = table.Columns[i];  
  694.    
  695.                         if (i == 0)  
  696.                         {  
  697.                             writer.Write(" ");  
  698.                         }  
  699.                         else  
  700.                         {  
  701.                             writer.Write("\t");  
  702.                         }  
  703.    
  704.                         writer.Write("[" + column.Name + "]");  
  705.    
  706.                         if (i < (table.Columns.Count - 1))  
  707.                         {  
  708.                             writer.Write(",");  
  709.                         }  
  710.    
  711.                         writer.WriteLine();  
  712.                     }  
  713.    
  714.                     writer.WriteLine("FROM [" + table.Name + "]");  
  715.                     writer.Write("WHERE");  
  716.    
  717.                     // Create the where clause  
  718.                     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)  
  719.                     {  
  720.                         writer.WriteLine();  
  721.                         writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]");  
  722.                         writer.WriteLine("GO");  
  723.                     }  
  724.                 }  
  725.             }  
  726.         }  
Now lets understand the code of ‘Generate Classes’ button click event handler given as below:
  1. private void btnGenClasses_Click(object sender, EventArgs e)  
  2. {  
  3.     try  
  4.     {  
  5.         GenerateCSharpClasses();  
  6.         MessageBox.Show("Class file(s) created Successfully at path mentioned in 'Class Files Path'", "Success");  
  7.         grpOutPut.Visible = true;  
  8.     }  
  9.     catch (Exception ex)  
  10.     {  
  11.         MessageBox.Show("Error : " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);  
  12.     }  
  13. }  
The above written code uses following methods in order to generate C# POCO classes and Dapper Repo classes of selected tables:
  1. private void GenerateCSharpClasses()  
  2. {  
  3.     string sFolderPath, sNameSpace;  
  4.     if (txtNamespace.Text.Trim() != "")  
  5.     {  
  6.         sFolderPath = CreateOutputDir(txtNamespace.Text.Trim());  
  7.         sNameSpace = txtNamespace.Text.Trim();  
  8.     }  
  9.     else  
  10.     {  
  11.         sFolderPath = CreateOutputDir(mSSqlDatabase);  
  12.         sNameSpace = mSSqlDatabase;  
  13.     }  
  14.         CreateBaseRepoClass(sFolderPath + "\\BaseRepository.cs", sNameSpace);  
  15.         var objTableNames = new ArrayList();  
  16.         string sConString = "";  
  17.         sConString = txtConnectionString.Text + ";Initial Catalog=" + mSSqlDatabase;  
  18.    
  19.         for (int iTableCount = 0; iTableCount < cblTableList.CheckedItems.Count; iTableCount++)  
  20.         {  
  21.             objTableNames.Add(cblTableList.CheckedItems[iTableCount].ToString());  
  22.         }  
  23.         txtFilesPath.Text = CSharpCodeGenerator.GenerateClassFiles(sFolderPath, sConString, txtSPPrefix.Text.Trim(), sNameSpace, "", objTableNames);  
  24.     CSharpCodeGenerator.GenerateRepoFiles(sFolderPath, sConString, txtSPPrefix.Text.Trim(), sNameSpace, "", objTableNames);  
  25.    
  26.     }  
  27. private void CreateBaseRepoClass(string aSFilePath, string targetNamespace)  
  28. {  
  29.     using (var streamWriter = new StreamWriter(aSFilePath))  
  30.     {  
  31.         #region Add Referances  
  32.    
  33.         streamWriter.WriteLine("using System;");  
  34.         streamWriter.WriteLine("using System.Data;");  
  35.         streamWriter.WriteLine("using System.Data.SqlClient;");  
  36.         streamWriter.WriteLine("using System.Linq;");  
  37.         streamWriter.WriteLine("using System.Web.Configuration;");  
  38.         streamWriter.WriteLine();  
  39.         streamWriter.WriteLine("namespace " + targetNamespace);  
  40.         streamWriter.WriteLine("{");  
  41.   
  42.         #endregion  
  43.   
  44.         #region Create Base Repository Class  
  45.    
  46.         streamWriter.WriteLine("\t public abstract class BaseRepository ");  
  47.         streamWriter.WriteLine("\t\t {");  
  48.         streamWriter.WriteLine(  
  49.             "\t\t\t protected static void SetIdentity<T>(IDbConnection connection, Action<T> setId) ");  
  50.         streamWriter.WriteLine("\t\t\t {");  
  51.         streamWriter.WriteLine(  
  52.             "\t\t\t dynamic identity = connection.Query(\"SELECT @@IDENTITY AS Id\").Single(); ");  
  53.         streamWriter.WriteLine("\t\t\t T newId = (T)identity.Id; ");  
  54.         streamWriter.WriteLine("\t\t\t setId(newId); ");  
  55.         streamWriter.WriteLine("\t\t\t }");  
  56.    
  57.         streamWriter.WriteLine(  
  58.             "\t\t\t protected static IDbConnection OpenConnection() ");  
  59.         streamWriter.WriteLine("\t\t\t {");  
  60.         streamWriter.WriteLine(  
  61.             "\t\t\t IDbConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings[\"DBConString\"].ConnectionString); ");  
  62.         streamWriter.WriteLine("\t\t\t connection.Open(); ");  
  63.         streamWriter.WriteLine("\t\t\t return connection; ");  
  64.         streamWriter.WriteLine("\t\t\t }");  
  65.         streamWriter.WriteLine("\t\t }");  
  66.   
  67.         #endregion  
  68.     }  
  69. }  
‘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:  
  1. public static string GenerateClassFiles(string outputDirectory, string connectionString, string storedProcedurePrefix, string targetNamespace, string daoSuffix, ArrayList tableNames)  
  2.         {  
  3.             string databaseName = "";  
  4.             string csPath;  
  5.             csPath = Path.Combine(outputDirectory, "CS");  
  6.             List  
  7. <Table> tableList = AppUtility.GetTableList(connectionString, outputDirectory, tableNames, ref databaseName);  
  8.             // Generate the necessary SQL and C# code for each table              
  9.             if (tableList.Count <= 0) return csPath;  
  10.             // Create the necessary directories                  
  11.             AppUtility.CreateSubDirectory(csPath, true);  
  12.             foreach (Table table in tableList)  
  13.             {  
  14.                 CreateModelClass(databaseName, table, targetNamespace, storedProcedurePrefix, csPath);  
  15.             }  
  16.             return csPath;  
  17.         }  
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:
  1.        internal static void CreateModelClass(string databaseName, Table table, string targetNamespace, string storedProcedurePrefix, string path)  
  2.         {  
  3.             var className = AppUtility.FormatClassName(table.Name);  
  4.             using (var streamWriter = new StreamWriter(Path.Combine(path, className + ".cs")))  
  5.             {  
  6.                 #region Create the header for the class  
  7.                 streamWriter.WriteLine("using System;");  
  8.                 streamWriter.WriteLine();  
  9.                 streamWriter.WriteLine("namespace " + targetNamespace);  
  10.                 streamWriter.WriteLine("{");  
  11.    
  12.                 streamWriter.WriteLine("\tpublic class " + className);  
  13.                 streamWriter.WriteLine("\t{");  
  14.                 #endregion  
  15.   
  16.                 #region  Append the public properties  
  17.                 streamWriter.WriteLine("\t\t#region Properties");  
  18.                 for (var i = 0; i < table.Columns.Count; i++)  
  19.                 {  
  20.                     var column = table.Columns[i];  
  21.                     var parameter = AppUtility.CreateMethodParameter(column);  
  22.                     var type = parameter.Split(' ')[0];  
  23.                     var name = parameter.Split(' ')[1];  
  24.                     streamWriter.WriteLine("\t\t///  
  25. <summary>");  
  26.                     streamWriter.WriteLine("\t\t/// Gets or sets the " + AppUtility.FormatPascal(name) + " value.");  
  27.                     streamWriter.WriteLine("\t\t/// </summary>  
  28.    
  29. ");  
  30.                     streamWriter.WriteLine("\t\tpublic " + type + " " + AppUtility.FormatPascal(name));  
  31.                     streamWriter.WriteLine("\t\t{ get; set; }");  
  32.                     if (i < (table.Columns.Count - 1))  
  33.                     {  
  34.                         streamWriter.WriteLine();  
  35.                     }  
  36.                 }  
  37.    
  38.                 streamWriter.WriteLine();  
  39.                 streamWriter.WriteLine("\t\t#endregion");  
  40.                 #endregion  
  41.                 // Close out the class and namespace  
  42.                 streamWriter.WriteLine("\t}");  
  43.                 streamWriter.WriteLine("}");  
  44.             }  
  45.         }  
The code of ‘GenerateRepoFiles’ method of ‘CSharpCodeGenerator’ is as follows:
  1. public static string GenerateRepoFiles(string outputDirectory, string connectionString, string storedProcedurePrefix, string targetNamespace, string daoSuffix, ArrayList tableNames)  
  2.         {  
  3.             string databaseName = "";  
  4.             string csPath = Path.Combine(outputDirectory, "Repo");  
  5.             List  
  6. <Table> tableList = AppUtility.GetTableList(connectionString, outputDirectory, tableNames, ref databaseName);  
  7.             // Generate the necessary SQL and C# code for each table              
  8.             if (tableList.Count <= 0) return csPath;  
  9.             // Create the necessary directories                  
  10.             AppUtility.CreateSubDirectory(csPath, true);  
  11.             // Create the CRUD stored procedures and data access code for each table  
  12.             foreach (Table table in tableList)  
  13.             {  
  14.                 CreateRepoClass(databaseName, table, targetNamespace, storedProcedurePrefix, csPath);  
  15.             }  
  16.             return csPath;  
  17.         }  
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:
  1. internal static void CreateRepoClass(string databaseName, Table table, string targetNamespace, string storedProcedurePrefix, string path)  
  2.  {  
  3.      var className = AppUtility.FormatClassName(table.Name);  
  4.      using (var streamWriter = new StreamWriter(Path.Combine(path, className + ".cs")))  
  5.      {  
  6.          #region Add References & Declare Class  
  7.          streamWriter.WriteLine("using System.Collections.Generic;");  
  8.          streamWriter.WriteLine("using System.Data;");  
  9.          streamWriter.WriteLine("using System.Linq;");  
  10.          streamWriter.WriteLine("using Dapper;");  
  11.          streamWriter.WriteLine();  
  12.          streamWriter.WriteLine("namespace " + targetNamespace);  
  13.          streamWriter.WriteLine("{");  
  14.          streamWriter.WriteLine("\t public class " + className + "Repo : BaseRepository");  
  15.          streamWriter.WriteLine("\t\t {");  
  16.          #endregion  
  17.   
  18.          #region Append the access methods  
  19.          streamWriter.WriteLine("\t\t#region Methods");  
  20.          streamWriter.WriteLine();  
  21.          CreateInsertMethod(table, streamWriter);  
  22.          CreateUpdateMethod(table, streamWriter);  
  23.          CreateSelectMethod(table, streamWriter);  
  24.          CreateSelectAllMethod(table, streamWriter);  
  25.          CreateSelectAllByMethods(table, storedProcedurePrefix, streamWriter);  
  26.          #endregion  
  27.    
  28.          streamWriter.WriteLine();  
  29.          streamWriter.WriteLine("\t\t#endregion");  
  30.    
  31.          // Close out the class and namespace  
  32.          streamWriter.WriteLine("\t\t}");  
  33.          streamWriter.WriteLine("}");  
  34.      }  
  35.  }  
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:
  1. /// <summary>  
  2.  /// Creates a string that represents the insert functionality of the data access class.  
  3.  /// </summary>  
  4.  /// <param name="table">The Table instance that this method will be created for.</param>  
  5.  /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>  
  6.  private static void CreateInsertMethod(Table table, TextWriter streamWriter)  
  7.  {  
  8.      var className = AppUtility.FormatClassName(table.Name);  
  9.      var variableName = "a" + className;  
  10.    
  11.      // Append the method header  
  12.      streamWriter.WriteLine("\t\t/// <summary>");  
  13.      streamWriter.WriteLine("\t\t/// Saves a record to the " + table.Name + " table.");  
  14.      streamWriter.WriteLine("\t\t/// returns True if value saved successfullyelse false");  
  15.      streamWriter.WriteLine("\t\t/// Throw exception with message value 'EXISTS' if the data is duplicate");  
  16.      streamWriter.WriteLine("\t\t/// </summary>");  
  17.      streamWriter.WriteLine("\t\tpublic bool Insert(" + className + " " + variableName + ")");  
  18.      streamWriter.WriteLine("\t\t{");  
  19.      streamWriter.WriteLine("\t\t var blResult = false;");  
  20.      streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");  
  21.      streamWriter.WriteLine("\t\t\t\t {");  
  22.      streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();");  
  23.      foreach (var column in table.Columns)  
  24.      { streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + column.Name + "\"," + variableName + "." + AppUtility.FormatPascal(column.Name) + ");"); }  
  25.      streamWriter.WriteLine("\t\t\t\t\t int iResult = vConn.Execute(\"" + table.Name + "Insert\", vParams, commandType: CommandType.StoredProcedure);");  
  26.      streamWriter.WriteLine("\t\t\t if (iResult == -1) blResult = true;");  
  27.      streamWriter.WriteLine("\t\t\t }");  
  28.      streamWriter.WriteLine("\t\t\t return blResult;");  
  29.      streamWriter.WriteLine("\t\t}");  
  30.      streamWriter.WriteLine();  
  31.  }  
  32.    
  33.  /// <summary>  
  34.  /// Creates a string that represents the update functionality of the data access class.  
  35.  /// </summary>  
  36.  /// <param name="table">The Table instance that this method will be created for.</param>  
  37.  /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>  
  38.  private static void CreateUpdateMethod(Table table, TextWriter streamWriter)  
  39.  {  
  40.      if (table.PrimaryKeys.Count <= 0 || table.Columns.Count == table.PrimaryKeys.Count ||  
  41.          table.Columns.Count == table.ForeignKeys.Count) return;  
  42.      var className = AppUtility.FormatClassName(table.Name);  
  43.      var variableName = "a" + className;  
  44.    
  45.      // Append the method header  
  46.      streamWriter.WriteLine("\t\t/// <summary>");  
  47.      streamWriter.WriteLine("\t\t/// Updates record to the " + table.Name + " table.");  
  48.      streamWriter.WriteLine("\t\t/// returns True if value saved successfullyelse false");  
  49.      streamWriter.WriteLine("\t\t/// Throw exception with message value 'EXISTS' if the data is duplicate");  
  50.      streamWriter.WriteLine("\t\t/// </summary>");  
  51.      streamWriter.WriteLine("\t\tpublic bool Update(" + className + " " + variableName + ")");  
  52.      streamWriter.WriteLine("\t\t{");  
  53.      streamWriter.WriteLine("\t\t var blResult = false;");  
  54.      streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");  
  55.      streamWriter.WriteLine("\t\t\t\t {");  
  56.      streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();");  
  57.      foreach (var column in table.Columns)  
  58.      { streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + column.Name + "\"," + variableName + "." + AppUtility.FormatPascal(column.Name) + ");"); }  
  59.      streamWriter.WriteLine("\t\t\t\t\t int iResult = vConn.Execute(\"" + table.Name + "Update\", vParams, commandType: CommandType.StoredProcedure);");  
  60.      streamWriter.WriteLine("\t\t\t\t if (iResult == -1) blResult = true;");  
  61.      streamWriter.WriteLine("\t\t\t\t }");  
  62.      streamWriter.WriteLine("\t\t\treturn blResult;");  
  63.      streamWriter.WriteLine("\t\t}");  
  64.      streamWriter.WriteLine();  
  65.  }  
  66.    
  67.  /// <summary>  
  68.  /// Creates a string that represents the "select" functionality of the data access class.  
  69.  /// </summary>  
  70.  /// <param name="table">The Table instance that this method will be created for.</param>  
  71.   /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>  
  72.  private static void CreateSelectMethod(Table table, TextWriter streamWriter)  
  73.  {  
  74.      if (table.PrimaryKeys.Count <= 0 || table.Columns.Count == table.PrimaryKeys.Count ||  
  75.          table.Columns.Count == table.ForeignKeys.Count) return;  
  76.      var className = AppUtility.FormatClassName(table.Name);  
  77.      var variableName = "a" + table.PrimaryKeys[0].Name;  
  78.    
  79.      // Append the method header  
  80.      streamWriter.WriteLine("\t\t/// <summary>");  
  81.      streamWriter.WriteLine("\t\t/// Selects the Single object of " + table.Name + " table.");  
  82.      streamWriter.WriteLine("\t\t/// </summary>");  
  83.      streamWriter.WriteLine("\t\tpublic "+ className + " Get"+ className +"(" + AppUtility.GetCsType(table.PrimaryKeys[0]) + " " + variableName + ")");  
  84.      streamWriter.WriteLine("\t\t{");  
  85.      streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");  
  86.      streamWriter.WriteLine("\t\t\t\t {");  
  87.      streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();");  
  88.      streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + table.PrimaryKeys[0].Name + "\"," + variableName + ");");   
  89.      streamWriter.WriteLine("\t\t\t\t\t return vConn.Query<"+ className + ">(\"" + table.Name + "Select\", vParams, commandType: CommandType.StoredProcedure);");  
  90.      streamWriter.WriteLine("\t\t\t\t }");  
  91.      streamWriter.WriteLine("\t\t}");  
  92.      streamWriter.WriteLine();  
  93.    
  94.    
  95.  }  
  96.    
  97.  /// <summary>  
  98.  /// Creates a string that represents the select functionality of the data access class.  
  99.  /// </summary>  
  100.  /// <param name="table">The Table instance that this method will be created for.</param>  
  101.  /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>  
  102.  private static void CreateSelectAllMethod(Table table, TextWriter streamWriter)  
  103.  {  
  104.      if (table.Columns.Count == table.PrimaryKeys.Count || table.Columns.Count == table.ForeignKeys.Count)  
  105.          return;  
  106.      var className = AppUtility.FormatClassName(table.Name);  
  107.      // Append the method header  
  108.      streamWriter.WriteLine("\t\t/// <summary>");  
  109.      streamWriter.WriteLine("\t\t/// Selects all records from the " + table.Name + " table.");  
  110.      streamWriter.WriteLine("\t\t/// </summary>");  
  111.      streamWriter.WriteLine("\t\t public IEnumerable<" + className + "> SelectAll()");  
  112.      streamWriter.WriteLine("\t\t{");  
  113.      // Append the stored procedure execution  
  114.      streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");  
  115.      streamWriter.WriteLine("\t\t\t{");  
  116.      streamWriter.WriteLine("\t\t\t\t return vConn.Query<" + className + ">(\"" + table.Name + "SelectAll\", commandType: CommandType.StoredProcedure).ToList();");  
  117.      streamWriter.WriteLine("\t\t\t}");  
  118.      streamWriter.WriteLine("\t\t}");  
  119.  }  
  120.    
  121.  /// <summary>  
  122.  /// Creates a string that represents the "select by" functionality of the data access class.  
  123.  /// </summary>  
  124.  /// <param name="table">The Table instance that this method will be created for.</param>  
  125.  /// <param name="storedProcedurePrefix">The prefix that is used on the stored procedure that this method will call.</param>  
  126.  /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>  
  127.  private static void CreateSelectAllByMethods(Table table, string storedProcedurePrefix, TextWriter streamWriter)  
  128.  {  
  129.      string className = AppUtility.FormatClassName(table.Name);  
  130.      string dtoVariableName = AppUtility.FormatCamel(className);  
  131.    
  132.      // Create a stored procedure for each foreign key  
  133.      foreach (List<Column> compositeKeyList in table.ForeignKeys.Values)  
  134.      {  
  135.          // Create the stored procedure name  
  136.          StringBuilder stringBuilder = new StringBuilder(255);  
  137.          stringBuilder.Append("SelectAllBy");  
  138.          for (var i = 0; i < compositeKeyList.Count; i++)  
  139.          {  
  140.              var column = compositeKeyList[i];  
  141.    
  142.              if (i > 0)  
  143.              {  
  144.                  stringBuilder.Append("_" + AppUtility.FormatPascal(column.Name));  
  145.              }  
  146.              else  
  147.              {  
  148.                  stringBuilder.Append(AppUtility.FormatPascal(column.Name));  
  149.              }  
  150.          }  
  151.          string methodName = stringBuilder.ToString();  
  152.          string procedureName = storedProcedurePrefix + table.Name + methodName;  
  153.    
  154.          // Create the select function based on keys  
  155.          // Append the method header  
  156.          streamWriter.WriteLine("\t\t/// <summary>");  
  157.          streamWriter.WriteLine("\t\t/// Selects all records from the " + table.Name + " table by a foreign key.");  
  158.          streamWriter.WriteLine("\t\t/// </summary>");  
  159.    
  160.          streamWriter.Write("\t\tpublic List<" + className + "> " + methodName + "(");  
  161.          for (int i = 0; i < compositeKeyList.Count; i++)  
  162.          {  
  163.              Column column = compositeKeyList[i];  
  164.              streamWriter.Write(AppUtility.CreateMethodParameter(column));  
  165.              if (i < (compositeKeyList.Count - 1))  
  166.              {  
  167.                  streamWriter.Write(",");  
  168.              }  
  169.          }  
  170.          streamWriter.WriteLine(")");  
  171.          streamWriter.WriteLine("\t\t{");  
  172.    
  173.          streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())");  
  174.          streamWriter.WriteLine("\t\t\t\t {");  
  175.          streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();");  
  176.          for (var i = 0; i < compositeKeyList.Count; i++)  
  177.          {  
  178.              var column = compositeKeyList[i];  
  179.              streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + column.Name + "\"," + AppUtility.FormatCamel(column.Name) + ");");  
  180.          }  
  181.          streamWriter.WriteLine("\t\t\t\t return vConn.Query<" + className + ">(\"" + table.Name + "SelectAll\", vParams, commandType: CommandType.StoredProcedure).ToList();");  
  182.          streamWriter.WriteLine("\t\t\t\t }");  
  183.          streamWriter.WriteLine("\t\t}");  
  184.          streamWriter.WriteLine();  
  185.      }  
  186.  }  
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.

genrate

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:


Similar Articles