/// <summary>
/// Creates an select all stored procedure SQL script for the specified table
/// </summary>
/// <param name="databaseName">The name of the database.</param>
/// <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>
public static void CreateSelectAllStoredProcedure(string databaseName, Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)
{
if (table.PrimaryKeys.Count > 0 && table.ForeignKeys.Count != table.Columns.Count)
{
// Create the stored procedure name
string procedureName = storedProcedurePrefix + table.Name + "SelectAll";
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 streamWriter = new StreamWriter(fileName, true))
{
// Create the "use" statement or the seperator
if (createMultipleFiles)
{
CreateUseDatabaseStatement(databaseName, streamWriter);
}
else
{
streamWriter.WriteLine();
streamWriter.WriteLine("/******************************************************************************");
streamWriter.WriteLine("******************************************************************************/");
}
// Create the drop statment
streamWriter.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and ObjectProperty(id, N'IsProcedure') = 1)");
streamWriter.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");
streamWriter.WriteLine("go");
streamWriter.WriteLine();
// Create the SQL for the stored procedure
streamWriter.WriteLine("create procedure [dbo].[" + procedureName + "]");
streamWriter.WriteLine();
streamWriter.WriteLine("as");
streamWriter.WriteLine();
streamWriter.WriteLine("set nocount on");
streamWriter.WriteLine();
streamWriter.Write("select");
// Create the list of columns
for (int i = 0; i < table.Columns.Count; i++)
{
Column column = table.Columns[i];
if (i == 0)
{
streamWriter.Write(" ");
}
else
{
streamWriter.Write("\t");
}
streamWriter.Write("[" + column.Name + "]");
if (i < (table.Columns.Count - 1))
{
streamWriter.Write(",");
}
streamWriter.WriteLine();
}
streamWriter.WriteLine("from [" + table.Name + "]");
streamWriter.WriteLine("go");
// Create the grant statement, if a user was specified
if (grantLoginName.Length > 0)
{
streamWriter.WriteLine();
streamWriter.WriteLine("grant execute on [dbo].[" + procedureName + "] to [" + grantLoginName + "]");
streamWriter.WriteLine("go");
}
}
}
}
/// <summary>
/// Creates an insert stored procedure SQL script for the specified table
/// </summary>
/// <param name="databaseName">The name of the database.</param>
/// <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>
public static void CreateInsertStoredProcedure(string databaseName, 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 streamWriter = new StreamWriter(fileName, true))
{
// Create the "use" statement or the seperator
if (createMultipleFiles)
{
CreateUseDatabaseStatement(databaseName, streamWriter);
}
else
{
streamWriter.WriteLine();
streamWriter.WriteLine("/******************************************************************************");
streamWriter.WriteLine("******************************************************************************/");
}
// Create the drop statment
streamWriter.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and ObjectProperty(id, N'IsProcedure') = 1)");
streamWriter.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");
streamWriter.WriteLine("go");
streamWriter.WriteLine();
// Create the SQL for the stored procedure
streamWriter.WriteLine("create procedure [dbo].[" + procedureName + "]");
streamWriter.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)
{
streamWriter.Write("\t" + Utility.CreateParameterString(column, true));
if (i < (table.Columns.Count - 1))
{
streamWriter.Write(",");
}
streamWriter.WriteLine();
}
}
streamWriter.WriteLine(")");
streamWriter.WriteLine();
streamWriter.WriteLine("as");
streamWriter.WriteLine();
streamWriter.WriteLine("set nocount on");
streamWriter.WriteLine();
// Initialize all RowGuidCol columns
foreach (Column column in table.Columns)
{
if (column.IsRowGuidCol)
{
streamWriter.WriteLine("set @" + column.Name + " = NewID()");
streamWriter.WriteLine();
break;
}
}
streamWriter.WriteLine("insert into [" + table.Name + "]");
streamWriter.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))
{
streamWriter.WriteLine("\t[" + column.Name + "],");
}
else
{
streamWriter.WriteLine("\t[" + column.Name + "]");
}
}
}
streamWriter.WriteLine(")");
streamWriter.WriteLine("values");
streamWriter.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))
{
streamWriter.WriteLine("\t@" + column.Name + ",");
}
else
{
streamWriter.WriteLine("\t@" + column.Name);
}
}
}
streamWriter.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)
{
streamWriter.WriteLine();
streamWriter.WriteLine("select scope_identity()");
break;
}
else if (column.IsRowGuidCol)
{
streamWriter.WriteLine();
streamWriter.WriteLine("Select @" + column.Name);
break;
}
}
streamWriter.WriteLine("go");
// Create the grant statement, if a user was specified
if (grantLoginName.Length > 0)
{
streamWriter.WriteLine();
streamWriter.WriteLine("grant execute on [dbo].[" + procedureName + "] to [" + grantLoginName + "]");
streamWriter.WriteLine("go");
}
}
}
/// <summary>
/// Creates one or more select stored procedures SQL script for the specified table and its foreign keys
/// </summary>
/// <param name="databaseName">The name of the database.</param>
/// <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>
public static void CreateSelectAllByStoredProcedures(string databaseName, 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("_" + Utility.FormatPascal(column.Name));
}
else
{
stringBuilder.Append(Utility.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 streamWriter = new StreamWriter(fileName, true))
{
// Create the "use" statement or the seperator
if (createMultipleFiles)
{
CreateUseDatabaseStatement(databaseName, streamWriter);
}
else
{
streamWriter.WriteLine();
streamWriter.WriteLine("/******************************************************************************");
streamWriter.WriteLine("******************************************************************************/");
}
// Create the drop statment
streamWriter.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and ObjectProperty(id, N'IsProcedure') = 1)");
streamWriter.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");
streamWriter.WriteLine("go");
streamWriter.WriteLine();
// Create the SQL for the stored procedure
streamWriter.WriteLine("create procedure [dbo].[" + procedureName + "]");
streamWriter.WriteLine("(");
// Create the parameter list
for (int i = 0; i < compositeKeyList.Count; i++)
{
Column column = compositeKeyList[i];
if (i < (compositeKeyList.Count - 1))
{
streamWriter.WriteLine("\t" + Utility.CreateParameterString(column, false) + ",");
}
else
{
streamWriter.WriteLine("\t" + Utility.CreateParameterString(column, false));
}
}
streamWriter.WriteLine(")");
streamWriter.WriteLine();
streamWriter.WriteLine("as");
streamWriter.WriteLine();
streamWriter.WriteLine("set nocount on");
streamWriter.WriteLine();
streamWriter.Write("select");
// Create the list of columns
for (int i = 0; i < table.Columns.Count; i++)
{
Column column = table.Columns[i];
if (i == 0)
{
streamWriter.Write(" ");
}
else
{
streamWriter.Write("\t");
}
streamWriter.Write("[" + column.Name + "]");
if (i < (table.Columns.Count - 1))
{
streamWriter.Write(",");
}
streamWriter.WriteLine();
}
streamWriter.WriteLine("from [" + table.Name + "]");
streamWriter.Write("where");
// Create the where clause
for (int i = 0; i < compositeKeyList.Count; i++)
{
Column column = compositeKeyList[i];
if (i == 0)
{
streamWriter.WriteLine(" [" + column.Name + "] = @" + column.Name);
}
else
{
streamWriter.WriteLine("\tand [" + column.Name + "] = @" + column.Name);
}
}
streamWriter.WriteLine("go");
// Create the grant statement, if a user was specified
if (grantLoginName.Length > 0)
{
streamWriter.WriteLine();
streamWriter.WriteLine("grant execute on [dbo].[" + procedureName + "] to [" + grantLoginName + "]");
streamWriter.WriteLine("go");
}
}
}
}
/// <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>
private static void QueryTable(SqlConnection connection, Table table)
{
// Get a list of the entities in the database
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(Utility.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 = Utility.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 = Utility.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>
/// Generates the SQL and C# code for the specified database.
/// </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="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="projectName">The name of the project file to be generated.</param>
/// <param name="targetNamespace">The namespace that the generated C# classes should contained in.</param>
/// <param name="daoSuffix">The suffix to be applied to all generated DAO classes.</param>
/// <param name="dtoSuffix">The suffix to be applied to all generated DTO classes.</param>
public static void Generate(string outputDirectory, string connectionString, string grantLoginName, string storedProcedurePrefix, bool createMultipleFiles, string projectName, string targetNamespace, string daoSuffix, string dtoSuffix)
{
List<Table> tableList = new List<Table>();
string databaseName;
string sqlPath;
string csPath;
using (SqlConnection connection = new SqlConnection(connectionString))
{
databaseName = Utility.FormatPascal(connection.Database);
sqlPath = Path.Combine(outputDirectory, "SQL");
csPath = Path.Combine(outputDirectory, "CS");
connection.Open();
// Get a list of the entities in the database
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(Utility.GetTableQuery(connection.Database), connection);
dataAdapter.Fill(dataTable);
// Process each table
foreach (DataRow dataRow in dataTable.Rows)
{
Table table = new Table();
table.Name = (string) dataRow["TABLE_NAME"];
QueryTable(connection, table);
tableList.Add(table);
}
}
DatabaseCounted(null, new CountEventArgs(tableList.Count));
// Generate the necessary SQL and C# code for each table
int count = 0;
if (tableList.Count > 0)
{
// Create the necessary directories
Utility.CreateSubDirectory(sqlPath, true);
Utility.CreateSubDirectory(csPath, true);
Utility.CreateSubDirectory(Path.Combine(csPath, "Repositories"), true);
// Create the necessary "use [database]" statement
SqlGenerator.CreateUseDatabaseStatement(databaseName, sqlPath, createMultipleFiles);
// Create the necessary database logins
SqlGenerator.CreateUserQueries(databaseName, grantLoginName, sqlPath, createMultipleFiles);
// Create the CRUD stored procedures and data access code for each table
foreach (Table table in tableList)
{
SqlGenerator.CreateInsertStoredProcedure(databaseName, table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
SqlGenerator.CreateUpdateStoredProcedure(databaseName, table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
SqlGenerator.CreateDeleteStoredProcedure(databaseName, table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
SqlGenerator.CreateDeleteAllByStoredProcedures(databaseName, table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
SqlGenerator.CreateSelectStoredProcedure(databaseName, table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
SqlGenerator.CreateSelectAllStoredProcedure(databaseName, table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
SqlGenerator.CreateSelectAllByStoredProcedures(databaseName, table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
CsGenerator.CreateDataTransferClass(table, targetNamespace, dtoSuffix, csPath);
CsGenerator.CreateDataAccessClass(databaseName, table, targetNamespace, storedProcedurePrefix, daoSuffix, dtoSuffix, csPath);
count++;
TableCounted(null, new CountEventArgs(count));
}
CsGenerator.CreateSharpCore(csPath);
CsGenerator.CreateAssemblyInfo(csPath, databaseName, databaseName);
CsGenerator.CreateProjectFile(csPath, projectName, tableList, daoSuffix, dtoSuffix);
}
}
/// <summary>
/// Creates a string that represents the "delete 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 CreateDeleteAllByMethods(Table table, string storedProcedurePrefix, StreamWriter streamWriter)
{
// 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("DeleteAllBy");
for (int i = 0; i < compositeKeyList.Count; i++)
{
Column column = compositeKeyList[i];
if (i > 0)
{
stringBuilder.Append("_" + Utility.FormatPascal(column.Name));
}
else
{
stringBuilder.Append(Utility.FormatPascal(column.Name));
}
}
string methodName = stringBuilder.ToString();
string procedureName = storedProcedurePrefix + table.Name + methodName;
// Create the delete function based on keys
// Append the method header
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Deletes a record from the " + table.Name + " table by a foreign key.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.Write("\t\tpublic void " + methodName + "(");
for (int i = 0; i < compositeKeyList.Count; i++)
{
Column column = compositeKeyList[i];
streamWriter.Write(Utility.CreateMethodParameter(column));
if (i < (compositeKeyList.Count - 1))
{
streamWriter.Write(", ");
}
}
streamWriter.WriteLine(")");
streamWriter.WriteLine("\t\t{");
// Append the parameter declarations
streamWriter.WriteLine("\t\t\tSqlParameter[] parameters = new SqlParameter[]");
streamWriter.WriteLine("\t\t\t{");
for (int i = 0; i < compositeKeyList.Count; i++)
{
Column column = compositeKeyList[i];
streamWriter.Write("\t\t\t\tnew SqlParameter(\"@" + column.Name + "\", " + Utility.FormatCamel(column.Name) + ")");
if (i < (compositeKeyList.Count - 1))
{
streamWriter.Write(",");
}
streamWriter.WriteLine();
}
streamWriter.WriteLine("\t\t\t};");
streamWriter.WriteLine();
// Append the stored procedure execution
streamWriter.WriteLine("\t\t\tSqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, \"" + procedureName + "\", parameters);");
// Append the method footer
streamWriter.WriteLine("\t\t}");
streamWriter.WriteLine();
}
}
/// <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="storedProcedurePrefix">The prefix that is used on the stored procedure that this method will call.</param>
/// <param name="dtoSuffix">The suffix to append to the name of each data transfer class.</param>
/// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
private static void CreateUpdateMethod(Table table, string storedProcedurePrefix, string dtoSuffix, StreamWriter streamWriter)
{
if (table.PrimaryKeys.Count > 0 && table.Columns.Count != table.PrimaryKeys.Count && table.Columns.Count != table.ForeignKeys.Count)
{
string className = Utility.FormatClassName(table.Name) + dtoSuffix;
string variableName = Utility.FormatVariableName(table.Name);
// Append the method header
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Updates a record in the " + table.Name + " table.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.WriteLine("\t\tpublic void Update(" + className + " " + variableName + ")");
streamWriter.WriteLine("\t\t{");
// Append validation for the parameter
streamWriter.WriteLine("\t\t\tValidationUtility.ValidateArgument(\"" + variableName + "\", " + variableName + ");");
streamWriter.WriteLine();
// Append the parameter declarations
streamWriter.WriteLine("\t\t\tSqlParameter[] parameters = new SqlParameter[]");
streamWriter.WriteLine("\t\t\t{");
for (int i = 0; i < table.Columns.Count; i++)
{
Column column = table.Columns[i];
streamWriter.Write("\t\t\t\t" + Utility.CreateSqlParameter(table, column));
if (i < (table.Columns.Count - 1))
{
streamWriter.Write(",");
}
streamWriter.WriteLine();
}
streamWriter.WriteLine("\t\t\t};");
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t\tSqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, \"" + table.Name + "Update\", parameters);");
// Append the method footer
streamWriter.WriteLine("\t\t}");
streamWriter.WriteLine();
}
}
/// <summary>
/// Creates a string that represents the select JSON 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="dtoSuffix">The suffix to append to the name of each data transfer class.</param>
/// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
private static void CreateSelectAllJsonMethod(Table table, string storedProcedurePrefix, string dtoSuffix, StreamWriter streamWriter)
{
if (table.Columns.Count != table.PrimaryKeys.Count && table.Columns.Count != table.ForeignKeys.Count)
{
string className = Utility.FormatClassName(table.Name) + dtoSuffix;
string dtoVariableName = Utility.FormatCamel(className);
// 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\tpublic string SelectAllJson()");
streamWriter.WriteLine("\t\t{");
// Append the stored procedure execution
streamWriter.WriteLine("\t\t\treturn SqlClientUtility.ExecuteJson(connectionStringName, CommandType.StoredProcedure, \"" + table.Name + "SelectAll\");");
// Append the method footer
streamWriter.WriteLine("\t\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="storedProcedurePrefix">The prefix that is used on the stored procedure that this method will call.</param>
/// <param name="dtoSuffix">The suffix to append to the name of each data transfer class.</param>
/// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
private static void CreateSelectAllMethod(Table table, string storedProcedurePrefix, string dtoSuffix, StreamWriter streamWriter)
{
if (table.Columns.Count != table.PrimaryKeys.Count && table.Columns.Count != table.ForeignKeys.Count)
{
string className = Utility.FormatClassName(table.Name) + dtoSuffix;
string dtoVariableName = Utility.FormatCamel(className);
// 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\tpublic List<" + className + "> SelectAll()");
streamWriter.WriteLine("\t\t{");
// Append the stored procedure execution
streamWriter.WriteLine("\t\t\tusing (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, \"" + table.Name + "SelectAll\"))");
streamWriter.WriteLine("\t\t\t{");
streamWriter.WriteLine("\t\t\t\tList<" + className + "> " + dtoVariableName + "List = new List<" + className + ">();");
streamWriter.WriteLine("\t\t\t\twhile (dataReader.Read())");
streamWriter.WriteLine("\t\t\t\t{");
streamWriter.WriteLine("\t\t\t\t\t" + className + " " + dtoVariableName + " = MapDataReader(dataReader);");
streamWriter.WriteLine("\t\t\t\t\t" + dtoVariableName + "List.Add(" + dtoVariableName + ");");
streamWriter.WriteLine("\t\t\t\t}");
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t\t\treturn " + dtoVariableName + "List;");
streamWriter.WriteLine("\t\t\t}");
// Append the method footer
streamWriter.WriteLine("\t\t}");
streamWriter.WriteLine();
}
}
/// <summary>
/// Creates a string that represents the "map" functionality of the data access class.
/// </summary>
/// <param name="table">The Table instance that this method will be created for.</param>
/// <param name="dtoSuffix">The suffix to append to the name of the data transfer class.</param>
/// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
private static void CreateMapMethod(Table table, string dtoSuffix, StreamWriter streamWriter)
{
string className = Utility.FormatClassName(table.Name) + dtoSuffix;
string variableName = Utility.FormatVariableName(className);
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Creates a new instance of the " + className + " class and populates it with data from the specified SqlDataReader.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.WriteLine("\t\tprivate " + className + " MapDataReader(SqlDataReader dataReader)");
streamWriter.WriteLine("\t\t{");
streamWriter.WriteLine("\t\t\t" + className + " " + variableName + " = new " + className + "();");
foreach (Column column in table.Columns)
{
string columnNamePascal = Utility.FormatPascal(column.Name);
streamWriter.WriteLine("\t\t\t" + variableName + "." + columnNamePascal + " = dataReader." + Utility.GetGetMethod(column) + "(\"" + column.Name + "\", " + Utility.GetDefaultValue(column) + ");");
}
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t\treturn " + variableName + ";");
streamWriter.WriteLine("\t\t}");
}
/// <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="dtoSuffix">The suffix to append to the name of each data transfer class.</param>
/// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
private static void CreateSelectAllByMethods(Table table, string storedProcedurePrefix, string dtoSuffix, StreamWriter streamWriter)
{
string className = Utility.FormatClassName(table.Name) + dtoSuffix;
string dtoVariableName = Utility.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 (int i = 0; i < compositeKeyList.Count; i++)
{
Column column = compositeKeyList[i];
if (i > 0)
{
stringBuilder.Append("_" + Utility.FormatPascal(column.Name));
}
else
{
stringBuilder.Append(Utility.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(Utility.CreateMethodParameter(column));
if (i < (compositeKeyList.Count - 1))
{
streamWriter.Write(", ");
}
}
streamWriter.WriteLine(")");
streamWriter.WriteLine("\t\t{");
// Append the parameter declarations
streamWriter.WriteLine("\t\t\tSqlParameter[] parameters = new SqlParameter[]");
streamWriter.WriteLine("\t\t\t{");
for (int i = 0; i < compositeKeyList.Count; i++)
{
Column column = compositeKeyList[i];
streamWriter.Write("\t\t\t\tnew SqlParameter(\"@" + column.Name + "\", " + Utility.FormatCamel(column.Name) + ")");
if (i < (compositeKeyList.Count - 1))
{
streamWriter.Write(",");
}
streamWriter.WriteLine();
}
streamWriter.WriteLine("\t\t\t};");
streamWriter.WriteLine();
// Append the stored procedure execution
streamWriter.WriteLine("\t\t\tusing (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, \"" + procedureName + "\", parameters))");
streamWriter.WriteLine("\t\t\t{");
streamWriter.WriteLine("\t\t\t\tList<" + className + "> " + dtoVariableName + "List = new List<" + className + ">();");
streamWriter.WriteLine("\t\t\t\twhile (dataReader.Read())");
streamWriter.WriteLine("\t\t\t\t{");
streamWriter.WriteLine("\t\t\t\t\t" + className + " " + dtoVariableName + " = MapDataReader(dataReader);");
streamWriter.WriteLine("\t\t\t\t\t" + dtoVariableName + "List.Add(" + dtoVariableName + ");");
streamWriter.WriteLine("\t\t\t\t}");
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t\t\treturn " + dtoVariableName + "List;");
streamWriter.WriteLine("\t\t\t}");
// Append the method footer
streamWriter.WriteLine("\t\t}");
streamWriter.WriteLine();
}
}
/// <summary>
/// Creates a C# data access class for all of the table's stored procedures.
/// </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="daoSuffix">The suffix to be appended to the data access class.</param>
/// <param name="dtoSuffix">The suffix to append to the name of each data transfer class.</param>
/// <param name="path">Path where the class should be created.</param>
public static void CreateDataAccessClass(string databaseName, Table table, string targetNamespace, string storedProcedurePrefix, string daoSuffix, string dtoSuffix, string path)
{
string className = Utility.FormatClassName(table.Name) + daoSuffix;
path = Path.Combine(path, "Repositories");
using (StreamWriter streamWriter = new StreamWriter(Path.Combine(path, className + ".cs")))
{
// Create the header for the class
streamWriter.WriteLine("using System;");
streamWriter.WriteLine("using System.Collections.Generic;");
streamWriter.WriteLine("using System.Data;");
streamWriter.WriteLine("using System.Data.SqlClient;");
streamWriter.WriteLine("using SharpCore.Data;");
streamWriter.WriteLine("using SharpCore.Extensions;");
streamWriter.WriteLine("using SharpCore.Utilities;");
streamWriter.WriteLine();
streamWriter.WriteLine("namespace " + targetNamespace + "." + daoSuffix);
streamWriter.WriteLine("{");
streamWriter.WriteLine("\tpublic class " + className);
streamWriter.WriteLine("\t{");
// Append the fields
streamWriter.WriteLine("\t\t#region Fields");
streamWriter.WriteLine();
streamWriter.WriteLine("\t\tprivate string connectionStringName;");
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t#endregion");
streamWriter.WriteLine();
// Append the constructors
streamWriter.WriteLine("\t\t#region Constructors");
streamWriter.WriteLine();
streamWriter.WriteLine("\t\tpublic " + className + "(string connectionStringName)");
streamWriter.WriteLine("\t\t{");
streamWriter.WriteLine("\t\t\tValidationUtility.ValidateArgument(\"connectionStringName\", connectionStringName);");
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t\tthis.connectionStringName = connectionStringName;");
streamWriter.WriteLine("\t\t}");
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t#endregion");
streamWriter.WriteLine();
// Append the access methods
streamWriter.WriteLine("\t\t#region Methods");
streamWriter.WriteLine();
CreateInsertMethod(table, storedProcedurePrefix, dtoSuffix, streamWriter);
CreateUpdateMethod(table, storedProcedurePrefix, dtoSuffix, streamWriter);
CreateDeleteMethod(table, storedProcedurePrefix, streamWriter);
CreateDeleteAllByMethods(table, storedProcedurePrefix, streamWriter);
CreateSelectMethod(table, storedProcedurePrefix, dtoSuffix, streamWriter);
CreateSelectJsonMethod(table, storedProcedurePrefix, dtoSuffix, streamWriter);
CreateSelectAllMethod(table, storedProcedurePrefix, dtoSuffix, streamWriter);
CreateSelectAllJsonMethod(table, storedProcedurePrefix, dtoSuffix, streamWriter);
CreateSelectAllByMethods(table, storedProcedurePrefix, dtoSuffix, streamWriter);
CreateSelectAllByJsonMethods(table, storedProcedurePrefix, dtoSuffix, streamWriter);
CreateMapMethod(table, dtoSuffix, streamWriter);
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t#endregion");
// Close out the class and namespace
streamWriter.WriteLine("\t}");
streamWriter.WriteLine("}");
}
}
/// <summary>
/// Creates a string that represents the insert 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="dtoSuffix">The suffix to append to the name of each data transfer class.</param>
/// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
private static void CreateInsertMethod(Table table, string storedProcedurePrefix, string dtoSuffix, StreamWriter streamWriter)
{
string className = Utility.FormatClassName(table.Name) + dtoSuffix;
string variableName = Utility.FormatVariableName(table.Name);
// Append the method header
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Saves a record to the " + table.Name + " table.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.WriteLine("\t\tpublic void Insert(" + className + " " + variableName + ")");
streamWriter.WriteLine("\t\t{");
// Append validation for the parameter
streamWriter.WriteLine("\t\t\tValidationUtility.ValidateArgument(\"" + variableName + "\", " + variableName + ");");
streamWriter.WriteLine();
// Append the parameter declarations
streamWriter.WriteLine("\t\t\tSqlParameter[] parameters = new SqlParameter[]");
streamWriter.WriteLine("\t\t\t{");
for (int i = 0; i < table.Columns.Count; i++)
{
Column column = table.Columns[i];
if (column.IsIdentity == false && column.IsRowGuidCol == false)
{
streamWriter.Write("\t\t\t\t" + Utility.CreateSqlParameter(table, column));
if (i < (table.Columns.Count - 1))
{
streamWriter.Write(",");
}
streamWriter.WriteLine();
}
}
streamWriter.WriteLine("\t\t\t};");
streamWriter.WriteLine();
bool hasReturnValue = false;
foreach (Column column in table.Columns)
{
if (column.IsIdentity || column.IsRowGuidCol)
{
if (column.IsIdentity && column.Length == "4")
{
streamWriter.WriteLine("\t\t\t" + variableName + "." + Utility.FormatPascal(column.Name) + " = (int) SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, \"" + table.Name + "Insert\", parameters);");
hasReturnValue = true;
}
else if (column.IsIdentity && column.Length == "8")
{
streamWriter.WriteLine("\t\t\t" + variableName + "." + Utility.FormatPascal(column.Name) + " = (long) SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, \"" + table.Name + "Insert\", parameters);");
hasReturnValue = true;
}
else if (column.IsRowGuidCol)
{
streamWriter.WriteLine("\t\t\t" + variableName + "." + Utility.FormatPascal(column.Name) + " = (Guid) SqlClientUtility.ExecuteScalar(connectionStringName, CommandType.StoredProcedure, \"" + table.Name + "Insert\", parameters);");
hasReturnValue = true;
}
}
}
if (hasReturnValue == false)
{
streamWriter.WriteLine("\t\t\tSqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, \"" + table.Name + "Insert\", parameters);");
}
// Append the method footer
streamWriter.WriteLine("\t\t}");
streamWriter.WriteLine();
}
/// <summary>
/// Creates a string that represents the delete 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 CreateDeleteMethod(Table table, string storedProcedurePrefix, StreamWriter streamWriter)
{
if (table.PrimaryKeys.Count > 0)
{
// Append the method header
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Deletes a record from the " + table.Name + " table by its primary key.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.Write("\t\tpublic void Delete(");
for (int i = 0; i < table.PrimaryKeys.Count; i++)
{
Column column = table.PrimaryKeys[i];
streamWriter.Write(Utility.CreateMethodParameter(column));
if (i < (table.PrimaryKeys.Count - 1))
{
streamWriter.Write(", ");
}
}
streamWriter.WriteLine(")");
streamWriter.WriteLine("\t\t{");
// Append the parameter declarations
streamWriter.WriteLine("\t\t\tSqlParameter[] parameters = new SqlParameter[]");
streamWriter.WriteLine("\t\t\t{");
for (int i = 0; i < table.PrimaryKeys.Count; i++)
{
Column column = table.PrimaryKeys[i];
streamWriter.Write("\t\t\t\tnew SqlParameter(\"@" + column.Name + "\", " + Utility.FormatCamel(column.Name) + ")");
if (i < (table.PrimaryKeys.Count - 1))
{
streamWriter.Write(",");
}
streamWriter.WriteLine();
}
streamWriter.WriteLine("\t\t\t};");
streamWriter.WriteLine();
// Append the stored procedure execution
streamWriter.WriteLine("\t\t\tSqlClientUtility.ExecuteNonQuery(connectionStringName, CommandType.StoredProcedure, \"" + table.Name + "Delete\", parameters);");
// Append the method footer
streamWriter.WriteLine("\t\t}");
streamWriter.WriteLine();
}
}
/// <summary>
/// Creates an update stored procedure SQL script for the specified table
/// </summary>
/// <param name="databaseName">The name of the database.</param>
/// <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>
public static void CreateUpdateStoredProcedure(string databaseName, Table table, string grantLoginName, string storedProcedurePrefix, string path, bool createMultipleFiles)
{
if (table.PrimaryKeys.Count > 0 && table.Columns.Count != table.PrimaryKeys.Count && table.Columns.Count != table.ForeignKeys.Count)
{
// Create the stored procedure name
string procedureName = storedProcedurePrefix + table.Name + "Update";
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 streamWriter = new StreamWriter(fileName, true))
{
// Create the "use" statement or the seperator
if (createMultipleFiles)
{
CreateUseDatabaseStatement(databaseName, streamWriter);
}
else
{
streamWriter.WriteLine();
streamWriter.WriteLine("/******************************************************************************");
streamWriter.WriteLine("******************************************************************************/");
}
// Create the drop statment
streamWriter.WriteLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + procedureName + "]') and ObjectProperty(id, N'IsProcedure') = 1)");
streamWriter.WriteLine("\tdrop procedure [dbo].[" + procedureName + "]");
streamWriter.WriteLine("go");
streamWriter.WriteLine();
// Create the SQL for the stored procedure
streamWriter.WriteLine("create procedure [dbo].[" + procedureName + "]");
streamWriter.WriteLine("(");
// Create the parameter list
for (int i = 0; i < table.Columns.Count; i++)
{
Column column = table.Columns[i];
if (i == 0)
{
}
if (i < (table.Columns.Count - 1))
{
streamWriter.WriteLine("\t" + Utility.CreateParameterString(column, false) + ",");
}
else
{
streamWriter.WriteLine("\t" + Utility.CreateParameterString(column, false));
}
}
streamWriter.WriteLine(")");
streamWriter.WriteLine();
streamWriter.WriteLine("as");
streamWriter.WriteLine();
streamWriter.WriteLine("set nocount on");
streamWriter.WriteLine();
streamWriter.WriteLine("update [" + table.Name + "]");
streamWriter.Write("set");
// Create the set statement
bool firstLine = true;
for (int i = 0; i < table.Columns.Count; i++)
{
Column column = (Column) table.Columns[i];
// Ignore Identity and RowGuidCol columns
if (table.PrimaryKeys.Contains(column) == false)
{
if (firstLine)
{
streamWriter.Write(" ");
firstLine = false;
}
else
{
streamWriter.Write("\t");
}
streamWriter.Write("[" + column.Name + "] = @" + column.Name);
if (i < (table.Columns.Count - 1))
{
streamWriter.Write(",");
}
streamWriter.WriteLine();
}
}
streamWriter.Write("where");
// Create the where clause
for (int i = 0; i < table.PrimaryKeys.Count; i++)
{
Column column = table.PrimaryKeys[i];
if (i == 0)
{
streamWriter.Write(" [" + column.Name + "] = @" + column.Name);
}
else
{
streamWriter.Write("\tand [" + column.Name + "] = @" + column.Name);
}
}
streamWriter.WriteLine();
streamWriter.WriteLine("go");
// Create the grant statement, if a user was specified
if (grantLoginName.Length > 0)
{
streamWriter.WriteLine();
streamWriter.WriteLine("grant execute on [dbo].[" + procedureName + "] to [" + grantLoginName + "]");
streamWriter.WriteLine("go");
}
}
}
}
/// <summary>
/// Creates a string that represents the select by primary key 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="dtoSuffix">The suffix to append to the name of each data transfer class.</param>
/// <param name="streamWriter">The StreamWriter instance that will be used to create the method.</param>
private static void CreateSelectMethod(Table table, string storedProcedurePrefix, string dtoSuffix, StreamWriter streamWriter)
{
if (table.PrimaryKeys.Count > 0 && table.Columns.Count != table.ForeignKeys.Count)
{
string className = Utility.FormatClassName(table.Name) + dtoSuffix;
// Append the method header
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Selects a single record from the " + table.Name + " table.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.Write("\t\tpublic " + className + " Select(");
for (int i = 0; i < table.PrimaryKeys.Count; i++)
{
Column column = table.PrimaryKeys[i];
streamWriter.Write(Utility.CreateMethodParameter(column));
if (i < (table.PrimaryKeys.Count - 1))
{
streamWriter.Write(", ");
}
}
streamWriter.WriteLine(")");
streamWriter.WriteLine("\t\t{");
// Append the parameter declarations
streamWriter.WriteLine("\t\t\tSqlParameter[] parameters = new SqlParameter[]");
streamWriter.WriteLine("\t\t\t{");
for (int i = 0; i < table.PrimaryKeys.Count; i++)
{
Column column = table.PrimaryKeys[i];
streamWriter.Write("\t\t\t\tnew SqlParameter(\"@" + column.Name + "\", " + Utility.FormatCamel(column.Name) + ")");
if (i < (table.PrimaryKeys.Count - 1))
{
streamWriter.Write(",");
}
streamWriter.WriteLine();
}
streamWriter.WriteLine("\t\t\t};");
streamWriter.WriteLine();
// Append the stored procedure execution
streamWriter.WriteLine("\t\t\tusing (SqlDataReader dataReader = SqlClientUtility.ExecuteReader(connectionStringName, CommandType.StoredProcedure, \"" + table.Name + "Select\", parameters))");
streamWriter.WriteLine("\t\t\t{");
streamWriter.WriteLine("\t\t\t\tif (dataReader.Read())");
streamWriter.WriteLine("\t\t\t\t{");
streamWriter.WriteLine("\t\t\t\t\treturn MapDataReader(dataReader);");
streamWriter.WriteLine("\t\t\t\t}");
streamWriter.WriteLine("\t\t\t\telse");
streamWriter.WriteLine("\t\t\t\t{");
streamWriter.WriteLine("\t\t\t\t\treturn null;");
streamWriter.WriteLine("\t\t\t\t}");
streamWriter.WriteLine("\t\t\t}");
// Append the method footer
streamWriter.WriteLine("\t\t}");
streamWriter.WriteLine();
}
}
/// <summary>
/// Creates a string for a SqlParameter representing the specified column.
/// </summary>
/// <param name="column">Object that stores the information for the column the parameter represents.</param>
/// <returns>String containing SqlParameter information of the specified column for a method call.</returns>
public static string CreateSqlParameter(Table table, Column column)
{
string className = Utility.FormatClassName(table.Name);
string variableName = Utility.FormatVariableName(className);
if (column.Type == "xml")
{
return "new SqlParameter(\"@" + column.Name + "\", SqlDbType.Xml) { Value = " + variableName + "." + FormatPascal(column.Name) + " }";
}
else
{
return "new SqlParameter(\"@" + column.Name + "\", " + variableName + "." + FormatPascal(column.Name) + ")";
}
}
/// <summary>
/// Creates a C# class for all of the table's stored procedures.
/// </summary>
/// <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="daoSuffix">The suffix to be appended to the data access class.</param>
/// <param name="path">Path where the class should be created.</param>
public static void CreateDataTransferClass(Table table, string targetNamespace, string dtoSuffix, string path)
{
string className = Utility.FormatClassName(table.Name) + dtoSuffix;
using (StreamWriter streamWriter = new StreamWriter(Path.Combine(path, className + ".cs")))
{
// 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{");
// Create an explicit public constructor
streamWriter.WriteLine("\t\t#region Constructors");
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Initializes a new instance of the " + className + " class.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.WriteLine("\t\tpublic " + className + "()");
streamWriter.WriteLine("\t\t{");
streamWriter.WriteLine("\t\t}");
streamWriter.WriteLine();
// Create the "partial" constructor
int parameterCount = 0;
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Initializes a new instance of the " + className + " class.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.Write("\t\tpublic " + className + "(");
for (int i = 0; i < table.Columns.Count; i++)
{
Column column = table.Columns[i];
if (column.IsIdentity == false && column.IsRowGuidCol == false)
{
streamWriter.Write(Utility.CreateMethodParameter(column));
if (i < (table.Columns.Count - 1))
{
streamWriter.Write(", ");
}
parameterCount++;
}
}
streamWriter.WriteLine(")");
streamWriter.WriteLine("\t\t{");
foreach (Column column in table.Columns)
{
if (column.IsIdentity == false && column.IsRowGuidCol == false)
{
streamWriter.WriteLine("\t\t\tthis." + Utility.FormatPascal(column.Name) + " = " + Utility.FormatCamel(column.Name) + ";");
}
}
streamWriter.WriteLine("\t\t}");
// Create the "full featured" constructor, if we haven't already
if (parameterCount < table.Columns.Count)
{
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Initializes a new instance of the " + className + " class.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.Write("\t\tpublic " + className + "(");
for (int i = 0; i < table.Columns.Count; i++)
{
Column column = table.Columns[i];
streamWriter.Write(Utility.CreateMethodParameter(column));
if (i < (table.Columns.Count - 1))
{
streamWriter.Write(", ");
}
}
streamWriter.WriteLine(")");
streamWriter.WriteLine("\t\t{");
foreach (Column column in table.Columns)
{
streamWriter.WriteLine("\t\t\tthis." + Utility.FormatCamel(column.Name) + " = " + Utility.FormatCamel(column.Name) + ";");
}
streamWriter.WriteLine("\t\t}");
}
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t#endregion");
streamWriter.WriteLine();
// Append the public properties
streamWriter.WriteLine("\t\t#region Properties");
for (int i = 0; i < table.Columns.Count; i++)
{
Column column = table.Columns[i];
string parameter = Utility.CreateMethodParameter(column);
string type = parameter.Split(' ')[0];
string name = parameter.Split(' ')[1];
streamWriter.WriteLine("\t\t/// <summary>");
streamWriter.WriteLine("\t\t/// Gets or sets the " + Utility.FormatPascal(name) + " value.");
streamWriter.WriteLine("\t\t/// </summary>");
streamWriter.WriteLine("\t\tpublic " + type + " " + Utility.FormatPascal(name) + " { get; set; }");
if (i < (table.Columns.Count - 1))
{
streamWriter.WriteLine();
}
}
streamWriter.WriteLine();
streamWriter.WriteLine("\t\t#endregion");
// Close out the class and namespace
streamWriter.WriteLine("\t}");
streamWriter.WriteLine("}");
}
}