/// <summary>
/// 生成 SQL 分页查询语句
/// </summary>
/// <param name="skip">要跳过记录数量</param>
/// <param name="take">要获取记录数</param>
/// <param name="parts">原始 SQL 查询语句被解析后的组成部分</param>
/// <param name="args">SQL 查询用的参数</param>
/// <returns>最终可以执行的 SQL 分页查询语句</returns>
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, object namedParameters)
{
parts.sqlOrderBy = string.IsNullOrEmpty(parts.sqlOrderBy) ? null : OrderByAlias.Replace(parts.sqlOrderBy, "$1");
var sqlPage = string.Format("SELECT {4} FROM (SELECT ROW_NUMBER() OVER ({0}) poco_rn, poco_base.* \nFROM ( \n{1}) poco_base ) poco_paged \nWHERE poco_rn > {2} AND poco_rn <= {3} \nORDER BY poco_rn",
parts.sqlOrderBy ?? "ORDER BY (SELECT NULL /*poco_dual*/)", parts.sqlUnordered, skip, take, parts.sqlColumns);
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));
}
/// <summary>
/// 生成 SQL 分页查询语句
/// </summary>
/// <param name="skip">要跳过记录数量</param>
/// <param name="take">要获取记录数</param>
/// <param name="parts">原始 SQL 查询语句被解析后的组成部分</param>
/// <param name="args">SQL 查询用的参数</param>
/// <returns>最终可以执行的 SQL 分页查询语句</returns>
/// <exception cref="System.Exception">Query must alias '*' when performing a paged query.\neg. select t.* from table t order by t.id</exception>
public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args)
{
if (parts.sqlSelectRemoved.StartsWith("*"))
{
throw new Exception(Localization.Resource.PageQueryAliasException);
}
// Same deal as SQL Server
return(Singleton <SqlServerDatabaseType> .Instance.BuildPageQuery(skip, take, parts, ref args));
}
public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args)
{
parts.sqlOrderBy = string.IsNullOrEmpty(parts.sqlOrderBy) ? null : OrderByAlias.Replace(parts.sqlOrderBy, "$1");
string sqlPage = string.Format("SELECT {4} FROM (SELECT poco_base.*, ROW_NUMBER() OVER ({0}) poco_rn \nFROM ( \n{1}) poco_base ) poco_paged \nWHERE poco_rn > @{2} AND poco_rn <= @{3} \nORDER BY poco_rn",
parts.sqlOrderBy ?? "ORDER BY (SELECT NULL /*poco_dual*/)", parts.sqlUnordered, args.Length, args.Length + 1, parts.sqlColumns);
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)
{
parts.sqlOrderBy = string.IsNullOrEmpty(parts.sqlOrderBy) ? null : OrderByAlias.Replace(parts.sqlOrderBy, "$1$3");
var sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, peta_base.* FROM ({1}) peta_base) peta_paged WHERE peta_rn>{2} AND peta_rn<={3} ORDER BY peta_rn",
parts.sqlOrderBy ?? "ORDER BY (SELECT NULL /*poco_dual*/)", parts.sqlUnordered, skip, skip + take);
args = args.Concat(new object[] { skip, skip + take }).ToArray();
return(sqlPage);
}
/// <summary>
/// 生成 SQL 分页查询语句
/// </summary>
/// <param name="skip">要跳过记录数量</param>
/// <param name="take">要获取记录数</param>
/// <param name="parts">原始 SQL 查询语句被解析后的组成部分</param>
/// <param name="args">SQL 查询用的参数</param>
/// <returns>最终可以执行的 SQL 分页查询语句</returns>
/// <exception cref="System.Exception">Query must alias '*' when performing a paged query.\neg. select t.* from table t order by t.id</exception>
public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args)
{
if (parts.sqlSelectRemoved.StartsWith("*"))
{
throw new Exception("执行分页查询时不能直接使用“*”来获取所有字段，必需加上表别名。例如：select t.* from table t order by t.id");
}
// Same deal as SQL Server
return(Singleton <SqlServerDatabaseType> .Instance.BuildPageQuery(skip, take, parts, ref args));
}
public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args)
{
parts.sqlSelectRemoved = PagingHelper.rxOrderBy.Replace(parts.sqlSelectRemoved, "", 1);
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 ?? "ORDER BY (SELECT NULL /*poco_dual*/)", 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)
{
var sqlPage = string.Format("{0}{1}\nOFFSET @{2} ROWS FETCH NEXT @{3} ROWS ONLY",
parts.sql,
!HasTopLevelOrderBy(parts.sql) ? "\nORDER BY (SELECT NULL)" : string.Empty,
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)
{
if (!parts.sql.ToLower().Contains("order by"))
{
throw new Exception("SQL Server 2012 Paging via OFFSET requires an ORDER BY statement.");
}
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);
}
/// <summary>
/// 生成 SQL 分页查询语句
/// </summary>
/// <param name="skip">要跳过记录数量</param>
/// <param name="take">要获取记录数</param>
/// <param name="parts">原始 SQL 查询语句被解析后的组成部分</param>
/// <param name="args">SQL 查询用的参数</param>
/// <returns>最终可以执行的 SQL 分页查询语句</returns>
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)
{
StringBuilder sql = new StringBuilder("SELECT ");
if (take > 0)
{
sql.AppendFormat("FIRST {0} ", take);
}
if (skip > 0)
{
sql.AppendFormat("SKIP {0} ", skip);
}
sql.Append(parts.sqlSelectRemoved);
return(sql.ToString());
}
public override string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts, ref object[] args)
{
return(PagingHelper.BuildPaging(skip, take, parts, ref args));
}
