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