private static PagingQuery CreateQueryFor(string sql)
{
// Reduce unnecessary work here...
var query = new PagingQuery();
string @select, @orderBy, sqlCount;
if (!SplitSqlForPaging(sql, out sqlCount, out @select, out @orderBy))
{
throw new Exception("Unable to parse SQL statement for paged query");
}
query.CountQuery = sqlCount;
switch (PagingExtensions.Dialect)
{
case SqlDialect.SqlServer:
RowOverPageQuery(@select, @orderBy, query);
break;
case SqlDialect.MySql:
case SqlDialect.Sqlite:
default:
query.PageQuery = string.Concat(sql, " LIMIT @PageSize OFFSET @ItemIndex");
break;
}
return(query);
}
private static void RowOverPageQuery(string @select, string @orderBy, PagingQuery query)
{
const string inner = "page_inner";
@select = SqlRegex.OrderBy.Replace(@select, "").Trim();
if (SqlRegex.Distinct.IsMatch(@select))
{
@select = string.Concat(inner, ".* FROM (SELECT ", @select, ") ", inner);
}
@orderBy = @orderBy ?? "ORDER BY (SELECT NULL)";
query.PageQuery = string.Concat(
"SELECT * FROM (SELECT ROW_NUMBER() OVER (", @orderBy,
") [RowNumber], ", @select,
") [PageData] WHERE [RowNumber] > @RowStart AND [RowNumber] <= @RowEnd"
);
}
private static void RowOverPageQuery(string @select, string @orderBy, PagingQuery query)
{
const string inner = "page_inner";
@select = SqlRegex.OrderBy.Replace(@select, "").Trim();
if (SqlRegex.Distinct.IsMatch(@select))
{
@select = string.Concat(inner, ".* FROM (SELECT ", @select, ") ", inner);
}
@orderBy = @orderBy ?? "ORDER BY (SELECT NULL)";
query.PageQuery = string.Concat(
"SELECT * FROM (SELECT ROW_NUMBER() OVER (", @orderBy,
") [RowNumber], ", @select,
") [PageData] WHERE [RowNumber] > @RowStart AND [RowNumber] <= @RowEnd"
);
}
private static PagingQuery CreateQueryFor(string sql)
{
// Reduce unnecessary work here...
var query = new PagingQuery();
string @select, @orderBy, sqlCount;
if (!SplitSqlForPaging(sql, out sqlCount, out @select, out @orderBy))
{
throw new Exception("Unable to parse SQL statement for paged query");
}
query.CountQuery = sqlCount;
switch (PagingExtensions.Dialect)
{
case SqlDialect.SqlServer:
RowOverPageQuery(@select, @orderBy, query);
break;
case SqlDialect.MySql:
case SqlDialect.Sqlite:
default:
query.PageQuery = string.Concat(sql, " LIMIT @PageSize OFFSET @ItemIndex");
break;
}
return query;
}
