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 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());
}
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());
}
public void GetRoleFunctionList(string personId)
{
//子查询
Base_Person_FunctionInfo personFun = new Base_Person_FunctionInfo()
{
PersonId = personId, DirectionFlag = -1
};
OQLChildFunc oqlChild = parent => OQL.From(parent, personFun)
.Select(true, personFun.FunctionId)
.Where(personFun.PersonId, personFun.DirectionFlag)
.END;
//查询所有功能，并从中过滤掉子查询的结果
Base_FunctionInfo fun = new Base_FunctionInfo();
Base_Person_RoleInfo personRole = new Base_Person_RoleInfo()
{
PersonId = personId
};
Base_Role_FunctionInfo roleFun = new Base_Role_FunctionInfo();
OQL oql = OQL.From(personRole)
.InnerJoin(roleFun).On(personRole.RoleId, roleFun.RoleId)
.InnerJoin(fun).On(roleFun.FunctionId, fun.FunctionId)
.Select(true, fun.FunctionId, fun.FunctionName, fun.NavigateAddress)
.Where(cmp => cmp.EqualValue(personRole.PersonId)
& cmp.Comparer(fun.FunctionId, OQLCompare.CompareType.NotIn, oqlChild))
.END;
//return EntityQuery<Base_FunctionInfo>.QueryList(oql);
Console.WriteLine("OQL Test Child Query:\r\n{0}\r\n", oql);
Console.WriteLine(oql.PrintParameterInfo());
}
void Test2()
{
Users user = new Users();
UserRoles roles = new UserRoles()
{
RoleName = "role1"
};
OQL q2 = new OQL(user);
q2.InnerJoin(roles).On(user.RoleID, roles.ID);
OQLCompare cmp = new OQLCompare(q2);
OQLCompare cmpResult =
(
cmp.Property(user.UserName) == "ABC" &
cmp.Comparer(user.Password, "=", "111") &
cmp.EqualValue(roles.RoleName)
)
|
(
(cmp.Comparer(user.UserName, "=", "CDE") &
cmp.Property(user.Password) == "222" &
cmp.Comparer(roles.RoleName, "like", "%Role2")
)
|
(cmp.Property(user.LastLoginTime) > DateTime.Now.AddDays(-1))
)
;
q2.Select().Where(cmpResult);
Console.WriteLine("OQL by OQLCompare Test:\r\n{0}", q2);
Console.WriteLine(q2.PrintParameterInfo());
}
/// <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 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());
}
void TestChild2()
{
/*
* SELECT * FROM [LT_Users] WHERE RoleID =
* (SELECT ID FROM dbo.LT_UserRoles r WHERE [LT_Users].NickName=r.NickName)
*/
Users user = new Users()
{
NickName = "_nickName"
};
UserRoles roles = new UserRoles()
{
NickName = "_roleNickName"
};
OQLChildFunc childFunc = parent => OQL.From(parent, roles)
.Select(roles.ID)
.Where(cmp => cmp.Comparer(user.NickName, "=", roles.NickName) //比较的字段顺序无所谓
& cmp.Property(roles.AddTime) > DateTime.Now.AddDays(-3))
.END;
OQL q = OQL.From(user)
.Select()
.Where(cmp => cmp.Comparer(user.RoleID, "=", childFunc))
.END;
q.SelectStar = true;
Console.WriteLine("OQL by 高级子查询Test:\r\n{0}", q);
Console.WriteLine(q.PrintParameterInfo());
}
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());
}
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);
}
void TestNotCondition()
{
Users user = new Users();
OQL q = OQL.From(user)
.Select(user.ID, user.UserName, user.Password)
.Where <Users>((cmp, u) => OQLCompare.Not(
cmp.Property(u.UserName) == "ABC" & cmp.Property(u.Password) == "123")
)
.END;
Console.WriteLine("OQL Test NOT Condition:\r\n{0}\r\n", q);
Console.WriteLine(q.PrintParameterInfo());
}
void Test2FieldOpt()
{
Users user = new Users();
// user.LastLoginTime-user.AddTime>'23:00:00'
// => user.LastLoginTime -'23:00:00'>user.AddTime
OQL q = OQL.From(user)
.Select()
.Where(cmp => cmp.Comparer(user.LastLoginTime, ">", user.AddTime, "{0}-'23:00:00'"))
.END;
q.SelectStar = true;
Console.WriteLine("OQL Test SQL Field compute:\r\n{0}\r\n", q);
Console.WriteLine(q.PrintParameterInfo());
}
//动态处理查询条件，详细见 http://www.cnblogs.com/bluedoctor/p/4470526.html
void TestIfCondition2()
{
Users user = new Users()
{
ID = 0, NickName = "abc", UserName = "******", Password = "******"
};
OQL q7 = OQL.From(user)
.Select()
.Where <Users>(CreateCondition)
.END;
Console.WriteLine("OQL by 动态构建 OQLCompare Test(委托函数方式):\r\n{0}", q7);
Console.WriteLine(q7.PrintParameterInfo());
}
public static void 杂查询()
{
SODUser user = new SODUser();
OQL q = OQL.From(user)
.Select()
.Where(cmp => cmp.Property(user.LogName) == "zhangyi" & cmp.Comparer(user.ID, "like", 1))
.END;
List <SODUser> users = EntityQuery <SODUser> .QueryList(q);
string sql = q.ToString();
sql = q.PrintParameterInfo();
}
void TestInsert()
{
Users user = new Users()
{
AddTime = DateTime.Now.AddDays(-1),
Authority = "Read",
NickName = "菜鸟"
};
OQL q = OQL.From(user)
.Insert(user.AddTime, user.Authority, user.NickName);
Console.WriteLine("OQL insert:\r\n{0}\r\n", q);
Console.WriteLine(q.PrintParameterInfo());
}
void TestDelete()
{
Users user = new Users()
{
ID = 99
};
OQL q = OQL.From(user)
.Delete()
//.Where(cmp => cmp.Property(user.RoleID) == 100)
.END;
Console.WriteLine("OQL 安全删除数据测试:\r\n{0}\r\n", q);
Console.WriteLine(q.PrintParameterInfo());
}
void TestInsertFrom()
{
Users user = new Users();
UserRoles roles = new UserRoles();
OQL child = OQL.From(roles)
.Select(roles.ID)
.Where(cmp => cmp.Comparer(roles.ID, ">", RoleNames.User))
.END;
OQL q = OQL.From(user)
.InsertFrom(child, user.RoleID);
Console.WriteLine("OQL insert from:\r\n{0}\r\n", q);
Console.WriteLine(q.PrintParameterInfo());
}
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();
}
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());
}
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());
}
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());
}
static void TestInChild()
{
p_hege_detail detailObj = new p_hege_detail();
p_hege phegeObj = new p_hege();
OQL phegeOq = new OQL(phegeObj);
phegeOq.Select(phegeObj.id).OrderBy(phegeObj.id, "DESC");
phegeOq.TopCount = 1;
OQL detailOq = new OQL(detailObj);
detailOq.Select(detailObj.coName, detailObj.coType, detailObj.coMessage)
.Where(cmp => cmp.Comparer(detailObj.coType, "=", "Status") & cmp.Comparer(detailObj.hegeID, "in", phegeOq)) //
.OrderBy(detailObj.id, "DESC");
Console.WriteLine("SQL:\r\n{0}\r\n,{1}", detailOq.ToString(), detailOq.PrintParameterInfo());
}
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 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 对象的方法执行
}
static void UpdateTest()
{
LT_Users userCmp = new LT_Users()
{
Authority = "admin", IsEnable = true, Remarks = "add"
};
// LT_Users userQ = new LT_Users();
//OQLCompare cmp = new OQLCompare(userCmp);
//OQL q = new OQL(userQ);
OQL q = new OQL(userCmp);
OQLCompare cmp = new OQLCompare(q);
cmp = cmp.Comparer(userCmp.ID, "in", new int[] { 1, 2, 3 })
& cmp.Comparer(userCmp.LastLoginIP, "=", "127.0.0.1");
//------分界线-------
q.Update(userCmp.Authority, userCmp.IsEnable, userCmp.Remarks).Where(cmp);
Console.WriteLine("update test:{0}\r\n{1}", q, q.PrintParameterInfo());
}
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());
}
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);
}
void Test1()
{
Users user = new Users()
{
NickName = "pdf.net", RoleID = RoleNames.Admin
};
UserRoles roles = new UserRoles()
{
RoleName = "role1"
};
//测试字段直接比较
OQL q00 = OQL.From(user)
.Select()
.Where(cmp => cmp.Comparer(user.AddTime, "=", user.LastLoginTime))
.END;
Console.WriteLine("q00:one table and select all fields \r\n{0}", q00);
Console.WriteLine(q00.PrintParameterInfo());
OQL q0 = OQL.From(user)
.Select()
.Where(user.NickName, user.RoleID)
.OrderBy(user.ID)
.END;
q0.SelectStar = true;
Console.WriteLine("q0:one table and select all fields \r\n{0}", q0);
Console.WriteLine(q0.PrintParameterInfo());
OQL q01 = OQL.From(user)
.Select()
.Where(cmp => cmp.Comparer(user.ID, OQLCompare.CompareType.Equal, 0))
.OrderBy(user.ID)
.END;
q01.SelectStar = true;
Console.WriteLine("q01:one table and select all fields \r\n{0}", q01);
Console.WriteLine(q01.PrintParameterInfo());
//var userList = EntityQuery<Users>.QueryList(q0);
//if (userList.Count > 0)
//{
// Users u = userList[0];
// Console.WriteLine("User Type is:" + u.RoleID.ToString());
// u.RoleID = RoleNames.User;
// EntityQuery<Users>.Instance.Update(u);
//}
OQL q = OQL.From(user)
.Select(user.ID, user.UserName, user.RoleID)
.END;
q.Select(user.LastLoginIP).Where(user.NickName);
Console.WriteLine("q1:one table and select some fields\r\n{0}", q);
Console.WriteLine(q.PrintParameterInfo());
//动态指定查询的字段和比较关心、值
q = OQL.From(user).Select().Where(new QueryParameter[]
{
new QueryParameter("ID", "=", 1)
}
).END;
Console.WriteLine("q1:QueryParameter Test\r\n{0}", q);
Console.WriteLine(q.PrintParameterInfo());
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());
OQL q3 = OQL.From(user, roles)
.Select(user.ID, user.UserName, roles.ID, roles.RoleName)
.Where(cmp => cmp.Comparer(user.RoleID, "=", roles.ID)
& cmp.EqualValue(roles.RoleName))
.OrderBy(user.ID)
.END;
Console.WriteLine("q3:two table query not use join\r\n{0}", q3);
Console.WriteLine(q3.PrintParameterInfo());
OQL q4 = OQL.From(user).InnerJoin(roles).On(user.RoleID, roles.ID)
.Select(user.RoleID).Count(user.RoleID, "roldid_count") //
.Where(user.NickName)
.GroupBy(user.RoleID)
.END;
Console.WriteLine("q4:count from two table query \r\n{0}", q4);
Console.WriteLine(q4.PrintParameterInfo());
OQL q5 = OQL.From(user)
.Select(user.RoleID).Count(user.RoleID, "count_rolid")
.GroupBy(user.RoleID)
.Having(p => p.Count(user.RoleID, OQLCompare.CompareType.GreaterThanOrEqual, 2))
.END;
Console.WriteLine("q5:having Test: \r\n{0}", q5);
Console.WriteLine(q5.PrintParameterInfo());
//q5 = OQL.From(user)
// .Select(user.Age).Sum(user.Age,"sum_age")
// .GroupBy(user.Age)
// .OrderBy(user.Age)
// .END;
//Console.WriteLine("q5:having Test: \r\n{0}", q5);
//Console.WriteLine(q5.PrintParameterInfo());
OQL q6 = OQL.From(user).Select()
.Where(cmp =>
cmp.Comparer(user.RoleID, "is not", null) &
cmp.Comparer(user.AddTime, ">=", DateTime.Now.AddDays(-1)) &
cmp.Comparer(user.AddTime, "<", DateTime.Now)
)
.END;
q6.SelectStar = true;
Console.WriteLine("q6:SQL 'IS' Test: \r\n{0}", q6);
Console.WriteLine(q6.PrintParameterInfo());
OQL q7 = OQL.From(user).Select()
.Where(cmp => cmp.Between(user.ID, 5, 10))
.END;
q7.SelectStar = true;
Console.WriteLine("q7:SQL Between Test: \r\n{0}", q7);
Console.WriteLine(q7.PrintParameterInfo());
//Compare 对象使用 ComparerSqlFunction 方法，解决SQL函数操作结果的类型跟字段类型不一致的问题
//感谢网友 【有事M我】发现此问题 2014.3.11
GOQL <Users> q8 = OQL.FromObject <Users>()
.Select()
.Where((cmp, u) => cmp.ComparerSqlFunction(u.NickName, ">", 0, "CHARINDEX( 'xiao',{0} )"))
.END;
string sql;
q8.Print(out sql);
Console.WriteLine("q8:SQL Function Test: \r\n{0}", sql);
}
public GOQL <T> Print(out string sqlInfo)
{
sqlInfo = string.Format("SQL:{0}\r\n{1}", currentOQL.ToString(), currentOQL.PrintParameterInfo());
return(this);
}
