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