// build paging sql string
private string BuildPagingSqlString(DbPagingParams paging)
{
StringBuilder sb = new StringBuilder();
if (_type == DbContextType.MySql)
{
sb.AppendFormat("SELECT * FROM ({0}) as t ", paging.SqlString);
if (!string.IsNullOrEmpty(paging.SortField))
{
sb.AppendFormat(" order by {0} {1} ", paging.SortField, paging.SortOrder);
}
sb.AppendFormat("LIMIT {0},{1} ", paging.PageIndex * paging.PageSize, paging.PageSize);
}
else
{
if (string.IsNullOrEmpty(paging.SortField))
{
throw new Exception("SQL分页查询必须指定排序字段");
}
var start = paging.PageIndex * paging.PageSize;
var end = start + paging.PageSize;
sb.Append("select * from (");
sb.Append(" select *, ROW_NUMBER() OVER (");
sb.AppendFormat(" ORDER BY {0} {1} ", paging.SortField, paging.SortOrder);
sb.AppendFormat(") as RN from ({0}) as gg", paging.SqlString);
sb.AppendFormat(") as t where t.RN between {0} and {1}", start + 1, end);
}
return(sb.ToString());
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="paging"></param>
/// <param name="args"></param>
/// <returns></returns>
public DbPagingResult PageQuery(DbPagingParams paging, params object[] args)
{
var sql = BuildPagingSqlString(paging); // 生成分页脚本
var data = this.Query(sql, args);
// get summary
var obj = this.GetSummary(paging.SqlString, paging.SumamryFields, args);
var total = obj.GetInt64("CNT");
return(new DbPagingResult
{
total = total,
data = data,
summary = total > 0 ? obj : null
});
}
