public override GenerationArtifact RenderTemplate(DatabaseTable table, CodeGenSettings settings)
{
//retrieve the embedded template
string contents = Core.ResourceFileHelper.ConvertStreamResourceToUTF8String(typeof(Model), "Chucksoft.Templates.Templates.StoredProcedure.template");
_content = contents.Trim();
//Check for expected content, if not found, badness has happen.
if (string.IsNullOrEmpty(contents))
{
throw new ContentNotFound("Can't find embeddedResource \"Chucksoft.Templates.Templates.Enity.template\"");
}
List<string> prodecures = GetProcedures(table, settings);
StringBuilder contentBuilder = new StringBuilder();
//append the procedures together and seperate them with a seperator
foreach (string prodecure in prodecures)
{
contentBuilder.AppendLine(prodecure);
contentBuilder.AppendLine(Environment.NewLine + "-----------------------------------------------------------" + Environment.NewLine);
}
//set the Generator object and return to calling method.
GenerationArtifact artifact = new GenerationArtifact { FileName = string.Format("{0}.sql", table.Name), Content = contentBuilder.ToString() };
return artifact;
}
private void generateButton_Click(object sender, EventArgs e)
{
List<DatabaseTable> databaseTables = new List<DatabaseTable>();
//Retrieve Tables in generation listbox
for(int index = 0; index < generationTablesListBox.Items.Count; index++ )
{
DatabaseTable table = new DatabaseTable();
table.Name = Convert.ToString(generationTablesListBox.Items[index]);
databaseTables.Add(table);
}
//Retrieve the columns in the selected tables
DatabaseHelper helper = new DatabaseHelper(User.Default.DatabaseConnectionString);
foreach (DatabaseTable table in databaseTables)
{
table.Columns = helper.RetrieveColumns(databasesComboBox.Text, table.Name);
}
//populate Settings class
CodeGenSettings settings = new CodeGenSettings();
settings.SolutionNamespace = User.Default.SolutionNamespace;
settings.CodeGenerationDirectory = User.Default.CodeGenerationDirectory;
settings.DatabaseConnectionString = User.Default.DatabaseConnectionString;
settings.CompiledTemplateLocation = User.Default.CompiledTemplateLocation;
settings.ReturnIdentityFromInserts = returnIdentityFromInserts.Checked;
settings.UseDynamicParameters = useDynamicParameters.Checked;
if (!string.IsNullOrEmpty(settings.CodeGenerationDirectory))
{
//Check that items have been added to the Generation Queue
if (generationTablesListBox.Items.Count > 0)
{
try
{
Cursor = Cursors.WaitCursor;
SolutionLogic.Generate(settings, databaseTables);
MessageBox.Show(this, "Generation Completed!", "Generation Completed!", MessageBoxButtons.OK,MessageBoxIcon.Information);
}
catch(Exception ex)
{
MessageBox.Show(this, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
Cursor = Cursors.Arrow;
}
}
else
{
MessageBox.Show(this, "Add tables to the generation queue", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else
{
MessageBox.Show(this, "A location to save the generated files is required. Please use the settings dialog to set the code generation path.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
/// <summary>
/// Renders the template.
/// </summary>
/// <param name="table">The table.</param>
/// <param name="settings">The settings.</param>
/// <returns></returns>
public override string RenderTemplate(DatabaseTable table, CodeGenSettings settings)
{
IGenerateMethods generateMethods = new DynamicDbParametersResources();
if(!settings.UseDynamicParameters)
{
generateMethods = new StaticDbParametersResources();
}
return generateMethods.Render(table, settings);
}
private void GenerateDeleteMethods(DatabaseTable table)
{
DatabaseColumn primaryColumn = table.Columns.Where(c => c.IsPrimaryKey).SingleOrDefault();
if (primaryColumn != null)
{
StringBuilder builder = new StringBuilder();
builder.Append(string.Format("\t\t\treturn _database.NonQuery(\"{0}_Delete\", new {{ {1} }});", table.Name, table.Name.ToLower() + "." + primaryColumn.Name));
_content = _content.Replace("<%[DeleteMethod]%>", builder.ToString());
}
}
public override string RenderTemplate(DatabaseTable table, CodeGenSettings settings)
{
string contents = Core.ResourceFileHelper.ConvertStreamResourceToUTF8String(typeof(Model), "Chucksoft.Templates.Templates.Enity.template");
//Check for expected content, if not found, badness has happen.
if (string.IsNullOrEmpty(contents))
{
throw new ContentNotFound("Can't find embeddedResource \"Chucksoft.Templates.Templates.Enity.template\"");
}
contents = contents.Replace("<%[Properties]%>", RenderProperties(table.Columns));
return contents;
}
private static string GetMakeParametersForOnlyPrimaryKeys(DatabaseTable table)
{
StringBuilder builder = new StringBuilder();
for (int index = 0; index < table.Columns.Count; index++)
{
bool isLastItem = (index == table.Columns.Count - 1);
string columnName = "@" + table.Columns[index].Name;
if (table.Columns[index].IsPrimaryKey)
{
builder.AppendLine(string.Format("\t\t\t\t_database.MakeParameter(\"{0}\",{1}){2}", columnName, table.Name.ToLower() + "." + table.Columns[index].Name, (isLastItem ? string.Empty : ",")));
}
}
return builder.ToString();
}
private void GenerateInsertMethods(DatabaseTable table, CodeGenSettings settings)
{
StringBuilder builder = new StringBuilder();
if (settings.ReturnIdentityFromInserts)
{
builder.AppendLine(string.Format("\t\t\tList<DbParameter> parameters = database.GetParameters({0});", table.Name));
builder.AppendLine("\r\n \t\t\tparameters.SelectIdentity();");
}
if (!settings.ReturnIdentityFromInserts)
{
builder.Append(string.Format("\t\t\treturn _database.NonQuery(\"{0}_Insert\", {1});", table.Name, table.Name.ToLower()));
}
else
{
builder.Append(string.Format("\r\n \t\t\t_database.NonQuery(\"{0}_Insert\", parameters); \r\n \t\t\t return parameters.Identity<int>(); ", table.Name));
}
_content = _content.Replace("<%[InsertMethod]%>", builder.ToString());
}
/// <summary>
/// Builds the Insert Statment parmeters
/// </summary>
/// <param name="table">The table.</param>
/// <param name="generationPrimaryKey">if set to <c>true</c> [generation primary key].</param>
/// <param name="appendToTheEnd">The append to the end.</param>
/// <returns></returns>
private static string GenerateParameterList(DatabaseTable table, bool generationPrimaryKey, string appendToTheEnd)
{
StringBuilder builder = new StringBuilder();
builder.AppendLine("( ");
//iterate through columns
for (int index = 0; index < table.Columns.Count; index++)
{
bool isLastColumn = (index == table.Columns.Count - 1);
if (generationPrimaryKey)
{
builder.AppendLine(GenrateParameterColumn(table.Columns[index], isLastColumn));
}
else
{
if(isLastColumn && !string.IsNullOrEmpty(appendToTheEnd))
{
string column = GenerateColumsWithoutPrimaryKey(table.Columns[index], false);
column += appendToTheEnd;
builder.AppendLine(column);
}
else
{
string column = GenerateColumsWithoutPrimaryKey(table.Columns[index], isLastColumn);
if(!string.IsNullOrEmpty(column))
{
builder.AppendLine(column);
}
}
}
}
builder.AppendLine(") ");
return builder.ToString();
}
public string Render(DatabaseTable table, CodeGenSettings settings)
{
_content = Core.ResourceFileHelper.ConvertStreamResourceToUTF8String(typeof(Resources), "Chucksoft.Templates.Templates.ResourcesWithoutPopulate.template");
//Check for expected content, if not found, badness has happen.
if (string.IsNullOrEmpty(_content))
{
throw new ContentNotFound("Can't find embeddedResource \"Chucksoft.Templates.Templates.ResourcesWithoutPopulate.template\"");
}
//GetItem(table);
GenerateDeleteMethods(table);
GenerateInsertMethods(table, settings);
GenerateUpdateMethods(table);
GenerateSelectAll(table);
GenerateSelectByPrimaryKey(table);
//GeneratePopulateMethod(table);
_content = _content.Replace("<%[TableName]%>", table.Name);
_content = _content.Replace("<%[TableParameter]%>", table.Name.ToLower());
return _content;
}
private void GenerateSelectByPrimaryKey(DatabaseTable table)
{
string makePararmeterList = GetMakeParametersForOnlyPrimaryKeys(table);
StringBuilder builder = new StringBuilder();
DatabaseColumn primaryColumn = table.Columns.Where(c => c.IsPrimaryKey).SingleOrDefault();
if (primaryColumn != null)
{
builder.AppendLine(string.Format("\r\n \t\t\treturn _database.PopulateItem(\"{0}_RetrieveByPrimaryKey\", new {{ {1} }}, _database.AutoPopulate<{0}>);", table.Name, primaryColumn.Name + " = key"));
_content = _content.Replace("<%[RetreveByPrimaryKeyMethod]%>", builder.ToString());
}
}
public abstract GenerationArtifact RenderTemplate(DatabaseTable table, CodeGenSettings settings);
public abstract string RenderTemplate(DatabaseTable table, CodeGenSettings settings);
private void GenerateDeleteMethods(DatabaseTable table)
{
//<%[UpdateMethod]%> -- Token
string makePararmeterList = GetMakeParametersForOnlyPrimaryKeys(table);
StringBuilder builder = new StringBuilder();
builder.AppendLine("\t\t\tList<DbParameter> parameters = new List<DbParameter> ");
builder.AppendLine("\t\t\t{");
builder.AppendLine(makePararmeterList);
builder.AppendLine("\t\t\t};");
builder.AppendLine(string.Format("\r\n \t\t\treturn _database.NonQuery(\"{0}_Delete\", );", table.Name));
_content = _content.Replace("<%[DeleteMethod]%>", builder.ToString());
}
private void GetItem(DatabaseTable table)
{
StringBuilder builder = new StringBuilder();
foreach (DatabaseColumn column in table.Columns)
{
builder.AppendLine(string.Format("\t\t\t{0}.{1} = {2};", table.Name.ToLower(), column.Name, SqlTypeConversion.FindConvertToMethodsBySqlType(column.SqlColumnType, "reader[\"" + column.Name + "\"]")));
}
_content = _content.Replace("<%[GetItemObjectPopulation]%>", builder.ToString());
}
/// <summary>
/// Generates the InsertStatements
/// </summary>
/// <param name="table"></param>
/// <returns>A complete insert statement</returns>
private string GenerateInsertStatement(DatabaseTable table, CodeGenSettings settings)
{
List<DatabaseColumn> columns = table.Columns.Where(c => !c.IsPrimaryKey).ToList();
string procedureName = table.Name + "_Insert";
string insertColumns = GetColumns(columns, false);
string insertParameters = GetColumns(columns, true);
string outIdentity = string.Empty;
//build the insert statement
string procedureStatement = string.Format("\tINSERT INTO [dbo].[{0}] ({1}) \r\n\tVALUES ({2})", table.Name, insertColumns, insertParameters);
if (settings.ReturnIdentityFromInserts)
{
StringBuilder builder = new StringBuilder();
builder.AppendLine("\r\n\t@Identity INT OUTPUT = null");
outIdentity = builder.ToString();
StringBuilder builder1 = new StringBuilder(procedureStatement);
const string newLine = "\r\n\t";
const string sql = newLine + newLine + "IF @Identity IS NOT NULL" +
newLine + "BEGIN" +
newLine + "\tSet @Identity = SELECT SCOPE_IDENTITY()" +
newLine + "END";
builder1.AppendLine(sql);
procedureStatement = builder1.ToString();
}
string insertStatementSignatureParameters = GenerateParameterList(table, false, outIdentity);
//replace tokens in the stored procedure template
string content = ProcedureTokenReplacement(procedureName, procedureStatement, insertStatementSignatureParameters);
return content;
}
private static string GetUpdateColumns(DatabaseTable table)
{
StringBuilder builder = new StringBuilder();
for (int index = 0; index < table.Columns.Count; index++)
{
bool isLastColumn = (index == table.Columns.Count - 1);
builder.AppendLine(string.Format("\t{0} = @{1}{2}", table.Columns[index].Name, table.Columns[index].Name, (!isLastColumn ? "," : string.Empty)));
}
return builder.ToString();
}
private List<string> GetProcedures(DatabaseTable table, CodeGenSettings settings)
{
List<string> prodecures = new List<string>();
//Add the generated Insert statement to the collection
string insertProcedure = GenerateInsertStatement(table, settings);
prodecures.Add(insertProcedure);
const string deleteProcedureName = "Delete";
string deleteSql = string.Format("\tDelete From {0}", table.Name);
string deleteProcedure = GenerateDeleteStatementByPrimaryKey(table, deleteProcedureName, deleteSql);
prodecures.Add(deleteProcedure);
//Generate the SelectByPrimaryKey procedure
string selectSql = string.Format("\tSelect {0} \r\n\tFrom {1}", GetColumns(table.Columns, false), table.Name);
const string selectProcedureName = "SelectByPrimaryKey";
string selectByPrimaryKey = GenerateDeleteStatementByPrimaryKey(table, selectProcedureName, selectSql);
prodecures.Add(selectByPrimaryKey);
//generate the Update procedure
string updateSql = string.Format("\tUpdate {0} \r\n\tSET {1}", table.Name, GetUpdateColumns(table));
string updateParameterList = GenerateParameterList(table, true, string.Empty);
const string updateProcedureName = "Update";
string updateProcedure = GenerateUpdateProcedure(table, updateProcedureName, updateSql, updateParameterList);
prodecures.Add(updateProcedure);
//generate the SelectAll procedure
string selectAllSql = string.Format("\tSelect {0} \r\n\tFrom {1}", GetColumns(table.Columns, false), table.Name);
const string selectAllProcedureName = "SelectAll";
string selectAllProcedure = GenerateSelectAllProcedure(table, selectAllProcedureName, selectAllSql);
prodecures.Add(selectAllProcedure);
return prodecures;
}
private string GenerateUpdateProcedure(DatabaseTable table, string procedureName, string statement, string parameterList)
{
List<DatabaseColumn> primaryKeyColumns = RetrievePrimaryKeys(table);
//Generates one or many primaryKeys
string _procedureName = string.Format("{0}_{1}", table.Name, procedureName);
statement += GenerateWhereClause(primaryKeyColumns);
string content = ProcedureTokenReplacement(_procedureName, statement, parameterList);
return content;
}
private string GenerateSelectAllProcedure(DatabaseTable table, string procedureName, string statement)
{
//set the local content variable, so the stored procedure template does not get stepped on.
string _procedureName = string.Format("{0}_{1}", table.Name, procedureName);
string content = ProcedureTokenReplacement(_procedureName, statement, string.Empty);
return content;
}
private void GenerateUpdateMethods(DatabaseTable table)
{
StringBuilder builder = new StringBuilder();
builder.AppendLine(string.Format("\t\t\treturn _database.NonQuery(\"{0}_Update\", {1});", table.Name, table.Name.ToLower()));
_content = _content.Replace("<%[UpdateMethod]%>", builder.ToString());
}
public override string RenderTemplate(DatabaseTable table, CodeGenSettings settings)
{
return "ChuckWasHere";
}
private void GenerateSelectByPrimaryKey(DatabaseTable table)
{
string makePararmeterList = GetMakeParametersForOnlyPrimaryKeys(table);
StringBuilder builder = new StringBuilder();
builder.AppendLine("\t\t\tList<DbParameter> parameters = new List<DbParameter> ");
builder.AppendLine("\t\t\t{");
builder.AppendLine(makePararmeterList);
builder.AppendLine("\t\t\t};");
builder.AppendLine(string.Format("\r\n \t\t\treturn _database.PopulateItem(\"{0}_RetrieveByPrimaryKey\", parameters, o => Populate(o));", table.Name));
_content = _content.Replace("<%[RetreveByPrimaryKeyMethod]%>", builder.ToString());
}
private void GenerateInsertMethods(DatabaseTable table, CodeGenSettings settings)
{
//<%[InsertMethod]%> -- Token
string makePararmeterList = GetMakeParameters(table, false);
StringBuilder builder = new StringBuilder();
builder.AppendLine("\t\t\tList<DbParameter> parameters = new List<DbParameter> ");
builder.AppendLine("\t\t\t{");
builder.AppendLine(makePararmeterList);
builder.AppendLine("\t\t\t};");
if (settings.ReturnIdentityFromInserts)
{
builder.AppendLine("\r\n \t\t\tparameters.SelectIdentity();");
}
if (!settings.ReturnIdentityFromInserts)
{
builder.AppendLine(string.Format("\r\n \t\t\treturn _database.NonQuery(\"{0}_Insert\", parameters);", table.Name));
}
else
{
builder.AppendLine(string.Format("\r\n \t\t\t_database.NonQuery(\"{0}_Insert\", parameters); \r\n \t\t\t return parameters.Identity<int>(); ", table.Name));
}
_content = _content.Replace("<%[InsertMethod]%>", builder.ToString());
}
/// <summary>
/// Extract the primary keys from the table.
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
private static List<DatabaseColumn> RetrievePrimaryKeys(DatabaseTable table)
{
List<DatabaseColumn> primaryKeyColumns = new List<DatabaseColumn>();
//loop through the columns
foreach (DatabaseColumn column in table.Columns)
{
//it's pretty simple, if it's a primary key, then add it to the collection
if(column.IsPrimaryKey)
{
primaryKeyColumns.Add(column);
}
}
return primaryKeyColumns;
}
private void GenerateSelectAll(DatabaseTable table)
{
StringBuilder builder = new StringBuilder();
builder.AppendLine(string.Format("\r\n \t\t\treturn _database.PopulateCollection(\"{0}_RetrieveAll\", _database.AutoPopulate<{0}>);", table.Name));
_content = _content.Replace("<%[RetreveAllMethod]%>", builder.ToString());
}
/// <summary>
/// Generates the populate method.
/// </summary>
/// <param name="table">The table.</param>
private void GeneratePopulateMethod(DatabaseTable table)
{
_content = _content.Replace("<%[LowerCaseTableName]%>", table.Name.ToLower());
StringBuilder builder = new StringBuilder();
for (int index = 0; index < table.Columns.Count; index++)
{
bool isLastItem = (index == table.Columns.Count - 1);
const string populate = "\r\n \t\t\t\t\t\t\t\t\t\t\t\t\t\t{0} = {1}{2}";
builder.Append(string.Format(populate, table.Columns[index].Name, SqlTypeConversion.FindConvertToMethodsBySqlType(table.Columns[index].SqlColumnType, table.Columns[index].Name), (isLastItem ? string.Empty : ",")));
}
_content = _content.Replace("<%[PopulateProperities]%>", builder.ToString());
}
