/// <summary> /// Writes parts to a builder based on a specified culture. /// </summary> /// <param name="builder">A string builder which aggregates a final SQL query.</param> /// <param name="parts">A database query parts.</param> /// <param name="builderCulture">A string builder culture.</param> protected override void BuildCore(StringBuilder builder, DatabaseQueryParts parts, CultureInfo builderCulture) { ThrowIf.Null(builder, "builder"); ThrowIf.Null(parts, "parts"); this.BuildColumnsPart(builder, parts); this.BuildFromJoinPart(builder, parts); this.BuildWherePart(builder, parts); this.BuildOrderByPart(builder, parts.OrderBy); bool hasTop = parts.Top.HasValue; if (hasTop) { builder.AppendFormat(builderCulture, "LIMIT {0} ", parts.Top); } if (parts.Skip.HasValue) { if (!hasTop) { builder.Append("LIMIT -1 "); } builder.AppendFormat(builderCulture, "OFFSET {0} ", parts.Skip); } }
/// <summary> /// Writes parts to a builder based on a specified culture. /// </summary> /// <param name="builder">A string builder which aggregates a final SQL query.</param> /// <param name="parts">A database query parts.</param> /// <param name="builderCulture">A string builder culture.</param> protected override void BuildCore(StringBuilder builder, DatabaseQueryParts parts, CultureInfo builderCulture) { ThrowIf.Null(builder, "builder"); ThrowIf.Null(parts, "parts"); if (parts.Skip.HasValue && parts.Skip != 0 && string.IsNullOrWhiteSpace(parts.OrderBy)) { var errorMessage = string.Format( "Predefined sort order is mandatory when skip is defined for {0}.{1}", parts.Schema ?? string.Empty, parts.FromSource); RetailLogger.Log.CrtDataAccessSqlServerError("SqlServerDatabaseQueryBuilder::BuildCore", errorMessage); throw new DatabaseException(errorMessage); } // Use "TOP" sql-part only if nothing needed to skip, otherwise top goes to fetch part of the "ORDER BY" statement. if (parts.Top.HasValue && (!parts.Skip.HasValue || parts.Skip == 0)) { builder.AppendFormat(builderCulture, "TOP ({0}) ", parts.Top); } this.BuildColumnsPart(builder, parts); this.BuildFromJoinPart(builder, parts); this.BuildWherePart(builder, parts); this.BuildOrderByPart(builder, parts.OrderBy); if (!string.IsNullOrWhiteSpace(parts.OrderBy)) { if (parts.Skip.HasValue && parts.Skip.Value != 0) { builder.AppendFormat(builderCulture, "OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY ", parts.Skip, parts.Top); } } if (!parts.Hints.IsNullOrEmpty()) { builder.AppendFormat(builderCulture, "OPTION ({0}) ", string.Join(", ", parts.Hints)); } }