// 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 }); }