void TestIfCondition() { Users user = new Users() { ID = 1, UserName = "******", Password = "******", NickName = "" }; //NickName = "abc", OQLCompareFunc cmpFun = cmp => { //5.2.2.0428 版本之前,仅支持被注释掉的写法,用一个独立的对象来接收条件比较结果,否则会出错. // 之后的版本,按照现在的写法没问题,不过仍然建议使用原来的写法. OQLCompare cmpResult = null; if (user.NickName != "") { //cmpResult = cmp.Property(user.AddTime) > new DateTime(2013, 2, 1); cmp = cmp.Property(user.AddTime) > new DateTime(2013, 2, 1); } if (user.ID > 0) { //cmpResult = cmpResult & cmp.Property(user.UserName) == "ABC" & cmp.Comparer(user.Password, "=", "111"); cmp = cmp & cmp.Property(user.UserName) == "ABC" & cmp.Comparer(user.Password, "=", "111"); } //return cmpResult; cmpResult = cmp; return(cmpResult); }; OQL q6 = OQL.From(user).Select().Where(cmpFun).END; Console.WriteLine("OQL by 动态构建 OQLCompare Test(Lambda方式):\r\n{0}", q6); Console.WriteLine(q6.PrintParameterInfo()); }
private static OQLCompareFunc <BCustomer, SalesOrder> GetCondtion3() { OQLCompareFunc <BCustomer, SalesOrder> cmpFun = (cmp, C, S) => { OQLCompare cmpResult = null; cmpResult = cmp.Comparer(S.iBillID, OQLCompare.CompareType.Equal, 1); if (!string.IsNullOrEmpty(S.iOrderTypeID)) { cmpResult = cmpResult & cmp.Comparer(S.iOrderTypeID, OQLCompare.CompareType.Equal, S.iOrderTypeID); } else { cmp.NewCompare(); } int iCityID = 0; //由于调用了关联实体类的 S.iOrderTypeID 用于条件比较【上面的IsNullOrEmpty 调用】,并且C.iCityID==iCityID==0 , //所以下面需要调用 cmp.NewCompare(),以清除OQL字段堆栈中的数据对Comparer 方法的影响 //感谢网友 红枫星空 发现此问题 //如果 C.iCityID != iCityID ,尽管上面调用了关联实体类的属性,但 Comparer 方法不受影响,也不需要调用 NewCompare 方法 cmpResult = cmpResult & cmp.Comparer(C.iCityID, OQLCompare.CompareType.Equal, iCityID); return(cmpResult); }; return(cmpFun); }
/// <summary> /// 获取符合条件的实体对象列表 OQLCompareFunc /// </summary> /// <param name="cp">where条件,没有可传人null</param> /// <param name="orders">排序字段对象</param> /// <returns></returns> public static List <T> GetAllList <T>(OQLCompareFunc <T> cp, OQLOrder orders) where T : EntityBase, ILongID, new() { T m = new T(); OQL q = new OQL(m); if ((object)cp != null) { if ((object)orders != null) { q.Select().Where(cp).OrderBy(orders); } else { q.Select().Where(cp); } } else { if ((object)orders != null) { q.Select().OrderBy(orders); } else { q.Select(); } } q.PageWithAllRecordCount = GetRecordCounts <T>(cp); string s = q.PrintParameterInfo(); return(EntityQuery <T> .QueryList(q)); }
void Test4() { OQLCompareFunc <Users, UserRoles> cmpResult = (cmp, U, R) => ( cmp.Property(U.UserName) == "ABC" & cmp.Comparer(U.Password, "=", "111") & cmp.Comparer(R.RoleName, "=", "Role1") ) | ( (cmp.Comparer(U.UserName, "=", "CDE") & cmp.Property(U.Password) == "222" & cmp.Comparer(R.RoleName, "like", "%Role2") ) | (cmp.Property(U.LastLoginTime) > DateTime.Now.AddDays(-1)) ) ; Users user = new Users(); UserRoles roles = new UserRoles() { RoleName = "role1" }; OQL q4 = OQL.From(user).InnerJoin(roles) .On(user.RoleID, roles.ID) .Select() .Where(cmpResult) .END; Console.WriteLine("OQL by OQLCompareFunc<T1,T2> Test:\r\n{0}", q4); Console.WriteLine(q4.PrintParameterInfo()); q4.Dispose(); }
void Test3() { Users user = new Users(); UserRoles roles = new UserRoles() { RoleName = "role1" }; OQLCompareFunc cmpResult = cmp => ( cmp.Property(user.UserName) == "ABC" & cmp.Comparer(user.Password, "=", "111") & cmp.EqualValue(roles.RoleName) ) | ( (cmp.Comparer(user.UserName, OQLCompare.CompareType.Equal, "BCD") & cmp.Property(user.Password) == 222 & cmp.Comparer(roles.ID, "in", new RoleNames[] { RoleNames.User, RoleNames.Manager, RoleNames.Admin }) ) | (cmp.Property(user.LastLoginTime) > DateTime.Now.AddDays(-1)) ) ; OQL q3 = OQL.From(user).InnerJoin(roles) .On(user.RoleID, roles.ID) .Select() .Where(cmpResult) .END; Console.WriteLine("OQL by OQLCompareFunc Test:\r\n{0}", q3); Console.WriteLine(q3.PrintParameterInfo()); }
/// <summary> /// 删除 /// </summary> /// <param name="nodeCodes"></param> public void DeleteBaseConfigs(string nodeCodes) { try { //开始事物 db_dataReportWJW.BeginTransaction(); FgSys_BaseConfig entity = new FgSys_BaseConfig(); string[] nodeCodeArray = nodeCodes.Split(','); for (int i = 0; i < nodeCodeArray.Length; i++) { OQLCompareFunc <FgSys_BaseConfig> cmpFun = (cmp, u) => { OQLCompare cmpResult = null; cmpResult = cmpResult & cmp.Comparer(entity.NodeCode, OQLCompare.CompareType.Equal, nodeCodeArray[i]); return(cmpResult); }; OQL deleteQ = OQL.From(entity).Delete().Where(cmpFun).END; EntityQuery <FgSys_BaseConfig> .ExecuteOql(deleteQ, db_dataReportWJW); } //提交事物 db_dataReportWJW.Commit(); } catch (Exception err) { //回滚事物 db_dataReportWJW.Rollback(); throw err; } }
/// <summary> /// 根据id获取实体对象 /// </summary> /// <param name="ID">对象id</param> /// <returns></returns> public static T GetModel <T>(OQLCompareFunc <T> cp, bool IsUseCache = false) where T : EntityBase, ILongID, new() { T model = new T(); model = OQL.FromObject <T>().Select().Where(cp).END.ToObject(); return(model); }
void TestIfCondition() { Users user = new Users() { ID = 1, NickName = "abc", UserName = "******", Password = "******" }; OQLCompareFunc cmpFun = cmp => { OQLCompare cmpResult = null; if (user.NickName != "") { cmpResult = cmp.Property(user.AddTime) > new DateTime(2013, 2, 1); } if (user.ID > 0) { cmpResult = cmpResult & cmp.Property(user.UserName) == "ABC" & cmp.Comparer(user.Password, "=", "111"); } return(cmpResult); }; OQL q6 = OQL.From(user).Select().Where(cmpFun).END; Console.WriteLine("OQL by 动态构建 OQLCompare Test(Lambda方式):\r\n{0}", q6); Console.WriteLine(q6.PrintParameterInfo()); }
public static void 连表查询in() { SODUser user = new SODUser(); OQL q = new OQL(user); OQLCompareFunc resultFunc = cmp => { OQLCompare resultCmp = cmp.Comparer(user.ID, ">", 2); resultCmp = resultCmp & cmp.Comparer(user.LogName, "like", "%zhang%"); return(resultCmp); }; q.Select(user.ID).Where(resultFunc); SODUserRemark userRemark = new SODUserRemark(); OQL r = new OQL(userRemark); OQLCompareFunc resultFunc2 = cmp => { OQLCompare resultCmp = cmp.Comparer(userRemark.UserId, "IN", q); return(resultCmp); }; r.Select().Where(resultFunc2).OrderBy(userRemark.ID, "desc"); var outlist = EntityQuery <SODUserRemark> .QueryList(r); }
/// <summary> /// 获取管理员列表(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <CustomModel> GetPageList(string searchTxt, int pageIndex, int pageSize) { CustomModel CustomModel = new Model.Custom.CustomModel(); JsonRsp <CustomModel> rsp = new JsonRsp <CustomModel>(); OQLCompareFunc <CustomModel> cmpResult = (cmp, c) => ( //cmp.Property(c.CustomName) == "ABC" & cmp.Comparer(c.CustomName, OQLCompare.CompareType.Like, "%" + searchTxt + "%") ); rsp.success = true; rsp.code = 0; rsp.data = GetList <CustomModel>(cmpResult, null, pageSize, pageIndex); //CustomModel model = new CustomModel(); //CustomTypeModel customType = new CustomTypeModel(); //model.TenantId = TenantId; ////Select 方法不指定具体要选择的实体类属性,可以推迟到EntityContainer类的MapToList 方法上指定 //OQL joinQ = OQL.From(model) // .Join(customType).On(model.CustomTypeId, customType.ID) // .Select() // .OrderBy(model.Sort, "desc") // .END; //joinQ.Limit(pageSize, pageIndex, true); //PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper(); //EntityContainer ec = new EntityContainer(joinQ, db); //rsp.data = (List<CustomViewModel>)ec.MapToList<CustomViewModel>(() => new CustomViewModel() //{ // ID = model.ID, // CustomTypeId = model.CustomTypeId, // CustomTypeName = customType.CustomTypeName, // CustomName = model.CustomName, // LinkPhone = model.LinkPhone, // LinkName = model.LinkName, // LinkMobile = model.LinkMobile, // CustomArea = model.CustomArea, // CustomAddress = model.CustomAddress, // CreateBy = model.CreateUser, // CreateIP = model.CreateIP, // CreateTime = model.CreateTime, // Sort = model.Sort, // Status = model.Status, // UpdateBy = model.UpdateUser, // UpdateIP = model.UpdateIP, // UpdateTime = model.UpdateTime, //}); //rsp.success = true; //rsp.code = 0; //rsp.count = joinQ.PageWithAllRecordCount; return(rsp); }
/// <summary> /// 查询所有字段匹配的实体类,传入的实体类需要保证要查询的字段有值 /// </summary> /// <param name="data">传入的实体类,查询字段需要有值</param> /// <param name="cmpFun">查询比较方法,传入null表示搜索表中所有数据</param> /// <returns></returns> public List <T> GetModelList(T data, OQLCompareFunc cmpFun) { if (cmpFun == null) { return(OQL.From(data).Select().END.ToList <T>()); } return(OQL.From(data).Select().Where(cmpFun).END.ToList <T>()); }
/// <summary> /// 查询字段匹配的实体类,传入的实体类需要保证查询的字段有值 /// </summary> /// <param name="data">传入的实体类,查询字段需要有值</param> /// <param name="cmpFun">查询比较方法</param> /// <returns></returns> public T GetModel(T data, OQLCompareFunc cmpFun) { if (cmpFun == null) { return(null); } return(OQL.From(data).Select().Where(cmpFun).END.ToEntity <T>()); }
/// <summary> /// 查询列表 /// </summary> /// <param name="sqlWhere"></param> /// <param name="pageSize"></param> /// <param name="pageCurrent"></param> /// <param name="listCount"></param> /// <param name="orderBy"></param> /// <returns></returns> public List <FgSys_BaseConfig> GetBaseConfigList(Dictionary <string, string> sqlWhere, int pageSize, int pageCurrent, ref int listCount, string[] orderBy) { try { FgSys_BaseConfig entity = new FgSys_BaseConfig(); OQLCompareFunc <FgSys_BaseConfig> cmpFun = (cmp, u) => { OQLCompare cmpResult = null; if (sqlWhere.Keys.Contains("NodeCode") && !string.IsNullOrEmpty(sqlWhere["NodeCode"])) { cmpResult = cmpResult & cmp.Comparer(entity.NodeCode, OQLCompare.CompareType.Like, "%" + sqlWhere["NodeCode"] + "%"); } if (sqlWhere.Keys.Contains("NodeDomain") && !string.IsNullOrEmpty(sqlWhere["NodeDomain"])) { cmpResult = cmpResult & cmp.Comparer(entity.NodeDomain, OQLCompare.CompareType.Like, "%" + sqlWhere["NodeDomain"] + "%"); } return(cmpResult); }; OQL query; if (orderBy != null) { query = OQL.From(entity).Select().Where(cmpFun).OrderBy(orderBy).END; } else { query = OQL.From(entity).Select().Where(cmpFun).OrderBy(new string[1] { "NodeCode asc" }).END; } if (pageSize != 0 && pageCurrent != 0) { //查询总条数 OQL qCount = OQL.From(entity).Select().Count(entity.NodeCode, "").Where(cmpFun).END; listCount = Convert.ToInt32(EntityQuery <FgSys_BaseConfig> .QueryObject(qCount, db_dataReportWJW).NodeCode);//总条数 //构造分页 query.Limit(pageSize, pageCurrent);//分页大小,第几页 query.PageWithAllRecordCount = listCount; } //OQL query = OQL.From(entity).Select().END; List <FgSys_BaseConfig> list = EntityQuery <FgSys_BaseConfig> .QueryList(query, db_dataReportWJW); return(list); } catch (Exception err) { throw err; } }
public JsonRsp CheckAccount(string accountName, long accountId = 0) { OQLCompareFunc <AdminAccount> cmpResult = (cmp, c) => ( cmp.Comparer(c.TenantId, OQLCompare.CompareType.Equal, TenantId) & cmp.Comparer(c.AccountName, OQLCompare.CompareType.Equal, accountName) ); var model = GetModel <AdminAccount>(cmpResult); return(new JsonRsp { success = (model == null) }); }
void Test6() { OQLCompareFunc <Users> cmpResult = (cmp, u) => cmp.Comparer(u.UserName, OQLCompare.CompareType.IN, new string[] { "zhang aa", "li bb", "wang cc" }); // Users user = new Users(); OQL q6 = OQL.From(user) .Select() .Where(cmpResult) .END; Console.WriteLine("OQL by OQLCompareFunc<T1> Test:\r\n{0}", q6); Console.WriteLine(q6.PrintParameterInfo()); q6.Dispose(); }
/// <summary> /// 获取管理员列表(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <CustomModel> GetPageList(string searchTxt, int pageIndex, int pageSize) { CustomModel CustomModel = new CustomModel(); JsonRsp <CustomModel> rsp = new JsonRsp <CustomModel>(); OQLCompareFunc <CustomModel> cmpResult = (cmp, c) => ( //cmp.Property(c.CustomName) == "ABC" & cmp.Comparer(c.CustomName, OQLCompare.CompareType.Like, "%" + searchTxt + "%") ); rsp.success = true; rsp.code = 0; rsp.data = GetList <CustomModel>(cmpResult, null, pageSize, pageIndex); return(rsp); }
/// <summary> /// 获取管理员列表(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <ProductTypeViewModel> GetPageList(string searchTxt, int pageIndex, int pageSize) { JsonRsp <ProductTypeViewModel> rsp = new JsonRsp <ProductTypeViewModel>(); ProductTypeModel pt = new ProductTypeModel(); MerchantTypeModel mt = new MerchantTypeModel(); OQLCompareFunc <ProductTypeModel> cmpFun = (cmp, u) => { OQLCompare cmpResult = null; //and 条件 cmpResult = cmpResult & cmp.Comparer(pt.ProductTypeName, OQLCompare.CompareType.Like, "%" + searchTxt + "%"); //or cmpResult = cmpResult | cmp.Comparer(pt.TenantId, OQLCompare.CompareType.Equal, TenantId); return(cmpResult); }; //Select 方法不指定具体要选择的实体类属性,可以推迟到EntityContainer类的MapToList 方法上指定 OQL joinQ = OQL.From(pt) .Join(mt).On(pt.MerchantTypeId, mt.ID) .Select(pt.ID, pt.ProductTypeName, mt.MerchantTypeName, pt.Status, pt.CreateTime) .Where(cmpFun) .OrderBy(pt.Sort, "desc") .END; joinQ.Limit(pageSize, pageIndex, true); PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper(); EntityContainer ec = new EntityContainer(joinQ, db); rsp.data = (List <ProductTypeViewModel>)ec.MapToList <ProductTypeViewModel>(() => new ProductTypeViewModel() { ID = pt.ID, ProductTypeName = pt.ProductTypeName, MerchantTypeId = mt.ID, MerchantTypeName = mt.MerchantTypeName, CreateId = pt.CreateId, CreateUser = pt.CreateUser, CreateIP = pt.CreateIP, CreateTime = pt.CreateTime, Status = pt.Status, }); rsp.success = true; rsp.code = 0; rsp.count = joinQ.PageWithAllRecordCount; return(rsp); }
/// <summary> /// 取得记录总数 /// </summary> /// <param name="cp">where条件</param> /// <returns></returns> public static int GetRecordCounts <T>(OQLCompareFunc <T> cp, AdoHelper ado) where T : EntityBase, ILongID, new() { T m = new T(); OQL q = new OQL(m); if ((object)cp != null) { q.Select().Count(m.ID, "Counts").Where(cp); } else { q.Select().Count(m.ID, "Counts"); } T mCounts = EntityQuery <T> .QueryObject(q, ado); return(Convert.ToInt32(mCounts.PropertyList("Counts"))); }
void Test5() { Users user = new Users(); OQLCompareFunc cmpResult = cmp => ( cmp.Property(user.AddTime) > new DateTime(2013, 2, 1) & cmp.Comparer(user.AddTime, "<", new DateTime(2013, 3, 1), "dateadd(hour,24,{0})") ) | ( cmp.Property(user.Authority) == "ABC" | cmp.Property(user.Authority) == "CDE" ) ; OQL q5 = OQL.From(user).Select().Where(cmpResult).END; Console.WriteLine("OQL by OQLCompareFunc 括号化简 Test:\r\n{0}", q5); Console.WriteLine(q5.PrintParameterInfo()); }
private static void TestOQL() { SalesOrder order = new SalesOrder(); //model.iOrderTypeID = "123"; BCustomer customer = new BCustomer(); //请注意方法 GetCondtion1,GetCondtion2,GetCondtion3 中变量 iCityID 的不同而带来的构造条件语句的不同 OQLCompareFunc <BCustomer, SalesOrder> cmpFun = GetCondtion1(); OQL q = OQL.From(order) .LeftJoin(customer).On(order.iCustomerID, customer.ISID) .Select() .Where(cmpFun) .OrderBy(order.iBillID, "desc") .END; Console.WriteLine(q); Console.WriteLine(q.PrintParameterInfo()); //此OQL 可以由 EntityContainer 对象的方法执行 }
public static void 分页查询() { SODUser user = new SODUser(); OQL q = new OQL(user); OQLCompareFunc resultFunc = cmp => { OQLCompare resultCmp = cmp.Comparer(user.ID, ">", 0); resultCmp = resultCmp & cmp.Comparer(user.LogName, "like", "%zhang%"); return(resultCmp); }; q.Select().Where(resultFunc).OrderBy(user.ID); //分页 q.PageEnable = true; q.PageWithAllRecordCount = 10; q.Limit(10, 1, true); var outlist = EntityQuery <SODUser> .QueryList(q); int pageCount = q.PageWithAllRecordCount; }
private static OQLCompareFunc <BCustomer, SalesOrder> GetCondtion1() { OQLCompareFunc <BCustomer, SalesOrder> cmpFun = (cmp, C, S) => { OQLCompare cmpResult = null; cmpResult = cmp.Comparer(S.iBillID, OQLCompare.CompareType.Equal, 1); if (!string.IsNullOrEmpty(S.iOrderTypeID)) { cmpResult = cmpResult & cmp.Comparer(S.iOrderTypeID, OQLCompare.CompareType.Equal, S.iOrderTypeID); } int iCityID = 30; //由于调用了关联实体类的 S.iOrderTypeID 用于条件比较,所以下面的比较需要注意: //必须确保 Comparer 方法第一个参数调用为实体类属性,而不是待比较的值 // 且第一个参数的值不能等于第三个参数的值,否则需要调用NewCompare() 方法 cmpResult = cmpResult & cmp.Comparer(C.iCityID, OQLCompare.CompareType.Equal, iCityID); return(cmpResult); }; return(cmpFun); }
public GOQL2 <T> Where(OQLCompareFunc <T> func) { this.currentOQL1.Where(func); return(new GOQL2 <T>(currentGOQL)); }
public Platform GetModel(Platform data, OQLCompareFunc cmpFun) { return(m_dbHelper.GetModel(data, cmpFun)); }
public List <User> GetModelList(User data, OQLCompareFunc cmpFun) { return(m_dbHelper.GetModelList(data, cmpFun)); }
public List <Platform> GetModelList(Platform data, OQLCompareFunc cmpFun) { return(m_dbHelper.GetModelList(data, cmpFun)); }
public User GetModel(User data, OQLCompareFunc cmpFun) { return(m_dbHelper.GetModel(data, cmpFun)); }