Example #1
0
        /// <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)}");
            }
        }
Example #2
0
        /// <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));
        }
Example #3
0
 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)}");
     }
 }