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")); }