public void Methods_should_delegate_to_underlying_string_builder()
{
var sqlStringBuilder = new SqlStringBuilder();
sqlStringBuilder.Append("foo");
sqlStringBuilder.AppendLine("bar");
sqlStringBuilder.AppendLine();
Assert.Equal(10, sqlStringBuilder.Length);
}
public void Visit_DbScanExpression_appends_SQL_if_defining_query_is_not_set()
{
var builder = new SqlStringBuilder();
var insertTree = new DbInsertCommandTree();
new DmlSqlGenerator.ExpressionTranslator(
builder, insertTree, true, new SqlGenerator(SqlVersion.Sql10))
.Visit(CreateMockScanExpression(null).Object);
Assert.Equal("[Kontainer].[Binky]", builder.ToString());
}
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());
}
// <summary>
// Initialize a new expression translator populating the given string builder
// with command text. Command text builder and command tree must not be null.
// </summary>
// <param name="commandText"> Command text with which to populate commands </param>
// <param name="commandTree"> Command tree generating SQL </param>
// <param name="preserveMemberValues"> Indicates whether the translator should preserve member values while compiling t-SQL (only needed for server generation) </param>
internal ExpressionTranslator(
SqlStringBuilder commandText,
DbModificationCommandTree commandTree,
bool preserveMemberValues,
SqlGenerator sqlGenerator,
ICollection<EdmProperty> localVariableBindings = null,
bool createParameters = true)
{
DebugCheck.NotNull(commandText);
DebugCheck.NotNull(commandTree);
_commandText = commandText;
_commandTree = commandTree;
_sqlGenerator = sqlGenerator;
_localVariableBindings = localVariableBindings;
_parameters = new List<SqlParameter>();
_memberValues = preserveMemberValues
? new Dictionary<EdmMember, SqlParameter>()
: null;
_createParameters = createParameters;
}
// <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 TimestampValue
// 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.
//
// On SQL Server 2005 and up, we have an additional syntax used for non integer return types:
//
// declare @generatedValues table(ID uniqueidentifier)
// insert dbo.MyTable
// output ID into @generated_values
// values (...);
// select ID
// from @generatedValues as g join dbo.MyTable as t on g.ID = t.ID
// where @@ROWCOUNT > 0;</code>
// </summary>
// <param name="commandText"> Builder containing command text </param>
// <param name="tree"> Modification command tree </param>
// <param name="tableType"> Type of table. </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>
internal static void GenerateReturningSql(
SqlStringBuilder commandText,
DbModificationCommandTree tree,
EntityType tableType,
ExpressionTranslator translator,
DbExpression returning,
bool useGeneratedValuesVariable)
{
// Nothing to do if there is no Returning expression
if (null == returning)
{
return;
}
// select
commandText.AppendKeyword("select ");
if (useGeneratedValuesVariable)
{
translator.PropertyAlias = "t";
}
returning.Accept(translator);
if (useGeneratedValuesVariable)
{
translator.PropertyAlias = null;
}
commandText.AppendLine();
if (useGeneratedValuesVariable)
{
// from @generated_keys
commandText.AppendKeyword("from ");
commandText.Append(GeneratedValuesVariableName);
commandText.AppendKeyword(" as ");
commandText.Append("g");
commandText.AppendKeyword(" join ");
tree.Target.Expression.Accept(translator);
commandText.AppendKeyword(" as ");
commandText.Append("t");
commandText.AppendKeyword(" on ");
var separator = string.Empty;
foreach (var keyMember in tableType.KeyMembers)
{
commandText.AppendKeyword(separator);
separator = " and ";
commandText.Append("g.");
var memberTSql = GenerateMemberTSql(keyMember);
commandText.Append(memberTSql);
commandText.Append(" = t.");
commandText.Append(memberTSql);
}
commandText.AppendLine();
commandText.AppendKeyword("where @@ROWCOUNT > 0");
}
else
{
// from
commandText.AppendKeyword("from ");
tree.Target.Expression.Accept(translator);
commandText.AppendLine();
// where
commandText.AppendKeyword("where @@ROWCOUNT > 0");
var table = ((DbScanExpression)tree.Target.Expression).Target;
var identity = false;
foreach (var keyMember in table.ElementType.KeyMembers)
{
commandText.AppendKeyword(" and ");
commandText.Append(GenerateMemberTSql(keyMember));
commandText.Append(" = ");
// retrieve member value sql. the translator remembers member values
// as it constructs the DML statement (which precedes the "returning"
// SQL)
SqlParameter value;
if (translator.MemberValues.TryGetValue(keyMember, out value))
{
commandText.Append(value.ParameterName);
}
else
{
// if no value is registered for the key member, it means it is an identity
// which can be retrieved using the scope_identity() function
if (identity)
{
// there can be only one server generated key
throw new NotSupportedException(Strings.Update_NotSupportedServerGenKey(table.Name));
}
if (!IsValidScopeIdentityColumnType(keyMember.TypeUsage))
{
throw new InvalidOperationException(
Strings.Update_NotSupportedIdentityType(
keyMember.Name, keyMember.TypeUsage.ToString()));
}
commandText.Append("scope_identity()");
identity = true;
}
}
}
}
internal static string GenerateUpdateSql(
DbUpdateCommandTree tree,
SqlGenerator sqlGenerator,
out List<SqlParameter> parameters,
bool generateReturningSql = true,
bool upperCaseKeywords = true)
{
const string dummySetParameter = "@p";
var commandText
= new SqlStringBuilder(CommandTextBuilderInitialCapacity)
{
UpperCaseKeywords = upperCaseKeywords
};
var translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, sqlGenerator);
if (tree.SetClauses.Count == 0)
{
commandText.AppendKeyword("declare ");
commandText.AppendLine(dummySetParameter + " int");
}
// 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. Introduce a fake set clause so that:
// - we acquire the appropriate locks
// - server-gen columns (e.g. timestamp) get recomputed
//
// We use the following pattern:
//
// update SomeTable
// set @p = 0
// where ...
commandText.Append(dummySetParameter + " = 0");
}
commandText.AppendLine();
// where c1 = ..., c2 = ...
commandText.AppendKeyword("where ");
tree.Predicate.Accept(translator);
commandText.AppendLine();
if (generateReturningSql)
{
GenerateReturningSql(commandText, tree, null, translator, tree.Returning, false);
}
parameters = translator.Parameters;
return commandText.ToString();
}
internal static string GenerateInsertSql(
DbInsertCommandTree tree,
SqlGenerator sqlGenerator,
out List<SqlParameter> parameters,
bool generateReturningSql = true,
bool upperCaseKeywords = true,
bool createParameters = true)
{
var commandText
= new SqlStringBuilder(CommandTextBuilderInitialCapacity)
{
UpperCaseKeywords = upperCaseKeywords
};
var translator
= new ExpressionTranslator(
commandText,
tree,
null != tree.Returning,
sqlGenerator,
createParameters: createParameters);
var useGeneratedValuesVariable = UseGeneratedValuesVariable(tree, sqlGenerator.SqlVersion);
var tableType = (EntityType)((DbScanExpression)tree.Target.Expression).Target.ElementType;
if (useGeneratedValuesVariable)
{
// manufacture the variable, e.g. "declare @generated_values table(id uniqueidentifier)"
commandText
.AppendKeyword("declare ")
.Append(GeneratedValuesVariableName)
.Append(" table(");
var first = true;
foreach (var column in tableType.KeyMembers)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
commandText
.Append(GenerateMemberTSql(column))
.Append(" ")
.Append(GetVariableType(sqlGenerator, column));
Facet collationFacet;
if (column.TypeUsage.Facets.TryGetValue(DbProviderManifest.CollationFacetName, false, out collationFacet))
{
var collation = collationFacet.Value as string;
if (!string.IsNullOrEmpty(collation))
{
commandText.AppendKeyword(" collate ").Append(collation);
}
}
}
Debug.Assert(!first, "if useGeneratedValuesVariable is true, it implies some columns do not have values");
commandText.AppendLine(")");
}
// 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(")");
}
else
{
commandText.AppendLine();
}
if (useGeneratedValuesVariable)
{
// output inserted.id into @generated_values
commandText.AppendKeyword("output ");
var first = true;
foreach (var column in tableType.KeyMembers)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
commandText.Append("inserted.");
commandText.Append(GenerateMemberTSql(column));
}
commandText
.AppendKeyword(" into ")
.AppendLine(GeneratedValuesVariableName);
}
if (0 < tree.SetClauses.Count)
{
// values c1, c2, ...
var 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
commandText.AppendKeyword("default values");
commandText.AppendLine();
}
if (generateReturningSql)
{
GenerateReturningSql(commandText, tree, tableType, translator, tree.Returning, useGeneratedValuesVariable);
}
parameters = translator.Parameters;
return commandText.ToString();
}
internal static string GenerateDeleteSql(
DbDeleteCommandTree tree,
SqlGenerator sqlGenerator,
out List<SqlParameter> parameters,
bool upperCaseKeywords = true,
bool createParameters = true)
{
var commandText
= new SqlStringBuilder(CommandTextBuilderInitialCapacity)
{
UpperCaseKeywords = upperCaseKeywords
};
var translator
= new ExpressionTranslator(
commandText,
tree,
false,
sqlGenerator,
createParameters: 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);
parameters = translator.Parameters;
return commandText.ToString();
}
private string IntroduceRequiredLocalVariables(EntityType entityType, DbInsertCommandTree commandTree)
{
DebugCheck.NotNull(entityType);
DebugCheck.NotNull(commandTree);
var storeGeneratedKeys
= entityType
.KeyProperties
.Where(p => p.IsStoreGeneratedIdentity)
.ToList();
var sql = new SqlStringBuilder { UpperCaseKeywords = true };
if (storeGeneratedKeys.Any())
{
foreach (var keyProperty in storeGeneratedKeys)
{
sql.Append(sql.Length == 0 ? "DECLARE " : ", ");
sql.Append("@");
sql.Append(keyProperty.Name);
sql.Append(" ");
sql.Append(DmlSqlGenerator.GetVariableType(_sqlGenerator, keyProperty));
}
sql.AppendLine();
var translator
= new DmlSqlGenerator.ExpressionTranslator(
sql,
commandTree,
true,
_sqlGenerator,
entityType.KeyProperties);
DmlSqlGenerator.GenerateReturningSql(
sql,
commandTree,
entityType,
translator,
commandTree.Returning,
DmlSqlGenerator.UseGeneratedValuesVariable(commandTree, _sqlGenerator.SqlVersion));
sql.AppendLine();
sql.AppendLine();
}
return sql.ToString();
}
