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 <MerchantViewModel> GetPageList(int pageIndex, int pageSize) { JsonRsp <MerchantViewModel> rsp = new JsonRsp <MerchantViewModel>(); MerchantModel model = new MerchantModel(); MerchantTypeModel MerchantType = new MerchantTypeModel(); model.TenantId = TenantId; //Select 方法不指定具体要选择的实体类属性,可以推迟到EntityContainer类的MapToList 方法上指定 OQL joinQ = OQL.From(model) .Join(MerchantType).On(model.MerchantTypeId, MerchantType.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 <MerchantViewModel>)ec.MapToList <MerchantViewModel>(() => new MerchantViewModel() { ID = model.ID, MerchantTypeId = model.MerchantTypeId, MerchantTypeName = MerchantType.MerchantTypeName, MerchantName = model.MerchantName, LinkPhone = model.LinkPhone, LinkName = model.LinkName, LinkMobile = model.LinkMobile, MerchantArea = model.MerchantArea, MerchantAddress = model.MerchantAddress, 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); }
void TestChild() { Users user = new Users(); UserRoles roles = new UserRoles(); OQL child = OQL.From(roles) .Select(roles.ID) .Where(p => p.Comparer(roles.NickName, "like", "%ABC")) .END; OQL q = OQL.From(user) .Select(user.ID, user.UserName) .Where(cmp => cmp.Comparer(user.RoleID, "in", child)) .END; Console.WriteLine("OQL by 子查询Test:\r\n{0}", q); Console.WriteLine(q.PrintParameterInfo()); }
/// <summary> /// 获取商品信息 /// </summary> /// <param name="productId"></param> /// <returns></returns> public ProductDto GetProductInfo(int productId) { ProductDbContext context = new ProductDbContext(); ProductEntity entity = OQL.From <ProductEntity>().With(OQL.SqlServerLock.NOLOCK) .Select() .Where((cmp, p) => cmp.Comparer(p.ID, "=", productId)) .END .ToObject(context.CurrentDataBase); ProductDto dto = new ProductDto(); if (entity != null) { //entity.MapToPOCO(dto); entity.CopyTo <ProductDto>(dto); } return(dto); }
/// <summary> /// 获取管理员列表(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <ContractViewModel> GetPageList(int pageIndex, int pageSize) { JsonRsp <ContractViewModel> rsp = new JsonRsp <ContractViewModel>(); ContractModel contract = new ContractModel(); CustomModel custom = new CustomModel(); CustomTypeModel customType = new CustomTypeModel(); //Select 方法不指定具体要选择的实体类属性,可以推迟到EntityContainer类的MapToList 方法上指定 OQL joinQ = OQL.From(contract) .Join(custom).On(contract.CustomId, custom.ID) .Join(customType).On(custom.CustomTypeId, customType.ID) .Select() .OrderBy(contract.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 <ContractViewModel>)ec.MapToList <ContractViewModel>(() => new ContractViewModel() { ID = contract.ID, CustomId = contract.CustomId, CustomName = custom.CustomName, CustomTypeName = customType.CustomTypeName, ContractNo = contract.ContractNo, ContractAmount = contract.ContractAmount, Deductions = contract.Deductions, Balance = contract.Balance, Remark = contract.Remark, Sort = contract.Sort, Status = contract.Status, CreateId = contract.CreateId, CreateUser = contract.CreateUser, CreateIP = contract.CreateIP, CreateTime = contract.CreateTime, }); rsp.success = true; rsp.code = 0; rsp.count = joinQ.PageWithAllRecordCount; return(rsp); }
void TestMapOql() { Users user = new Users() { NickName = "pdf.net", RoleID = RoleNames.Admin }; //插入一个数据便于测试 EntityQuery <Users> .Instance.Insert(user); OQL q = OQL.From(user).Select().Where(user.RoleID).END; EntityContainer ec = new EntityContainer(q); //第一种映射方式,适用于OQL单实体类查询: var list = ec.MapToList(user, u => new { P1 = u.ID, P2 = u.NickName } ); UserRoles role = new UserRoles(); OQL q2 = OQL.From(user) .Join(role).On(user.RoleID, role.ID) .Select() .Where(cmp => cmp.Comparer(role.ID, "=", RoleNames.User)) .OrderBy(role.RoleName) .END; q2.Limit(10, 2); q2.PageWithAllRecordCount = 0; EntityContainer ec2 = new EntityContainer(q2); //第二种映射方式,推荐用于OQL多个实体类关联查询的情况: var list2 = ec2.MapToList(() => new { P1 = user.ID, P2 = user.NickName, P3 = role.ID, P4 = role.Description }); Console.WriteLine("OQL TestMapOql Query:\r\n{0}\r\n", q2); Console.WriteLine(q2.PrintParameterInfo()); }
/// <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> /// <returns></returns> public List <CashierRegisterMachines> GetAllCashierRegisterMachines() { Facility fac = new Facility() { FacilityName = "收银机" }; OQL q = OQL.From(fac).Select(fac.SerialNumber).Where(fac.FacilityName).END; List <Facility> list = EntityQuery <Facility> .QueryList(q); return(list.ConvertAll <CashierRegisterMachines>(p => { return new CashierRegisterMachines() { CashRegisterNo = p.SerialNumber }; } )); }
static void OqlInTest() { LT_UserRoles roles = new LT_UserRoles() { NickName = "Role1" }; LT_Users users = new LT_Users(); OQL qRole = OQL.From(roles).Select(roles.ID).Where( cmp => cmp.Comparer(roles.NickName, "like", "123%") ).END; OQL qUser = new OQL(users); qUser.Select().Where(qUser.Condition .AND(users.LastLoginTime, ">=", DateTime.Now.AddDays(-10)) .NotIn(users.RoleID, qRole)); Console.WriteLine("OQL to SQL:\r\n{0},\r\n{1}", qUser, qUser.PrintParameterInfo()); }
void TestLimit() { Users user = new Users() { NickName = "pdf.net" }; OQL q0 = OQL.From(user) .Select() .Where(user.NickName) .OrderBy(user.ID) .END; q0.Limit(10, 2); Console.WriteLine("one table and select page number 2,page size 10: \r\n{0}", q0); Console.WriteLine("因为OQL是抽象的SQL,而分页语法又是特定于数据库的,所以具体的分页SQL要到查询真正执行的时候才会生成。"); Console.WriteLine(q0.PrintParameterInfo()); }
public static int SetDeleteB <T>(string IDs, AdoHelper ado) where T : EntityBase, IStringID, IIsDelete, new() { string[] strs = IDs.Split(new char[] { ',' }); T t = new T() { IsDelete = true }; OQL q = OQL.From(t).Update(t.IsDelete).Where <T>((cmp1, u) => cmp1.Comparer(u.ID, "in", strs)).END; try { return(EntityQuery <T> .ExecuteOql(q, ado)); } catch { return(0); } }
/// <summary> /// 获取管理员列表(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <CinemaViewModel> GetPageList(int pageIndex, int pageSize) { JsonRsp <CinemaViewModel> rsp = new JsonRsp <CinemaViewModel>(); CinemaModel m = new CinemaModel(); OQL q = OQL.From(m) .Select() .OrderBy(m.ID, "asc") .END; //分页 q.Limit(pageSize, pageIndex, true); //q.PageWithAllRecordCount = allCount; //List<Employee> list= EntityQuery<Employee>.QueryList(q); List <CinemaModel> list = q.ToList <CinemaModel>();//使用OQL扩展 rsp.data = list.ConvertAll <CinemaViewModel>(o => { return(new CinemaViewModel() { ID = o.ID, CinemaChainId = o.CinemaChainId, CinemaName = o.CinemaName, LinkPhone = o.LinkPhone, LinkName = o.LinkName, LinkMobile = o.LinkMobile, CinemaArea = o.CinemaArea, CinemaAddress = o.CinemaAddress, CreateBy = o.CreateUser, CreateIP = o.CreateIP, CreateTime = o.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"), Sort = o.Sort, Status = o.Status, UpdateBy = o.UpdateUser, UpdateIP = o.UpdateIP, UpdateTime = o.UpdateTime == null ? "" : Convert.ToDateTime(o.UpdateTime).ToString("yyyy-MM-dd HH:mm:ss"), }); } ); rsp.success = true; rsp.code = 0; rsp.count = q.PageWithAllRecordCount; return(rsp); }
/// <summary> /// 启用/禁用 /// </summary> /// <param name="accountStatus"></param> /// <returns></returns> public JsonRsp SetStatus(long[] Ids, int status) { if (Ids == null) { return(new JsonRsp { success = false, retmsg = "请选择要操作的数据" }); } CustomModel model = new CustomModel(); OQL q = OQL.From(model) .Update(model.Status, model.UpdateId, model.UpdateUser, model.UpdateIP, model.UpdateIP) .Where(cmp => cmp.Comparer(model.ID, "IN", Ids)) //为了安全,不带Where条件是不会全部删除数据的 .END; int returnvalue = EntityQuery <CustomModel> .Instance.ExecuteOql(q); return(new JsonRsp { success = returnvalue > 0, code = returnvalue }); }
/// <summary> /// 获取用户权限菜单 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public List <MenuViewModel> GetMenuListByAccountId(long accountId, long menuType) { AdminRole role = new AdminRole(); OQL qRole = OQL.From(role) .Select() .OrderBy(role.ID, "asc") .END; StringBuilder sbMenuIds = new StringBuilder(); foreach (AdminRole item in qRole.ToList <AdminRole>()) { sbMenuIds.Append(item.MenuIds); } JsonRsp <MenuViewModel> rsp = new JsonRsp <MenuViewModel>(); AdminMenu model = new AdminMenu(); OQL q = new OQL(model); q.Select() .Where(q.Condition.AND(model.MenuType, "=", menuType)) .OrderBy(model.ID, "asc"); List <AdminMenu> list = q.ToList <AdminMenu>();//使用OQL扩展 return(list.ConvertAll <MenuViewModel>(o => { return new MenuViewModel() { ID = o.ID, ParentID = o.ParentID, MenuKey = o.MenuKey, MenuName = o.MenuName, MenuUrl = o.MenuUrl, MenuType = o.MenuType, IDPath = o.IDPath, Remark = o.Remark, Sort = o.Sort, Status = o.Status, CreateTIme = o.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"), }; } )); }
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()); }
/// <summary> /// 获取管理员列表(全部) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <TicketViewModel> GetAllList() { JsonRsp <TicketViewModel> rsp = new JsonRsp <TicketViewModel>(); TicketInfo model = new TicketInfo(); OQL q = OQL.From(model) .Select() .OrderBy(model.ID, "asc") .END; List <TicketInfo> list = q.ToList <TicketInfo>();//使用OQL扩展 rsp.data = list.ConvertAll <TicketViewModel>(ticket => { return(new TicketViewModel() { ID = ticket.ID, TicketCode = ticket.TicketCode, TicketTypeId = ticket.TicketTypeId, Consumptionlevel = BaseEnum.ConsumptionlevelEnum.初始化, MoneyTyp = BaseEnum.MoneyTypeEnum.初始化, CustomID = 0, InitialAmount = 0, CostAmount = 0, Balance = 0, Status = BaseEnum.CredentialEnum.未交付, IsExpire = false, IsActivated = false, //MakeTime=null, //ExpireDate=null, TicketBatchNo = model.TicketBatchNo, GrantBy = 0, //GrantTime=null, CreateBy = AdminName, CreateIP = Util.GetLocalIP(), CreateTime = ticket.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"), Sort = ticket.Sort, }); } ); rsp.success = true; rsp.code = 0; return(rsp); }
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 对象的方法执行 }
/// <summary> /// 获取管理员列表(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <TicketBatchViewModel> GetPageList(int pageIndex, int pageSize) { JsonRsp <TicketBatchViewModel> rsp = new JsonRsp <TicketBatchViewModel>(); TicketBatchModel ticket = new TicketBatchModel(); TicketTypeModel ticketType = new TicketTypeModel(); //Select 方法不指定具体要选择的实体类属性,可以推迟到EntityContainer类的MapToList 方法上指定 OQL joinQ = OQL.From(ticket) .Join(ticketType).On(ticket.TicketTypeId, ticketType.ID) .Select() .OrderBy(ticket.ID, "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 <TicketBatchViewModel>)ec.MapToList <TicketBatchViewModel>(() => new TicketBatchViewModel() { ID = ticket.ID, TicketTypeId = ticket.TicketTypeId, TicketTypeIdStr = ticketType.TicketTypeName, TicketBatchNo = ticket.TicketBatchNo, TicketPrefix = ticket.TicketPrefix, DataFrom = ticket.DataFrom, Count = ticket.Count, TicketBatchName = ticket.TicketBatchName, CreateId = ticket.CreateId, CreateUser = ticket.CreateUser, CreateIP = ticket.CreateIP, CreateTime = ticket.CreateTime, Status = ticket.Status, }); rsp.success = true; rsp.code = 0; rsp.count = joinQ.PageWithAllRecordCount; return(rsp); }
static void OqlJoinTest() { LT_UserRoles roles = new LT_UserRoles() { NickName = "Role1" }; LT_UserRoles roles2 = new LT_UserRoles(); OQL q2 = OQL.From(roles) .Join(roles2).On(roles.ID, roles2.ID) .Select(roles.ID, roles2.RoleName) .Where(cmp => cmp.EqualValue(roles.NickName)) .END; Console.WriteLine("OQL 自连接:{0}", q2); LT_Users users = new LT_Users(); OQL q = OQL.From(users) .Join(roles).On(users.RoleID, roles.ID) .Select( users.ID, users.UserName, roles.ID, roles.RoleName ) .Where( cmp => cmp.EqualValue(roles.NickName) ) .END; Console.WriteLine("OQL to SQL:\r\n{0}", q); EntityContainer ec = new EntityContainer(q); var list = ec.Map <UserRoleDto>(u => { u.UserID = ec.GetItemValue <int>(0); u.UserName = ec.GetItemValue <string>(1); u.RolesID = ec.GetItemValue <int>(2); u.RoleName = ec.GetItemValue <string>(3); return(u); }); }
/// <summary> /// 验证密码是否正确 /// </summary> /// <param name="userName"></param> /// <param name="passWord"></param> /// <returns></returns> public JsonRsp CheckPassWord(string accountName, string accountPwd) { JsonRsp json = new JsonRsp(); AdminAccount model = new AdminAccount(); model.AccountName = accountName; OQL q = OQL.From(model) .Select() .Where(model.AccountName) //以用户名和密码来验证登录 .END; var list = q.ToList <AdminAccount>();//ToEntity,OQL扩展方法 if (list.Count == 0) { json.success = false; json.retmsg = "用户不存在"; return(json); } else if (list.Count == 1) { model = list[0]; if (model.AccountPwd == EncryptHelper.MD5Encoding(accountPwd, model.Salt)) { json.success = true; json.code = 0; json.returnObj = model; } else { json.success = false; json.retmsg = "账号或密码不匹配"; return(json); } } else { model = new AdminAccount(); } return(json); }
void TestUpdate() { Users user = new Users() { AddTime = DateTime.Now.AddDays(-1), Authority = "Read", NickName = "菜鸟" }; OQL q = OQL.From(user) .Update(user.AddTime, user.Authority, user.NickName) .Where(cmp => cmp.Property(user.RoleID) == 100) .END; //OQL q = OQL.From(user) // .Update(user.AddTime) // .Where(user.Authority, user.NickName) // .END; Console.WriteLine("OQL update:\r\n{0}\r\n", q); Console.WriteLine(q.PrintParameterInfo()); }
/// <summary> /// 启用/禁用 /// </summary> /// <param name="accountStatus"></param> /// <returns></returns> public JsonRsp SetStatus(long[] Ids, int status) { if (Ids == null) { return(new JsonRsp { success = false, retmsg = "请选择要操作的数据" }); } BankModel user = new BankModel(); user.Status = status; OQL q = OQL.From(user) .Update(user.Status) .Where(cmp => cmp.Comparer(user.ID, "IN", Ids)) //为了安全,不带Where条件是不会全部删除数据的 .END; int returnvalue = EntityQuery <BankModel> .Instance.ExecuteOql(q); return(new JsonRsp { success = returnvalue > 0, code = returnvalue }); }
/// <summary> /// 启用/禁用/删除 /// </summary> /// <param name="accountStatus"></param> /// <returns></returns> public JsonRsp SetStatus(long[] Ids, int status) { if (Ids == null) { return(new JsonRsp { success = false, retmsg = "请选择要操作的数据" }); } AdminAccount model = new AdminAccount(); model.Status = status; OQL q = OQL.From(model) .Update(model.Status) .Where(cmp => cmp.Comparer(model.ID, "IN", Ids)) //为了安全,不带Where条件是不会全部删除数据的 .END; int returnvalue = EntityQuery <AdminAccount> .Instance.ExecuteOql(q); return(new JsonRsp { success = returnvalue > 0, code = returnvalue }); }
/// <summary> /// 获取所有的收银员 /// </summary> /// <returns></returns> public List <Cashier> GetAllCashiers() { Employee emp = new Employee(); emp.JobName = "收银员"; OQL q = OQL.From(emp) .Select(emp.WorkNumber, emp.EmployeeName) .Where(emp.JobName) .OrderBy(emp.EmployeeName, "asc") .END; List <Employee> list = EntityQuery <Employee> .QueryList(q); return(list.ConvertAll <Cashier>(p => { return new Cashier() { CashierName = p.EmployeeName, WorkNumber = p.WorkNumber }; } )); }
public static int SetDelete <T>(string IDs) where T : EntityBase, IIntID, IIsDelete, new() { string[] strs = IDs.Split(new char[] { ',' }); T t = new T() { IsDelete = true }; OQL.From(t).Select(t.ID, t.IsDelete); OQL q = OQL.From(t).Update(t.IsDelete).Where <T>((cmp1, user) => cmp1.Comparer(user.ID.ToString(), "in", strs)).END; try { return(EntityQuery <T> .ExecuteOql(q, MyDB.GetDBHelper())); } catch { return(0); } }
/// <summary> /// 查询对象 /// </summary> /// <param name="nodeCode"></param> /// <returns></returns> public FgSys_BaseConfig GetBaseConfig(string nodeCode) { try { FgSys_BaseConfig entity = new FgSys_BaseConfig() { NodeCode = nodeCode }; if (!string.IsNullOrEmpty(nodeCode)) { OQL query = OQL.From(entity).Select().Where(entity.NodeCode).END; entity = EntityQuery <FgSys_BaseConfig> .QueryObject(query, db_dataReportWJW); } return(entity); } catch (Exception err) { throw err; } }
void TestOqlPage() { Users user = new Users() { NickName = "pdf.net", RoleID = RoleNames.Admin }; UserRoles roles = new UserRoles() { RoleName = "role1" }; //测试字段直接比较 OQL q00 = OQL.From(user) .Select(user.ID, user.NickName, user.LastLoginIP) .Where(cmp => cmp.Comparer(user.AddTime, "=", user.LastLoginTime)) .OrderBy(o => o.Desc(user.LastLoginTime)) .END; Console.WriteLine("q00:one table and select all fields \r\n{0}", q00); Console.WriteLine(q00.PrintParameterInfo()); string pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q00.ToString(), "", 10, 2, 999); Console.WriteLine("Page SQL"); Console.WriteLine(pageSql); OQL q2 = OQL.From(user) .InnerJoin(roles).On(user.RoleID, roles.ID) .Select(user.RoleID, roles.RoleName) .Where(user.NickName, roles.RoleName) .GroupBy(user.RoleID, roles.RoleName) .OrderBy(user.ID) .END; Console.WriteLine("q2:two table query use join\r\n{0}", q2); Console.WriteLine(q2.PrintParameterInfo()); pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q2.ToString(), "", 10, 2, 999); Console.WriteLine("Page SQL"); Console.WriteLine(pageSql); }
private void btnLoadData_Click(object sender, EventArgs e) { //List<User> list = OQL.From<User>().Select().END.ToList<User>(); //上面一行是简写的方式,下面是传统的方式,带分页,当前选定第一页 User user = new User(); OQL q = OQL.From(user) .Select(user.UserID, user.UserName, user.UserType, user.RegisterDate, user.Expenditure) .OrderBy(user.UserName, "asc") .END; q.Distinct = true; q.Limit(10); List <User> list = EntityQuery <User> .QueryList(q); foreach (var item in list) { UserBindingList.Add(item); } this.dataGridView1.DataSource = UserBindingList; //this.dataGridView1.DataSource = list; }
/// <summary> /// 获取列表(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <CustomTypeViewModel> GetPageList(int pageIndex, int pageSize) { JsonRsp <CustomTypeViewModel> rsp = new JsonRsp <CustomTypeViewModel>(); CustomTypeModel m = new CustomTypeModel(); OQL q = OQL.From(m) .Select() .OrderBy(m.ID, "asc") .END; //分页 q.Limit(pageSize, pageIndex, true); //q.PageWithAllRecordCount = allCount; //List<Employee> list= EntityQuery<Employee>.QueryList(q); List <CustomTypeModel> list = q.ToList <CustomTypeModel>();//使用OQL扩展 rsp.data = list.ConvertAll <CustomTypeViewModel>(o => { return(new CustomTypeViewModel() { ID = o.ID, CustomTypeName = o.CustomTypeName, CreateId = o.CreateId, CreateUser = o.CreateUser, CreateIP = o.CreateIP, CreateTime = o.CreateTime, Sort = o.Sort, Status = o.Status, UpdateBy = o.UpdateUser, UpdateIP = o.UpdateIP, UpdateTime = o.UpdateTime, }); } ); rsp.success = true; rsp.code = 0; rsp.count = q.PageWithAllRecordCount; return(rsp); }
/// <summary> /// 获取管理员列表(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <BankAccountViewModel> GetPageList(int pageIndex, int pageSize, bool limit = true) { JsonRsp <BankAccountViewModel> rsp = new JsonRsp <BankAccountViewModel>(); BankAccountModel ba = new BankAccountModel(); BankModel b = new BankModel(); OQL joinQ = OQL.From(ba) .Join(b).On(ba.BankId, b.ID) .Select() .OrderBy(ba.Sort, "desc") .END; //分页 if (limit) { joinQ.Limit(pageSize, pageIndex, true); } PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper(); EntityContainer ec = new EntityContainer(joinQ, db); rsp.data = (List <BankAccountViewModel>)ec.MapToList <BankAccountViewModel>(() => new BankAccountViewModel() { ID = ba.ID, BankAccountName = ba.BankAccountName, BankName = b.BankName, BankId = ba.BankId, BankAccountCode = ba.BankAccountCode, CreateId = ba.CreateId, CreateUser = ba.CreateUser, CreateIP = ba.CreateIP, CreateTime = ba.CreateTime, Sort = ba.Sort, Status = ba.Status, }); rsp.success = true; rsp.code = 0; rsp.count = joinQ.PageWithAllRecordCount; return(rsp); }