/// <summary> /// Creates a string that represents the "select by" functionality of the data access class. /// </summary> /// <param name="table">The Table instance that this method will be created for.</param> /// <param name="storedProcedurePrefix">The prefix that is used on the stored procedure that this method will call.</param> /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param> private static void CreateSelectAllByMethods(Table table, string storedProcedurePrefix, TextWriter streamWriter) { string className = AppUtility.FormatClassName(table.Name); string dtoVariableName = AppUtility.FormatCamel(className); // Create a stored procedure for each foreign key foreach (List <Column> compositeKeyList in table.ForeignKeys.Values) { // Create the stored procedure name StringBuilder stringBuilder = new StringBuilder(255); stringBuilder.Append("SelectAllBy"); for (var i = 0; i < compositeKeyList.Count; i++) { var column = compositeKeyList[i]; if (i > 0) { stringBuilder.Append("_" + AppUtility.FormatPascal(column.Name)); } else { stringBuilder.Append(AppUtility.FormatPascal(column.Name)); } } string methodName = stringBuilder.ToString(); string procedureName = storedProcedurePrefix + table.Name + methodName; // Create the select function based on keys // Append the method header streamWriter.WriteLine("\t\t/// <summary>"); streamWriter.WriteLine("\t\t/// Selects all records from the " + table.Name + " table by a foreign key."); streamWriter.WriteLine("\t\t/// </summary>"); streamWriter.Write("\t\tpublic List<" + className + "> " + methodName + "("); for (int i = 0; i < compositeKeyList.Count; i++) { Column column = compositeKeyList[i]; streamWriter.Write(AppUtility.CreateMethodParameter(column)); if (i < (compositeKeyList.Count - 1)) { streamWriter.Write(","); } } streamWriter.WriteLine(")"); streamWriter.WriteLine("\t\t{"); streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())"); streamWriter.WriteLine("\t\t\t\t {"); streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();"); for (var i = 0; i < compositeKeyList.Count; i++) { var column = compositeKeyList[i]; streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + column.Name + "\"," + AppUtility.FormatCamel(column.Name) + ");"); } streamWriter.WriteLine("\t\t\t\t return vConn.Query<" + className + ">(\"" + table.Name + "SelectAll\", vParams, commandType: CommandType.StoredProcedure).ToList();"); streamWriter.WriteLine("\t\t\t\t }"); streamWriter.WriteLine("\t\t}"); streamWriter.WriteLine(); } }
/// <summary> /// Creates a C# Model /POCO class for all of the table's of Database. /// </summary> /// <param name="databaseName">The name of the database.</param> /// <param name="table">Instance of the Table class that represents the table this class will be created for.</param> /// <param name="targetNamespace">The namespace that the generated C# classes should contained in.</param> /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param> /// <param name="path">Path where the class should be created.</param> internal static void CreateModelClass(string databaseName, Table table, string targetNamespace, string storedProcedurePrefix, string path) { var className = AppUtility.FormatClassName(table.Name); using (var streamWriter = new StreamWriter(Path.Combine(path, className + ".cs"))) { #region Create the header for the class streamWriter.WriteLine("using System;"); streamWriter.WriteLine(); streamWriter.WriteLine("namespace " + targetNamespace); streamWriter.WriteLine("{"); streamWriter.WriteLine("\tpublic class " + className); streamWriter.WriteLine("\t{"); #endregion #region Append the public properties streamWriter.WriteLine("\t\t#region Properties"); for (var i = 0; i < table.Columns.Count; i++) { var column = table.Columns[i]; var parameter = AppUtility.CreateMethodParameter(column); var type = parameter.Split(' ')[0]; var name = parameter.Split(' ')[1]; streamWriter.WriteLine("\t\t/// <summary>"); streamWriter.WriteLine("\t\t/// Gets or sets the " + AppUtility.FormatPascal(name) + " value."); streamWriter.WriteLine("\t\t/// </summary>"); streamWriter.WriteLine("\t\tpublic " + type + " " + AppUtility.FormatPascal(name)); streamWriter.WriteLine("\t\t{ get; set; }"); if (i < (table.Columns.Count - 1)) { streamWriter.WriteLine(); } } streamWriter.WriteLine(); streamWriter.WriteLine("\t\t#endregion"); #endregion // Close out the class and namespace streamWriter.WriteLine("\t}"); streamWriter.WriteLine("}"); } }
/// <summary> /// Retrives the list of all the tables selected with their Column and other details. /// </summary> /// <param name="outputDirectory">The directory where the C# and SQL code should be created.</param> /// <param name="connectionString">The connection string to be used to connect the to the database.</param> /// <param name="tableNames">ArrayList of Table names whose whole details have to be fetched has to be created.</param> /// <param name="databaseName">Reference parameter which is used to get DB name from the method</param> /// <returns></returns> internal static List <Table> GetTableList(string connectionString, string outputDirectory, ArrayList tableNames, ref String databaseName) { List <Table> tableList = new List <Table>(); string sqlPath; sqlPath = Path.Combine(outputDirectory, "SQL"); using (SqlConnection connection = new SqlConnection(connectionString)) { databaseName = AppUtility.FormatPascal(connection.Database); connection.Open(); // Process each table for (int iCount = 0; iCount < tableNames.Count; iCount++) { Table table = new Table(); table.Name = (string)tableNames[iCount]; QueryTable(connection, table); tableList.Add(table); } } return(tableList); }
/// <summary> /// Creates a string that represents the update functionality of the data access class. /// </summary> /// <param name="table">The Table instance that this method will be created for.</param> /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param> private static void CreateUpdateMethod(Table table, TextWriter streamWriter) { if (table.PrimaryKeys.Count <= 0 || table.Columns.Count == table.PrimaryKeys.Count || table.Columns.Count == table.ForeignKeys.Count) { return; } var className = AppUtility.FormatClassName(table.Name); var variableName = "a" + className; // Append the method header streamWriter.WriteLine("\t\t/// <summary>"); streamWriter.WriteLine("\t\t/// Updates record to the " + table.Name + " table."); streamWriter.WriteLine("\t\t/// returns True if value saved successfullyelse false"); streamWriter.WriteLine("\t\t/// Throw exception with message value 'EXISTS' if the data is duplicate"); streamWriter.WriteLine("\t\t/// </summary>"); streamWriter.WriteLine("\t\tpublic bool Update(" + className + " " + variableName + ")"); streamWriter.WriteLine("\t\t{"); streamWriter.WriteLine("\t\t var blResult = false;"); streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())"); streamWriter.WriteLine("\t\t\t\t {"); streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();"); foreach (var column in table.Columns) { streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + column.Name + "\"," + variableName + "." + AppUtility.FormatPascal(column.Name) + ");"); } streamWriter.WriteLine("\t\t\t\t\t int iResult = vConn.Execute(\"" + table.Name + "Update\", vParams, commandType: CommandType.StoredProcedure);"); streamWriter.WriteLine("\t\t\t\t if (iResult == -1) blResult = true;"); streamWriter.WriteLine("\t\t\t\t }"); streamWriter.WriteLine("\t\t\treturn blResult;"); streamWriter.WriteLine("\t\t}"); streamWriter.WriteLine(); }
/// <summary> /// Creates one or more select stored procedures SQL script for the specified table and its foreign keys /// </summary> /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param> /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param> /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param> /// <param name="path">Path where the stored procedure script should be created.</param> /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param> internal static void CreateSelectAllByStoredProcedures(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles) { // Create a stored procedure for each foreign key foreach (List <Column> compositeKeyList in table.ForeignKeys.Values) { // Create the stored procedure name StringBuilder stringBuilder = new StringBuilder(255); stringBuilder.Append(storedProcedurePrefix + table.Name + "SelectAllBy"); // Create the parameter list for (int i = 0; i < compositeKeyList.Count; i++) { Column column = compositeKeyList[i]; if (i > 0) { stringBuilder.Append("_" + AppUtility.FormatPascal(column.Name)); } else { stringBuilder.Append(AppUtility.FormatPascal(column.Name)); } } string procedureName = stringBuilder.ToString(); string fileName; // Determine the file name to be used if (createMultipleFiles) { fileName = Path.Combine(path, procedureName + ".sql"); } else { fileName = Path.Combine(path, "StoredProcedures.sql"); } using (StreamWriter writer = new StreamWriter(fileName, true)) { // Create the seperator if (createMultipleFiles == false) { writer.WriteLine(); writer.WriteLine("/******************************************************************************"); writer.WriteLine("******************************************************************************/"); } // Create the drop statment writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)"); writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]"); writer.WriteLine("GO"); writer.WriteLine(); // Create the SQL for the stored procedure writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]"); writer.WriteLine("("); // Create the parameter list for (int i = 0; i < compositeKeyList.Count; i++) { Column column = compositeKeyList[i]; if (i < (compositeKeyList.Count - 1)) { writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false) + ","); } else { writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false)); } } writer.WriteLine(")"); writer.WriteLine(); writer.WriteLine("AS"); writer.WriteLine(); writer.WriteLine("SET NOCOUNT ON"); writer.WriteLine(); writer.Write("SELECT"); // Create the list of columns for (int i = 0; i < table.Columns.Count; i++) { Column column = table.Columns[i]; if (i == 0) { writer.Write(" "); } else { writer.Write("\t"); } writer.Write("[" + column.Name + "]"); if (i < (table.Columns.Count - 1)) { writer.Write(","); } writer.WriteLine(); } writer.WriteLine("FROM [" + table.Name + "]"); writer.Write("WHERE"); // Create the where clause for (int i = 0; i < compositeKeyList.Count; i++) { Column column = compositeKeyList[i]; if (i == 0) { writer.WriteLine(" [" + column.Name + "] = @" + column.Name); } else { writer.WriteLine("\tAND [" + column.Name + "] = @" + column.Name); } } writer.WriteLine("GO"); // Create the grant statement, if a user was specified if (grantLoginName.Length > 0) { writer.WriteLine(); writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]"); writer.WriteLine("GO"); } } } }