public string GetProcedureName(StoredProcedureKind procedureKind, string prefix, string tableName, bool uppercaseName)
{
var result = $"{prefix}_{tableName}_{procedureKind}";
if (uppercaseName)
{
result = result.ToUpper();
}
return(result);
}
public string GetHeader(StoredProcedureKind procedureKind, string prefix, string tableName, string schema, bool includeDrop, bool uppercaseName)
{
var result = new StringBuilder();
string fullName = GetProcedureName(procedureKind, prefix, tableName, uppercaseName);
if (includeDrop)
{
result.AppendLine($"IF EXISTS(SELECT * FROM sys.procedures WHERE name = '{fullName}')");
result.AppendLine("BEGIN");
result.AppendLine($"\tDROP PROCEDURE [{schema}].[{fullName}]");
result.AppendLine("END");
result.AppendLine("GO");
}
result.AppendLine($"CREATE PROCEDURE [{schema}].[{fullName}]");
return(result.ToString());
}
public string GetBody(StoredProcedureKind procedureKind, List <ColumnInfo> columns, string sortByColumns, string searchColumns)
{
var result = new StringBuilder();
result.AppendLine("(");
if (procedureKind == StoredProcedureKind.Insert)
{
result.Append(GetInsertStatementParameters(columns));
}
else if (procedureKind == StoredProcedureKind.Update)
{
result.Append(GetUpdateStatementParameters(columns));
}
else if (procedureKind == StoredProcedureKind.Delete)
{
result.Append(GetDeleteStatementParameters(columns));
}
else if (procedureKind == StoredProcedureKind.Search)
{
result.Append(GetSearchParameters());
}
else if (procedureKind == StoredProcedureKind.GetById)
{
result.Append(GetGetByIdStatementParameters(columns));
}
else if (procedureKind == StoredProcedureKind.GetByParentId)
{
result.Append(GetGetByParentIdStatementParameters(columns));
}
result.AppendLine(")");
result.AppendLine("AS");
var firstColumn = columns.First();
if (procedureKind == StoredProcedureKind.Insert)
{
result.AppendLine(GetInsertStatementOutputVariableDeclaration(columns));
result.Append($"INSERT INTO [{firstColumn.SchemaName}].[{firstColumn.TableName}]");
result.AppendLine("(");
result.Append(GetInsertStatementColumnsList(columns));
result.AppendLine(")");
result.AppendLine(GetInsertStatementOutput(columns));
result.AppendLine("VALUES");
result.AppendLine("(");
result.Append(GetInsertStatementValues(columns));
result.AppendLine(")");
if (columns.Any(c => c.IsRowVersion || c.IsCustomRowVersion || c.IdentityColumn))
{
result.AppendLine("SELECT * FROM @OUTPUTTABLE");
}
}
else if (procedureKind == StoredProcedureKind.Update)
{
result.AppendLine(GetInsertStatementOutputVariableDeclaration(columns));
result.AppendLine($"UPDATE [{firstColumn.SchemaName}].[{firstColumn.TableName}] SET");
result.Append(GetUpdateStatementColumnsList(columns));
result.AppendLine(GetInsertStatementOutput(columns));
result.AppendLine("WHERE");
result.AppendLine(GetUpdateStatementWhere(columns));
if (columns.Any(c => c.IsRowVersion || c.IsCustomRowVersion || c.IdentityColumn))
{
result.AppendLine("SELECT * FROM @OUTPUTTABLE");
}
}
else if (procedureKind == StoredProcedureKind.Delete)
{
result.AppendLine($"DELETE FROM [{firstColumn.SchemaName}].[{firstColumn.TableName}]");
result.AppendLine("WHERE");
result.AppendLine(GetUpdateStatementWhere(columns));
}
else if (procedureKind == StoredProcedureKind.Search)
{
result.AppendLine(GetSearchRawData(columns, sortByColumns, searchColumns));
result.AppendLine(GetSearchFinalSelect());
}
else if (procedureKind == StoredProcedureKind.GetById)
{
result.AppendLine(@"BEGIN");
result.AppendLine(@"SET NOCOUNT ON;");
result.AppendLine(selectQueryString);
result.Append(GetSelectStatementColumnsList(columns));
result.AppendLine($"{fromQueryString}[{firstColumn.SchemaName}].[{firstColumn.TableName}]");
result.AppendLine(whereQueryString);
result.AppendLine(GetGetByIdStatementWhere(columns));
result.AppendLine(@"END");
}
else if (procedureKind == StoredProcedureKind.GetByParentId)
{
result.AppendLine(@"BEGIN");
result.AppendLine(@"SET NOCOUNT ON;");
result.AppendLine(selectQueryString);
result.Append(GetSelectStatementColumnsList(columns));
result.AppendLine($"{fromQueryString}[{firstColumn.SchemaName}].[{firstColumn.TableName}]");
result.AppendLine(whereQueryString);
result.AppendLine(GetGetByParentIdStatementWhere(columns));
result.AppendLine(@"END");
}
return(result.ToString());
}
public string FileName(StoredProcedureKind kind, bool uppercaseName)
{
return(Path.Combine(Environment.CurrentDirectory, storedProcedureGenerator.GetProcedureName(kind, Prefix, Table, uppercaseName) + ".sql"));
}
