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);
}
