// <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 MyTable
// where IdentityValue = @@identity
//
// NOTE: not scope_identity() because we don't support it.</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(
SqlStringBuilder commandText,
DbModificationCommandTree tree,
ExpressionTranslator translator,
DbExpression returning)
{
if (returning != null)
{
commandText.AppendKeyword("select ");
returning.Accept(translator);
commandText.AppendLine();
commandText.AppendKeyword("from ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
commandText.AppendKeyword("where ");
var target = ((DbScanExpression)tree.Target.Expression).Target;
var flag = false;
var isFirst = true;
foreach (var member in target.ElementType.KeyMembers)
{
if (!isFirst)
{
commandText.AppendKeyword(" and ");
}
else
{
isFirst = false;
}
commandText.Append(GenerateMemberTSql(member));
commandText.Append(" = ");
flag = HandleIdentity(commandText, translator, member, flag, target);
}
}
}
public override void Visit(DbIsNullExpression expression)
{
Check.NotNull(expression, "expression");
expression.Argument.Accept(this);
_commandText.AppendKeyword(" is null");
}
public void AppendKeyWord_should_case_based_on_property()
{
var sqlStringBuilder = new SqlStringBuilder();
sqlStringBuilder.AppendKeyword("select");
Assert.Equal("select", sqlStringBuilder.ToString());
sqlStringBuilder.UpperCaseKeywords = true;
sqlStringBuilder.AppendKeyword(" from");
Assert.Equal("select FROM", sqlStringBuilder.ToString());
}
internal static string[] GenerateDeleteSql(
DbDeleteCommandTree tree,
out List <DbParameter> parameters,
bool isLocalProvider,
bool upperCaseKeywords = true,
bool createParameters = true)
{
var commandTexts = new List <String>();
var commandText
= new SqlStringBuilder(CommandTextBuilderInitialCapacity)
{
UpperCaseKeywords = upperCaseKeywords
};
var translator
= new ExpressionTranslator(
commandText,
tree,
false,
isLocalProvider,
createParameters);
// delete [schemaName].[tableName]
commandText.AppendKeyword("delete ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where c1 = ... AND c2 = ...
commandText.AppendKeyword("where ");
tree.Predicate.Accept(translator);
commandTexts.Add(commandText.ToString());
commandText.Length = 0;
parameters = translator.Parameters;
return(commandTexts.ToArray());
}
internal static string[] GenerateUpdateSql(
DbUpdateCommandTree tree,
out List <DbParameter> parameters,
bool isLocalProvider,
bool upperCaseKeywords = true)
{
var commandTexts = new List <String>();
var commandText
= new SqlStringBuilder(CommandTextBuilderInitialCapacity)
{
UpperCaseKeywords = upperCaseKeywords
};
var translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, isLocalProvider);
// update [schemaName].[tableName]
commandText.AppendKeyword("update ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// set c1 = ..., c2 = ..., ...
var first = true;
commandText.AppendKeyword("set ");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
commandText.Append(" = ");
setClause.Value.Accept(translator);
}
if (first)
{
// If first is still true, it indicates there were no set
// clauses.
// - we acquire the appropriate locks
// - server-gen columns (e.g. timestamp) get recomputed
//
// Fix #13533 : A fake update DML updating some column item
// with the same value as before to acquire the lock on the table
// while updating some columns in another table. This happens when
// both the table are dependent on an entity and the members of entity
// which is mapped to one table is being updated and the other table
// needs to be locked for consistency.
string updatableColumnName;
if (GetUpdatableColumn(tree, out updatableColumnName))
{
commandText.Append("[");
commandText.Append(CommonUtils.EscapeSquareBraceNames(updatableColumnName));
commandText.Append("] ");
commandText.Append(" = ");
commandText.Append("[");
commandText.Append(CommonUtils.EscapeSquareBraceNames(updatableColumnName));
commandText.Append("] ");
}
else
{
// Throw some meaningful error
throw ADP1.Update(
EntityRes.GetString(EntityRes.UpdateStatementCannotBeGeneratedForAcquiringLock),
null);
}
}
commandText.AppendLine();
// where c1 = ..., c2 = ...
commandText.AppendKeyword("where ");
tree.Predicate.Accept(translator);
commandText.AppendLine();
commandTexts.Add(commandText.ToString());
commandText.Length = 0;
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
if (!String.IsNullOrEmpty(commandText.ToString()))
{
commandTexts.Add(commandText.ToString());
}
parameters = translator.Parameters;
return(commandTexts.ToArray());
}
internal static string[] GenerateInsertSql(
DbInsertCommandTree tree,
out List <DbParameter> parameters,
bool isLocalProvider,
bool upperCaseKeywords = true,
bool createParameters = true)
{
var commandTexts = new List <String>();
var commandText
= new SqlStringBuilder(CommandTextBuilderInitialCapacity)
{
UpperCaseKeywords = upperCaseKeywords
};
var translator
= new ExpressionTranslator(
commandText,
tree,
null != tree.Returning,
isLocalProvider,
createParameters);
// insert [schemaName].[tableName]
commandText.AppendKeyword("insert ");
tree.Target.Expression.Accept(translator);
if (0 < tree.SetClauses.Count)
{
// (c1, c2, c3, ...)
commandText.Append("(");
var first = true;
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.AppendKeyword("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(")");
}
else
{
// default values
throw ADP1.NotSupported("Default values not supported");
}
commandTexts.Add(commandText.ToString());
commandText.Length = 0;
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
if (!String.IsNullOrEmpty(commandText.ToString()))
{
commandTexts.Add(commandText.ToString());
}
parameters = translator.Parameters;
return(commandTexts.ToArray());
}
internal static string[] GenerateUpdateSql(
DbUpdateCommandTree tree,
out List<DbParameter> parameters,
bool isLocalProvider,
bool upperCaseKeywords = true)
{
var commandTexts = new List<String>();
var commandText
= new SqlStringBuilder(CommandTextBuilderInitialCapacity)
{
UpperCaseKeywords = upperCaseKeywords
};
var translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, isLocalProvider);
// update [schemaName].[tableName]
commandText.AppendKeyword("update ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// set c1 = ..., c2 = ..., ...
var first = true;
commandText.AppendKeyword("set ");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
commandText.Append(" = ");
setClause.Value.Accept(translator);
}
if (first)
{
// If first is still true, it indicates there were no set
// clauses.
// - we acquire the appropriate locks
// - server-gen columns (e.g. timestamp) get recomputed
//
// Fix #13533 : A fake update DML updating some column item
// with the same value as before to acquire the lock on the table
// while updating some columns in another table. This happens when
// both the table are dependent on an entity and the members of entity
// which is mapped to one table is being updated and the other table
// needs to be locked for consistency.
string updatableColumnName;
if (GetUpdatableColumn(tree, out updatableColumnName))
{
commandText.Append("[");
commandText.Append(CommonUtils.EscapeSquareBraceNames(updatableColumnName));
commandText.Append("] ");
commandText.Append(" = ");
commandText.Append("[");
commandText.Append(CommonUtils.EscapeSquareBraceNames(updatableColumnName));
commandText.Append("] ");
}
else
{
// Throw some meaningful error
throw ADP1.Update(
EntityRes.GetString(EntityRes.UpdateStatementCannotBeGeneratedForAcquiringLock),
null);
}
}
commandText.AppendLine();
// where c1 = ..., c2 = ...
commandText.AppendKeyword("where ");
tree.Predicate.Accept(translator);
commandText.AppendLine();
commandTexts.Add(commandText.ToString());
commandText.Length = 0;
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
if (!String.IsNullOrEmpty(commandText.ToString()))
{
commandTexts.Add(commandText.ToString());
}
parameters = translator.Parameters;
return commandTexts.ToArray();
}
// <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 MyTable
// where IdentityValue = @@identity
//
// NOTE: not scope_identity() because we don't support it.</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(
SqlStringBuilder commandText,
DbModificationCommandTree tree,
ExpressionTranslator translator,
DbExpression returning)
{
if (returning != null)
{
commandText.AppendKeyword("select ");
returning.Accept(translator);
commandText.AppendLine();
commandText.AppendKeyword("from ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
commandText.AppendKeyword("where ");
var target = ((DbScanExpression)tree.Target.Expression).Target;
var flag = false;
var isFirst = true;
foreach (var member in target.ElementType.KeyMembers)
{
if (!isFirst)
{
commandText.AppendKeyword(" and ");
}
else
{
isFirst = false;
}
commandText.Append(GenerateMemberTSql(member));
commandText.Append(" = ");
flag = HandleIdentity(commandText, translator, member, flag, target);
}
}
}
internal static string[] GenerateInsertSql(
DbInsertCommandTree tree,
out List<DbParameter> parameters,
bool isLocalProvider,
bool upperCaseKeywords = true,
bool createParameters = true)
{
var commandTexts = new List<String>();
var commandText
= new SqlStringBuilder(CommandTextBuilderInitialCapacity)
{
UpperCaseKeywords = upperCaseKeywords
};
var translator
= new ExpressionTranslator(
commandText,
tree,
null != tree.Returning,
isLocalProvider,
createParameters);
// insert [schemaName].[tableName]
commandText.AppendKeyword("insert ");
tree.Target.Expression.Accept(translator);
if (0 < tree.SetClauses.Count)
{
// (c1, c2, c3, ...)
commandText.Append("(");
var first = true;
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.AppendKeyword("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(")");
}
else
{
// default values
throw ADP1.NotSupported("Default values not supported");
}
commandTexts.Add(commandText.ToString());
commandText.Length = 0;
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
if (!String.IsNullOrEmpty(commandText.ToString()))
{
commandTexts.Add(commandText.ToString());
}
parameters = translator.Parameters;
return commandTexts.ToArray();
}
internal static string[] GenerateDeleteSql(
DbDeleteCommandTree tree,
out List<DbParameter> parameters,
bool isLocalProvider,
bool upperCaseKeywords = true,
bool createParameters = true)
{
var commandTexts = new List<String>();
var commandText
= new SqlStringBuilder(CommandTextBuilderInitialCapacity)
{
UpperCaseKeywords = upperCaseKeywords
};
var translator
= new ExpressionTranslator(
commandText,
tree,
false,
isLocalProvider,
createParameters);
// delete [schemaName].[tableName]
commandText.AppendKeyword("delete ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where c1 = ... AND c2 = ...
commandText.AppendKeyword("where ");
tree.Predicate.Accept(translator);
commandTexts.Add(commandText.ToString());
commandText.Length = 0;
parameters = translator.Parameters;
return commandTexts.ToArray();
}