public CreateProcedureStatement GenerateUpdateStoredProcedure(Table table, QuoteType quoteType = QuoteType.NotQuoted)
{
var pkColumn = table.Indexes.SingleOrDefault(x => x.IsPrimary).Name;
var columnNames = GetColumnNamesWithoutPrimaryKey(table, pkColumn);
string storedProcedureName = string.Concat("Update", table.Name);
var parameters = CreateProcedureParameters(table, true);
var setClauses = new List <SetClause>();
string primaryKeyName = "";
foreach (var column in table.Columns)
{
if (column.IsPrimaryKey())
{
primaryKeyName = column.Name;
continue;
}
setClauses.Add(ScriptFactory.EqualsAssignmentSetClause(
ScriptFactory.ColumnReference(column.Name, quoteType),
ScriptFactory.IdentifierLiteral(
CreateProcedureParameterVariableName(column), QuoteType.NotQuoted)));
}
StatementList statements = ScriptFactory.List(
ScriptFactory.Update(ScriptFactory.UpdateSpecification(null,
ScriptFactory.Where(
ScriptFactory.BooleanEqualsComparison(ScriptFactory.IdentifierLiteral(primaryKeyName, quoteType), ScriptFactory.VariableReference(primaryKeyName))),
CreateNamedTableReference(table, quoteType), null, null, null, setClauses)));
return(ScriptFactory.CreateProcedure(false,
ScriptFactory.ProcedureReference(
GenerateStoredProcedureName(storedProcedureName, table, quoteType)),
ScriptFactory.List(
ScriptFactory.BeginEndBlock(statements)), null, null, parameters));
}
public CreateProcedureStatement GenerateFindStoredProcedure(Table table, QuoteType quoteType = QuoteType.NotQuoted)
{
Database database = table.Parent;
var pkColumn = GetPrimaryKeyColumnName(table);
var columnNames = GetColumnNamesWithoutPrimaryKey(table, pkColumn);
var pkDataType = table.Columns.Single(x => x.Name == pkColumn).DataType.ToSqlDataTypeOption();
string storedProcedureName = string.Concat("Find", table.Name);
var parameters = new List <ProcedureParameter>();
parameters.Add(ScriptFactory.ProcedureParameter(
ScriptFactory.Identifier(MakeVariableName(pkColumn)),
false,
ScriptFactory.SqlDataType(pkDataType)));
StatementList statements = ScriptFactory.List(
ScriptFactory.Select(
default(Identifier),
ScriptFactory.Query(
ScriptFactory.From(ScriptFactory.NamedTableReference(GenerateSchemaObjectName(table, quoteType))),
ScriptFactory.Where(ScriptFactory.BooleanEqualsComparison(ScriptFactory.IdentifierLiteral(pkColumn, quoteType), ScriptFactory.VariableReference(pkColumn))),
ScriptFactory.TopRowFilter(false, false, ScriptFactory.IntegerLiteral("1")),
ScriptFactory.ElementList(columnNames.Select(x => ScriptFactory.SelectScalarExpression(x, quoteType)))
)
)
);
return(ScriptFactory.CreateProcedure(false,
ScriptFactory.ProcedureReference(
GenerateStoredProcedureName(storedProcedureName, table, quoteType)),
ScriptFactory.List(
ScriptFactory.BeginEndBlock(statements)), null, null, parameters));
}
