C# Rafy.PagingInfo類代碼示例

 /// <summary>
 /// 在 sqlce 下，不支持 rowNumber 方案，但是支持 not in 方案。
 /// 鑒於實現 not in 方案比較耗時，所以暫時決定使用 IDataReader 分頁完成。
 /// 
 /// not in 分頁，參見以下 Sql：
 /// select top 10 [AuditItem].* from 
 /// [AuditItem] where 
 /// [AuditItem].id not in
 /// (
 ///     select top 100 [AuditItem].id from [AuditItem] order by LogTime desc
 /// )
 /// order by LogTime desc
 /// </summary>
 protected override PagingLocation GetPagingLocation(PagingInfo pagingInfo)
 {
     if (!PagingInfo.IsNullOrEmpty(pagingInfo) && pagingInfo.PageNumber == 1 && !pagingInfo.IsNeedCount)
     {
         return PagingLocation.Database;
     }
     return PagingLocation.Memory;
 }

        protected override sealed EntityList DoGetAll(PagingInfo paging, EagerLoadOptions eagerLoad)
        {
            if (!PagingInfo.IsNullOrEmpty(paging)) { throw new NotSupportedException(); }

            DataProvider.EnsureStore();
            var items = DataProvider._memoryRows.Values.Select(v => DataProvider.FromRow(v));
            var list = this.CreateList(items, false);
            TreeHelper.MarkTreeFullLoaded(list);

            return list;
        }

 public new OtherStorageInBillList GetByParentIdList(object[] parentIdList, PagingInfo paging = null, EagerLoadOptions eagerLoad = null)
 {
     return base.GetByParentIdList(parentIdList, paging, eagerLoad) as OtherStorageInBillList;
 }

 public new ChapterList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
 {
     return base.GetAll(paging, eagerLoad) as ChapterList;
 }

 public new OperationACList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
 {
     return base.GetAll(paging, eagerLoad) as OperationACList;
 }

        /// <summary>
        /// 訪問 sql 語法樹中的每一個結點，並生成相應的 Sql 語句。
        /// </summary>
        /// <param name="tree">The tree.</param>
        /// <param name="pagingInfo">The paging information.</param>
        public void Generate(SqlSelect tree, PagingInfo pagingInfo = null)
        {
            ISqlSelect res = tree;
            if (!PagingInfo.IsNullOrEmpty(pagingInfo))
            {
                res = ModifyToPagingTree(tree, pagingInfo);
            }

            base.Visit(res);
        }

 public new ProductAttachementList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
 {
     return base.GetAll(paging, eagerLoad) as ProductAttachementList;
 }

 public new MonthPlanList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
 {
     return base.GetAll(paging, eagerLoad) as MonthPlanList;
 }

 public new DevLanguageItemList GetAll(PagingInfo paging= null, EagerLoadOptions eagerLoad = null)
 {
     return base.GetAll(paging, eagerLoad) as DevLanguageItemList;
 }

 /// <summary>
 /// 在內存中對 IDataReader 進行讀取，並以樹的方式進行節點的加載。
 /// </summary>
 /// <param name="reader">表格類數據。</param>
 /// <param name="readType">是否索引還是名稱去讀取 IDataReader。</param>
 /// <param name="list">需要把讀取的實體中的第一級的節點，加入到這個列表中。</param>
 /// <param name="markTreeFullLoaded">如果某次查詢結果是一棵完整的子樹，那麽必須設置此參數為 true ，才可以把整個樹標記為完整加載。</param>
 /// <param name="pagingInfo">對根節點進行分頁的信息。</param>
 private void FillTreeIntoList(
     IDataReader reader, ReadDataType readType, IList<Entity> list,
     bool markTreeFullLoaded, PagingInfo pagingInfo)
 {
     var entities = this.ReadToEntity(reader, readType);
     if (PagingInfo.IsNullOrEmpty(pagingInfo))
     {
         TreeHelper.LoadTreeData(list, entities, _repository.TreeIndexOption);
     }
     else
     {
         //在內存中分頁。
         var tempList =new List<Entity>();
         TreeHelper.LoadTreeData(tempList, entities, _repository.TreeIndexOption);
         var paged = tempList.JumpToPage(pagingInfo);
         foreach (var item in paged) { list.Add(item); }
     }
     if (markTreeFullLoaded)
     {
         TreeHelper.MarkTreeFullLoaded(list);
     }
 }

 /// <summary>
 /// 判斷指定的分頁操作，支持在哪個層麵進行分頁。
 /// </summary>
 /// <param name="pagingInfo">The paging information.</param>
 /// <returns></returns>
 protected abstract PagingLocation GetPagingLocation(PagingInfo pagingInfo);

        /// <summary>
        /// 在內存中對 IDataReader 進行讀取。
        /// 注意！！！
        /// 此方法中會釋放 Reader。外層不能再用 Using。
        /// </summary>
        /// <param name="reader">表格類數據。</param>
        /// <param name="readType">是否索引還是名稱去讀取 IDataReader。</param>
        /// <param name="list">需要把讀取的實體，加入到這個列表中。</param>
        /// <param name="fetchingFirst">是否隻讀取一條數據即返回。</param>
        /// <param name="pagingInfo">如果不是隻取一行數據，則這個參數表示列表內存分頁的信息。</param>
        /// <param name="markTreeFullLoaded">如果某次查詢結果是一棵完整的子樹，那麽必須設置此參數為 true ，才可以把整個樹標記為完整加載。</param>
        protected void FillDataIntoList(
            IDataReader reader, ReadDataType readType,
            IList<Entity> list, bool fetchingFirst, PagingInfo pagingInfo, bool markTreeFullLoaded
            )
        {
            if (_repository.SupportTree)
            {
                this.FillTreeIntoList(reader, readType, list, markTreeFullLoaded, pagingInfo);
                return;
            }

            //如果正在分頁，而且支持數據庫層麵的分頁，則不使用內存分頁。
            if (!PagingInfo.IsNullOrEmpty(pagingInfo) && this.GetPagingLocation(pagingInfo) == PagingLocation.Database)
            {
                pagingInfo = null;
            }

            var readByIndex = readType == ReadDataType.ByIndex;
            Action<IDataReader> rowReader = dr =>
            {
                var entity = readByIndex ? this.CreateByIndex(dr) : this.CreateByName(dr);
                list.Add(entity);
            };
            if (fetchingFirst)
            {
                if (reader.Read())
                {
                    rowReader(reader);
                }
            }
            else
            {
                PagingHelper.MemoryPaging(reader, rowReader, pagingInfo);
            }
        }

        /// <summary>
        /// 如果需要統計，則生成統計語句進行查詢。
        /// </summary>
        /// <param name="dba"></param>
        /// <param name="pagingInfo"></param>
        /// <param name="parts"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private static void QueryTotalCountIf(IDbAccesser dba, PagingInfo pagingInfo, PagingSqlParts parts, object[] parameters)
        {
            if (pagingInfo.IsNeedCount)
            {
                var pagingCountSql = "SELECT COUNT(0) " + parts.FromWhere;

                //查詢值。（由於所有參數都不會在 OrderBy、Select 語句中，所以把所有參數都傳入。
                var value = dba.QueryValue(pagingCountSql, parameters);
                pagingInfo.TotalCount = Convert.ToInt32(value);
            }
        }

 /// <summary>
 /// SqlServer 、 Oracle 都支持在數據庫層麵進行分頁。
 /// </summary>
 protected override PagingLocation GetPagingLocation(PagingInfo pagingInfo)
 {
     //雖然本類默認使用數據庫分頁，但是它的子類可以重寫本方法來使用內存分頁。
     //所以本類中的所有方法，在重新實現時，都會分辨這兩種情況。
     return PagingLocation.Database;
 }

        protected virtual void CreatePagingSql(ref PagingSqlParts parts, PagingInfo pagingInfo)
        {
            /*********************** 代碼塊解釋 *********************************
             *
             * 注意，這個方法隻支持不太複雜 SQL 的轉換。
             *
             * 源格式：
             * select ...... from ...... order by xxxx asc, yyyy desc
             * 不限於以上格式，隻要滿足沒有複雜的嵌套查詢，最外層是一個 Select 和 From 語句即可。
             *
             * 目標格式：
             * select * from (select ......, row_number() over(order by xxxx asc, yyyy desc) _rowNumber from ......) x where x._rowNumber<10 and x._rowNumber>5;
            **********************************************************************/

            var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1;
            var endRow = startRow + pagingInfo.PageSize - 1;

            var sql = new StringBuilder("SELECT * FROM (");

            //在 Select 和 From 之間插入：
            //,row_number() over(order by UPDDATETIME desc) rn
            sql.AppendLine().Append(parts.Select)
                .Append(", row_number() over(")
                .Append(parts.OrderBy);
            //query.AppendSqlOrder(res, this);
            sql.Append(") dataRowNumber ").Append(parts.FromWhere)
                .Append(") x").AppendLine()
                .Append("WHERE x.dataRowNumber >= ").Append(startRow)
                .Append(" AND x.dataRowNumber <= ").Append(endRow);

            parts.PagingSql = sql.ToString();
        }