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