OQL测试完整源码与SQL输出
本文是
ORM查询语言（OQL）简介--高级篇（续）：庐山真貌
相关的附录内容，详细内容请看链接原文。
OQL测试完整源码
using System; using System.Collections.Generic; //using System.Linq; using System.Text; using PWMIS.DataMap.Entity; namespace OQLTest { class Program { static void Main(string[] args) { Program p = new Program(); p.Test1(); p.Test2(); p.Test3(); p.Test4(); p.Test5(); p.TestLimit(); p.TestIfCondition(); p.TestIfCondition2(); p.TestChild(); p.TestChild2(); p.TestOQLOrder(); p.TestUpdate(); p.TestDelete(); p.TestInsert(); p.TestInsertFrom(); p.TestSqlLock(); Console.Read(); } void Test1() { Users user = new Users() { NickName = "pdf.net", RoleID=5 }; UserRoles roles = new UserRoles() { RoleName = "role1" }; 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 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()); 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()); } 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(); 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()); } 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 int[] { 1,2,3 }) ) | (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()); } 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 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()); } void TestIfCondition() { Users user = new Users() { ID=1, NickName="abc",UserName="zhagnsan",Password="pwd."}; 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 TestIfCondition2() { Users user = new Users() { ID = 1, NickName = "abc"}; OQL q7 = OQL.From(user) .Select() .Where<Users>(CreateCondition) .END; Console.WriteLine("OQL by 动态构建 OQLCompare Test(委托函数方式):\r\n{0}", q7); Console.WriteLine(q7.PrintParameterInfo()); } OQLCompare CreateCondition(OQLCompare cmp,Users user) { 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; } 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()); } 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 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 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 TestDelete() { Users user = new Users(); OQL q = OQL.From(user) .Delete() .Where(cmp => cmp.Property(user.RoleID) == 100) .END; Console.WriteLine("OQL delete:\r\n{0}\r\n", q); Console.WriteLine(q.PrintParameterInfo()); } void TestOQLOrder() { Users user = new Users(); //OQLOrderAction<Users> action = this.OQLOrder; OQL q = OQL.From(user) .Select(user.UserName,user.ID) //.OrderBy(p => p.Desc(user.UserName).Asc(user.ID)) //.OrderBy(action,user) .OrderBy<Users>(OQLOrder,user) //3种OQLOrder 对象的使用方法 .END; Console.WriteLine("OQL test OQLOrder object:\r\n{0}\r\n", q); } void OQLOrder(OQLOrder p, Users user) { p.Desc(user.UserName).Asc(user.ID); } 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 TestInsertFrom() { Users user = new Users(); UserRoles roles = new UserRoles(); OQL child = OQL.From(roles) .Select(roles.ID) .Where(cmp => cmp.Comparer(roles.ID, ">", 100)) .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 TestSqlLock() { Users user = new Users(); OQL q = OQL.From(user) //.With(OQL.SqlServerLock.NOLOCK) .With("nolock") .Select(user.ID,user.UserName,user.NickName) .END; Console.WriteLine("OQL Test SQL NoLock:\r\n{0}\r\n", q); } } }
OQL测试程序输出的SQL信息
q0:one table and select all fields SELECT * FROM [LT_Users] WHERE [NickName]=@P0 AND [RoleID]=@P1 ORDER BY [ID] --------OQL Parameters information---------- have 2 parameter,detail: @P0=pdf.net Type:String @P1=5 Type:Int32 ------------------End------------------------ q1:one table and select some fields SELECT [LastLoginIP], [RoleID], [UserName], [ID] FROM [LT_Users] WHERE [NickName]=@P0 --------OQL Parameters information---------- have 1 parameter,detail: @P0=pdf.net Type:String ------------------End------------------------ q2:two table query use join SELECT M.[RoleID], T0.[RoleName] FROM [LT_Users] M INNER JOIN [LT_UserRoles] T0 ON M.[RoleID] = T0.[ID] WHERE M.[NickName]=@P0 AND T0.[RoleName]=@P1 GROUP BY M.[RoleID], T0.[RoleName] ORDER BY M.[ID] --------OQL Parameters information---------- have 2 parameter,detail: @P0=pdf.net Type:String @P1=role1 Type:String ------------------End------------------------ q3:two table query not use join SELECT M.[ID], M.[UserName], T0.[ID], T0.[RoleName] FROM [LT_Users] M ,[LT_UserRoles] T0 WHERE M.[RoleID] = T0.[ID] AND T0.[RoleName] = @P0 ORDER BY M.[ID] --------OQL Parameters information---------- have 1 parameter,detail: @P0=role1 Type:String ------------------End------------------------ q4:count from two table query SELECT M.[RoleID] ,COUNT( M.[RoleID]) AS roldid_count FROM [LT_Users] M INNER JOIN [LT_UserRoles] T0 ON M.[RoleID] = T0.[ID] WHERE M.[NickName]=@P0 GROUP BY M.[RoleID] --------OQL Parameters information---------- have 1 parameter,detail: @P0=pdf.net Type:String ------------------End------------------------ q5:having Test: SELECT [RoleID] ,COUNT( [RoleID]) AS count_rolid FROM [LT_Users] GROUP BY [RoleID] HAVING COUNT( [RoleID]) >= @P0 --------OQL Parameters information---------- have 1 parameter,detail: @P0=2 Type:Int32 ------------------End------------------------ OQL by OQLCompare Test: SELECT M.*,T0.* FROM [LT_Users] M INNER JOIN [LT_UserRoles] T0 ON M.[RoleID] = T0.[ID] WHERE ( M.[UserName] = @P0 AND M.[Password] = @P1 AND T0.[RoleName] = @P2 ) OR ( ( M.[UserName] = @P3 AND M.[Password] = @P4 AND T0.[RoleName] LIKE @P5 ) OR M.[LastLoginTime] > @P6 ) --------OQL Parameters information---------- have 7 parameter,detail: @P0=ABC Type:String @P1=111 Type:String @P2=role1 Type:String @P3=CDE Type:String @P4=222 Type:String @P5=%Role2 Type:String @P6=2013/7/28 22:15:38 Type:DateTime ------------------End------------------------ OQL by OQLCompareFunc Test: SELECT M.*,T0.* FROM [LT_Users] M INNER JOIN [LT_UserRoles] T0 ON M.[RoleID] = T0.[ID] WHERE ( M.[UserName] = @P0 AND M.[Password] = @P1 AND T0.[RoleName] = @P2 ) OR ( ( M.[UserName] = @P3 AND M.[Password] = @P4 AND T0.[ID] IN (1,2,3) ) OR M.[LastLoginTime] > @P5 ) --------OQL Parameters information---------- have 6 parameter,detail: @P0=ABC Type:String @P1=111 Type:String @P2=role1 Type:String @P3=BCD Type:String @P4=222 Type:Int32 @P5=2013/7/28 22:15:38 Type:DateTime ------------------End------------------------ OQL by OQLCompareFunc<T1,T2> Test: SELECT M.*,T0.* FROM [LT_Users] M INNER JOIN [LT_UserRoles] T0 ON M.[RoleID] = T0.[ID] WHERE ( M.[UserName] = @P0 AND M.[Password] = @P1 AND T0.[RoleName] = @P2 ) OR ( ( M.[UserName] = @P3 AND M.[Password] = @P4 AND T0.[RoleName] LIKE @P5 ) OR M.[LastLoginTime] > @P6 ) --------OQL Parameters information---------- have 7 parameter,detail: @P0=ABC Type:String @P1=111 Type:String @P2=Role1 Type:String @P3=CDE Type:String @P4=222 Type:String @P5=%Role2 Type:String @P6=2013/7/28 22:15:38 Type:DateTime ------------------End------------------------ OQL by OQLCompareFunc 括号化简 Test: SELECT [ID],[UserName],[Password],[NickName],[RoleID],[Authority],[IsEnable],[LastLoginTime],[LastLoginIP],[Remarks],[AddTime] FROM [LT_Users] WHERE ( [AddTime] > @P0 AND dateadd(hour,24,@P1) < [AddTime] ) OR [Authority] = @P2 OR [Authority] = @P3 --------OQL Parameters information---------- have 4 parameter,detail: @P0=2013/2/1 0:00:00 Type:DateTime @P1=1900/1/1 0:00:00 Type:DateTime @P2=ABC Type:String @P3=CDE Type:String ------------------End------------------------ one table and select page number 2,page size 10: SELECT [ID],[UserName],[Password],[NickName],[RoleID],[Authority],[IsEnable],[LastLoginTime],[LastLoginIP],[Remarks],[AddTime] FROM [LT_Users] WHERE [NickName]=@P0 ORDER BY [ID] 因为OQL是抽象的SQL，而分页语法又是特定于数据库的，所以具体的分页SQL要到查询真正执行的时候才会生成。 --------OQL Parameters information---------- have 1 parameter,detail: @P0=pdf.net Type:String ------------------End------------------------ OQL by 动态构建 OQLCompare Test(Lambda方式): SELECT [ID],[UserName],[Password],[NickName],[RoleID],[Authority],[IsEnable],[LastLoginTime],[LastLoginIP],[Remarks],[AddTime] FROM [LT_Users] WHERE [AddTime] > @P0 AND [UserName] = @P1 AND [Password] = @P2 --------OQL Parameters information---------- have 3 parameter,detail: @P0=2013/2/1 0:00:00 Type:DateTime @P1=ABC Type:String @P2=111 Type:String ------------------End------------------------ OQL by 动态构建 OQLCompare Test(委托函数方式): SELECT [ID],[UserName],[Password],[NickName],[RoleID],[Authority],[IsEnable],[LastLoginTime],[LastLoginIP],[Remarks],[AddTime] FROM [LT_Users] WHERE [AddTime] > @P0 AND [UserName] = @P1 AND [Password] = @P2 --------OQL Parameters information---------- have 3 parameter,detail: @P0=2013/2/1 0:00:00 Type:DateTime @P1=ABC Type:String @P2=111 Type:String ------------------End------------------------ OQL by 子查询Test: SELECT [ID], [UserName] FROM [LT_Users] WHERE [RoleID] IN (SELECT [ID] FROM [LT_UserRoles] WHERE [RoleNickName] LIKE @P0 ) --------OQL Parameters information---------- have 1 parameter,detail: @P0=%ABC Type:String ------------------End------------------------ OQL by 高级子查询Test: SELECT * FROM [LT_Users] M WHERE [RoleID] = (SELECT [ID] FROM [LT_UserRoles] WHERE M.[NickName] = [RoleNickName] AND [AddTime] > @P0 ) --------OQL Parameters information---------- have 1 parameter,detail: @P0=2013/7/26 22:15:38 Type:DateTime ------------------End------------------------ OQL test OQLOrder object: SELECT [UserName], [ID] FROM [LT_Users] ORDER BY [UserName] DESC, [ID] ASC OQL update: UPDATE [LT_Users] SET [AddTime] = @P0, [Authority] = @P1, [NickName] = @P2 WHERE [RoleID] = @P3 --------OQL Parameters information---------- have 4 parameter,detail: @P0=2013/7/28 22:15:38 Type:DateTime @P1=Read Type:String @P2=菜鸟 Type:String @P3=100 Type:Int32 ------------------End------------------------ OQL delete: DELETE FROM [LT_Users] WHERE [RoleID] = @P0 --------OQL Parameters information---------- have 1 parameter,detail: @P0=100 Type:Int32 ------------------End------------------------ OQL insert: INSERT INTO [LT_Users] ( [AddTime], [Authority], [NickName]) VALUES (@P0,@P1,@P2) --------OQL Parameters information---------- have 3 parameter,detail: @P0=2013/7/28 22:15:38 Type:DateTime @P1=Read Type:String @P2=菜鸟 Type:String ------------------End------------------------ OQL insert from: INSERT INTO [LT_Users] ( [RoleID] ) SELECT [ID] FROM [LT_UserRoles] WHERE @P0 > [ID] --------OQL Parameters information---------- have 1 parameter,detail: @P0=0 Type:Int32 ------------------End------------------------ OQL Test SQL NoLock: SELECT [ID], [UserName], [NickName] FROM [LT_Users] WITH(NOLOCK)
