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