/// <summary> /// 获取管理员列表(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <RoleViewModel> GetPageList(int pageIndex, int pageSize) { JsonRsp <RoleViewModel> rsp = new JsonRsp <RoleViewModel>(); AdminRole m = new AdminRole(); OQL q = OQL.From(m) .Select(m.ID, m.RoleName, m.Status, m.CreateTIme) .OrderBy(m.ID, "asc") .END; //分页 q.Limit(pageSize, pageIndex, true); //q.PageWithAllRecordCount = allCount; //List<Employee> list= EntityQuery<Employee>.QueryList(q); List <AdminRole> list = q.ToList <AdminRole>();//使用OQL扩展 rsp.data = list.ConvertAll <RoleViewModel>(o => { return(new RoleViewModel() { ID = o.ID, RoleName = o.RoleName, Status = o.Status, CreateTIme = o.CreateTIme.ToString("yyyy-MM-dd HH:mm:ss"), }); } ); rsp.success = true; rsp.code = 0; rsp.count = q.PageWithAllRecordCount; return(rsp); }
/// <summary> /// 获取列表数据 /// </summary> /// <param name="cp">where条件,没有可传人null</param> /// <param name="orders">排序字段对象</param> /// <param name="pageSize">每页显示记录数</param> /// <param name="pageIndex">当前页</param> /// <returns></returns> public static List <T> GetList <T>(OQLCompare cp, OQLOrder orders, int pageSize, int pageIndex) 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); if (pageSize > 0 && pageIndex > 0 && q.PageWithAllRecordCount > 0) { q.Limit(pageSize, pageIndex); } return(EntityQuery <T> .QueryList(q)); }
/// <summary> /// 获取管理员列表(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <AdminActionViewModel> GetPageList(int pageIndex, int pageSize) { JsonRsp <AdminActionViewModel> rsp = new JsonRsp <AdminActionViewModel>(); AdminAction action = new AdminAction(); AdminMenu menu = new AdminMenu(); //Select 方法不指定具体要选择的实体类属性,可以推迟到EntityContainer类的MapToList 方法上指定 OQL joinQ = OQL.From(action) .Join(menu).On(action.MenuId, menu.ID) .Select(action.ID, menu.MenuName, action.ActionKey, action.ActionName, action.Status, action.CreateTime) .OrderBy(action.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 <AdminActionViewModel>)ec.MapToList <AdminActionViewModel>(() => new AdminActionViewModel() { ID = action.ID, MenuId = menu.ID, MenuName = menu.MenuName, ActionKey = action.ActionKey, ActionName = action.ActionName, Status = action.Status, CreateTime = action.CreateTime, }); rsp.success = true; rsp.code = 0; rsp.count = joinQ.PageWithAllRecordCount; return(rsp); }
void TestLimit() { Users user = new Users() { NickName = "pdf.net" }; OQL q0 = OQL.From(user) .Select() .Where(user.NickName) .OrderBy(user.ID, "asc") .END; q0.Distinct = true; q0.Limit(10, 2); try { var list = EntityQuery <Users> .QueryList(q0); } catch (Exception ex) { Console.WriteLine("查询错误:{0}", ex.Message); } 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()); }
static void TestDataSetAndOQL(object para) { string sql = "select top 10 * from Table_User"; Table_User user = new Table_User(); user.MapNewTableName("Table_User"); //user.MapNewTableName("Table_User"); OQL qt = new OQL(user); qt.Select().Where(qt.Condition.IN(user.Name, new object[] { "a", "b", "c" })).OrderBy(user.Name); //qt.Select().Where(cmp => cmp.Comparer(user.Name, "in", new string[] { "a", "b", "c" })); //OQL qt = OQL.From(user).Select().END; //qt.TopCount = 10; qt.Limit(10, 10); qt.PageWithAllRecordCount = 0; AdoHelper db = MyDB.GetDBHelper(); //测试下面2种方式对连接数量的影响,经测试,发现 MyDB.Instance 跟 MyDB.GetDBHelper() 没有区别。 //List<Table_User> list = EntityQuery<Table_User>.QueryList(qt); //DataSet ds = MyDB.Instance.ExecuteDataSet(sql); //测试连接会话,下面db的连接会在using 结束后关闭 using (db.OpenSession()) { List <Table_User> list = EntityQuery <Table_User> .QueryList(qt, db); DataSet ds = db.ExecuteDataSet(sql); } }
void TestOqlPage() { UserEntity ue = new UserEntity(); OQL q = OQL.From(ue) .Select(ue.ID, ue.Name, ue.Age) .Where(cmp => cmp.Comparer(ue.Age, ">", 20)) .OrderBy(ue.Age) .END; q.Limit(2, 3, true); Console.WriteLine("q:Page SQL is \r\n{0}", q); Console.WriteLine(q.PrintParameterInfo()); //当前测试总记录数5,查询后,OQL会得到总记录数 AdoHelper db = MyDB.GetDBHelperByConnectionName("conn2"); var list = EntityQuery <UserEntity> .QueryList(q, db); Users user = new Users() { NickName = "pdf.net", RoleID = RoleNames.Admin, Age = 20 }; 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); }
/// <summary> /// 获取指定页的联系人信息 /// </summary> /// <param name="pageSize"></param> /// <param name="pageNumber"></param> /// <param name="allCount"></param> /// <returns></returns> public List <CustomerContactInfo> GetContactInfoList(int pageSize, int pageNumber, int allCount) { CustomerContactInfo info = new CustomerContactInfo(); OQL q = new OQL(info); q.Select().OrderBy(info.CustomerName, "asc"); q.Limit(pageSize, pageNumber); q.PageWithAllRecordCount = allCount; return(EntityQuery <CustomerContactInfo> .QueryList(q)); }
/// <summary> /// 获取列表(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <ChargeCardViewModel> GetPageList(int pageIndex, int pageSize, bool limit = true) { JsonRsp <ChargeCardViewModel> rsp = new JsonRsp <ChargeCardViewModel>(); ChargeCardsModel c = new ChargeCardsModel(); CustomModel custom = new CustomModel(); TicketTypeModel t = new TicketTypeModel(); OQL joinQ = OQL.From(c) .Join(custom).On(c.CustomId, custom.ID) .LeftJoin(t).On(c.TicketTypeID, t.ID) .Select() .OrderBy(c.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 <ChargeCardViewModel>)ec.MapToList <ChargeCardViewModel>(() => new ChargeCardViewModel() { ID = c.ID, CustomId = c.CustomId, CustomName = custom.CustomName, TicketTypeID = c.TicketTypeID, TicketTypeName = t.TicketTypeName, MoneyType = c.MoneyType, CurrentCount = c.CurrentCount, FaceAmount = c.FaceAmount, CurrentAmount = c.CurrentAmount, ExpireDate = c.ExpireDate, TicketBatchId = c.TicketBatchId, TicketStart = c.TicketStart, TicketEnd = c.TicketEnd, Consumptionlevel = c.Consumptionlevel, IsCommonCard = c.IsCommonCard, Sort = c.Sort, CreateId = c.CreateId, CreateUser = c.CreateUser, CreateIP = c.CreateIP, CreateTime = c.CreateTime, UpdateBy = c.UpdateUser, UpdateIP = c.UpdateIP, UpdateTime = c.UpdateTime, }); rsp.success = true; rsp.code = 0; rsp.count = joinQ.PageWithAllRecordCount; return(rsp); }
public List <GoodsBaseInfoVM> GetGoodsBaseInfoList(int pageSize, int pageNumber, int allCount) { GoodsBaseInfo info = new GoodsBaseInfo(); OQL q = new OQL(info); q.Select() .OrderBy(info.SerialNumber, "asc"); q.Limit(pageSize, pageNumber); q.PageWithAllRecordCount = allCount; return(GetGoodsBaseInfoList(q)); }
/// <summary> /// 模糊查询用户,返回用户列表,使用OQLCompare 委托 /// </summary> /// <param name="likeName">要匹配的用户名</param> /// <returns>用户列表</returns> public List<User> FuzzyQueryUser(string likeName) { User user = new User(); OQL q = OQL.From(user) .Select() .Where(cmp => cmp.Comparer(user.Name, "like", likeName+"%") ) .OrderBy (user.ID ) .END; q.Limit(10,5); List<User> users = EntityQuery<User>.QueryList(q); return users; }
/// <summary> /// 获取管理员列表(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <TicketViewModel> GetPageList(int pageIndex, int pageSize) { JsonRsp <TicketViewModel> rsp = new JsonRsp <TicketViewModel>(); TicketInfo ticket = new TicketInfo(); TicketTypeModel ticketType = new TicketTypeModel(); //Select 方法不指定具体要选择的实体类属性,可以推迟到EntityContainer类的MapToList 方法上指定 OQL joinQ = OQL.From(ticket) .Join(ticketType).On(ticket.TicketTypeId, ticketType.ID) .Select() .OrderBy(ticket.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 <TicketViewModel>)ec.MapToList <TicketViewModel>(() => 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 = ticket.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; rsp.count = joinQ.PageWithAllRecordCount; return(rsp); }
/// <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, CreateId = model.CreateId, CreateUser = 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="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); }
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()); }
/// <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="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); }
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; }
/// <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); }
/// <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); }
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 <CustomReceiptedViewModel> GetPageList(int pageIndex, int pageSize) { JsonRsp <CustomReceiptedViewModel> rsp = new JsonRsp <CustomReceiptedViewModel>(); CustomReceiptedModel m = new CustomReceiptedModel(); 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 <CustomReceiptedModel> list = q.ToList <CustomReceiptedModel>();//使用OQL扩展 rsp.data = list.ConvertAll <CustomReceiptedViewModel>(o => { return(new CustomReceiptedViewModel() { ID = o.ID, CustomAccReceiptId = o.CustomAccReceiptId, BankAccountId = o.BankAccountId, CurrentAmount = o.CurrentAmount, BankSerialNumber = o.BankSerialNumber, DateOfEntry = o.DateOfEntry, CreateId = o.CreateId, CreateUser = o.CreateUser, CreateIP = o.CreateIP, CreateTime = o.CreateTime, Status = o.Status, }); } ); 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 <TicketTypeViewModel> GetPageList(int pageIndex, int pageSize) { JsonRsp <TicketTypeViewModel> rsp = new JsonRsp <TicketTypeViewModel>(); TicketTypeModel m = new TicketTypeModel(); OQL q = OQL.From(m) .Select() .OrderBy(m.Sort, "desc") .END; //分页 q.Limit(pageSize, pageIndex, true); //q.PageWithAllRecordCount = allCount; //List<Employee> list= EntityQuery<Employee>.QueryList(q); List <TicketTypeModel> list = q.ToList <TicketTypeModel>();//使用OQL扩展 rsp.data = list.ConvertAll <TicketTypeViewModel>(o => { return(new TicketTypeViewModel() { ID = o.ID, TicketTypeName = o.TicketTypeName, 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="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <MenuViewModel> GetPageList(int pageIndex, int pageSize) { JsonRsp <MenuViewModel> rsp = new JsonRsp <MenuViewModel>(); AdminMenu m = new AdminMenu(); 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 <AdminMenu> list = q.ToList <AdminMenu>();//使用OQL扩展 rsp.data = list.ConvertAll <MenuViewModel>(o => { return(new MenuViewModel() { ID = o.ID, 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"), }); } ); 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 <CustomAccReceiptViewModel> GetPageList(int pageIndex, int pageSize) { JsonRsp <CustomAccReceiptViewModel> rsp = new JsonRsp <CustomAccReceiptViewModel>(); CustomAccReceiptModel CustomAccReceipt = new CustomAccReceiptModel(); CustomModel custom = new CustomModel(); //Select 方法不指定具体要选择的实体类属性,可以推迟到EntityContainer类的MapToList 方法上指定 OQL joinQ = OQL.From(CustomAccReceipt) .Join(custom).On(CustomAccReceipt.CustomId, custom.ID) .Select() .OrderBy(CustomAccReceipt.CreateTime, "asc") .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 <CustomAccReceiptViewModel>)ec.MapToList <CustomAccReceiptViewModel>(() => new CustomAccReceiptViewModel() { ID = CustomAccReceipt.ID, CustomId = CustomAccReceipt.CustomId, CustomName = custom.CustomName, ChargeCardNo = CustomAccReceipt.ChargeCardNo, CurrentAmount = CustomAccReceipt.CurrentAmount, Remark = CustomAccReceipt.Remark, Status = CustomAccReceipt.Status, CreateId = CustomAccReceipt.CreateId, CreateUser = CustomAccReceipt.CreateUser, CreateIP = CustomAccReceipt.CreateIP, CreateTime = CustomAccReceipt.CreateTime, }); rsp.success = true; rsp.code = 0; rsp.count = joinQ.PageWithAllRecordCount; return(rsp); }
/// <summary> /// 获取管理员列表(分页) /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public JsonRsp <AccountViewModel> GetPageList(int pageIndex, int pageSize) { JsonRsp <AccountViewModel> rsp = new JsonRsp <AccountViewModel>(); AdminAccount m = new AdminAccount(); OQL q = OQL.From(m) .Select(m.ID, m.AccountName, m.TrueName, m.Status, m.LoginTime, m.LoginCount, m.LastLoginTime, m.CreateTIme) .OrderBy(m.ID, "asc") .END; //分页 q.Limit(pageSize, pageIndex, true); //q.PageWithAllRecordCount = allCount; //List<Employee> list= EntityQuery<Employee>.QueryList(q); List <AdminAccount> list = q.ToList <AdminAccount>();//使用OQL扩展 rsp.data = list.ConvertAll <AccountViewModel>(o => { return(new AccountViewModel() { ID = o.ID, AccountName = o.AccountName, TrueName = o.TrueName, Status = o.Status, LoginTime = o.LoginTime, LastLoginTime = o.LastLoginTime, LoginCount = o.LoginCount, CreateTime = o.CreateTIme, }); } ); rsp.success = true; rsp.code = 0; rsp.count = q.PageWithAllRecordCount; return(rsp); }
private static void TestTransaction() { AdoHelper db = MyDB.GetDBHelper(); EntityQuery <AuctionOperationLog> query = new EntityQuery <AuctionOperationLog>(db); AuctionOperationLog optLog = new AuctionOperationLog(); optLog.OperaterID = 1000; optLog.Module = "Login"; optLog.Operation = "登录成功1"; optLog.LogSource = "PC"; db.BeginTransaction(); try { query.Insert(optLog); //必须设置为全部属性已经修改,否则仅会更新 Operation 字段 optLog.ResetChanges(true); optLog.Operation = "退出登录"; query.Insert(optLog); //optLog.Module = "Login"; //OQL q = OQL.From(optLog).Select().Where(optLog.Module).END; OQL q = new OQL(optLog); //q.Select().Where(q.Condition.AND(optLog.Operation, "like", "%登录%")); q.Select().Count(optLog.OperaterID, "");//使用空字符串参数,这样统计的值将放到 OperaterID 属性中 //必须指定db参数,否则不再一个事务中,无法进行统计查询 optLog = EntityQuery <AuctionOperationLog> .QueryObject(q, db); int allCount = optLog.OperaterID; //optLog 已经使用过,在生成OQL的查询前,必须使用新的实体对象, // 否则下面的查询仅会使用OperaterID 字段从而导致分页查询出错 optLog = new AuctionOperationLog(); q = new OQL(optLog); q.Select().OrderBy(optLog.Module, "asc").OrderBy(optLog.AtDateTime, "desc"); q.Limit(10, 2); q.PageEnable = true; q.PageWithAllRecordCount = allCount; //查询列表并更新到数据库 List <AuctionOperationLog> list = EntityQuery <AuctionOperationLog> .QueryList(q, db); foreach (AuctionOperationLog logItem in list) { logItem.AtDateTime = DateTime.Now; } query.Update(list); db.Commit(); Console.WriteLine("事务操作成功。"); } catch (Exception ex) { Console.WriteLine("Error:" + ex.Message); db.Rollback(); } }
/// <summary> /// 获取商品销售价格信息 /// </summary> /// <returns></returns> public IEnumerable <GoodsSaleInfoVM> GetGoodsSaleInfo() { GoodsBaseInfo bInfo = new GoodsBaseInfo(); GoodsStock stock = new GoodsStock(); /* * Select采用指定详细实体类属性的方式: * OQL joinQ = OQL.From(bInfo) * .Join(stock).On(bInfo.SerialNumber, stock.SerialNumber) * .Select( * bInfo.GoodsName, * bInfo.Manufacturer, * bInfo.SerialNumber, * stock.GoodsPrice, * stock.MakeOnDate, * bInfo.CanUserMonth, * stock.Stocks, * stock.GoodsID) * .OrderBy(bInfo.GoodsName, "asc") * .END; */ //Select 方法不指定具体要选择的实体类属性,可以推迟到EntityContainer类的MapToList 方法上指定 OQL joinQ = OQL.From(bInfo) .Join(stock).On(bInfo.SerialNumber, stock.SerialNumber) .Select() .OrderBy(bInfo.SerialNumber, "asc").OrderBy(bInfo.GoodsName, "asc") .END; joinQ.Limit(3, 3); PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper(); EntityContainer ec = new EntityContainer(joinQ, db); /* * 如果OQL的Select方法指定了详细的实体类属性,那么映射结果,可以采取下面的方式: * var result = ec.Map<GoodsSaleInfoVM>(e => * { * e.GoodsName = ec.GetItemValue<string>(0); * e.Manufacturer = ec.GetItemValue<string>(1); * e.SerialNumber = ec.GetItemValue<string>(2); * e.GoodsPrice = ec.GetItemValue<decimal>(3); * e.MakeOnDate = ec.GetItemValue<DateTime>(4); * e.CanUserMonth = ec.GetItemValue<int>(5); * e.Stocks = ec.GetItemValue<int>(6); * e.GoodsID = ec.GetItemValue<int>(7); * return e; * } * ); */ var result = ec.MapToList <GoodsSaleInfoVM>(() => new GoodsSaleInfoVM() { GoodsName = bInfo.GoodsName, Manufacturer = bInfo.Manufacturer, SerialNumber = bInfo.SerialNumber, GoodsPrice = stock.GoodsPrice, MakeOnDate = stock.MakeOnDate, CanUserMonth = bInfo.CanUserMonth, Stocks = stock.Stocks, GoodsID = stock.GoodsID, ExpireDate = stock.MakeOnDate.AddMonths(bInfo.CanUserMonth) }); return(result); }