/// <summary> /// 数据分页查询 异步版本 /// </summary> /// <param name="pager"></param> /// <param name="sp"></param> /// <returns></returns> public async Task <IDataReader> ExecPageReaderAsync(IPager p, params IDataParameter[] sp) { if (p is DBPagerInfo) { DBPagerInfo pager = p as DBPagerInfo; var tp = GetSortFieldFromOrderBy(pager.OrderBy, pager.KeyId); string orderby = tp[0]; string sql = "SELECT COUNT(1) FROM (" + pager.Query + ")c"; pager.RecordCount = CommOp.ToInt(await Helper.ExecGetObjectAsync(sql, sp)); sql = $@"WITH PAGED AS ( SELECT ROW_NUMBER() OVER(ORDER BY {pager.OrderBy}) AS rowNum, * FROM ({pager.Query})a) SELECT TT.* FROM PAGED P INNER JOIN ({pager.Query})TT ON P.{pager.KeyId} = TT.{pager.KeyId} WHERE ROWNUM BETWEEN {pager.StartIndex + 1} AND {pager.StartIndex + pager.PageSize} ORDER BY {pager.OrderBy}"; //pager.OrderBy, pager.Query, pager.KeyId, pager.StartIndex + 1, pager.StartIndex + pager.PageSize); return(await Helper.ExecReaderAsync(sql, sp)); } else if (p is MasterDetailPagerInfo) { return(await ExecMasterDetailPageReaderAsync((MasterDetailPagerInfo)p, sp)); } else { throw new ArgumentException($"请传入{nameof(DBPagerInfo)}或{nameof(MasterDetailPagerInfo)}"); } }
/// <summary> /// 数据分页查询 /// </summary> /// <param name="pager"></param> /// <param name="sp"></param> /// <returns></returns> public IDataReader ExecPageReader(DBPagerInfo pager, params IDataParameter[] sp) { string sql = "SELECT COUNT(*) FROM (" + pager.Query + ")c"; pager.RecordCount = (int)Helper.ExecGetObject(sql, sp); sql = String.Format(@"WITH PAGED AS ( SELECT ROW_NUMBER() OVER(ORDER BY {0}) AS rowNum, * FROM ({1})a) SELECT TT.* FROM PAGED P INNER JOIN ({1})TT ON P.{2} = TT.{2} WHERE ROWNUM BETWEEN {3} AND {4} ORDER BY {0}", pager.OrderBy, pager.Query, pager.KeyId, pager.StartIndex + 1, pager.StartIndex + pager.PageSize); return(Helper.ExecReader(sql, sp)); }
public async Task <IDataReader> ExecPageReaderAsync(IPager p, params IDataParameter[] sp) { if (p is DBPagerInfo) { DBPagerInfo pager = p as DBPagerInfo; var tp = GetSortFieldFromOrderBy(pager.OrderBy, pager.KeyId); string orderby = tp[0]; string sql = "SELECT COUNT(1) FROM (" + pager.Query + ")c"; pager.RecordCount = CommOp.ToInt(await Helper.ExecGetObjectAsync(sql, sp)); sql = String.Format(@"SELECT * FROM(SELECT A.*, rownum r FROM({0} ORDER BY {1})A WHERE rownum <= {2})B WHERE r>{3}", pager.Query, orderby, pager.StartIndex + pager.PageSize, pager.StartIndex); return(await Helper.ExecReaderAsync(sql, sp)); } else if (p is MasterDetailPagerInfo) { return(await ExecMasterDetailPageReaderAsync((MasterDetailPagerInfo)p, sp)); } else { throw new ArgumentException($"请传入{nameof(DBPagerInfo)}或{nameof(MasterDetailPagerInfo)}"); } }