/// <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));
}
}
