/// <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="ID"></param> /// <returns></returns> public T GetModel(params object[] id) { if (id == null || id.Length == 0) { return(null); } Field[] pkfields = Hxj.Data.Common.EntityCache.GetPrimaryKeyFields <T>(); if (id == null) { throw new ArgumentNullException("请指定主键值!"); } if (pkfields == null || pkfields.Length == 0) { throw new Exception("数据表[" + Hxj.Data.Common.EntityCache.GetTableName <T>() + "]不存在任何主键!"); } T t = null; if (pkfields.Length == 1) { if (_trans != null) { t = _trans.From <T>().Where(Hxj.Data.Common.EntityCache.GetPrimaryKeyFields <T>()[0] == id).ToFirst(); } t = DBSession.CurrentSession.From <T>().Where(Hxj.Data.Common.EntityCache.GetPrimaryKeyFields <T>()[0] == id[0]).ToFirst(); } else { if (id.Length != pkfields.Length) { throw new Exception("指定的主键值个数与数据表的主键个数不符!"); } FromSection <T> section = null; if (_trans != null) { section = _trans.From <T>(); } else { section = DBSession.CurrentSession.From <T>(); } WhereClipBuilder wcb = new WhereClipBuilder(); for (int i = 0; i < pkfields.Length; i++) { wcb.And(pkfields[i] == id[i]); } t = section.Where(wcb.ToWhereClip()).ToFirst(); } GetRelations(ref t); return(t); }
/// <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; }
private void BindRepeater() { int page = AspNetPager1.CurrentPageIndex; if (!this.IsPostBack) { if (Request["page"] + "" != "") { page = int.Parse(Request["page"] + ""); } } FromSection <Dos.Model.Manager_Groups> fromsection = DbSession.Default.From <Dos.Model.Manager_Groups>() .Where(s => s.Status.Equals(0)) .OrderBy(s => s.AddTime).OrderByDescending(s => s.Id); //List<object> listDesc = new List<object>(); //var carlist = DbSession.Default.fr.Manager_Groups.Where(s => s.Status.Equals(0)).OrderBy(o => o.AddTime).ThenByDescending(o => o.Id); if (!string.IsNullOrEmpty(txtKeywords.Text.Trim())) { fromsection = fromsection.Where(s => s.Name.Contains(txtKeywords.Text)); } //carlist.ToPagedList(page, AspNetPager1.PageSize); AspNetPager1.RecordCount = fromsection.Count(); ltlCount.Text = "<b>" + fromsection.Count() + "</b>"; var dblist = fromsection.Page(AspNetPager1.PageSize, page).ToList(); if (!this.IsPostBack) { if (Request["page"] + "" != "") { int temp = int.Parse(Request["page"] + ""); AspNetPager1.CurrentPageIndex = temp; } } WebUtil.CtrlToList <Dos.Model.Manager_Groups>(rptLoop, dblist); }
/// <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="where"></param> /// <param name="order"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="recordCount"></param> /// <returns></returns> public DataTable GetDataTable(FromSection fromSection, WhereClip where, OrderByClip order, int pageIndex, int pageSize, ref int recordCount) { recordCount = fromSection.Where(where).Count(); return(fromSection.Where(where).OrderBy(order).Page(pageSize, pageIndex).ToDataTable()); }
/// <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="where"></param> /// <returns></returns> public TableRelation <T> Where(WhereClip where) { section.Where(where); return(this); }