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 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 TestSqlPage()
{
Console.WriteLine("----SQL 词法分析 自动分页语句构造测试 开始----");
//分页，需要在 select 子句中包含排序的字段，然后排序使用字段别名
string sql = @"
SELECT
M.[ID],
M.[NickName],
T0.[ID] AS [T0_ID],
T0.[Description] AS [T0_Description]
FROM [LT_Users] M
INNER JOIN [LT_UserRoles] T0 ON M.[RoleID] = T0.[ID]
WHERE T0.[ID] = 1
ORDER BY T0.[RoleName] ASC
";
//如果上面的SQL语句升序排序，每页10行，取第二页，那么应该生成下面的分页语句
/*
* SELECT Top 10 * FROM(
* SELECT Top 10 * FROM (
* SELECT top 20
* M.[ID],
* M.[NickName],
* T0.[ID] AS [T0_ID],
* T0.[Description] AS [T0_Description],
* T0.[RoleName]
* FROM [LT_Users] M
* INNER JOIN [LT_UserRoles] T0 ON M.[RoleID] = T0.[ID]
* WHERE T0.[ID] = 1
* ORDER BY T0.[RoleName] ASC
* ) P_T0
* ORDER BY [RoleName] DESC
* ) P_T1
* ORDER BY [RoleName] ASC
*
* 注意内部的分页语句，应该附加上排序的字段 T0.[RoleName] 才可以供外部的排序条件使用，
* 所以OQL处理的时候应该考虑这个问题，比如下面的查询：
* SELECT Top 10 * FROM(
* SELECT Top 10 * FROM (
* SELECT top 20
* M.[ID],
* M.[NickName],
* T0.[ID] AS [T0_ID],
* T0.[Description] AS [T0_Description],
* T0.[RoleName] AS [T0_RoleName]
* FROM [LT_Users] M
* INNER JOIN [LT_UserRoles] T0 ON M.[RoleID] = T0.[ID]
* WHERE T0.[ID] = 1
* ORDER BY T0.[RoleName] ASC
* ) P_T0
* ORDER BY [T0_RoleName] DESC
* ) P_T1
* ORDER BY [T0_RoleName] ASC
*
* 跟上面的查询的区别， T0.[RoleName] AS [T0_RoleName] 这里对参与排序字段使用了别名
*/
string pageSql = string.Empty;
pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, sql, "", 10, 2, 999);
Console.WriteLine("SQL:");
Console.WriteLine(sql);
Console.WriteLine("Page SQL:");
Console.WriteLine(pageSql);
//其它SQL查询
sql = "select ID,[User Name],Age from User where Age>20 order by Age desc ";
pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, sql, "", 10, 2, 999);
Console.WriteLine("SQL:");
Console.WriteLine(sql);
Console.WriteLine("Page SQL:");
Console.WriteLine(pageSql);
//下面的查询会把排序字段 Age 附加到select字段里面
sql = "select ID,[User Name] from User where Age>20 order by Age desc ";
pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, sql, "", 10, 2, 999);
Console.WriteLine("SQL:");
Console.WriteLine(sql);
Console.WriteLine("Page SQL:");
Console.WriteLine(pageSql);
pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, sql, "", 10, 2, 0);
Console.WriteLine("Count SQL:");
Console.WriteLine(pageSql);
Console.WriteLine("----SQL 词法分析 自动分页语句构造测试 结束----");
}