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);
q = OQL.From(ue)
.Select(ue.Age).Sum(ue.Age, "sum_age")
.GroupBy(ue.Age)
.OrderBy(ue.Age)
.END;
q.Limit(2);
var list2 = EntityQuery <UserEntity> .QueryList(q, db);
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 qU = OQL.From(user)
.Update(user.RoleID)
//.Delete()
.WhereAll()
.END;
Console.WriteLine("qU:one table and update all record\r\n{0}", qU);
//测试字段直接比较
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 q09 = OQL.From(user)
.Select().Count(user.ID, "")
.Where(cmp => cmp.Comparer(user.AddTime, "=", user.LastLoginTime))
.END;
Console.WriteLine("q09:one table and select all fields \r\n{0}", q09);
Console.WriteLine(q09.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);
}