protected virtual void CreatePagingSql(ref PagingSqlParts parts, PagingInfo pagingInfo)
{
/*********************** 代码块解释 *********************************
*
* 注意，这个方法只支持不太复杂 SQL 的转换。
*
* 源格式：
* select ...... from ...... order by xxxx asc, yyyy desc
* 不限于以上格式，只要满足没有复杂的嵌套查询，最外层是一个 Select 和 From 语句即可。
*
* 目标格式：
* select * from (select ......, row_number() over(order by xxxx asc, yyyy desc) _rowNumber from ......) x where x._rowNumber<10 and x._rowNumber>5;
**********************************************************************/
var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1;
var endRow = startRow + pagingInfo.PageSize - 1;
var sql = new StringBuilder("SELECT * FROM (");
//在 Select 和 From 之间插入：
//,row_number() over(order by UPDDATETIME desc) rn
sql.AppendLine().Append(parts.Select)
.Append(", row_number() over(")
.Append(parts.OrderBy);
//query.AppendSqlOrder(res, this);
sql.Append(") dataRowNumber ").Append(parts.FromWhere)
.Append(") x").AppendLine()
.Append("WHERE x.dataRowNumber >= ").Append(startRow)
.Append(" AND x.dataRowNumber <= ").Append(endRow);
parts.PagingSql = sql.ToString();
}
/// <summary>
/// 创建MySql的分页语句
/// </summary>
/// <param name="parts">原始语句</param>
/// <param name="pagingInfo">分页对象</param>
protected override void CreatePagingSql(ref PagingSqlParts parts, PagingInfo pagingInfo)
{
var pageNumber = pagingInfo.PageNumber;
var pageSize = pagingInfo.PageSize;
var sql = new StringBuilder("SELECT * FROM (");
sql.AppendLine().Append(parts.RawSql)
.Append(") T ")
.Append("limit ").Append((pageNumber - 1) * pageSize)
.Append(",").Append(pageSize);
parts.PagingSql = sql.ToString();
}
private static PagingSqlParts ParsePagingSqlParts(string sql)
{
var fromIndex = FromRegex.Match(sql).Index;
var orderByIndex = sql.LastIndexOf("ORDER BY", StringComparison.OrdinalIgnoreCase);
if (orderByIndex < 0)
{
throw new InvalidProgramException("使用数据库分页时，Sql 语句中必须指定 OrderBy 语句。");
}
var parts = new PagingSqlParts();
parts.RawSql = sql;
parts.Select = sql.Substring(0, fromIndex).Trim();
parts.FromWhere = sql.Substring(fromIndex, orderByIndex - fromIndex).Trim();
parts.OrderBy = sql.Substring(orderByIndex).Trim();
return(parts);
}
/// <summary>
/// 如果需要统计，则生成统计语句进行查询。
/// </summary>
/// <param name="dba"></param>
/// <param name="pagingInfo"></param>
/// <param name="parts"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private static void QueryTotalCountIf(IDbAccesser dba, PagingInfo pagingInfo, PagingSqlParts parts, object[] parameters)
{
if (pagingInfo.IsNeedCount)
{
var pagingCountSql = "SELECT COUNT(0) " + parts.FromWhere;
//查询值。（由于所有参数都不会在 OrderBy、Select 语句中，所以把所有参数都传入。
var value = dba.QueryValue(pagingCountSql, parameters);
pagingInfo.TotalCount = Convert.ToInt32(value);
}
}
private static PagingSqlParts ParsePagingSqlParts(string sql)
{
var fromIndex = FromRegex.Match(sql).Index;
var orderByIndex = sql.LastIndexOf("ORDER BY", StringComparison.OrdinalIgnoreCase);
if (orderByIndex < 0) { throw new InvalidProgramException("使用数据库分页时，Sql 语句中必须指定 OrderBy 语句。"); }
var parts = new PagingSqlParts();
parts.RawSql = sql;
parts.Select = sql.Substring(0, fromIndex).Trim();
parts.FromWhere = sql.Substring(fromIndex, orderByIndex - fromIndex).Trim();
parts.OrderBy = sql.Substring(orderByIndex).Trim();
return parts;
}
protected virtual void CreatePagingSql(ref PagingSqlParts parts, PagingInfo pagingInfo)
{
/*********************** 代码块解释 *********************************
*
* 注意，这个方法只支持不太复杂 SQL 的转换。
*
* 源格式：
* select ...... from ...... order by xxxx asc, yyyy desc
* 不限于以上格式，只要满足没有复杂的嵌套查询，最外层是一个 Select 和 From 语句即可。
*
* 目标格式：
* select * from (select ......, row_number() over(order by xxxx asc, yyyy desc) _rowNumber from ......) x where x._rowNumber<10 and x._rowNumber>5;
**********************************************************************/
var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1;
var endRow = startRow + pagingInfo.PageSize - 1;
var sql = new StringBuilder("SELECT * FROM (");
//在 Select 和 From 之间插入：
//,row_number() over(order by UPDDATETIME desc) rn
sql.AppendLine().Append(parts.Select)
.Append(", row_number() over(")
.Append(parts.OrderBy);
//query.AppendSqlOrder(res, this);
sql.Append(") dataRowNumber ").Append(parts.FromWhere)
.Append(") x").AppendLine()
.Append("WHERE x.dataRowNumber >= ").Append(startRow)
.Append(" AND x.dataRowNumber <= ").Append(endRow);
parts.PagingSql = sql.ToString();
}
/// <summary>
/// 如果需要统计，则生成统计语句进行查询。
/// </summary>
/// <param name="dba"></param>
/// <param name="pagingInfo"></param>
/// <param name="parts"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private static void QueryTotalCountIf(IDbAccesser dba, PagingInfo pagingInfo, PagingSqlParts parts, object[] parameters)
{
if (pagingInfo.IsNeedCount)
{
var pagingCountSql = "SELECT COUNT(0) " + parts.FromWhere;
//查询值。（由于所有参数都不会在 OrderBy、Select 语句中，所以把所有参数都传入。
var value = dba.QueryValue(pagingCountSql, parameters);
pagingInfo.TotalCount = Convert.ToInt64(value);
}
}
