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