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