/// <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 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 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"); } } } }