public override string PagingBuild(ref PartedSql partedSql, object args, long skip, long take)
{
if (string.IsNullOrEmpty(partedSql.OrderBy))
{
throw new InvalidOperationException("miss order by");
}
var hasDistinct = partedSql.Select.IndexOf("DISTINCT", StringComparison.OrdinalIgnoreCase) == 0;
var select = "SELECT";
if (hasDistinct)
{
partedSql.Select = partedSql.Select.Substring("DISTINCT".Length);
select = "SELECT DISTINCT";
}
if (skip <= 0)
{
var sbSql = StringBuilderCache.Allocate().AppendFormat("{0} TOP {1} {2}", select, take, partedSql.Select)
.Append(" FROM ").Append(partedSql.Body).Append(" order by ").Append(partedSql.OrderBy);
return(StringBuilderCache.ReturnAndFree(sbSql));
}
else
{
var sbSql = StringBuilderCache.Allocate()
.AppendFormat("SELECT * FROM (SELECT {0}, ROW_NUMBER() OVER " +
"(order by {1}) As RowNum FROM {2}) AS RowConstrainedResult " +
"WHERE RowNum > {3} AND RowNum <= {4}",
partedSql.Select, partedSql.OrderBy, partedSql.Body, skip, skip + take);
return(StringBuilderCache.ReturnAndFree(sbSql));
}
}
public override string PagingBuild(ref PartedSql partedSql, object args, long skip, long take)
{
if (string.IsNullOrEmpty(partedSql.OrderBy))
{
throw new ArgumentException("miss order by");
}
return($"{partedSql.Raw} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY");
}
public virtual string PagingBuild(ref PartedSql partedSql, object args, long skip, long take)
{
var pageSql = $"{partedSql.Raw} LIMIT @{take} OFFSET @{skip}";
return(pageSql);
}