/// <summary> /// Creates a string that represents the "select" functionality of the data access class. /// </summary> /// <param name="table">The Table instance that this method will be created for.</param> /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param> private static void CreateSelectMethod(Table table, TextWriter streamWriter) { if (table.PrimaryKeys.Count <= 0 || table.Columns.Count == table.PrimaryKeys.Count || table.Columns.Count == table.ForeignKeys.Count) { return; } var className = AppUtility.FormatClassName(table.Name); var variableName = "a" + table.PrimaryKeys[0].Name; // Append the method header streamWriter.WriteLine("\t\t/// <summary>"); streamWriter.WriteLine("\t\t/// Selects the Single object of " + table.Name + " table."); streamWriter.WriteLine("\t\t/// </summary>"); streamWriter.WriteLine("\t\tpublic " + className + " Get" + className + "(" + AppUtility.GetCsType(table.PrimaryKeys[0]) + " " + variableName + ")"); streamWriter.WriteLine("\t\t{"); streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())"); streamWriter.WriteLine("\t\t\t\t {"); streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();"); streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + table.PrimaryKeys[0].Name + "\"," + variableName + ");"); streamWriter.WriteLine("\t\t\t\t\t return vConn.Query<" + className + ">(\"" + table.Name + "Select\", vParams, commandType: CommandType.StoredProcedure);"); streamWriter.WriteLine("\t\t\t\t }"); streamWriter.WriteLine("\t\t}"); streamWriter.WriteLine(); }
/// <summary> /// Creates a string that represents the "select 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> /// Generate CRUD SP SQL scripts for the DB selected Tables /// </summary> /// <param name="outputDirectory">The directory where the SQL code should be created.</param> /// <param name="connectionString">The connection string to be used to connect the to the database.</param> /// <param name="grantLoginName">The SQL Server login name that should be granted execute rights on the generated stored procedures.</param> /// <param name="storedProcedurePrefix">The prefix that should be used when creating stored procedures.</param> /// <param name="createMultipleFiles">A flag indicating if the generated stored procedures should be created in one file or separate files.</param> /// <param name="tableNames">ArrayList of Table names whose SPs has to be created.</param> /// <returns></returns> public static string GenerateSQLFiles(string outputDirectory, string connectionString, string grantLoginName, string storedProcedurePrefix, bool createMultipleFiles, ArrayList tableNames) { string databaseName = ""; string sqlPath; sqlPath = Path.Combine(outputDirectory, "SQL"); List <Table> tableList = AppUtility.GetTableList(connectionString, outputDirectory, tableNames, ref databaseName); // Generate the necessary SQL for each table int count = 0; if (tableList.Count > 0) { // Create the necessary directories AppUtility.CreateSubDirectory(sqlPath, true); // Create the necessary database logins CreateUserQueries(databaseName, grantLoginName, sqlPath, createMultipleFiles); // Create the CRUD stored procedures and data access code for each table foreach (Table table in tableList) { CreateInsertStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles); CreateUpdateStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles); CreateDeleteStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles); CreateDeleteAllByStoredProcedures(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles); CreateSelectStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles); CreateSelectAllStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles); CreateSelectAllByStoredProcedures(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles); count++; } } return(sqlPath); }
/// <summary> /// Creates a string that represents the update functionality of the data access class. /// </summary> /// <param name="table">The Table instance that this method will be created for.</param> /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param> private static void CreateUpdateMethod(Table table, TextWriter streamWriter) { if (table.PrimaryKeys.Count <= 0 || table.Columns.Count == table.PrimaryKeys.Count || table.Columns.Count == table.ForeignKeys.Count) { return; } var className = AppUtility.FormatClassName(table.Name); var variableName = "a" + className; // Append the method header streamWriter.WriteLine("\t\t/// <summary>"); streamWriter.WriteLine("\t\t/// Updates record to the " + table.Name + " table."); streamWriter.WriteLine("\t\t/// returns True if value saved successfullyelse false"); streamWriter.WriteLine("\t\t/// Throw exception with message value 'EXISTS' if the data is duplicate"); streamWriter.WriteLine("\t\t/// </summary>"); streamWriter.WriteLine("\t\tpublic bool Update(" + className + " " + variableName + ")"); streamWriter.WriteLine("\t\t{"); streamWriter.WriteLine("\t\t var blResult = false;"); streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())"); streamWriter.WriteLine("\t\t\t\t {"); streamWriter.WriteLine("\t\t\t\t var vParams = new DynamicParameters();"); foreach (var column in table.Columns) { streamWriter.WriteLine("\t\t\t\t\t vParams.Add(\"@" + column.Name + "\"," + variableName + "." + AppUtility.FormatPascal(column.Name) + ");"); } streamWriter.WriteLine("\t\t\t\t\t int iResult = vConn.Execute(\"" + table.Name + "Update\", vParams, commandType: CommandType.StoredProcedure);"); streamWriter.WriteLine("\t\t\t\t if (iResult == -1) blResult = true;"); streamWriter.WriteLine("\t\t\t\t }"); streamWriter.WriteLine("\t\t\treturn blResult;"); streamWriter.WriteLine("\t\t}"); streamWriter.WriteLine(); }
/// <summary> /// Creates a project file that references each generated C# code file for data access. /// </summary> /// <param name="path">The path where the project file should be created.</param> /// <param name="projectName">The name of the project.</param> /// <param name="tableList">The list of tables code files were created for.</param> /// <param name="daoSuffix">The suffix to append to the name of each data access class.</param> internal static void CreateProjectFile(string path, string projectName, List <Table> tableList, string daoSuffix) { string projectXml = AppUtility.GetResource("PocoSqlGenerator.Resources.Project.xml"); var document = new XmlDocument(); document.LoadXml(projectXml); XmlNamespaceManager namespaceManager = new XmlNamespaceManager(document.NameTable); namespaceManager.AddNamespace(String.Empty, "http://schemas.microsoft.com/developer/msbuild/2003"); namespaceManager.AddNamespace("msbuild", "http://schemas.microsoft.com/developer/msbuild/2003"); var selectSingleNode = document.SelectSingleNode("/msbuild:Project/msbuild:PropertyGroup/msbuild:ProjectGuid", namespaceManager); if (selectSingleNode != null) { selectSingleNode.InnerText = "{" + Guid.NewGuid().ToString() + "}"; } var singleNode = document.SelectSingleNode("/msbuild:Project/msbuild:PropertyGroup/msbuild:RootNamespace", namespaceManager); if (singleNode != null) { singleNode.InnerText = projectName; } var xmlNode = document.SelectSingleNode("/msbuild:Project/msbuild:PropertyGroup/msbuild:AssemblyName", namespaceManager); if (xmlNode != null) { xmlNode.InnerText = projectName; } XmlNode itemGroupNode = document.SelectSingleNode("/msbuild:Project/msbuild:ItemGroup[msbuild:Compile]", namespaceManager); foreach (Table table in tableList) { string className = AppUtility.FormatClassName(table.Name); XmlNode dtoCompileNode = document.CreateElement("Compile", "http://schemas.microsoft.com/developer/msbuild/2003"); XmlAttribute dtoAttribute = document.CreateAttribute("Include"); dtoAttribute.Value = className + ".cs"; if (dtoCompileNode.Attributes != null) { dtoCompileNode.Attributes.Append(dtoAttribute); } if (itemGroupNode != null) { itemGroupNode.AppendChild(dtoCompileNode); } } document.Save(Path.Combine(path, projectName + ".csproj")); }
/// <summary> /// Creates the AssemblyInfo.cs file for the project. /// </summary> /// <param name="path">The root path of the project.</param> /// <param name="assemblyTitle">The title of the assembly.</param> /// <param name="databaseName">The name of the database the assembly provides access to.</param> internal static void CreateAssemblyInfo(string path, string assemblyTitle, string databaseName) { string assemblyInfo = AppUtility.GetResource("PocoSqlGenerator.Resources.AssemblyInfo.txt"); assemblyInfo.Replace("#AssemblyTitle", assemblyTitle); assemblyInfo.Replace("#DatabaseName", databaseName); string propertiesDirectory = Path.Combine(path, "Properties"); if (Directory.Exists(propertiesDirectory) == false) { Directory.CreateDirectory(propertiesDirectory); } File.WriteAllText(Path.Combine(propertiesDirectory, "AssemblyInfo.cs"), assemblyInfo); }
/// <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> /// Creates a string that represents the select functionality of the data access class. /// </summary> /// <param name="table">The Table instance that this method will be created for.</param> /// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param> private static void CreateSelectAllMethod(Table table, TextWriter streamWriter) { if (table.Columns.Count == table.PrimaryKeys.Count || table.Columns.Count == table.ForeignKeys.Count) { return; } var className = AppUtility.FormatClassName(table.Name); // Append the method header streamWriter.WriteLine("\t\t/// <summary>"); streamWriter.WriteLine("\t\t/// Selects all records from the " + table.Name + " table."); streamWriter.WriteLine("\t\t/// </summary>"); streamWriter.WriteLine("\t\t public IEnumerable<" + className + "> SelectAll()"); streamWriter.WriteLine("\t\t{"); // Append the stored procedure execution streamWriter.WriteLine("\t\t\t using (var vConn = OpenConnection())"); streamWriter.WriteLine("\t\t\t{"); streamWriter.WriteLine("\t\t\t\t return vConn.Query<" + className + ">(\"" + table.Name + "SelectAll\", commandType: CommandType.StoredProcedure).ToList();"); streamWriter.WriteLine("\t\t\t}"); streamWriter.WriteLine("\t\t}"); }
/// <summary> /// 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); }
public static string GenerateRepoFiles(string outputDirectory, string connectionString, string storedProcedurePrefix, string targetNamespace, string daoSuffix, ArrayList tableNames) { string databaseName = ""; string csPath = Path.Combine(outputDirectory, "Repo"); List <Table> tableList = AppUtility.GetTableList(connectionString, outputDirectory, tableNames, ref databaseName); // Generate the necessary SQL and C# code for each table if (tableList.Count <= 0) { return(csPath); } // Create the necessary directories AppUtility.CreateSubDirectory(csPath, true); // Create the CRUD stored procedures and data access code for each table foreach (Table table in tableList) { CreateRepoClass(databaseName, table, targetNamespace, storedProcedurePrefix, csPath); } CreateAssemblyInfo(csPath, targetNamespace, databaseName); CreateProjectFile(csPath, targetNamespace, tableList, daoSuffix); return(csPath); }
/// <summary> /// Creates the SQL script that is responsible for granting the specified login access to the specified database. /// </summary> /// <param name="databaseName">The name of the database that the login 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="path">Path where the script should be created.</param> /// <param name="createMultipleFiles">Indicates the script should be created in its own file.</param> internal static void CreateUserQueries(string databaseName, string grantLoginName, string path, bool createMultipleFiles) { if (grantLoginName.Length > 0) { string fileName; // Determine the file name to be used if (createMultipleFiles) { fileName = Path.Combine(path, "GrantUserPermissions.sql"); } else { fileName = Path.Combine(path, "StoredProcedures.sql"); } using (StreamWriter writer = new StreamWriter(fileName, true)) { writer.Write(AppUtility.GetUserQueries(databaseName, grantLoginName)); } } }
internal static void CreateRepoClass(string databaseName, Table table, string targetNamespace, string storedProcedurePrefix, string path) { var className = AppUtility.FormatClassName(table.Name); using (var streamWriter = new StreamWriter(Path.Combine(path, className + ".cs"))) { #region Add References & Declare Class streamWriter.WriteLine("using System.Collections.Generic;"); streamWriter.WriteLine("using System.Data;"); streamWriter.WriteLine("using System.Linq;"); streamWriter.WriteLine("using Dapper;"); streamWriter.WriteLine(); streamWriter.WriteLine("namespace " + targetNamespace); streamWriter.WriteLine("{"); streamWriter.WriteLine("\t public class " + className + "Repo : BaseRepository"); streamWriter.WriteLine("\t\t {"); #endregion #region Append the access methods streamWriter.WriteLine("\t\t#region Methods"); streamWriter.WriteLine(); CreateInsertMethod(table, streamWriter); CreateUpdateMethod(table, streamWriter); CreateSelectMethod(table, streamWriter); CreateSelectAllMethod(table, streamWriter); CreateSelectAllByMethods(table, storedProcedurePrefix, streamWriter); #endregion streamWriter.WriteLine(); streamWriter.WriteLine("\t\t#endregion"); // Close out the class and namespace streamWriter.WriteLine("\t\t}"); streamWriter.WriteLine("}"); } }
/// <summary> /// Retrieves the column, primary key, and foreign key information for the specified table. /// </summary> /// <param name="connection">The SqlConnection to be used when querying for the table information.</param> /// <param name="table">The table instance that information should be retrieved for.</param> internal static void QueryTable(SqlConnection connection, Table table) { // Get a list of the entities in the database DataTable dataTable = new DataTable(); SqlDataAdapter dataAdapter = new SqlDataAdapter(AppUtility.GetColumnQuery(table.Name), connection); dataAdapter.Fill(dataTable); foreach (DataRow columnRow in dataTable.Rows) { Column column = new Column(); column.Name = columnRow["COLUMN_NAME"].ToString(); column.Type = columnRow["DATA_TYPE"].ToString(); column.Precision = columnRow["NUMERIC_PRECISION"].ToString(); column.Scale = columnRow["NUMERIC_SCALE"].ToString(); // Determine the column's length if (columnRow["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value) { column.Length = columnRow["CHARACTER_MAXIMUM_LENGTH"].ToString(); } else { column.Length = columnRow["COLUMN_LENGTH"].ToString(); } // Is the column a RowGuidCol column? if (columnRow["IS_ROWGUIDCOL"].ToString() == "1") { column.IsRowGuidCol = true; } // Is the column an Identity column? if (columnRow["IS_IDENTITY"].ToString() == "1") { column.IsIdentity = true; } // Is columnRow column a computed column? if (columnRow["IS_COMPUTED"].ToString() == "1") { column.IsComputed = true; } table.Columns.Add(column); } // Get the list of primary keys DataTable primaryKeyTable = AppUtility.GetPrimaryKeyList(connection, table.Name); foreach (DataRow primaryKeyRow in primaryKeyTable.Rows) { string primaryKeyName = primaryKeyRow["COLUMN_NAME"].ToString(); foreach (Column column in table.Columns) { if (column.Name == primaryKeyName) { table.PrimaryKeys.Add(column); break; } } } // Get the list of foreign keys DataTable foreignKeyTable = AppUtility.GetForeignKeyList(connection, table.Name); foreach (DataRow foreignKeyRow in foreignKeyTable.Rows) { string name = foreignKeyRow["FK_NAME"].ToString(); string columnName = foreignKeyRow["FKCOLUMN_NAME"].ToString(); if (table.ForeignKeys.ContainsKey(name) == false) { table.ForeignKeys.Add(name, new List <Column>()); } List <Column> foreignKeys = table.ForeignKeys[name]; foreach (Column column in table.Columns) { if (column.Name == columnName) { foreignKeys.Add(column); break; } } } }
/// <summary> /// Creates an insert stored procedure SQL script for the specified table /// </summary> /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param> /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param> /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param> /// <param name="path">Path where the stored procedure script should be created.</param> /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param> internal static void CreateInsertStoredProcedure(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles) { // Create the stored procedure name string procedureName = storedProcedurePrefix + table.Name + "Insert"; string fileName; // Determine the file name to be used if (createMultipleFiles) { fileName = Path.Combine(path, procedureName + ".sql"); } else { fileName = Path.Combine(path, "StoredProcedures.sql"); } using (StreamWriter writer = new StreamWriter(fileName, true)) { // Create the seperator if (createMultipleFiles == false) { writer.WriteLine(); writer.WriteLine("/******************************************************************************"); writer.WriteLine("******************************************************************************/"); } // Create the drop statment writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)"); writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]"); writer.WriteLine("GO"); writer.WriteLine(); // Create the SQL for the stored procedure writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]"); writer.WriteLine("("); // Create the parameter list for (int i = 0; i < table.Columns.Count; i++) { Column column = table.Columns[i]; if (column.IsIdentity == false && column.IsRowGuidCol == false) { writer.Write("\t" + AppUtility.CreateParameterString(column, true)); if (i < (table.Columns.Count - 1)) { writer.Write(","); } writer.WriteLine(); } } writer.WriteLine(")"); writer.WriteLine(); writer.WriteLine("AS"); writer.WriteLine(); writer.WriteLine("SET NOCOUNT ON"); writer.WriteLine(); // Initialize all RowGuidCol columns foreach (Column column in table.Columns) { if (column.IsRowGuidCol) { writer.WriteLine("SET @" + column.Name + " = NEWID()"); writer.WriteLine(); break; } } writer.WriteLine("INSERT INTO [" + table.Name + "]"); writer.WriteLine("("); // Create the parameter list for (int i = 0; i < table.Columns.Count; i++) { Column column = table.Columns[i]; // Ignore any identity columns if (column.IsIdentity == false) { // Append the column name as a parameter of the insert statement if (i < (table.Columns.Count - 1)) { writer.WriteLine("\t[" + column.Name + "],"); } else { writer.WriteLine("\t[" + column.Name + "]"); } } } writer.WriteLine(")"); writer.WriteLine("VALUES"); writer.WriteLine("("); // Create the values list for (int i = 0; i < table.Columns.Count; i++) { Column column = table.Columns[i]; // Is the current column an identity column? if (column.IsIdentity == false) { // Append the necessary line breaks and commas if (i < (table.Columns.Count - 1)) { writer.WriteLine("\t@" + column.Name + ","); } else { writer.WriteLine("\t@" + column.Name); } } } writer.WriteLine(")"); // Should we include a line for returning the identity? foreach (Column column in table.Columns) { // Is the current column an identity column? if (column.IsIdentity) { writer.WriteLine(); writer.WriteLine("SELECT SCOPE_IDENTITY()"); break; } if (column.IsRowGuidCol) { writer.WriteLine(); writer.WriteLine("SELECT @" + column.Name); break; } } writer.WriteLine("GO"); // Create the grant statement, if a user was specified if (grantLoginName.Length > 0) { writer.WriteLine(); writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]"); writer.WriteLine("GO"); } } }
/// <summary> /// Creates 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"); } } } }
/// <summary> /// Creates an delete stored procedure SQL script for the specified table /// </summary> /// <param name="table">Instance of the Table class that represents the table this stored procedure will be created for.</param> /// <param name="grantLoginName">Name of the SQL Server user that should have execute rights on the stored procedure.</param> /// <param name="storedProcedurePrefix">Prefix to be appended to the name of the stored procedure.</param> /// <param name="path">Path where the stored procedure script should be created.</param> /// <param name="createMultipleFiles">Indicates the procedure(s) generated should be created in its own file.</param> internal static void CreateDeleteStoredProcedure(Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles) { if (table.PrimaryKeys.Count > 0) { // Create the stored procedure name string procedureName = storedProcedurePrefix + table.Name + "Delete"; string fileName; // Determine the file name to be used if (createMultipleFiles) { fileName = Path.Combine(path, procedureName + ".sql"); } else { fileName = Path.Combine(path, "StoredProcedures.sql"); } using (StreamWriter writer = new StreamWriter(fileName, true)) { // Create the seperator if (createMultipleFiles == false) { writer.WriteLine(); writer.WriteLine("/******************************************************************************"); writer.WriteLine("******************************************************************************/"); } // Create the drop statment writer.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)"); writer.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]"); writer.WriteLine("GO"); writer.WriteLine(); // Create the SQL for the stored procedure writer.WriteLine("CREATE PROCEDURE [dbo].[" + procedureName + "]"); writer.WriteLine("("); // Create the parameter list for (int i = 0; i < table.PrimaryKeys.Count; i++) { Column column = table.PrimaryKeys[i]; if (i < (table.PrimaryKeys.Count - 1)) { writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false) + ","); } else { writer.WriteLine("\t" + AppUtility.CreateParameterString(column, false)); } } writer.WriteLine(")"); writer.WriteLine(); writer.WriteLine("AS"); writer.WriteLine(); writer.WriteLine("SET NOCOUNT ON"); writer.WriteLine(); writer.WriteLine("DELETE FROM [" + table.Name + "]"); writer.Write("WHERE"); // Create the where clause for (int i = 0; i < table.PrimaryKeys.Count; i++) { Column column = table.PrimaryKeys[i]; if (i == 0) { writer.WriteLine(" [" + column.Name + "] = @" + column.Name); } else { writer.WriteLine("\tAND [" + column.Name + "] = @" + column.Name); } } writer.WriteLine("GO"); // Create the grant statement, if a user was specified if (grantLoginName.Length > 0) { writer.WriteLine(); writer.WriteLine("GRANT EXECUTE ON [dbo].[" + procedureName + "] TO [" + grantLoginName + "]"); writer.WriteLine("GO"); } } } }