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. {
  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);
  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. }
  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;
  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. }
  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. }
  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();
  33. // Create the SQL for the stored procedure
  34. writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");
  35. writer.WriteLine("(");
  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(")");
  53. writer.WriteLine();
  54. writer.WriteLine("AS");
  55. writer.WriteLine();
  56. writer.WriteLine("SET NOCOUNT ON");
  57. writer.WriteLine();
  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. }
  70. writer.WriteLine("INSERT INTO [" + table.Name + "]");
  71. writer.WriteLine("(");
  73. // Create the parameter list
  74. for (int i = 0; i < table.Columns.Count; i++)
  75. {
  76. Column column = table.Columns[i];
  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. }
  93. writer.WriteLine(")");
  94. writer.WriteLine("VALUES");
  95. writer.WriteLine("(");
  97. // Create the values list
  98. for (int i = 0; i < table.Columns.Count; i++)
  99. {
  100. Column column = table.Columns[i];
  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. }
  115. ///
  116. <summary>
  117. /// Creates an update stored procedure SQL script for the specified table
  118. /// </summary>
  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;
  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. }
  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. }
  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();
  159. // Create the SQL for the stored procedure
  160. writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");
  161. writer.WriteLine("(");
  163. // Create the parameter list
  164. for (int i = 0; i < table.Columns.Count; i++)
  165. {
  166. Column column = table.Columns[i];
  168. if (i == 0)
  169. {
  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(")");
  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");
  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];
  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. }
  210. writer.Write("[" + column.Name + "] = @" + column.Name);
  212. if (i < (table.Columns.Count - 1))
  213. {
  214. writer.Write(",");
  215. }
  217. writer.WriteLine();
  218. }
  219. }
  221. writer.Write("WHERE");
  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. }
  234. ///
  235. <summary>
  236. /// Creates an delete stored procedure SQL script for the specified table
  237. /// </summary>
  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;
  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. }
  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. }
  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();
  278. // Create the SQL for the stored procedure
  279. writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");
  280. writer.WriteLine("(");
  282. // Create the parameter list
  283. for (int i = 0; i < table.PrimaryKeys.Count; i++)
  284. {
  285. Column column = table.PrimaryKeys[i];
  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(")");
  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");
  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. }
  317. ///
  318. <summary>
  319. /// Creates one or more delete stored procedures SQL script for the specified table and its foreign keys
  320. /// </summary>
  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");
  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. }
  347. string procedureName = stringBuilder.ToString();
  348. string fileName;
  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. }
  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. }
  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();
  376. // Create the SQL for the stored procedure
  377. writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");
  378. writer.WriteLine("(");
  380. // Create the parameter list
  381. for (int i = 0; i < compositeKeyList.Count; i++)
  382. {
  383. Column column = compositeKeyList[i];
  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(")");
  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");
  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. }
  415. ///
  416. <summary>
  417. /// Creates an select stored procedure SQL script for the specified table
  418. /// </summary>
  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;
  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. }
  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. }
  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();
  459. // Create the SQL for the stored procedure
  460. writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");
  461. writer.WriteLine("(");
  463. // Create the parameter list
  464. for (int i = 0; i < table.PrimaryKeys.Count; i++)
  465. {
  466. Column column = table.PrimaryKeys[i];
  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. }
  478. writer.WriteLine(")");
  480. writer.WriteLine();
  481. writer.WriteLine("AS");
  482. writer.WriteLine();
  483. writer.WriteLine("SET NOCOUNT ON");
  484. writer.WriteLine();
  485. writer.Write("SELECT");
  487. // Create the list of columns
  488. for (int i = 0; i < table.Columns.Count; i++)
  489. {
  490. Column column = table.Columns[i];
  492. if (i == 0)
  493. {
  494. writer.Write(" ");
  495. }
  496. else
  497. {
  498. writer.Write("\t");
  499. }
  501. writer.Write("[" + column.Name + "]");
  503. if (i < (table.Columns.Count - 1))
  504. {
  505. writer.Write(",");
  506. }
  508. writer.WriteLine();
  509. }
  511. writer.WriteLine("FROM [" + table.Name + "]");
  512. writer.Write("WHERE");
  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. }
  525. ///
  526. <summary>
  527. /// Creates an select all stored procedure SQL script for the specified table
  528. /// </summary>
  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;
  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. }
  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. }
  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();
  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");
  578. // Create the list of columns
  579. for (int i = 0; i < table.Columns.Count; i++)
  580. {
  581. Column column = table.Columns[i];
  583. if (i == 0)
  584. {
  585. writer.Write(" ");
  586. }
  587. else
  588. {
  589. writer.Write("\t");
  590. }
  592. writer.Write("[" + column.Name + "]");
  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. }
  604. ///
  605. <summary>
  606. /// Creates one or more select stored procedures SQL script for the specified table and its foreign keys
  607. /// </summary>
  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");
  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. }
  634. string procedureName = stringBuilder.ToString();
  635. string fileName;
  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. }
  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. }
  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();
  663. // Create the SQL for the stored procedure
  664. writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]");
  665. writer.WriteLine("(");
  667. // Create the parameter list
  668. for (int i = 0; i < compositeKeyList.Count; i++)
  669. {
  670. Column column = compositeKeyList[i];
  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(")");
  683. writer.WriteLine();
  684. writer.WriteLine("AS");
  685. writer.WriteLine();
  686. writer.WriteLine("SET NOCOUNT ON");
  687. writer.WriteLine();
  688. writer.Write("SELECT");
  690. // Create the list of columns
  691. for (int i = 0; i < table.Columns.Count; i++)
  692. {
  693. Column column = table.Columns[i];
  695. if (i == 0)
  696. {
  697. writer.Write(" ");
  698. }
  699. else
  700. {
  701. writer.Write("\t");
  702. }
  704. writer.Write("[" + column.Name + "]");
  706. if (i < (table.Columns.Count - 1))
  707. {
  708. writer.Write(",");
  709. }
  711. writer.WriteLine();
  712. }
  714. writer.WriteLine("FROM [" + table.Name + "]");
  715. writer.Write("WHERE");
  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;
  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);
  26. }
  27. private void CreateBaseRepoClass(string aSFilePath, string targetNamespace)
  28. {
  29. using (var streamWriter = new StreamWriter(aSFilePath))
  30. {
  31. #region Add Referances
  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("{");
  42. #endregion
  44. #region Create Base Repository Class
  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 }");
  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 }");
  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("{");
  12. streamWriter.WriteLine("\tpublic class " + className);
  13. streamWriter.WriteLine("\t{");
  14. #endregion
  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>
  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. }
  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
  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
  28. streamWriter.WriteLine();
  29. streamWriter.WriteLine("\t\t#endregion");
  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;
  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. }
  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;
  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. }
  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;
  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();
  95. }
  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. }
  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);
  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];
  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;
  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>");
  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{");
  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: