internal static string GenerateUpdateSql(DbUpdateCommandTree tree, out List<DbParameter> parameters)
{
StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning);
bool first = true;
commandText.Append("UPDATE ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// set c1 = ..., c2 = ..., ...
commandText.Append("SET ");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
commandText.Append(" = ");
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
if (first)
{
// If first is still true, it indicates there were no set
// clauses. Introduce a fake set clause so that:
// - we acquire the appropriate locks
// - server-gen columns (e.g. timestamp) get recomputed
EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
// hope this column isn't indexed to not waste power
EdmMember someColumn = table.ElementType.Members.Last(x => !IsStoreGenerated(x));
commandText.AppendFormat("{0} = {0}", GenerateMemberSql(someColumn));
}
commandText.AppendLine();
// where c1 = ..., c2 = ...
commandText.Append("WHERE ");
tree.Predicate.Accept(translator);
commandText.AppendLine();
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
parameters = translator.Parameters;
return commandText.ToString();
}
internal static string GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters)
{
StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning);
bool first = true;
commandText.Append("INSERT INTO ");
tree.Target.Expression.Accept(translator);
// (c1, c2, c3, ...)
commandText.Append("(");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
// values c1, c2, ...
first = true;
commandText.Append("VALUES (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(")");
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
parameters = translator.Parameters;
return commandText.ToString();
}
internal static string GenerateDeleteSql(DbDeleteCommandTree tree, out List<DbParameter> parameters)
{
StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, false);
commandText.Append("DELETE FROM ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where c1 = ... AND c2 = ...
commandText.Append("WHERE ");
tree.Predicate.Accept(translator);
parameters = translator.Parameters;
return commandText.ToString();
}
/// <summary>
/// Generates SQL fragment returning server-generated values.
/// Requires: translator knows about member values so that we can figure out
/// how to construct the key predicate.
/// <code>
/// Sample SQL:
///
/// select IdentityValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and IdentityValue = scope_identity()
///
/// or
///
/// select TimestamptValue
/// from dbo.MyTable
/// where @@ROWCOUNT > 0 and Id = 1
///
/// Note that we filter on rowcount to ensure no rows are returned if no rows were modified.
/// </code>
/// </summary>
/// <param name="commandText">Builder containing command text</param>
/// <param name="tree">Modification command tree</param>
/// <param name="translator">Translator used to produce DML SQL statement
/// for the tree</param>
/// <param name="returning">Returning expression. If null, the method returns
/// immediately without producing a SELECT statement.</param>
private static void GenerateReturningSql(
StringBuilder commandText,
DbModificationCommandTree tree,
ExpressionTranslator translator,
DbExpression returning)
{
// Nothing to do if there is no Returning expression
if (returning == null)
{
return;
}
EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target;
IEnumerable<EdmMember> columnsToFetch =
table.ElementType.Members
.Where(m => IsStoreGenerated(m))
.Except((!(tree is DbInsertCommandTree) ? table.ElementType.KeyMembers : Enumerable.Empty<EdmMember>()));
StringBuilder startBlock = new StringBuilder();
string separator = string.Empty;
startBlock.AppendLine("EXECUTE BLOCK (");
separator = string.Empty;
foreach (FbParameter param in translator.Parameters)
{
startBlock.Append(separator);
startBlock.Append(param.ParameterName.Replace("@", string.Empty));
startBlock.Append(" ");
EdmMember member = translator.MemberValues.First(m => m.Value.Contains(param)).Key;
startBlock.Append(SqlGenerator.GetSqlPrimitiveType(member.TypeUsage));
if (param.FbDbType == FbDbType.VarChar || param.FbDbType == FbDbType.Char)
startBlock.Append(" CHARACTER SET UTF8");
startBlock.Append(" = ");
startBlock.Append(param.ParameterName);
separator = ", ";
}
startBlock.AppendLine(") ");
startBlock.AppendLine("RETURNS (");
separator = string.Empty;
foreach (EdmMember m in columnsToFetch)
{
startBlock.Append(separator);
startBlock.Append(GenerateMemberSql(m));
startBlock.Append(" ");
startBlock.Append(SqlGenerator.GetSqlPrimitiveType(m.TypeUsage));
separator = ", ";
}
startBlock.AppendLine(")");
startBlock.AppendLine("AS BEGIN");
string newCommand = ChangeParamsToPSQLParams(commandText.ToString(), translator.Parameters.Select(p => p.ParameterName).ToArray());
commandText.Remove(0, commandText.Length);
commandText.Insert(0, newCommand);
commandText.Insert(0, startBlock.ToString());
commandText.Append("RETURNING ");
separator = string.Empty;
foreach (EdmMember m in columnsToFetch)
{
commandText.Append(separator);
commandText.Append(GenerateMemberSql(m));
separator = ", ";
}
commandText.Append(" INTO ");
separator = string.Empty;
foreach (EdmMember m in columnsToFetch)
{
commandText.Append(separator);
commandText.Append(":" + GenerateMemberSql(m));
separator = ", ";
}
commandText.AppendLine(";");
commandText.AppendLine("SUSPEND;");
commandText.AppendLine("END");
}
