public override void QueryList(IDbAccesser dba, ISqlSelectArgs args) { var pagingInfo = args.PagingInfo; if (PagingInfo.IsNullOrEmpty(pagingInfo) || this.GetPagingLocation(pagingInfo) == PagingLocation.Memory) { base.QueryList(dba, args); } else { //转换为分页查询 SQL var parts = ParsePagingSqlParts(args.FormattedSql); CreatePagingSql(ref parts, pagingInfo); //读取分页的实体 using (var reader = dba.QueryDataReader(parts.PagingSql, args.Parameters)) { this.FillDataIntoList( reader, ReadDataType.ByName, args.List, args.FetchingFirst, PagingInfo.Empty, args.MarkTreeFullLoaded ); } QueryTotalCountIf(dba, pagingInfo, parts, args.Parameters); } }
/// <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); } }
public override void QueryTable(IDbAccesser dba, ITableQueryArgs args) { var pagingInfo = args.PagingInfo; if (PagingInfo.IsNullOrEmpty(pagingInfo) || this.GetPagingLocation(pagingInfo) == PagingLocation.Memory) { base.QueryTable(dba, args); } else { //转换为分页查询 SQL var parts = ParsePagingSqlParts(args.FormattedSql); CreatePagingSql(ref parts, pagingInfo); //读取分页的数据 var table = args.ResultTable; var reader = dba.QueryDataReader(parts.PagingSql, args.Parameters); using (reader) { LiteDataTableAdapter.Fill(table, reader); } QueryTotalCountIf(dba, pagingInfo, parts, args.Parameters); } }
/// <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 entityReader = CreateEntityReader(readType); Action<IDataReader> rowReader = dr => { var entity = entityReader.Read(dr); list.Add(entity); }; if (fetchingFirst) { if (reader.Read()) { rowReader(reader); } } else { PagingHelper.MemoryPaging(reader, rowReader, 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); }
/// <summary> /// 只有一些特定的查询,可以进行分批查询。 /// </summary> /// <param name="dba">The dba.</param> /// <param name="args">The arguments.</param> /// <returns></returns> private bool TryBatchQuery(IDbAccesser dba, IEntitySelectArgs args) { if (!PagingInfo.IsNullOrEmpty(args.PagingInfo)) { return(false); } //分批查询的条件:WHERE 条件中只有 IN 或 NOT IN 语句。 var query = args.Query; var inClause = query.Where as IColumnConstraint; if (inClause == null || inClause.Operator != PropertyOperator.In && inClause.Operator != PropertyOperator.NotIn) { return(false); } var values = inClause.Value as IEnumerable; var parameters = values as IList ?? values.Cast <object>().ToArray(); var autoSelection = AutoSelectionForLOB(query); var readType = autoSelection ? ReadDataType.ByIndex : ReadDataType.ByName; /*********************** 代码块解释 ********************************* * 以下分批进行查询。算法: * 先临时把树中的条件中的值改成子集合, * 然后使用新的树生成对应的 Sql 语句并查询实体。 * 所有查询完成后,再把树中的集合还原为原始的大集合。 **********************************************************************/ var maxItemsCount = SqlGenerator.CampatibleMaxItemsInInClause; var start = 0; var paramSection = new List <object>(maxItemsCount); inClause.Value = paramSection;//临时把树中的条件中的值改成子集合。 while (start < parameters.Count) { paramSection.Clear(); var end = Math.Min(start + maxItemsCount - 1, parameters.Count - 1); for (int i = start; i <= end; i++) { paramSection.Add(parameters[i]); } //生成 Sql var generator = this.CreateSqlGenerator(); QueryFactory.Instance.Generate(generator, query); var sql = generator.Sql; base.QueryDataReader(dba, args, readType, sql); start += paramSection.Count; } return(true); }
/// <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); }
/// <summary> /// 子类重写此方法,查询从持久层加载列表的具体实现。 /// </summary> /// <param name="args">The arguments.</param> /// <param name="entityList">The entity list.</param> protected virtual void QueryListCore(SqlQueryArgs args, EntityList entityList) { var dataProvider = RdbDataProvider.Get(Repo); using (var dba = dataProvider.CreateDbAccesser()) { //访问数据库 if (args.Filter != null) { #region 内存过滤式加载 if (!PagingInfo.IsNullOrEmpty(args.PagingInfo)) { throw new NotSupportedException("使用内存过滤器的同时,不支持提供分页参数。"); } args.EntityType = Repo.EntityType; args.MemoryList = new List <Entity>(); dataProvider.DbTable.QueryList(dba, args); this.LoadByFilter(args); #endregion } else { if (args.FetchType == FetchType.Count) { #region 查询 Count var value = dba.QueryValue(args.FormattedSql, args.Parameters); var count = RdbTable.ConvertCount(value); entityList.SetTotalCount(count); #endregion } else { //是否需要为 PagingInfo 设置统计值。 var pagingInfoCount = !PagingInfo.IsNullOrEmpty(args.PagingInfo) && args.PagingInfo.IsNeedCount; //如果 pagingInfoCount 为真,则在访问数据库时,会设置好 PagingInfo 的总行数。 args.EntityType = Repo.EntityType; dataProvider.DbTable.QueryList(dba, args); //最后,还需要设置列表的 TotalCount。 if (pagingInfoCount) { entityList.SetTotalCount(args.PagingInfo.TotalCount); } } } } }
/// <summary> /// 子类重写此方法,查询从持久层加载列表的具体实现。 /// </summary> /// <param name="args">The arguments.</param> /// <param name="entityList">The entity list.</param> /// <exception cref="System.NotSupportedException">使用内存过滤器的同时,不支持提供分页参数。</exception> protected override void QueryListCore(EntityQueryArgs args, EntityList entityList) { var dp = RdbDataProvider.Get(this.Repo); using (var dba = dp.CreateDbAccesser()) { //以下代码,开始访问数据库查询数据。 var dbTable = dp.DbTable; if (args.Filter != null) { #region 内存过滤式加载 if (!PagingInfo.IsNullOrEmpty(args.PagingInfo)) { throw new NotSupportedException("使用内存过滤器的同时,不支持提供分页参数。"); } args.MemoryList = new List <Entity>(); dbTable.QueryList(dba, args); this.LoadByFilter(args); #endregion } else { if (args.FetchType == FetchType.Count) { #region 查询 Count var count = dbTable.Count(dba, args.Query); entityList.SetTotalCount(count); #endregion } else { //是否需要为 PagingInfo 设置统计值。 var pi = args.PagingInfo; var pagingInfoCount = !PagingInfo.IsNullOrEmpty(pi) && pi.IsNeedCount; //如果 pagingInfoCount 为真,则在访问数据库时,会设置好 PagingInfo 的总行数。 dbTable.QueryList(dba, args); //最后,还需要设置列表的 TotalCount。 if (pagingInfoCount) { entityList.SetTotalCount(pi.TotalCount); } } } } }
public override void QueryList(IDbAccesser dba, IEntitySelectArgs args) { /*********************** 代码块解释 ********************************* * * 以下代码用于支持数据库分页 * **********************************************************************/ //检查分页条件。(如果是树状实体,也不支持在数据库中进行分页。) var pagingInfo = args.PagingInfo; bool isPaging = !PagingInfo.IsNullOrEmpty(pagingInfo) && this.GetPagingLocation(pagingInfo) == PagingLocation.Database && !Repository.SupportTree; if (isPaging) { var query = args.Query; if (!(query as TableQuery).HasOrdered()) { throw new InvalidProgramException("分页查询的同时,必须指定排序属性。"); } var autoSelection = AutoSelectionForLOB(query); //生成分页 Sql var pk = query.From.FindTable(Repository).Column(Entity.IdProperty); var generator = this.CreateSqlGenerator(); var pagedSelect = generator.ModifyToPagingTree(query as SqlSelect, pk as SqlColumn, pagingInfo); generator.Generate(pagedSelect); var pagingSql = generator.Sql; //查询数据库 using (var reader = dba.QueryDataReader(pagingSql, pagingSql.Parameters)) { //填充到列表中。 this.FillDataIntoList( reader, autoSelection ? ReadDataType.ByIndex : ReadDataType.ByName, args.List, false, pagingInfo, args.MarkTreeFullLoaded ); } //最后,如果需要,则统计一下总行数。 if (pagingInfo.IsNeedCount) { pagingInfo.TotalCount = this.Count(dba, query); } } else { base.QueryList(dba, args); } }
protected sealed override 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 override void QueryList(IDbAccesser dba, IEntitySelectArgs args) { /*********************** 代码块解释 ********************************* * * 以下代码用于支持数据库分页 * **********************************************************************/ //检查分页条件。(如果是树状实体,也不支持在数据库中进行分页。) var pagingInfo = args.PagingInfo; bool isPaging = !PagingInfo.IsNullOrEmpty(pagingInfo) && this.GetPagingLocation(pagingInfo) == PagingLocation.Database && !Repository.SupportTree; if (isPaging) { var query = args.Query; var autoSelection = AutoSelectionForLOB(query); //生成分页 Sql var generator = this.CreateSqlGenerator(); generator.Generate(query as SqlSelect, pagingInfo); var pagingSql = generator.Sql; //查询数据库 using (var reader = dba.QueryDataReader(pagingSql, pagingSql.Parameters)) { //填充到列表中。 this.FillDataIntoList( reader, autoSelection ? ReadDataType.ByIndex : ReadDataType.ByName, args.List, false, pagingInfo, args.MarkTreeFullLoaded ); } //最后,如果需要,则统计一下总行数。 if (pagingInfo.IsNeedCount) { pagingInfo.TotalCount = this.Count(dba, query); } } else { base.QueryList(dba, args); } }
/// <summary> /// 使用 ROWNUM 来进行分页。 /// </summary> /// <param name="raw">The raw.</param> /// <param name="pagingInfo">The paging information.</param> /// <returns></returns> /// <exception cref="System.ArgumentNullException">pagingInfo</exception> /// <exception cref="System.InvalidProgramException">必须排序后才能使用分页功能。</exception> protected override ISqlSelect ModifyToPagingTree(SqlSelect raw, PagingInfo pagingInfo) { if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); } if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); } var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1; var endRow = startRow + pagingInfo.PageSize - 1; var res = MakePagingTree(raw, startRow, endRow); return(res); }
/// <summary> /// 为指定的原始查询生成指定分页效果的新查询。 /// </summary> /// <param name="raw">原始查询</param> /// <param name="pagingInfo">分页信息。</param> /// <returns></returns> protected override ISqlSelect ModifyToPagingTree(SqlSelect raw, PagingInfo pagingInfo) { if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); } if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); } var pageNumber = pagingInfo.PageNumber; var pageSize = pagingInfo.PageSize; var res = MakePagingTree(raw, pageNumber, pageSize); return(res); }
/// <summary> /// 使用 IDataReader 的内存分页读取方案。 /// </summary> /// <param name="reader"></param> /// <param name="rowReader">每一行数据,会调用此方法进行调取。</param> /// <param name="pagingInfo">分页信息。如果这个参数不为空,则使用其中描述的分页规则进行内存分页查询。</param> public static void MemoryPaging(IDataReader reader, Action <IDataReader> rowReader, PagingInfo pagingInfo) { bool isPaging = !PagingInfo.IsNullOrEmpty(pagingInfo); bool needCount = isPaging && pagingInfo.IsNeedCount; int totalCount = 0; int startRow = 1;//从一开始的行号 int endRow = int.MaxValue; if (isPaging) { startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1; endRow = startRow + pagingInfo.PageSize - 1; } while (reader.Read()) { totalCount++; if (totalCount >= startRow) { if (totalCount <= endRow) { rowReader(reader); } else { //如果已经超出该页,而且需要统计行数,则直接快速循环到最后。 if (needCount) { while (reader.Read()) { totalCount++; } break; } } } } if (needCount) { pagingInfo.TotalCount = totalCount; } }
internal void SetDataLoadOptions(PagingInfo paging = null, EagerLoadOptions eagerLoad = null) { if (!PagingInfo.IsNullOrEmpty(paging)) { this.PagingInfo = paging; } if (eagerLoad != null && eagerLoad.CoreList.Count > 0) { if (this.EagerLoadOptions != null) { for (int i = 0, c = eagerLoad.CoreList.Count; i < c; i++) { var item = eagerLoad.CoreList[i]; this.EagerLoad(item); } } else { this.EagerLoadOptions = eagerLoad; } } }
/// <summary> /// 为指定的原始查询生成指定分页效果的新查询。 /// </summary> /// <param name="raw">原始查询</param> /// <param name="pagingInfo">分页信息。</param> /// <returns></returns> private ISqlSelect ModifyToPagingTreeWithNotIn(SqlSelect raw, PagingInfo pagingInfo) { if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); } if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); } //如果是第一页,则只需要使用 TOP 语句即可。 if (pagingInfo.PageNumber == 1) { return(new SqlSelect { Selection = new SqlNodeList { new SqlLiteral { FormattedSql = "TOP " + pagingInfo.PageSize + " " }, raw.Selection ?? SqlSelectAll.Default }, From = raw.From, Where = raw.Where, OrderBy = raw.OrderBy }); } /*********************** 代码块解释 ********************************* * * 转换方案: * * SELECT * * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * * 转换分页后: * * SELECT TOP 10 * * FROM ASN * WHERE ASN.Id > 0 AND ASN.Id NOT IN( * SELECT TOP 20 Id * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * ) * ORDER BY ASN.AsnCode ASC * **********************************************************************/ //先要找到主表的 PK,分页时需要使用此主键列来生成分页 Sql。 //这里约定 Id 为主键列名。 var finder = new FirstTableFinder(); var pkTable = finder.Find(raw.From); var pkColumn = new SqlColumn { Table = pkTable, ColumnName = EntityConvention.IdColumnName }; //先生成内部的 Select var excludeSelect = new SqlSelect { Selection = new SqlNodeList { new SqlLiteral { FormattedSql = "TOP " + (pagingInfo.PageNumber - 1) * pagingInfo.PageSize + " " }, pkColumn }, From = raw.From, Where = raw.Where, OrderBy = raw.OrderBy, }; var res = new SqlSelect { Selection = new SqlNodeList { new SqlLiteral { FormattedSql = "TOP " + pagingInfo.PageSize + " " }, raw.Selection ?? SqlSelectAll.Default }, From = raw.From, OrderBy = raw.OrderBy, }; var newWhere = new SqlColumnConstraint { Column = pkColumn, Operator = SqlColumnConstraintOperator.NotIn, Value = excludeSelect }; if (raw.Where != null) { res.Where = new SqlBinaryConstraint { Left = raw.Where, Opeartor = SqlBinaryConstraintType.And, Right = newWhere }; } else { res.Where = newWhere; } return(res); }
/// <summary> /// 为指定的原始查询生成指定分页效果的新查询。 /// </summary> /// <param name="raw">原始查询</param> /// <param name="pagingInfo">分页信息。</param> /// <returns></returns> protected ISqlSelect ModifyToPagingTreeWithRowNumberOver(SqlSelect raw, PagingInfo pagingInfo) { if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); } if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); } //如果是第一页,则只需要使用 TOP 语句即可。 if (pagingInfo.PageNumber == 1) { return(new SqlSelect { Selection = new SqlNodeList { new SqlLiteral { FormattedSql = "TOP " + pagingInfo.PageSize + " " }, raw.Selection ?? SqlSelectAll.Default }, From = raw.From, Where = raw.Where, OrderBy = raw.OrderBy }); } /*********************** 代码块解释 ********************************* * * 转换方案: * * SELECT * * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * * 转换分页后: * * SELECT * FROM * ( * SELECT A.*, ROW_NUMBER() OVER (order by Id)_rowNumber * FROM A * ) T * WHERE _rowNumber between 1 and 10 **********************************************************************/ var newRaw = new SqlSelect { Selection = new SqlNodeList() { new SqlLiteral { FormattedSql = "ROW_NUMBER() OVER (" }, raw.OrderBy, new SqlLiteral { FormattedSql = ")_rowNumber," }, raw.Selection ?? SqlSelectAll.Default }, From = raw.From, Where = raw.Where, IsDistinct = raw.IsDistinct, IsCounting = raw.IsCounting, }; var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1; var endRow = startRow + pagingInfo.PageSize - 1; var res = MakePagingTree(newRaw, startRow, endRow); return(res); }
/// <summary> /// 通过 IQuery 对象来查询实体。 /// </summary> /// <param name="args">The arguments.</param> /// <returns></returns> /// <exception cref="System.NotSupportedException">使用内存过滤器的同时,不支持提供分页参数。</exception> /// <exception cref="System.InvalidProgramException"></exception> protected EntityList QueryList(EntityQueryArgs args) { if (args.Query == null) { throw new ArgumentException("EntityQueryArgs.Query 属性不能为空。"); } this.PrepareArgs(args); this.BuildDefaultQuerying(args); this.OnQuerying(args); var entityList = args.EntityList; var oldCount = entityList.Count; bool autoIndexEnabled = entityList.AutoTreeIndexEnabled; try { //在加载数据时,自动索引功能都不可用。 entityList.AutoTreeIndexEnabled = false; using (var dba = Repo.RdbDataProvider.CreateDbAccesser()) { //以下代码,开始访问数据库查询数据。 var dbTable = Repo.RdbDataProvider.DbTable; if (args.Filter != null) { #region 内存过滤式加载 if (!PagingInfo.IsNullOrEmpty(args.PagingInfo)) { throw new NotSupportedException("使用内存过滤器的同时,不支持提供分页参数。"); } args.MemoryList = new List <Entity>(); dbTable.QueryList(dba, args); this.LoadByFilter(args); #endregion } else { if (args.FetchType == FetchType.Count) { #region 查询 Count var count = dbTable.Count(dba, args.Query); entityList.SetTotalCount(count); #endregion } else { //是否需要为 PagingInfo 设置统计值。 var pi = args.PagingInfo; var pagingInfoCount = !PagingInfo.IsNullOrEmpty(pi) && pi.IsNeedCount; //如果 pagingInfoCount 为真,则在访问数据库时,会设置好 PagingInfo 的总行数。 dbTable.QueryList(dba, args); //最后,还需要设置列表的 TotalCount。 if (pagingInfoCount) { entityList.SetTotalCount(pi.TotalCount); } } } } } finally { entityList.AutoTreeIndexEnabled = autoIndexEnabled; } this.EagerLoadOnCompleted(args, entityList, oldCount); return(entityList); }
/// <summary> /// 使用 sql 语句来查询实体。 /// </summary> /// <param name="args">The arguments.</param> /// <returns></returns> /// <exception cref="System.NotSupportedException">使用内存过滤器的同时,不支持提供分页参数。</exception> protected EntityList QueryList(SqlQueryArgs args) { this.PrepareArgs(args); var entityList = args.EntityList; var oldCount = entityList.Count; bool autoIndexEnabled = entityList.AutoTreeIndexEnabled; try { //在加载数据时,自动索引功能都不可用。 entityList.AutoTreeIndexEnabled = false; var dataProvider = Repo.RdbDataProvider; using (var dba = dataProvider.CreateDbAccesser()) { //访问数据库 if (args.Filter != null) { #region 内存过滤式加载 if (!PagingInfo.IsNullOrEmpty(args.PagingInfo)) { throw new NotSupportedException("使用内存过滤器的同时,不支持提供分页参数。"); } args.EntityType = Repo.EntityType; args.MemoryList = new List <Entity>(); dataProvider.DbTable.QueryList(dba, args); this.LoadByFilter(args); #endregion } else { if (args.FetchType == FetchType.Count) { #region 查询 Count var value = dba.QueryValue(args.FormattedSql, args.Parameters); var count = DbTable.ConvertCount(value); entityList.SetTotalCount(count); #endregion } else { //是否需要为 PagingInfo 设置统计值。 var pagingInfoCount = !PagingInfo.IsNullOrEmpty(args.PagingInfo) && args.PagingInfo.IsNeedCount; //如果 pagingInfoCount 为真,则在访问数据库时,会设置好 PagingInfo 的总行数。 args.EntityType = Repo.EntityType; dataProvider.DbTable.QueryList(dba, args); //最后,还需要设置列表的 TotalCount。 if (pagingInfoCount) { entityList.SetTotalCount(args.PagingInfo.TotalCount); } } } } } finally { entityList.AutoTreeIndexEnabled = autoIndexEnabled; } this.EagerLoadOnCompleted(args, entityList, oldCount); return(entityList); }
/// <summary> /// 为指定的原始查询生成指定分页效果的新查询。 /// </summary> /// <param name="raw">原始查询</param> /// <param name="pkColumn">需要指定主键列</param> /// <param name="pagingInfo">分页信息。</param> /// <returns></returns> public virtual SqlSelect ModifyToPagingTree(SqlSelect raw, SqlColumn pkColumn, PagingInfo pagingInfo) { if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); } if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); } //如果是第一页,则只需要使用 TOP 语句即可。 if (pagingInfo.PageNumber == 1) { raw.Top = pagingInfo.PageSize; return(raw); } /*********************** 代码块解释 ********************************* * * 转换方案: * * SELECT * * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * * SELECT TOP 10 * * FROM ASN * WHERE ASN.Id > 0 AND ASN.Id NOT IN( * SELECT TOP 20 Id * FROM ASN * WHERE ASN.Id > 0 * ORDER BY ASN.AsnCode ASC * ) * ORDER BY ASN.AsnCode ASC * **********************************************************************/ var excludeSelect = new SqlSelect { Top = (pagingInfo.PageNumber - 1) * pagingInfo.PageSize, Selection = pkColumn, From = raw.From, Where = raw.Where, OrderBy = raw.OrderBy, }; var res = new SqlSelect { Top = pagingInfo.PageSize, Selection = raw.Selection, From = raw.From, OrderBy = raw.OrderBy, }; var newWhere = new SqlColumnConstraint { Column = pkColumn, Operator = SqlColumnConstraintOperator.NotIn, Value = excludeSelect }; if (raw.Where != null) { res.Where = new SqlBinaryConstraint { Left = raw.Where, Opeartor = SqlBinaryConstraintType.And, Right = newWhere }; } else { res.Where = newWhere; } return(res); }
/// <summary> /// 为指定的原始查询生成指定分页效果的新查询。 /// </summary> /// <param name="raw">原始查询</param> /// <param name="pagingInfo">分页信息。</param> /// <returns></returns> /// <exception cref="System.ArgumentNullException">pagingInfo</exception> /// <exception cref="System.InvalidProgramException">必须排序后才能使用分页功能。</exception> protected virtual ISqlSelect ModifyToPagingTree(SqlSelect raw, PagingInfo pagingInfo) { if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); } if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); } /*********************** 代码块解释 ********************************* * * 使用 ROW_NUMBER() 函数,此函数 SqlServer、Oracle 都可使用。 * 注意,这个方法只支持不太复杂 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 innerSelect = new SqlSelect(); var selection = new SqlArray(); if (raw.Selection != null) { selection.Items.Add(raw.Selection); } selection.Items.Add(new SqlNodeList { new SqlLiteral { FormattedSql = "row_number() over (" }, raw.OrderBy, new SqlLiteral { FormattedSql = ") _rowNumber" } }); innerSelect.Selection = selection; var subSelect = new SqlSubSelect { Select = innerSelect, Alias = "x" }; var rowNumberColumn = new SqlTree.SqlColumn { Table = subSelect, ColumnName = "_rowNumber" }; var pagingSelect = new SqlSelect(); pagingSelect.From = subSelect; pagingSelect.Where = new SqlTree.SqlBinaryConstraint { Left = new SqlTree.SqlColumnConstraint { Column = rowNumberColumn, Operator = SqlColumnConstraintOperator.GreaterEqual, Value = startRow }, Opeartor = SqlBinaryConstraintType.And, Right = new SqlTree.SqlColumnConstraint { Column = rowNumberColumn, Operator = SqlColumnConstraintOperator.LessEqual, Value = endRow } }; return(pagingSelect); }