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));
}
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 GenerateInsertStoredProcedure(Table table, QuoteType quoteType = QuoteType.NotQuoted)
{
Database database = table.Parent;
string storedProcedureName = string.Concat("Insert", table.Name);
List <ColumnReferenceExpression> columns = new List <ColumnReferenceExpression>();
List <ScalarExpression> scalarExpressions = new List <ScalarExpression>();
List <RowValue> rowValues = new List <RowValue>();
RowValue rowValue = ScriptFactory.RowValue();
rowValues.Add(rowValue);
List <ProcedureParameter> parameters = new List <ProcedureParameter>();
foreach (var column in table.Columns)
{
if (column.IsPrimaryKey())
{
continue;
}
scalarExpressions.Add(
ScriptFactory.IdentifierLiteral(
CreateProcedureParameterVariableName(column), QuoteType.NotQuoted));
columns.Add(
ScriptFactory.ColumnReferenceExpression(
ScriptFactory.MultiPartIdentifier(
ScriptFactory.Identifier(column.Name, quoteType))));
parameters.Add(
ScriptFactory.ProcedureParameter(CreateProcedureParameterVariableName(column), false,
GenerateDataTypeReference(SqlDataType.Parse(column.DataType))));
}
rowValue.ColumnValues.AddRange(scalarExpressions);
StatementList statements = ScriptFactory.List(
ScriptFactory.PredicateSet(true, SetOptions.NoCount),
ScriptFactory.Insert(
ScriptFactory.InsertSpecification(
ScriptFactory.ValuesInsertSource(false, rowValues),
ScriptFactory.NamedTableReference(
GenerateSchemaObjectName(table, quoteType)),
null, null, null, InsertOption.Into, columns)),
ScriptFactory.Return(ScriptFactory.IntegerLiteral("1")));
return(ScriptFactory.CreateProcedure(false,
ScriptFactory.ProcedureReference(
GenerateStoredProcedureName(storedProcedureName, table, quoteType)),
ScriptFactory.List(
ScriptFactory.BeginEndBlock(statements)), null, null, parameters));
}
public CreateProcedureStatement GenerateReadStoredProcedure(Table table, QuoteType quoteType = QuoteType.NotQuoted)
{
var pkColumn = table.Indexes.SingleOrDefault(x => x.IsPrimary).Name;
var columnNames = GetColumnNamesWithoutPrimaryKey(table, pkColumn);
Database database = table.Parent;
string storedProcedureName = string.Concat("Read", table.Name);
StatementList statements = ScriptFactory.List(
ScriptFactory.Select(default(Identifier),
ScriptFactory.Query(
ScriptFactory.From(ScriptFactory.NamedTableReference(GenerateSchemaObjectName(table, quoteType))),
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, null));
}