/// <summary>
/// Builds an SQL query suitable for performing page based queries to the database
/// </summary>
/// <param name="skip">The number of rows that should be skipped by the query</param>
/// <param name="take">The number of rows that should be retruend by the query</param>
/// <param name="parts">The original SQL query after being parsed into it's component parts</param>
/// <param name="args">Arguments to any embedded parameters in the SQL query</param>
/// <returns>The final SQL query that should be executed.</returns>
public virtual string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args)
{
var sql = string.Format("{0}\nLIMIT @{1} OFFSET @{2}", parts.sql, args.Length, args.Length + 1);
args = args.Concat(new object[] { take, skip }).ToArray();
return sql;
}
public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args)
{
var sqlPage = string.Format("{0}\nOFFSET @{1} ROWS FETCH NEXT @{2} ROWS ONLY", parts.sql, args.Length, args.Length + 1);
args = args.Concat(new object[] { skip, take }).ToArray();
return sqlPage;
}
public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args)
{
parts.sqlSelectRemoved = PagingHelper.rxOrderBy.Replace(parts.sqlSelectRemoved, "", 1);
if (PagingHelper.rxDistinct.IsMatch(parts.sqlSelectRemoved))
{
parts.sqlSelectRemoved = "peta_inner.* FROM (SELECT " + parts.sqlSelectRemoved + ") peta_inner";
}
var sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>@{2} AND peta_rn<=@{3}",
parts.sqlOrderBy == null ? "ORDER BY (SELECT NULL)" : parts.sqlOrderBy, parts.sqlSelectRemoved, args.Length, args.Length + 1);
args = args.Concat(new object[] { skip, skip + take }).ToArray();
return sqlPage;
}
public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args)
{
if (parts.sqlSelectRemoved.StartsWith("*"))
throw new Exception("Query must alias '*' when performing a paged query.\neg. select t.* from table t order by t.id");
// Same deal as SQL Server
return Singleton<SqlServerDatabaseType>.Instance.BuildPageQuery(skip, take, parts, ref args);
}