/// <summary> /// 创建分页查询 /// </summary> /// <param name="fromSection"></param> /// <param name="startIndex"></param> /// <param name="endIndex"></param> /// <returns></returns> public override FromSection CreatePageFromSection(FromSection fromSection, int startIndex, int endIndex) { Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual <int>(1)); Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual <int>(1)); Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!"); Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty); //Check.Require(fromSection.OrderByClip, "query.OrderByClip", Check.NotNullOrEmpty); //OrderByClip orderBy = fromSection.OrderByClip; fromSection.TableName = string.Concat("(", fromSection.SqlString, ") tmpi_table"); fromSection.Select(new Field("tmpi_table.*")); fromSection.AddSelect(new Field("rownum AS rn")); fromSection.OrderBy(OrderByClip.None); fromSection.DistinctString = string.Empty; fromSection.PrefixString = string.Empty; fromSection.GroupBy(GroupByClip.None); fromSection.Parameters = fromSection.Parameters; fromSection.Where(new WhereClip("rownum <=" + endIndex.ToString())); if (startIndex > 1) { fromSection.TableName = string.Concat("(", fromSection.SqlString, ")"); fromSection.Select(Field.All); fromSection.Where(new WhereClip(string.Concat("rn>=", startIndex.ToString()))); } return(fromSection); }
/// <summary> /// 创建分页查询 /// </summary> /// <param name="fromSection"></param> /// <param name="startIndex"></param> /// <param name="endIndex"></param> /// <returns></returns> public override FromSection CreatePageFromSection(FromSection fromSection, int startIndex, int endIndex) { Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual<int>(1)); Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual<int>(1)); Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!"); Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty); //Check.Require(fromSection.OrderByClip, "query.OrderByClip", Check.NotNullOrEmpty); //OrderByClip orderBy = fromSection.OrderByClip; fromSection.TableName = string.Concat("(", fromSection.SqlString, ") tmpi_table"); fromSection.Select(new Field("tmpi_table.*")); fromSection.AddSelect(new Field("rownum AS rn")); fromSection.OrderBy(OrderByClip.None); fromSection.DistinctString = string.Empty; fromSection.PrefixString = string.Empty; fromSection.GroupBy(GroupByClip.None); fromSection.Parameters = fromSection.Parameters; fromSection.Where(new WhereClip("rownum <=" + endIndex.ToString())); if (startIndex > 1) { fromSection.TableName = string.Concat("(", fromSection.SqlString, ")"); fromSection.Select(Field.All); fromSection.Where(new WhereClip(string.Concat("rn>=", startIndex.ToString()))); } return fromSection; }
/// <summary> /// 创建分页查询 /// </summary> /// <param name="fromSection"></param> /// <param name="startIndex"></param> /// <param name="endIndex"></param> /// <returns></returns> public override FromSection CreatePageFromSection(FromSection fromSection, int startIndex, int endIndex) { Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual <int>(1)); Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual <int>(1)); Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!"); Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty); if (startIndex == 1) { return(base.CreatePageFromSection(fromSection, startIndex, endIndex)); } if (OrderByClip.IsNullOrEmpty(fromSection.OrderByClip)) { foreach (Field f in fromSection.Fields) { if (!f.PropertyName.Equals("*")) { fromSection.OrderBy(f.Asc); break; } } } Check.Require(!OrderByClip.IsNullOrEmpty(fromSection.OrderByClip), "query.OrderByClip could not be null or empty!"); if (fromSection.Fields.Count == 0) { fromSection.Select(Field.All); } fromSection.AddSelect(new Field(string.Concat("row_number() over(", fromSection.OrderByString, ") AS tmp_rowid"))); //OrderByClip tempOrderBy = fromSection.OrderByClip; fromSection.OrderBy(OrderByClip.None); fromSection.TableName = string.Concat("(", fromSection.SqlString, ") AS tmp_table"); fromSection.Parameters = fromSection.Parameters; fromSection.DistinctString = string.Empty; fromSection.PrefixString = string.Empty; fromSection.GroupBy(GroupByClip.None); fromSection.Select(Field.All); //fromSection.OrderBy(tempOrderBy); fromSection.Where(new WhereClip(string.Concat("tmp_rowid BETWEEN ", startIndex.ToString(), " AND ", endIndex.ToString()))); return(fromSection); }
/// <summary> /// 创建分页查询 /// </summary> /// <param name="fromSection"></param> /// <param name="startIndex"></param> /// <param name="endIndex"></param> /// <returns></returns> public override FromSection CreatePageFromSection(FromSection fromSection, int startIndex, int endIndex) { Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual<int>(1)); Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual<int>(1)); Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!"); Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty); if (startIndex == 1) { return base.CreatePageFromSection(fromSection, startIndex, endIndex); } if (OrderByClip.IsNullOrEmpty(fromSection.OrderByClip)) { foreach (Field f in fromSection.Fields) { if (!f.PropertyName.Equals("*")) { fromSection.OrderBy(f.Asc); break; } } } Check.Require(!OrderByClip.IsNullOrEmpty(fromSection.OrderByClip), "query.OrderByClip could not be null or empty!"); if (fromSection.Fields.Count == 0) { fromSection.Select(Field.All); } fromSection.AddSelect(new Field(string.Concat("row_number() over(", fromSection.OrderByString, ") AS tmp_rowid"))); //OrderByClip tempOrderBy = fromSection.OrderByClip; fromSection.OrderBy(OrderByClip.None); fromSection.TableName = string.Concat("(", fromSection.SqlString, ") AS tmp_table"); fromSection.Parameters = fromSection.Parameters; fromSection.DistinctString = string.Empty; fromSection.PrefixString = string.Empty; fromSection.GroupBy(GroupByClip.None); fromSection.Select(Field.All); //fromSection.OrderBy(tempOrderBy); fromSection.Where(new WhereClip(string.Concat("tmp_rowid BETWEEN ", startIndex.ToString(), " AND ", endIndex.ToString()))); return fromSection; }
/// <summary> /// 使用linq查询(分页) /// </summary> /// <typeparam name="TEntity"></typeparam> /// <typeparam name="OutEntity"></typeparam> /// <param name="linq"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="lambdaOrderBy"></param> /// <returns></returns> public PageResponse <OutEntity> getListByPaging <TEntity, OutEntity>(FromSection <TEntity> linq, int pageSize, int pageIndex, Expression <Func <TEntity, object> > lambdaOrderBy = null) where TEntity : Entity { if (pageIndex <= 0) { pageIndex = 1; } if (pageSize <= 0) { pageSize = 10; } PageResponse <OutEntity> result = new PageResponse <OutEntity>(); result.totalItems = linq.Count(); List <OutEntity> sqlList = new List <OutEntity>(); if (lambdaOrderBy != null) { sqlList = linq.OrderBy(lambdaOrderBy).Page(pageSize, pageIndex).ToList <OutEntity>(); } else { sqlList = linq.Page(pageSize, pageIndex).ToList <OutEntity>(); } if (sqlList == null || sqlList.Count == 0) { return(null); } result.items = sqlList; result.currentPage = pageIndex; result.itemsPerPage = pageSize; result.totalPages = result.totalItems / pageSize; if ((result.totalItems % pageSize) != 0) { result.totalPages++; } return(result); }
/// <summary> /// 使用linq查询(分页) /// </summary> /// <typeparam name="TEntity"></typeparam> /// <typeparam name="OutEnity"></typeparam> /// <param name="linq"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="lambdaOrderBy"></param> /// <returns></returns> public ActionResult getListByPaging <TEntity, OutEnity>(FromSection <TEntity> linq, Pagination pagination, OrderByClip lambdaOrderBy = null) where TEntity : Entity { int pageIndex = pagination.page; int pageSize = pagination.rows <= 0 ? 20 : pagination.rows; List <OutEnity> sqlList = new List <OutEnity>(); if (lambdaOrderBy != null) { sqlList = linq.OrderBy(lambdaOrderBy).Page(pageSize, pageIndex).ToList <OutEnity>(); } else { sqlList = linq.Page(pageSize, pageIndex).ToList <OutEnity>(); } int totalCount = linq.Count(); return(Content(new { rows = sqlList, total = (totalCount / pageSize) + 1, page = pagination.page, records = totalCount }.ToJson())); }
/// <summary> /// 创建分页查询 /// </summary> /// <param name="fromSection"></param> /// <param name="startIndex"></param> /// <param name="endIndex"></param> /// <returns></returns> public virtual FromSection CreatePageFromSection(FromSection fromSection, int startIndex, int endIndex) { Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual <int>(1)); Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual <int>(1)); Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!"); Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty); int pageSize = endIndex - startIndex + 1; if (startIndex == 1) { fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString()); } else { if (OrderByClip.IsNullOrEmpty(fromSection.OrderByClip)) { foreach (Field f in fromSection.Fields) { if (!f.PropertyName.Equals("*") && f.PropertyName.IndexOf('(') == -1) { fromSection.OrderBy(f.Asc); break; } } } Check.Require(!OrderByClip.IsNullOrEmpty(fromSection.OrderByClip), "query.OrderByClip could not be null or empty!"); int count = fromSection.Count(fromSection); List <Parameter> list = fromSection.Parameters; if (endIndex > count) { int lastnumber = count - startIndex + 1; if (startIndex > count) { lastnumber = count % pageSize; } fromSection.PrefixString = string.Concat(" TOP ", lastnumber.ToString()); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip); // fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS temp_table "); fromSection.PrefixString = string.Empty; fromSection.DistinctString = string.Empty; fromSection.GroupBy(GroupByClip.None); fromSection.Select(Field.All); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip); fromSection.Where(WhereClip.All); } else { if (startIndex < count / 2) { fromSection.PrefixString = string.Concat(" TOP ", endIndex.ToString()); fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempIntable "); fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString()); fromSection.DistinctString = string.Empty; fromSection.GroupBy(GroupByClip.None); fromSection.Select(Field.All); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip); fromSection.Where(WhereClip.All); // fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempOuttable "); fromSection.PrefixString = string.Empty; fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip); } else { fromSection.PrefixString = string.Concat(" TOP ", (count - startIndex + 1).ToString()); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip); fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempIntable "); fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString()); fromSection.DistinctString = string.Empty; fromSection.GroupBy(GroupByClip.None); fromSection.Select(Field.All); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip); fromSection.Where(WhereClip.All); } } fromSection.Parameters = list; } return(fromSection); }
/// <summary> /// 创建分页查询 /// </summary> /// <param name="fromSection"></param> /// <param name="startIndex"></param> /// <param name="endIndex"></param> /// <returns></returns> public virtual FromSection CreatePageFromSection(FromSection fromSection, int startIndex, int endIndex) { Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual<int>(1)); Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual<int>(1)); Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!"); Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty); int pageSize = endIndex - startIndex + 1; if (startIndex == 1) { fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString()); } else { if (OrderByClip.IsNullOrEmpty(fromSection.OrderByClip)) { foreach (Field f in fromSection.Fields) { if (!f.PropertyName.Equals("*") && f.PropertyName.IndexOf('(') == -1) { fromSection.OrderBy(f.Asc); break; } } } Check.Require(!OrderByClip.IsNullOrEmpty(fromSection.OrderByClip), "query.OrderByClip could not be null or empty!"); int count = fromSection.Count(fromSection); List<Parameter> list = fromSection.Parameters; if (endIndex > count) { int lastnumber = count - startIndex + 1; if (startIndex > count) lastnumber = count % pageSize; fromSection.PrefixString = string.Concat(" TOP ", lastnumber.ToString()); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip); // fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS temp_table "); fromSection.PrefixString = string.Empty; fromSection.DistinctString = string.Empty; fromSection.GroupBy(GroupByClip.None); fromSection.Select(Field.All); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip); fromSection.Where(WhereClip.All); } else { if (startIndex < count / 2) { fromSection.PrefixString = string.Concat(" TOP ", endIndex.ToString()); fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempIntable "); fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString()); fromSection.DistinctString = string.Empty; fromSection.GroupBy(GroupByClip.None); fromSection.Select(Field.All); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip); fromSection.Where(WhereClip.All); // fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempOuttable "); fromSection.PrefixString = string.Empty; fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip); } else { fromSection.PrefixString = string.Concat(" TOP ", (count - startIndex + 1).ToString()); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip); fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempIntable "); fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString()); fromSection.DistinctString = string.Empty; fromSection.GroupBy(GroupByClip.None); fromSection.Select(Field.All); fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip); fromSection.Where(WhereClip.All); } } fromSection.Parameters = list; } return fromSection; }
/// <summary> /// 进行排序操作 /// </summary> /// <param name="orderBy"></param> /// <returns></returns> public TableRelation <T> OrderBy(OrderByClip orderBy) { section.OrderBy(orderBy); return(this); }