public void Should_return_empty_if_orderBy_is_null() { _orderBy.Add(null); string result = _builder.ToString(); result.ShouldBeEqualTo(""); }
public void Add_with_index_adds_orderby() { OrderBy orderBy = new OrderBy("foo asc"); orderBy.Add("bar desc"); orderBy.Add(0, "fooBar desc"); orderBy.Sql().MustBe("order by fooBar desc,foo asc,bar desc"); }
public List <OrderBy <Activity> > DynamicOrderBy(string orderBy, int?EntityId = null) { var OrderByList = new List <OrderBy <Activity> >(); if (!string.IsNullOrEmpty(orderBy)) { List <string> orders = orderBy.Split(',').ToList(); orders.ForEach(x => { if (x.Contains("[")) { var ComplexOrder = x.Split(' ').ToList(); switch (ComplexOrder[0].Trim()) { case "[Reviews.Rate]": OrderByList.Add(OrderBy <Activity> .Add(y => y.Reviews.Where(z => !z.IsDeleted).OrderBy(z => z.Rate).Select(z => z.Rate).Sum(), ComplexOrder.Count > 1 ? ComplexOrder[1].DecodeDirection() : OrderDirection.ASC)); break; } } else { OrderByList.Add(OrderBy <Activity> .Add(x)); } }); } return(OrderByList); }
public Part AddOrderBy(string value, string name) { var part = new Part(value, name); OrderBy.Add(part); return(part); }
public static void ConstructTableObjectFromPostRequest(JSONTable jt, ref BaseTable bt, ref int pageIndex, ref int pageSize, ref int totalRows, ref SqlFilter whereFilter, ref BaseFilter join, ref OrderBy orderBy) { pageIndex = jt.PageIndex; pageSize = jt.PageSize; totalRows = jt.TotalRows; bt = (BaseTable)DatabaseObjects.GetTableObject(jt.TableName); ColumnList selCols = new ColumnList(); foreach (JTableSelectColumn col in jt.JSelectColumns) { selCols.Add(bt.TableDefinition.ColumnList.GetByCodeName(col.ColumnName), true); } bt.SelectedColumns.Clear(); bt.SelectedColumns.AddRange(selCols); if ((jt.JOrderByList != null)) { foreach (JOrderBy jOrderBy in jt.JOrderByList) { orderBy = new OrderBy(true, false); orderBy.Add(bt.TableDefinition.ColumnList.GetByCodeName(jOrderBy.ColumnName), OrderByItem.ToOrderDir(jOrderBy.OrderDirection)); } } if (jt.JWhereClause != null && jt.JWhereClause.Trim() != "") { whereFilter = new SqlFilter(jt.JWhereClause); } }
public void CommonUse() { // some times the select can be static in son place (the service of a DAO) Select s = new Select("f.Name, f.Description, b.Descriptoin").From("Foo f join f.Bar b"); // Create a new where by checking some contition Where where = new Where(); // check something and set a condition of where clause where.And("f.Name like :pName"); // check something else and set another condition of where clause where.And("b.Asociated > :pAso"); // Inject the where to the select s.From().SetWhere(where); // Create a basic order by OrderBy order = new OrderBy().Add("b.Asociated", true); // Check some condition and add an order order.Add("f.Name"); // Inject the OrderBy to the select s.From().SetOrderBy(order); s.From().OrderBy("b.Other"); // And: The winner is.... string expected = "select f.Name, f.Description, b.Descriptoin from Foo f join f.Bar b where ((f.Name like :pName) and (b.Asociated > :pAso)) order by b.Asociated desc, f.Name, b.Other"; Assert.AreEqual(expected, s.Clause); }
public PageView(NameValueCollection nvc) { if (!string.IsNullOrEmpty(nvc["page"])) { PageIndex = Convert.ToInt32(nvc["page"]) - 1; } else { PageIndex = 0; } PageSize = !string.IsNullOrEmpty(nvc["rp"]) ? Convert.ToInt32(nvc["rp"]) : 35; if (!string.IsNullOrEmpty(nvc["sortname"]) && nvc["sortname"] != "undefined") { OrderByType type = nvc["sortorder"] == "desc" ? OrderByType.Desc : OrderByType.Asc; OrderBy.Add(nvc["sortname"], type); } if (!string.IsNullOrEmpty(nvc["qtype"])) { QuickLuanch = new QuickLuanch { FieldName = nvc["qtype"], Operator = (nvc["qop"] == "Eq" ? Operator.Eq : Operator.Like), Value = nvc["query"] }; } }
public void Add_adds_orderby() { OrderBy orderBy = new OrderBy("foo asc"); orderBy.Add("bar desc"); orderBy.Sql().MustBe("order by foo asc,bar desc"); }
public void AddOrderBy(string key, string column) { if (OrderBy == null) { OrderBy = new Dictionary <string, string>(); } OrderBy.Add(key, column); }
public CargoQueryExpression ThenOrderBy(Expression expression, bool descending = false) { Debug.Assert(expression != null); var newInst = (CargoQueryExpression)MemberwiseClone(); newInst.OrderBy = OrderBy.Add(new OrderByExpression(expression, descending)); return(newInst); }
// GET api/<controller>/5 public DBModel.Person_FaceInfoModel Get(int id) { /******************************************使用示例******************************************/ //根据ID查询 Person_FaceInfoModel 对应表的所有记录 DBModel.Person_FaceInfoModel addModel = new DBModel.Person_FaceInfoModel() { //给字段赋值 }; //新增一条数据 YEasyModel.ModelDAL.Insert(addModel); //根据主键ID修改一条数据 YEasyModel.ModelDAL.Update(addModel); //修改一条数据,指定条件、更新字段 YEasyModel.ModelDAL.Update <DBModel.Person_FaceInfoModel>(addModel, q => q.FaceID == 1); YEasyModel.ModelDAL.Update <DBModel.Person_FaceInfoModel>(addModel, q => q.FaceID == 1, q => q.FacePath, q => q.Remark); //根据ID查询 Person_FaceInfoModel 对应表的所有记录 YEasyModel.ModelDAL.GetModel <DBModel.Person_FaceInfoModel>(id); //查询所有数据 YEasyModel.ModelDAL.Select <DBModel.Person_FaceInfoModel>(); //根据FaceID查询单条记录 YEasyModel.ModelDAL.SelectSingleRecord <DBModel.Person_FaceInfoModel>(q => q.FaceID == id); //按条件查询数据 YEasyModel.ModelDAL.Select <DBModel.Person_FaceInfoModel>(q => q.Create_Date > DateTime.Now.AddDays(-1) && q.Create_Date < DateTime.Now && q.Remark != ""); //按条件查询、指定字段1、字段2... YEasyModel.ModelDAL.SelectSingleRecord <DBModel.Person_FaceInfoModel>(q => q.FaceID == id, null, q => q.Person_ID, q => q.FacePath); //创建字段排序规则 var order = new OrderBy <DBModel.Person_FaceInfoModel>(); order.Add(q => q.Create_Date, OrderByEnum.asc); //查询所有数据+排序 YEasyModel.ModelDAL.Select <DBModel.Person_FaceInfoModel>(null, order, null); //删除指定ID数据 YEasyModel.ModelDAL.Delete <DBModel.Person_FaceInfoModel>(1); //删除指定条件的数据 YEasyModel.ModelDAL.Delete <DBModel.Person_FaceInfoModel>(q => q.FacePath == "" && q.Remark == ""); DataTable dt = new DataTable();//查询数据 //DataTable数据转实体列表 YEasyModel.ModelUtil.DataTableParse <DBModel.Person_FaceInfoModel>(dt); //DataRow数据转实体 YEasyModel.ModelUtil.DataRowParse <DBModel.Person_FaceInfoModel>(dt.Rows[0]); return(YEasyModel.ModelDAL.Select <DBModel.Person_FaceInfoModel>(q => q.FaceID == id)[0]);//根据FaceID查询记录 }
public void ToRowCount() { Select s = new Select("f.Name, f.Description, b.Descriptoin").From("Foo f"); s.From().Join("f.Bar b"); Where where = new Where(); where.And("f.Name like :pName"); where.And("b.Asociated > :pAso"); s.From().SetWhere(where); OrderBy order = new OrderBy().Add("b.Asociated", true); order.Add("f.Name"); s.From().SetOrderBy(order); DetachedDynQuery ddq = new DetachedDynQuery(s); DetachedQuery drc = (DetachedQuery)ddq.TransformToRowCount(); Assert.AreEqual("select count(*) from Foo f join f.Bar b where ((f.Name like :pName) and (b.Asociated > :pAso))", drc.Hql); }
private bool ParseOrderByExpression(MethodCallExpression expression, string order = null) { UnaryExpression unary = (UnaryExpression)expression.Arguments[1]; LambdaExpression lambdaExpression = (LambdaExpression)unary.Operand; if (!string.IsNullOrEmpty(order)) { order = " " + order.Trim(); } MemberExpression body = lambdaExpression.Body is MemberExpression ? (MemberExpression)lambdaExpression.Body : ((UnaryExpression)lambdaExpression.Body).Operand as MemberExpression; if (body != null) { var exp = new List <string>(); exp.Add(body.Member.Name); while (body.Expression is MemberExpression) { body = (MemberExpression)body.Expression; exp.Insert(0, body.Member.Name); } if (OrderBy == null) { OrderBy = new List <string>(); } OrderBy.Add(string.Format("{0}{1}", string.Join(".", exp), order)); return(true); } return(false); }
// Get the filters' data for UOMNameFilter. protected virtual void PopulateUOMNameFilter(string selectedValue, int maxItems) { this.UOMNameFilter.Items.Clear(); // Set up the WHERE and the ORDER BY clause by calling the CreateWhereClause_UOMNameFilter function. // It is better to customize the where clause there. // Setup the WHERE clause, WhereClause wc = this.CreateWhereClause_UOMNameFilter(); // Setup the static list items // Add the All item. this.UOMNameFilter.Items.Insert(0, new ListItem(this.Page.GetResourceValue("Txt:All", "FPCEstimate"), "--ANY--")); OrderBy orderBy = new OrderBy(false, false); orderBy.Add(UOMTable.UOMName, OrderByItem.OrderDir.Asc); string[] values = UOMTable.GetValues(UOMTable.UOMName, wc, orderBy, maxItems); foreach (string itemValue in values) { // Create the item and add to the list. string fvalue; if ( UOMTable.UOMName.IsColumnValueTypeBoolean()) { fvalue = itemValue; }else { fvalue = UOMTable.UOMName.Format(itemValue); } ListItem item = new ListItem(fvalue, itemValue); this.UOMNameFilter.Items.Add(item); } // Set the selected value. MiscUtils.SetSelectedValue(this.UOMNameFilter, selectedValue); }
// Fill the UOMID list. protected virtual void PopulateUOMIDDropDownList(string selectedValue, int maxItems) { this.UOMID.Items.Clear(); // 1. Setup the static list items // Add the Please Select item. this.UOMID.Items.Insert(0, new ListItem(this.Page.GetResourceValue("Txt:PleaseSelect", "FPCEstimate"), "--PLEASE_SELECT--")); // 2. Set up the WHERE and the ORDER BY clause by calling the CreateWhereClause_UOMIDDropDownList function. // It is better to customize the where clause there. WhereClause wc = CreateWhereClause_UOMIDDropDownList(); // Create the ORDER BY clause to sort based on the displayed value. OrderBy orderBy = new OrderBy(false, false); orderBy.Add(UOMTable.UOMName, OrderByItem.OrderDir.Asc); System.Collections.Generic.IDictionary<string, object> variables = new System.Collections.Generic.Dictionary<string, object> (); // 3. Read a total of maxItems from the database and insert them into the UOMIDDropDownList. UOMRecord[] itemValues = null; if (wc.RunQuery) { int counter = 0; int pageNum = 0; FormulaEvaluator evaluator = new FormulaEvaluator(); do { itemValues = UOMTable.GetRecords(wc, orderBy, pageNum, maxItems); foreach (UOMRecord itemValue in itemValues) { // Create the item and add to the list. string cvalue = null; string fvalue = null; if (itemValue.UOMIDSpecified) { cvalue = itemValue.UOMID.ToString().ToString(); if (counter < maxItems && this.UOMID.Items.FindByValue(cvalue) == null) { Boolean _isExpandableNonCompositeForeignKey = ScopeTable.Instance.TableDefinition.IsExpandableNonCompositeForeignKey(ScopeTable.UOMID); if(_isExpandableNonCompositeForeignKey && ScopeTable.UOMID.IsApplyDisplayAs) fvalue = ScopeTable.GetDFKA(itemValue, ScopeTable.UOMID); if ((!_isExpandableNonCompositeForeignKey) || (String.IsNullOrEmpty(fvalue))) fvalue = itemValue.Format(UOMTable.UOMName); if (fvalue == null || fvalue.Trim() == "") fvalue = cvalue; ListItem newItem = new ListItem(fvalue, cvalue); this.UOMID.Items.Add(newItem); counter += 1; } } } pageNum++; } while (itemValues.Length == maxItems && counter < maxItems); } // 4. Set the selected value (insert if not already present). if (selectedValue != null && selectedValue.Trim() != "" && !MiscUtils.SetSelectedValue(this.UOMID, selectedValue) && !MiscUtils.SetSelectedDisplayText(this.UOMID, selectedValue)) { // construct a whereclause to query a record with UOM.UOMID = selectedValue CompoundFilter filter2 = new CompoundFilter(CompoundFilter.CompoundingOperators.And_Operator, null); WhereClause whereClause2 = new WhereClause(); filter2.AddFilter(new BaseClasses.Data.ColumnValueFilter(UOMTable.UOMID, selectedValue, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, false)); whereClause2.AddFilter(filter2, CompoundFilter.CompoundingOperators.And_Operator); // Execute the query try { UOMRecord[] rc = UOMTable.GetRecords(whereClause2, new OrderBy(false, false), 0, 1); System.Collections.Generic.IDictionary<string, object> vars = new System.Collections.Generic.Dictionary<string, object> (); // if find a record, add it to the dropdown and set it as selected item if (rc != null && rc.Length == 1) { string fvalue = ScopeTable.UOMID.Format(selectedValue); ListItem item = new ListItem(fvalue, selectedValue); item.Selected = true; this.UOMID.Items.Add(item); } } catch { } } }
// Get the filters' data for SiteNameFilter. protected virtual void PopulateSiteNameFilter(string selectedValue, int maxItems) { this.SiteNameFilter.Items.Clear(); // Set up the WHERE and the ORDER BY clause by calling the CreateWhereClause_SiteNameFilter function. // It is better to customize the where clause there. // Setup the WHERE clause, WhereClause wc = this.CreateWhereClause_SiteNameFilter(); // Setup the static list items OrderBy orderBy = new OrderBy(false, false); orderBy.Add(VwSiteView.SiteName, OrderByItem.OrderDir.Asc); string[] values = VwSiteView.GetValues(VwSiteView.SiteName, wc, orderBy, maxItems); foreach (string itemValue in values) { // Create the item and add to the list. string fvalue; if ( VwSiteView.SiteName.IsColumnValueTypeBoolean()) { fvalue = itemValue; }else { fvalue = VwSiteView.SiteName.Format(itemValue); } ListItem item = new ListItem(fvalue, itemValue); this.SiteNameFilter.Items.Add(item); } // Set the selected value. MiscUtils.SetSelectedValue(this.SiteNameFilter, selectedValue); }
// Get the filters' data for ParentCatIDFilter. protected virtual void PopulateParentCatIDFilter(string selectedValue, int maxItems) { this.ParentCatIDFilter.Items.Clear(); // Set up the WHERE and the ORDER BY clause by calling the CreateWhereClause_ParentCatIDFilter function. // It is better to customize the where clause there. //Setup the WHERE clause. WhereClause wc =this.CreateWhereClause_ParentCatIDFilter(); // Setup the static list items // Add the All item. this.ParentCatIDFilter.Items.Insert(0, new ListItem(this.Page.GetResourceValue("Txt:All", "FPCEstimate"), "--ANY--")); OrderBy orderBy = new OrderBy(false, false); orderBy.Add(CategoryTable.CatName, OrderByItem.OrderDir.Asc); System.Collections.Generic.IDictionary<string, object> variables = new System.Collections.Generic.Dictionary<string, object> (); string noValueFormat = Page.GetResourceValue("Txt:Other", "FPCEstimate"); CategoryRecord[] itemValues = null; if (wc.RunQuery) { int counter = 0; int pageNum = 0; FormulaEvaluator evaluator = new FormulaEvaluator(); do { itemValues = CategoryTable.GetRecords(wc, orderBy, pageNum, maxItems); foreach (CategoryRecord itemValue in itemValues) { // Create the item and add to the list. string cvalue = null; string fvalue = null; if (itemValue.CatIDSpecified) { cvalue = itemValue.CatID.ToString(); if (counter < maxItems && this.ParentCatIDFilter.Items.FindByValue(cvalue) == null) { Boolean _isExpandableNonCompositeForeignKey = CategoryTable.Instance.TableDefinition.IsExpandableNonCompositeForeignKey(CategoryTable.ParentCatID); if(_isExpandableNonCompositeForeignKey && CategoryTable.ParentCatID.IsApplyDisplayAs) fvalue = CategoryTable.GetDFKA(itemValue, CategoryTable.ParentCatID); if ((!_isExpandableNonCompositeForeignKey) || (String.IsNullOrEmpty(fvalue))) fvalue = itemValue.Format(CategoryTable.CatName); if (fvalue == null || fvalue.Trim() == "") fvalue = cvalue; ListItem newItem = new ListItem(fvalue, cvalue); this.ParentCatIDFilter.Items.Add(newItem); counter += 1; } } } pageNum++; } while (itemValues.Length == maxItems && counter < maxItems); } // Set the selected value. MiscUtils.SetSelectedValue(this.ParentCatIDFilter, selectedValue); }