private void GenerateSelectAllFunction(StringBuilder classText, SQLTable table)
{
classText.AppendLine($"\tfunc all{table.Name}s() -> [{table.Name}]? {{");
classText.AppendLine($"\t\tvar rows = [{table.Name}]()");
classText.AppendLine($"\t\tlet querySql = \"SELECT * FROM {table.Name};\"");
classText.AppendLine($"\t\tguard let queryStatement = try? prepareStatement(sql: querySql) else {{");
classText.AppendLine($"\t\t\treturn nil");
classText.AppendLine($"\t\t}}");
classText.AppendLine($"\t\tdefer {{");
classText.AppendLine($"\t\t\tsqlite3_finalize(queryStatement)");
classText.AppendLine($"\t\t}}");
classText.AppendLine("");
classText.AppendLine($"\t\tvar result = sqlite3_step(queryStatement)");
classText.AppendLine("");
classText.AppendLine($"\t\twhile result == SQLITE_ROW {{");
int columnCounter = 0;
foreach (SQLTableColumn column in table.Columns)
{
classText.AppendLine($"\t\t\t{GenerateReadQueryValueText(column, columnCounter)}");
columnCounter++;
}
classText.AppendLine("");
classText.AppendLine($"\t\t\tlet {Library.LowerFirstCharacter(table.Name)} = {table.Name}({string.Join(", ", table.Columns.Select(co => Library.LowerFirstCharacter(co.Name) + ": " + Library.LowerFirstCharacter(co.Name)))})");
classText.AppendLine("");
classText.AppendLine($"\t\t\trows.append({Library.LowerFirstCharacter(table.Name)})");
classText.AppendLine($"\t\t\tresult = sqlite3_step(queryStatement)");
classText.AppendLine($"\t\t}}");
classText.AppendLine("\t\treturn rows");
classText.AppendLine("\t}");
}
private void GenerateSelectFunction(StringBuilder classText, SQLTable table)
{
classText.AppendLine($"\tfunc {Library.LowerFirstCharacter(table.Name)}({Library.LowerFirstCharacter(table.PrimaryKey.Name)}: {table.PrimaryKey.iosDataType}) -> {table.Name}? {{");
classText.AppendLine($"\t\tlet querySql = \"SELECT * FROM {table.Name} WHERE {table.PrimaryKey.Name} = ?;\"");
classText.AppendLine($"\t\tguard let queryStatement = try? prepareStatement(sql: querySql) else {{");
classText.AppendLine($"\t\t\treturn nil");
classText.AppendLine($"\t\t}}");
classText.AppendLine($"\t\tdefer {{");
classText.AppendLine($"\t\t\tsqlite3_finalize(queryStatement)");
classText.AppendLine($"\t\t}}");
classText.AppendLine($"\t\tguard {GenerateBindText("queryStatement", table.PrimaryKey, 1, false)} else {{");
classText.AppendLine($"\t\t\treturn nil");
classText.AppendLine($"\t\t}}");
classText.AppendLine($"\t\tguard sqlite3_step(queryStatement) == SQLITE_ROW else {{");
classText.AppendLine($"\t\t\treturn nil");
classText.AppendLine($"\t\t}}");
int columnCounter = 0;
foreach (SQLTableColumn column in table.Columns)
{
classText.AppendLine($"\t\t{GenerateReadQueryValueText(column, columnCounter)}");
columnCounter++;
}
classText.AppendLine("");
classText.AppendLine($"\t\treturn {table.Name}({string.Join(", ", table.Columns.Select(co => Library.LowerFirstCharacter(co.Name) + ": " + Library.LowerFirstCharacter(co.Name)))})");
classText.AppendLine($"\t}}");
}
internal override void GenerateFilePerTable(SQLTable table)
{
classText.AppendLine($"extension {table.Name}: SQLTable {{");
classText.AppendLine($"\tstatic var createStatement: String {{");
classText.AppendLine($"\t\treturn \"\"\"");
classText.AppendLine($"\t\tCREATE TABLE IF NOT EXISTS {table.Name}(");
bool prependComma = false;
foreach (SQLTableColumn column in table.Columns)
{
if (prependComma)
{
classText.Append("," + Environment.NewLine);
}
classText.Append("\t\t\t" + column.Name + " " + column.sqlLiteDataType + column.SizeForSQLProcedureParameters);
classText.Append(column.PrimaryKey ? " PRIMARY KEY" : "");
classText.Append(column.Nullable ? " NOT NULL" : "");
prependComma = true;
}
classText.Append(Environment.NewLine);
classText.AppendLine($"\t\t);");
classText.AppendLine($"\t\t\"\"\"");
classText.AppendLine($"\t}}");
classText.AppendLine($"}}");
}
private string DeleteRecordInTableFunction(SQLTable table, bool appendTableNameToFunction)
{
StringBuilder deleteFunction = new StringBuilder();
deleteFunction.AppendLine($"\t\tprivate void Delete{(appendTableNameToFunction ? table.Name : "")}({table.PrimaryKey.cSharpDataType} {Library.LowerFirstCharacter(table.Name)}Id)");
deleteFunction.AppendLine($"\t\t{{");
deleteFunction.AppendLine($"\t\t\tint start{table.Name}Count = _{Library.LowerFirstCharacter(table.Name)}Repository.GetAll().Count;");
deleteFunction.AppendLine("");
deleteFunction.AppendLine($"\t\t\t_{Library.LowerFirstCharacter(table.Name)}Repository.Delete({Library.LowerFirstCharacter(table.Name)}Id);");
deleteFunction.AppendLine("");
deleteFunction.AppendLine($"\t\t\t{(table.Name == _nameSpace ? $"Repository.{table.Name}" : table.Name)} {Library.LowerFirstCharacter(table.Name)} = _{Library.LowerFirstCharacter(table.Name)}Repository.GetByID({Library.LowerFirstCharacter(table.Name)}Id);");
deleteFunction.AppendLine("");
deleteFunction.AppendLine($"\t\t\tint end{table.Name}Count = _{Library.LowerFirstCharacter(table.Name)}Repository.GetAll().Count;");
deleteFunction.AppendLine("");
deleteFunction.AppendLine($"\t\t\tConsole.WriteLine($\"{table.Name} deleted id: {{{Library.LowerFirstCharacter(table.Name)}Id}}\");");
deleteFunction.AppendLine("");
deleteFunction.AppendLine($"\t\t\tAssert.Null({Library.LowerFirstCharacter(table.Name)});");
deleteFunction.AppendLine($"\t\t\tAssert.Equal(end{table.Name}Count + 1, start{table.Name}Count);");
deleteFunction.AppendLine("");
deleteFunction.AppendLine($"\t\t}}");
return(deleteFunction.ToString());
}
private void WriteListPage(SQLTable table, string destinationFolder, string masterPageName, string bodyContentID, string nameSpace, string omNameSpace)
{
//Unfortunately this function isn't much use at the moment; it may be returned to at a later date.
StringBuilder listPage = new StringBuilder();
listPage.AppendLine("<%@ Page Title=\"" + table.Name + "s\" Language=\"C#\" MasterPageFile=\"~/" + masterPageName + "\" AutoEventWireup=\"true\" CodeFile=\"" + table.Name + "s.aspx.cs\" Inherits=\"" + table.Name + "\" %>");
listPage.AppendLine("<asp:Content ID=\"cntAdvertisersHead\" ContentPlaceHolderID=\"HeadContent\" Runat=\"Server\">");
listPage.AppendLine("</asp:Content>");
listPage.AppendLine("<asp:Content ID=\"cntAdvertisersBody\" ContentPlaceHolderID=\"MainContent\" Runat=\"Server\">");
listPage.AppendLine(" <asp:ObjectDataSource ID=\"odsAdvertisers\" runat=\"server\" ");
listPage.AppendLine(" SelectMethod=\"GetAllAdvertisers\" TypeName=\"InSizeMediaOM.Advertiser\"></asp:ObjectDataSource>");
listPage.AppendLine(" <asp:GridView ID=\"gvwAdvertisers\" runat=\"server\" AutoGenerateColumns=\"False\" ");
listPage.AppendLine(" DataSourceID=\"odsAdvertisers\">");
listPage.AppendLine(" <Columns>");
listPage.AppendLine(" <asp:TemplateField HeaderText=\"Name\" SortExpression=\"Name\">");
listPage.AppendLine(" <ItemTemplate>");
listPage.AppendLine(" <asp:HyperLink ID=\"hlAdvertiser\" runat=\"server\" NavigateUrl='<%# \"advertiser.aspx?adv=\" + Eval(\"AdvertiserID\") %>' Text='<% #Bind(\"Name\") %>'></asp:HyperLink>");
listPage.AppendLine(" </ItemTemplate>");
listPage.AppendLine(" </asp:TemplateField>");
listPage.AppendLine(" <asp:BoundField DataField=\"Name\" HeaderText=\"Name\" SortExpression=\"Name\" />");
listPage.AppendLine(" <asp:TemplateField HeaderText=\"Name\"></asp:TemplateField>");
listPage.AppendLine(" </Columns>");
listPage.AppendLine(" </asp:GridView>");
listPage.AppendLine("</asp:Content>");
}
private void WriteReadOnlyCodeBehind(SQLTable table, string destinationFolder, string nameSpace, string omNameSpace)
{
StringBuilder codeBehindText = new StringBuilder();
AppendStartOfCodeBehind(codeBehindText, nameSpace, false, omNameSpace, table);
if (table.PrimaryKey.DataType == SQLDataTypes.uniqueIdentifier)
{
codeBehindText.AppendLine("Guid " + Library.LowerFirstCharacter(table.PrimaryKey.Name) + " = new Guid(Request.QueryString[\"" + table.Name + "ID parameter\"]);");
}
else
{
codeBehindText.AppendLine(Library.LowerFirstCharacter(table.PrimaryKey.Name) + " = Convert.ToInt64(Request.QueryString[\"" + table.Name + "ID parameter\"]]);");
}
codeBehindText.AppendLine(Library.LowerFirstCharacter(table.Name) + " = " + omNameSpace + "." + table.Name + ".Get" + table.Name + "ByID(" + Library.LowerFirstCharacter(table.PrimaryKey.Name) + ");");
codeBehindText.AppendLine("Write" + table.Name + "ToReadOnlyControls(" + Library.LowerFirstCharacter(table.Name) + ");");
codeBehindText.AppendLine("}");
codeBehindText.AppendLine("private void Write" + table.Name + "ToReadOnlyControls(" + omNameSpace + "." + table.Name + " " + Library.LowerFirstCharacter(table.Name) + ")");
codeBehindText.AppendLine("{");
foreach (SQLTableColumn column in table.Columns)
{
if (!column.PrimaryKey)
{
codeBehindText.AppendLine("lblSaved" + column.Name + ".Text = " + Library.LowerFirstCharacter(table.Name) + "." + column.Name + ";");
}
}
codeBehindText.AppendLine("}");
AppendEndOfCodeBehind(codeBehindText);
WriteCodeToFile(destinationFolder + table.Name + ".aspx.cs", codeBehindText);
}
internal override void GenerateFilePerTable(SQLTable table)
{
string className = table.Name;
string objectName = Library.LowerFirstCharacter(table.Name);
GenerateFile(className, objectName, true);
}
private void WriteEditCodeBehind(SQLTable table, string destinationFolder, string nameSpace, string omNameSpace)
{
StringBuilder codeBehindText = new StringBuilder();
AppendStartOfCodeBehind(codeBehindText, nameSpace, true, omNameSpace, table);
codeBehindText.AppendLine("if (!IsPostBack)");
codeBehindText.AppendLine("Write" + table.Name + "ToControls(" + Library.LowerFirstCharacter(table.Name) + ");");
codeBehindText.AppendLine("}");
codeBehindText.AppendLine("private void Write" + table.Name + "ToControls(" + omNameSpace + "." + table.Name + " " + Library.LowerFirstCharacter(table.Name) + ")");
codeBehindText.AppendLine("{");
foreach (SQLTableColumn column in table.Columns)
{
if (!column.PrimaryKey)
{
codeBehindText.AppendLine("txt" + column.Name + ".Text = " + Library.LowerFirstCharacter(table.Name) + "." + column.Name + ";");
}
}
codeBehindText.AppendLine("}");
codeBehindText.AppendLine("protected void btnSave" + table.Name + "_Click(object sender, EventArgs e)");
codeBehindText.AppendLine("{");
codeBehindText.AppendLine("if (Page.IsValid)");
codeBehindText.AppendLine("{");
codeBehindText.AppendLine("if ( " + Library.LowerFirstCharacter(table.Name) + " == null)");
codeBehindText.AppendLine("{");
codeBehindText.AppendLine(" " + Library.LowerFirstCharacter(table.Name) + " = new " + omNameSpace + "." + table.Name + "();");
codeBehindText.AppendLine("}");
foreach (SQLTableColumn column in table.Columns)
{
if (!column.PrimaryKey)
{
codeBehindText.AppendLine(" " + Library.LowerFirstCharacter(table.Name) + "." + column.Name + " = txt" + column.Name + ".Text;");
}
}
codeBehindText.AppendLine(Library.LowerFirstCharacter(table.Name) + ".Save" + table.Name + "();");
codeBehindText.AppendLine("}");
codeBehindText.AppendLine("}");
codeBehindText.AppendLine("protected void btnCancel_Click(object sender, EventArgs e)");
codeBehindText.AppendLine("{");
codeBehindText.AppendLine("Response.Redirect(\"~/Appropriate page to redirect to\");");
codeBehindText.AppendLine("}");
AppendEndOfCodeBehind(codeBehindText);
WriteCodeToFile(destinationFolder + table.Name + "Edit.aspx.cs", codeBehindText);
}
private string CreateRecordInTableFunction(SQLTable table, bool foreignKeyTable)
{
StringBuilder createFunction = new StringBuilder();
createFunction.AppendLine($"\t\tprivate {table.PrimaryKey.cSharpDataType} Create{(foreignKeyTable?table.Name:"")}()");
createFunction.AppendLine($"\t\t{{");
createFunction.AppendLine($"\t\t\t{(table.Name == _nameSpace ? $"Repository.{table.Name}" : table.Name)} {Library.LowerFirstCharacter(table.Name)} = new {(table.Name == _nameSpace ? $"Repository.{table.Name}" : table.Name)}();");
createFunction.AppendLine("");
foreach (SQLTableColumn columnSetValue in table.Columns)
{
if (!columnSetValue.PrimaryKey)
{
string value = $"_{Library.LowerFirstCharacter(columnSetValue.Name)}";
createFunction.AppendLine($"\t\t\t{Library.LowerFirstCharacter(table.Name)}.{columnSetValue.Name} = {value};");
}
}
createFunction.AppendLine("");
createFunction.AppendLine($"\t\t\t_{Library.LowerFirstCharacter(table.Name)}Repository.Save({Library.LowerFirstCharacter(table.Name)});");
createFunction.AppendLine("");
createFunction.AppendLine($"\t\t\tConsole.WriteLine($\"{table.Name} created id:{{{Library.LowerFirstCharacter(table.Name)}.{table.Name}Id}}\");");
createFunction.AppendLine("");
string idNotSetValue = table.PrimaryKey.cSharpDataType == "Guid" ? "Guid.Empty" : "0";
createFunction.AppendLine($"\t\t\tAssert.NotEqual({idNotSetValue}, {Library.LowerFirstCharacter(table.Name)}.{table.Name}Id);");
createFunction.AppendLine("");
createFunction.AppendLine($"\t\t\treturn {Library.LowerFirstCharacter(table.Name)}.{table.Name}Id;");
createFunction.AppendLine($"\t\t}}");
return(createFunction.ToString());
}
private string ForeignKeyCode(SQLTable table, CodeFunction codeFunction, bool prepend)
{
StringBuilder foreignKeyCode = new StringBuilder();
foreach (SQLForeignKeyRelation foreignKey in sQLForeignKeyRelationsForTable(table))
{
SQLTableColumn column = foreignKey.ReferencedTableColumn;
if (prepend)
{
foreignKeyCode.Insert(0, codeFunction(column.ParentTable) + Environment.NewLine);
}
else
{
foreignKeyCode.Append(codeFunction(column.ParentTable) + Environment.NewLine);
}
string childForeignKeyCreateFunctions = ForeignKeyCode(column.ParentTable, codeFunction, prepend);
if (childForeignKeyCreateFunctions != "")
{
if (prepend)
{
foreignKeyCode.Insert(0, childForeignKeyCreateFunctions);
}
else
{
foreignKeyCode.Append(childForeignKeyCreateFunctions);
}
}
}
return(foreignKeyCode.ToString());
}
void GenerateSelectStatement(SQLTable table, StringBuilder sqlStatement)
{
sqlStatement.Append("AS" + Environment.NewLine);
sqlStatement.Append("BEGIN" + Environment.NewLine);
sqlStatement.Append("SET NOCOUNT ON; "+ Environment.NewLine);
sqlStatement.Append("SELECT " + Environment.NewLine);
bool appendComma = false;
foreach (SQLTableColumn column in table.Columns)
{
if (appendComma)
{
sqlStatement.Append("," + Environment.NewLine);
}
sqlStatement.Append("[" + column.Name + "]");
appendComma = true;
}
sqlStatement.Append(Environment.NewLine + "FROM [" + table.Name + "]" + Environment.NewLine);
sqlStatement.AppendLine("");
}
internal override void GenerateFilePerTable(SQLTable table)
{
string className = table.Name;
string primaryKey = table.PrimaryKey.Name;
string objectName = Library.LowerFirstCharacter(table.Name);
classText.AppendLine($"package com.example.{Library.LowerFirstCharacter(_nameSpace)}.dao");
classText.AppendLine("");
classText.AppendLine("import androidx.room.Dao");
classText.AppendLine("import androidx.room.Delete");
classText.AppendLine("import androidx.room.Insert");
classText.AppendLine("import androidx.room.Query");
classText.AppendLine("import kotlinx.coroutines.flow.Flow");
classText.AppendLine("");
classText.AppendLine("@Dao");
classText.AppendLine($"interface {className}Dao {{");
classText.AppendLine($"\t@Query(\"SELECT * FROM {className}\")");
classText.AppendLine($"\tfun getAll(): Flow<List<{className}>>");
classText.AppendLine($"\t@Query(\"SELECT * FROM {className} WHERE {primaryKey} = :{primaryKey}\")");
classText.AppendLine($"\tfun getById({primaryKey}: String): Flow<{className}>");
classText.AppendLine("\t@Insert");
classText.AppendLine($"\tsuspend fun insertAll(vararg {objectName}s: {className})");
classText.AppendLine("\t@Insert");
classText.AppendLine($"\tsuspend fun insert(vararg {objectName}: {className})");
classText.AppendLine("\t@Delete");
classText.AppendLine($"\tsuspend fun delete({objectName}: {className})");
classText.AppendLine("}");
}
private string TableColumnsCode(SQLTable table, CodeForColumn codeFunction)
{
StringBuilder columnsCode = new StringBuilder();
foreach (SQLTableColumn column in table.Columns)
{
if (!column.PrimaryKey)
{
columnsCode.AppendLine(codeFunction(column));
}
}
return(columnsCode.ToString());
}
internal override void GenerateFilePerTable(SQLTable table)
{
classText.AppendLine("using System;");
classText.AppendLine("using System.ComponentModel.DataAnnotations;");
classText.AppendLine(Environment.NewLine);
classText.AppendLine($"namespace {_nameSpace}.Repository");
classText.AppendLine("{");
classText.AppendLine($"\tpublic class {table.Name}");
classText.AppendLine("\t{");
classText.AppendLine("#region Constructors");
if (table.PrimaryKey.DataType == SQLDataTypes.uniqueIdentifier)
{
classText.AppendLine("\t\tpublic " + table.Name + "(Guid id)");
}
else
{
classText.AppendLine("\t\tpublic " + table.Name + "(int id)");
}
classText.AppendLine("\t\t{");
classText.AppendLine($"\t\t\tthis.{table.PrimaryKey.Name} = id;");
classText.AppendLine("\t\t}");
classText.AppendLine("\t\tpublic " + table.Name + "()");
classText.AppendLine("\t\t{ }");
classText.AppendLine("#endregion");
foreach (SQLTableColumn column in table.Columns)
{
if (!column.Nullable && !column.PrimaryKey)
{
classText.AppendLine($"\t\t[Required(ErrorMessage= \"{column.Name} is required\")]");
}
if (column.cSharpDataType == "string")
{
classText.AppendLine($"\t\t[MaxLength(length:{column.MaximumLength}, ErrorMessage = \"{column.Name} cannot be longer than {column.MaximumLength} characters\")]");
}
classText.AppendLine($"\t\tpublic {column.cSharpDataType}{(column.Nullable && column.cSharpDataType != "string" ? "?" : "")} {column.Name} {{ get; set; }}");
}
classText.AppendLine("\t}");
classText.AppendLine("}");
}
void GenerateDeleteStatement(SQLTable table, StringBuilder sqlStatement)
{
sqlStatement.AppendLine(go);
sqlStatement.AppendLine("CREATE PROCEDURE [dbo].[" + table.Name + "Delete]" + Environment.NewLine);
sqlStatement.AppendLine($"@{table.PrimaryKey.Name} {table.PrimaryKey.DataType}");
sqlStatement.AppendLine(sqlAs);
sqlStatement.AppendLine(begin);
sqlStatement.AppendLine(setNoCount);
sqlStatement.AppendLine($"DELETE FROM [{table.Name}]");
sqlStatement.AppendLine(sqlRowIdentifier(table.PrimaryKey.Name));
sqlStatement.AppendLine(end);
}
void GenerateUpdateStatement(SQLTable table, StringBuilder sqlStatement)
{
sqlStatement.AppendLine("GO");
sqlStatement.Append("CREATE PROCEDURE [dbo].[" + table.Name + "Update]" + Environment.NewLine);
bool appendComma = false;
foreach (SQLTableColumn column in table.Columns)
{
if (appendComma)
{
sqlStatement.Append("," + Environment.NewLine);
}
sqlStatement.Append("@" + column.Name + " " + column.DataType.ToString());
sqlStatement.Append(column.SizeForSQLProcedureParameters);
appendComma = true;
}
sqlStatement.Append(Environment.NewLine + "AS" + Environment.NewLine + "BEGIN" + Environment.NewLine);
sqlStatement.Append("UPDATE [" + table.Name + "]" + Environment.NewLine);
sqlStatement.Append("SET" + Environment.NewLine);
appendComma = false;
foreach (SQLTableColumn column in table.Columns)
{
if (!column.PrimaryKey)
{
if (appendComma)
{
sqlStatement.Append("," + Environment.NewLine);
}
sqlStatement.Append("[" + column.Name + "] = @" + column.Name);
appendComma = true;
}
}
sqlStatement.AppendLine(sqlRowIdentifier(table.PrimaryKey.Name));
sqlStatement.AppendLine("END");
}
void GenerateForeignKeyStatements(SQLTable table, StringBuilder sqlStatement)
{
List <SQLForeignKeyRelation> foreignKeys = sQLForeignKeyRelationsForTable(table);
foreach (SQLForeignKeyRelation foreignKey in foreignKeys)
{
SQLTableColumn column = foreignKey.ReferencedTableColumn;
sqlStatement.AppendLine(go);
sqlStatement.Append("CREATE PROCEDURE [dbo].[" + table.Name + "GetBy" + column.Name + "]" + Environment.NewLine);
sqlStatement.Append("@" + column.Name + " " + column.DataType.ToString() + Environment.NewLine);
GenerateSelectStatement(table, sqlStatement);
sqlStatement.AppendLine(sqlRowIdentifier(column.Name));
sqlStatement.AppendLine(end);
sqlStatement.AppendLine(procedureDivider);
}
}
internal override void GenerateFilePerTable(SQLTable table)
{
classText.AppendLine($"import Foundation");
classText.AppendLine($"struct {table.Name} : Decodable, Identifiable");
classText.AppendLine($" {{");
foreach (SQLTableColumn column in table.Columns)
{
if (column.PrimaryKey)
{
classText.AppendLine($"\tvar id: {column.iosDataType} {{ {Library.LowerFirstCharacter(column.Name)} }}");
}
classText.AppendLine($"\tvar {Library.LowerFirstCharacter(column.Name)}: {column.iosDataType}");
}
classText.AppendLine($"}}");
}
internal List <SQLForeignKeyRelation> sQLForeignKeyRelationsForTable(SQLTable table)
{
List <SQLForeignKeyRelation> foreignKeys = new List <SQLForeignKeyRelation>();
foreach (SQLTable otherTable in _sQLTables.Where(tb => tb != table))
{
foreach (SQLTableColumn sQLTableColumn in otherTable.Columns)
{
foreach (SQLForeignKeyRelation foreignKeyRelation in sQLTableColumn.ForeignKeys)
{
if (foreignKeyRelation.ParentTableColum.TableName == table.Name)
{
foreignKeys.Add(foreignKeyRelation);
}
}
}
}
return(foreignKeys);
}
private string ColumnParametersForTable(SQLTable table)
{
StringBuilder columnNames = new StringBuilder();
foreach (SQLTableColumn column in table.Columns)
{
if (!column.PrimaryKey)
{
if (column.DataType == SQLDataTypes.uniqueIdentifier)
{
columnNames.AppendLine($"\t\tprivate {column.cSharpDataType} _{Library.LowerFirstCharacter(column.Name)} = Guid.NewGuid();");
}
else
{
columnNames.AppendLine($"\t\tprivate {column.cSharpDataType} _{Library.LowerFirstCharacter(column.Name)} = {column.RandomValue()};");
}
}
}
return(columnNames.ToString());
}
void GenerateForeignKeyDeleteStatements(SQLTable table, StringBuilder sqlStatement)
{
List <SQLForeignKeyRelation> foreignKeys = sQLForeignKeyRelationsForTable(table);
foreach (SQLForeignKeyRelation foreignKey in foreignKeys)
{
SQLTableColumn column = foreignKey.ReferencedTableColumn;
sqlStatement.AppendLine(go);
sqlStatement.AppendLine("CREATE PROCEDURE [dbo].[" + table.Name + "DeleteFor" + column.TableName + "]" + Environment.NewLine);
sqlStatement.AppendLine($"@{column.Name} {column.DataType}");
sqlStatement.AppendLine(sqlAs);
sqlStatement.AppendLine(begin);
sqlStatement.AppendLine(setNoCount);
sqlStatement.AppendLine($"DELETE FROM [{table.Name}]");
sqlStatement.AppendLine(sqlRowIdentifier(column.Name));
sqlStatement.AppendLine(end);
}
}
public static List <SQLTable> LoadTables(string connectionString)
{
List <SQLTable> tables = new List <SQLTable>();
SqlDataReader dataReader;
dataReader = SQLDataServer.ExecuteSQLStringReturnDataReader("select name, object_id from sys.tables where name <> 'sysdiagrams' and name not like 'aspnet_%'", connectionString);
while (dataReader.Read())
{
SQLTable table = new SQLTable();
table.Name = Convert.ToString(dataReader["name"]);
table.id = Convert.ToInt32(dataReader["object_id"]);
table.Columns = SQLTableColumn.LoadColumnsForTable(table.Name, connectionString, table, ref tables);
tables.Add(table);
}
dataReader.Close();
return(tables);
}
private void GenerateInsertFunction(StringBuilder classText, SQLTable table)
{
classText.AppendLine($"\tfunc insert{table.Name}({Library.LowerFirstCharacter(table.Name)}: {table.Name}) throws {{");
classText.AppendLine($"\t\tlet insertSql = \"INSERT INTO {table.Name} ({string.Join(", ", table.Columns.Select(co => co.Name))}) VALUES ({string.Join(", ", table.Columns.Select(co => "?"))});\"");
classText.AppendLine($"\t\tlet insertStatement = try prepareStatement(sql: insertSql)");
classText.AppendLine($"\t\tdefer {{");
classText.AppendLine($"\t\t\tsqlite3_finalize(insertStatement)");
classText.AppendLine($"\t\t}}");
classText.AppendLine($"\t\tguard");
bool prependAmpresands = false;
int columnCounter = 1;
foreach (SQLTableColumn column in table.Columns)
{
if (prependAmpresands)
{
classText.AppendLine(" &&");
}
classText.Append($"\t\t\t{GenerateBindText("insertStatement", column, columnCounter, true)}");
columnCounter += 1;
prependAmpresands = true;
}
classText.AppendLine("");
classText.AppendLine($"\t\t\telse {{");
classText.AppendLine($"\t\t\t\tthrow SQLiteError.Bind(message: errorMessage)");
classText.AppendLine($"\t\t}}");
classText.AppendLine($"\t\tguard sqlite3_step(insertStatement) == SQLITE_DONE else {{");
classText.AppendLine($"\t\t\tthrow SQLiteError.Step(message: errorMessage)");
classText.AppendLine($"\t\t}}");
classText.AppendLine($"\t\tprint(\"Successfully inserted row.\")");
classText.AppendLine($"\t}}");
}
private string CreateRecordInTableFunction(SQLTable table)
{
return(CreateRecordInTableFunction(table, true));
}
private string ForeignKeyCode(SQLTable table, CodeFunction codeFunction)
{
return(ForeignKeyCode(table, codeFunction, false));
}
private string DeleteParentObjectCode(SQLTable table)
{
return($"\t\t\tDelete{table.Name}(_{Library.LowerFirstCharacter(table.PrimaryKey.Name)});");
}
private string CreateRepository(SQLTable table)
{
return($"\t\tprivate {table.Name}RepositorySql _{Library.LowerFirstCharacter(table.Name)}Repository = new {table.Name}RepositorySql();");
}
internal override void GenerateFilePerTable(SQLTable table)
{
List <SQLForeignKeyRelation> foreignKeys = sQLForeignKeyRelationsForTable(table);
classText.AppendLine($"using {_nameSpace}.Repository;");
classText.AppendLine("using System;");
classText.AppendLine("using Xunit;");
classText.AppendLine("");
classText.AppendLine($"namespace {_nameSpace}.SqlRepository.Test");
classText.AppendLine("{");
classText.AppendLine($"\tpublic class {table.Name}RepositoryIntegrationTest");
classText.AppendLine($"\t{{");
classText.AppendLine(ColumnParametersForTable(table));
classText.AppendLine(ForeignKeyCode(table, ColumnParametersForTable));
classText.AppendLine(CreateRepository(table));
classText.AppendLine(ForeignKeyCode(table, CreateRepository));
classText.AppendLine("");
classText.AppendLine($"\t\t[Fact]");
classText.AppendLine($"\t\tpublic void {table.Name}CRD()");
classText.AppendLine($"\t\t{{");
classText.AppendLine($"\t\t\t{table.PrimaryKey.cSharpDataType} {Library.LowerFirstCharacter(table.Name)}Id;");
classText.AppendLine("");
classText.AppendLine(ForeignKeyCode(table, CreateParentObjectCode, prepend: true));
classText.AppendLine($"\t\t\t{Library.LowerFirstCharacter(table.Name)}Id = Create();");
classText.AppendLine($"\t\t\tGetById({Library.LowerFirstCharacter(table.Name)}Id);");
classText.AppendLine($"\t\t\tDelete({Library.LowerFirstCharacter(table.Name)}Id);");
classText.AppendLine(ForeignKeyCode(table, DeleteParentObjectCode));
classText.AppendLine($"\t\t}}");
classText.AppendLine("");
classText.AppendLine(ForeignKeyCode(table, CreateRecordInTableFunction));
classText.AppendLine(CreateRecordInTableFunction(table, false));
classText.AppendLine("");
classText.AppendLine($"\t\tprivate void GetById({table.PrimaryKey.cSharpDataType} {Library.LowerFirstCharacter(table.Name)}Id)");
classText.AppendLine($"\t\t{{");
classText.AppendLine("");
classText.AppendLine($"\t\t\t{dataObjectClassIdentifier} {Library.LowerFirstCharacter(table.Name)} = _{Library.LowerFirstCharacter(table.Name)}Repository.GetByID({Library.LowerFirstCharacter(table.Name)}Id);");
classText.AppendLine("");
foreach (SQLTableColumn columnCheckValue in table.Columns)
{
if (columnCheckValue.PrimaryKey)
{
classText.AppendLine($"\t\t\tAssert.Equal({Library.LowerFirstCharacter(columnCheckValue.Name)}, {Library.LowerFirstCharacter(table.Name)}.{columnCheckValue.Name});");
}
else
{
classText.AppendLine($"\t\t\tAssert.Equal(_{Library.LowerFirstCharacter(columnCheckValue.Name)}, {Library.LowerFirstCharacter(table.Name)}.{columnCheckValue.Name});");
}
}
classText.AppendLine("");
classText.AppendLine($"\t\t}}");
classText.AppendLine(Environment.NewLine);
classText.AppendLine(DeleteRecordInTableFunction(table, false));
classText.AppendLine(ForeignKeyCode(table, DeleteRecordInTableFunction));
classText.AppendLine("\t}");
classText.AppendLine("}");
}
internal override void GenerateFilePerTable(SQLTable table)
{
classText.AppendLine("using Microsoft.AspNetCore.Mvc;");
classText.AppendLine($"using {_nameSpace}.Repository;");
classText.AppendLine("using System;");
classText.AppendLine("using System.Collections.Generic;");
classText.AppendLine("");
classText.AppendLine($"namespace {_nameSpace}.Controllers");
classText.AppendLine("{");
classText.AppendLine("\t[Route(\"api/[controller]\")]");
classText.AppendLine("\t[ApiController]");
classText.AppendLine($"\tpublic class {table.Name}Controller : ControllerBase");
classText.AppendLine("\t{");
classText.AppendLine($"\t\tIRepository <{ClassName(table.Name)}, {table.PrimaryKey.cSharpDataType}> {Library.LowerFirstCharacter(table.Name)}Repository;");
classText.AppendLine("");
classText.AppendLine($"\t\tpublic {table.Name}Controller(IRepository<{ClassName(table.Name)}, {table.PrimaryKey.cSharpDataType}> {Library.LowerFirstCharacter(table.Name)}Repository)");
classText.AppendLine("\t\t{");
classText.AppendLine($"\t\t\tthis.{Library.LowerFirstCharacter(table.Name)}Repository = {Library.LowerFirstCharacter(table.Name)}Repository;");
classText.AppendLine("\t\t}");
classText.AppendLine("");
classText.AppendLine($"\t\t[HttpGet]");
classText.AppendLine($"\t\tpublic IEnumerable<{ClassName(table.Name)}> Get()");
classText.AppendLine("\t\t{");
classText.AppendLine($"\t\t\treturn {Library.LowerFirstCharacter(table.Name)}Repository.GetAll();");
classText.AppendLine("\t\t}");
classText.AppendLine($"\t\t");
classText.AppendLine("\t\t[HttpGet(\"{id}\")]");
classText.AppendLine($"\t\tpublic {ClassName(table.Name)} Get({table.PrimaryKey.cSharpDataType} id)");
classText.AppendLine("\t\t{");
classText.AppendLine($"\t\t\treturn {Library.LowerFirstCharacter(table.Name)}Repository.GetByID(id);");
classText.AppendLine($"\t\t}}");
classText.AppendLine($"\t\t");
classText.AppendLine($"\t\t[HttpPost]");
classText.AppendLine($"\t\tpublic {table.PrimaryKey.cSharpDataType} Post([FromBody] {ClassName(table.Name)} value)");
classText.AppendLine("\t\t{");
classText.AppendLine($"\t\t\t{ClassName(table.Name)} new{table.Name} = value;");
classText.AppendLine($"\t\t\t{Library.LowerFirstCharacter(table.Name)}Repository.Save(new{table.Name});");
classText.AppendLine($"\t\t");
classText.AppendLine($"\t\t\treturn new{table.Name}.{table.Name}Id;");
classText.AppendLine("\t\t}");
classText.AppendLine($"\t\t");
classText.AppendLine("\t\t[HttpPut(\"{id}\")]");
classText.AppendLine($"\t\tpublic void Put({table.PrimaryKey.cSharpDataType} id, [FromBody] {ClassName(table.Name)} value)");
classText.AppendLine("\t\t{");
classText.AppendLine($"\t\t\t{ClassName(table.Name)} existing{table.Name} = value;");
classText.AppendLine($"\t\t\t");
classText.AppendLine($"\t\t\texisting{table.Name}.{table.Name}Id = id;");
classText.AppendLine($"\t\t\t");
classText.AppendLine($"\t\t\t{Library.LowerFirstCharacter(table.Name)}Repository.Save(existing{table.Name});");
classText.AppendLine("\t\t}");
classText.AppendLine($"\t\t");
classText.AppendLine($"\t\t[HttpDelete(\"{{id}}\")]");
classText.AppendLine($"\t\tpublic void Delete({table.PrimaryKey.cSharpDataType} id)");
classText.AppendLine("\t\t{");
classText.AppendLine($"\t\t\t{Library.LowerFirstCharacter(table.Name)}Repository.Delete(id);");
classText.AppendLine("\t\t}");
classText.AppendLine($"\t\t");
classText.AppendLine("\t}");
classText.AppendLine("}");
}
internal override void GenerateFilePerTable(SQLTable table)
{
throw new NotImplementedException();
}
