public PageData <TResult> Pager <TResult>(IDbConnection connection, IDbTransaction transaction, int?commandTimeout, SqlAdapterPagerParameters adapterParam) { var sqlStr = $"select {adapterParam.Select} from {adapterParam.Table} {adapterParam.Where} {adapterParam.Order}" + $"limit {(adapterParam.PageIndex - 1) * adapterParam.PageSize},{adapterParam.PageSize};" + $"select count(0) from {adapterParam.Table} {adapterParam.Where};"; var multi = connection.QueryMultiple(sqlStr, adapterParam.Params, transaction, commandTimeout); var pageData = new PageData <TResult>() { Data = multi.Read <TResult>(), TotalCount = multi.ReadSingle <long>(), PageIndex = adapterParam.PageIndex, PageSize = adapterParam.PageSize }; pageData.PageCount = (int)Math.Ceiling(pageData.TotalCount * 1.0 / pageData.PageSize); return(pageData); }
public PageData <TResult> Pager <TResult>(IDbConnection connection, IDbTransaction transaction, int?commandTimeout, SqlAdapterPagerParameters adapterParam) { //开始 int start = (adapterParam.PageIndex - 1) * adapterParam.PageSize + 1; //结束 int end = adapterParam.PageIndex * adapterParam.PageSize; var sqlStr = $"SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY {adapterParam.Order}) AS ROW_NUMBER, " + $" {adapterParam.Select} from {adapterParam.Table} {adapterParam.Where} ) AS Tab WHERE ROW_NUMBER BETWEEN {start} AND {end}; " + $" SELECT COUNT(0) AS DataCount FROM (SELECT 1 as [count] from {adapterParam.Table} {adapterParam.Where}) AS CountTb;"; //$" SELECT COUNT(0) AS DataCount FROM (SELECT {adapterParam.Select} from {adapterParam.Table} {adapterParam.Where}) AS CountTb;"; var multi = connection.QueryMultiple(sqlStr, adapterParam.Params, transaction, commandTimeout); var pageData = new PageData <TResult>() { Data = multi.Read <TResult>(), TotalCount = multi.ReadSingle <long>(), PageIndex = adapterParam.PageIndex, PageSize = adapterParam.PageSize }; pageData.PageCount = (int)Math.Ceiling(pageData.TotalCount * 1.0 / pageData.PageSize); return(pageData); }