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)   

 

 

 

posted on 2013-07-30 15:09 深蓝医生 阅读(...) 评论(...) 编辑 收藏

导航