// <summary> // Write out a SQL select statement as a string. // We have to // <list type="number"> // <item> // Check whether the aliases extents we use in this statement have // to be renamed. // We first create a list of all the aliases used by the outer extents. // For each of the FromExtents( or AllJoinExtents if it is non-null), // rename it if it collides with the previous list. // </item> // <item>Write each of the clauses (if it exists) as a string</item> // </list> // </summary> public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator) { #region Check if FROM aliases need to be renamed // Create a list of the aliases used by the outer extents // JoinSymbols have to be treated specially. List <string> outerExtentAliases = null; if ((null != outerExtents) && (0 < outerExtents.Count)) { foreach (var outerExtent in outerExtents.Keys) { var joinSymbol = outerExtent as JoinSymbol; if (joinSymbol != null) { foreach (var symbol in joinSymbol.FlattenedExtentList) { if (null == outerExtentAliases) { outerExtentAliases = new List <string>(); } outerExtentAliases.Add(symbol.NewName); } } else { if (null == outerExtentAliases) { outerExtentAliases = new List <string>(); } outerExtentAliases.Add(outerExtent.NewName); } } } // An then rename each of the FromExtents we have // If AllJoinExtents is non-null - it has precedence. // The new name is derived from the old name - we append an increasing int. var extentList = AllJoinExtents ?? fromExtents; if (null != extentList) { foreach (var fromAlias in extentList) { if ((null != outerExtentAliases) && outerExtentAliases.Contains(fromAlias.Name)) { var i = sqlGenerator.AllExtentNames[fromAlias.Name]; string newName; do { ++i; newName = fromAlias.Name + i.ToString(CultureInfo.InvariantCulture); }while (sqlGenerator.AllExtentNames.ContainsKey(newName)); sqlGenerator.AllExtentNames[fromAlias.Name] = i; fromAlias.NewName = newName; // Add extent to list of known names (although i is always incrementing, "prefix11" can // eventually collide with "prefix1" when it is extended) sqlGenerator.AllExtentNames[newName] = 0; } // Add the current alias to the list, so that the extents // that follow do not collide with me. if (null == outerExtentAliases) { outerExtentAliases = new List <string>(); } outerExtentAliases.Add(fromAlias.NewName); } } #endregion // Increase the indent, so that the Sql statement is nested by one tab. writer.Indent += 1; // ++ can be confusing in this context @select.WriteSql(writer, sqlGenerator); writer.WriteLine(); writer.Write("FROM "); From.WriteSql(writer, sqlGenerator); if ((null != @where) && !Where.IsEmpty) { writer.WriteLine(); writer.Write("WHERE "); Where.WriteSql(writer, sqlGenerator); } if ((null != groupBy) && !GroupBy.IsEmpty) { writer.WriteLine(); writer.Write("GROUP BY "); GroupBy.WriteSql(writer, sqlGenerator); } if ((null != orderBy) && !OrderBy.IsEmpty && (IsTopMost || Select.Top != null || Select.Skip != null)) { writer.WriteLine(); writer.Write("ORDER BY "); OrderBy.WriteSql(writer, sqlGenerator); } if (null != Select.Skip) { writer.WriteLine(); WriteOffsetFetch(writer, Select.Top, Select.Skip, sqlGenerator); // Write OFFSET, FETCH clause. } --writer.Indent; }
// This function generates OFFSET, FETCH clause from Top and Skip information. // Note that this should be used only when Skip is present. // private static void WriteOffsetFetch(SqlWriter writer, TopClause top, SkipClause skip, SqlGenerator sqlGenerator) { DebugCheck.NotNull(skip); skip.WriteSql(writer, sqlGenerator); if (top != null) { writer.Write("FETCH NEXT "); top.TopCount.WriteSql(writer, sqlGenerator); writer.Write(" ROWS ONLY "); } }
// Generates T-SQL describing a member // Requires: member must belong to an entity type (a safe requirement for DML // SQL gen, where we only access table columns) internal static string GenerateMemberTSql(EdmMember member) { return(SqlGenerator.QuoteIdentifier(member.Name)); }
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()); }
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator) { // Symbol pair should never be part of a SqlBuilder. Debug.Assert(false); }
public DmlFunctionSqlGenerator(SqlGenerator sqlGenerator) { DebugCheck.NotNull(sqlGenerator); _sqlGenerator = sqlGenerator; }