/// <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> /// 创建分页查询 /// </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="groupBy"></param> /// <returns></returns> public TableRelation <T> GroupBy(GroupByClip groupBy) { section.GroupBy(groupBy); return(this); }