Exemple #1
0
        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();
        }
Exemple #2
0
        void TestLimit()
        {
            Users user = new Users()
            {
                NickName = "pdf.net"
            };
            OQL q0 = OQL.From(user)
                     .Select()
                     .Where(user.NickName)
                     .OrderBy(user.ID, "asc")
                     .END;

            q0.Distinct = true;
            q0.Limit(10, 2);
            try
            {
                var list = EntityQuery <Users> .QueryList(q0);
            }
            catch (Exception ex)
            {
                Console.WriteLine("查询错误:{0}", ex.Message);
            }
            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());
        }
Exemple #3
0
        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 RoleNames[] { RoleNames.User, RoleNames.Manager, RoleNames.Admin })
                )
                |
                (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());
        }
Exemple #4
0
        public void GetRoleFunctionList(string personId)
        {
            //子查询
            Base_Person_FunctionInfo personFun = new Base_Person_FunctionInfo()
            {
                PersonId = personId, DirectionFlag = -1
            };
            OQLChildFunc oqlChild = parent => OQL.From(parent, personFun)
                                    .Select(true, personFun.FunctionId)
                                    .Where(personFun.PersonId, personFun.DirectionFlag)
                                    .END;

            //查询所有功能,并从中过滤掉子查询的结果
            Base_FunctionInfo    fun        = new Base_FunctionInfo();
            Base_Person_RoleInfo personRole = new Base_Person_RoleInfo()
            {
                PersonId = personId
            };
            Base_Role_FunctionInfo roleFun = new Base_Role_FunctionInfo();
            OQL oql = OQL.From(personRole)
                      .InnerJoin(roleFun).On(personRole.RoleId, roleFun.RoleId)
                      .InnerJoin(fun).On(roleFun.FunctionId, fun.FunctionId)
                      .Select(true, fun.FunctionId, fun.FunctionName, fun.NavigateAddress)
                      .Where(cmp => cmp.EqualValue(personRole.PersonId)
                             & cmp.Comparer(fun.FunctionId, OQLCompare.CompareType.NotIn, oqlChild))
                      .END;

            //return EntityQuery<Base_FunctionInfo>.QueryList(oql);

            Console.WriteLine("OQL Test Child Query:\r\n{0}\r\n", oql);
            Console.WriteLine(oql.PrintParameterInfo());
        }
Exemple #5
0
        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(q2);
            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());
        }
Exemple #6
0
        /// <summary>
        /// 获取符合条件的实体对象列表 OQLCompareFunc
        /// </summary>
        /// <param name="cp">where条件,没有可传人null</param>
        /// <param name="orders">排序字段对象</param>
        /// <returns></returns>
        public static List <T> GetAllList <T>(OQLCompareFunc <T> cp, OQLOrder orders) where T : EntityBase, ILongID, new()
        {
            T   m = new T();
            OQL q = new OQL(m);

            if ((object)cp != null)
            {
                if ((object)orders != null)
                {
                    q.Select().Where(cp).OrderBy(orders);
                }
                else
                {
                    q.Select().Where(cp);
                }
            }
            else
            {
                if ((object)orders != null)
                {
                    q.Select().OrderBy(orders);
                }
                else
                {
                    q.Select();
                }
            }
            q.PageWithAllRecordCount = GetRecordCounts <T>(cp);
            string s = q.PrintParameterInfo();

            return(EntityQuery <T> .QueryList(q));
        }
Exemple #7
0
        void TestIfCondition()
        {
            Users user = new Users()
            {
                ID = 1, NickName = "abc", UserName = "******", Password = "******"
            };
            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());
        }
Exemple #8
0
        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());
        }
Exemple #9
0
        void TestIfCondition()
        {
            Users user = new Users()
            {
                ID = 1, UserName = "******", Password = "******", NickName = ""
            };                                                                                          //NickName = "abc",
            OQLCompareFunc cmpFun = cmp =>
            {
                //5.2.2.0428 版本之前,仅支持被注释掉的写法,用一个独立的对象来接收条件比较结果,否则会出错.
                //           之后的版本,按照现在的写法没问题,不过仍然建议使用原来的写法.
                OQLCompare cmpResult = null;
                if (user.NickName != "")
                {
                    //cmpResult = cmp.Property(user.AddTime) > new DateTime(2013, 2, 1);
                    cmp = 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");
                    cmp = cmp & cmp.Property(user.UserName) == "ABC" & cmp.Comparer(user.Password, "=", "111");
                }
                //return cmpResult;
                cmpResult = cmp;
                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());
        }
Exemple #10
0
        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);
        }
Exemple #11
0
        void TestNotCondition()
        {
            Users user = new Users();
            OQL   q    = OQL.From(user)
                         .Select(user.ID, user.UserName, user.Password)
                         .Where <Users>((cmp, u) => OQLCompare.Not(
                                            cmp.Property(u.UserName) == "ABC" & cmp.Property(u.Password) == "123")
                                        )
                         .END;

            Console.WriteLine("OQL Test NOT Condition:\r\n{0}\r\n", q);
            Console.WriteLine(q.PrintParameterInfo());
        }
Exemple #12
0
        void Test2FieldOpt()
        {
            Users user = new Users();
            //    user.LastLoginTime-user.AddTime>'23:00:00'
            // => user.LastLoginTime -'23:00:00'>user.AddTime
            OQL q = OQL.From(user)
                    .Select()
                    .Where(cmp => cmp.Comparer(user.LastLoginTime, ">", user.AddTime, "{0}-'23:00:00'"))
                    .END;

            q.SelectStar = true;
            Console.WriteLine("OQL Test SQL Field compute:\r\n{0}\r\n", q);
            Console.WriteLine(q.PrintParameterInfo());
        }
Exemple #13
0
        //动态处理查询条件,详细见 http://www.cnblogs.com/bluedoctor/p/4470526.html
        void TestIfCondition2()
        {
            Users user = new Users()
            {
                ID = 0, NickName = "abc", UserName = "******", Password = "******"
            };
            OQL q7 = OQL.From(user)
                     .Select()
                     .Where <Users>(CreateCondition)
                     .END;

            Console.WriteLine("OQL by 动态构建 OQLCompare Test(委托函数方式):\r\n{0}", q7);
            Console.WriteLine(q7.PrintParameterInfo());
        }
Exemple #14
0
        public static void  杂查询()
        {
            SODUser user = new SODUser();
            OQL     q    = OQL.From(user)
                           .Select()
                           .Where(cmp => cmp.Property(user.LogName) == "zhangyi" & cmp.Comparer(user.ID, "like", 1))
                           .END;

            List <SODUser> users = EntityQuery <SODUser> .QueryList(q);

            string sql = q.ToString();

            sql = q.PrintParameterInfo();
        }
Exemple #15
0
        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());
        }
Exemple #16
0
        void TestDelete()
        {
            Users user = new Users()
            {
                ID = 99
            };

            OQL q = OQL.From(user)
                    .Delete()
                    //.Where(cmp => cmp.Property(user.RoleID) == 100)
                    .END;

            Console.WriteLine("OQL 安全删除数据测试:\r\n{0}\r\n", q);
            Console.WriteLine(q.PrintParameterInfo());
        }
Exemple #17
0
        void TestInsertFrom()
        {
            Users     user  = new Users();
            UserRoles roles = new UserRoles();

            OQL child = OQL.From(roles)
                        .Select(roles.ID)
                        .Where(cmp => cmp.Comparer(roles.ID, ">", RoleNames.User))
                        .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());
        }
Exemple #18
0
        void Test6()
        {
            OQLCompareFunc <Users> cmpResult = (cmp, u) =>
                                               cmp.Comparer(u.UserName, OQLCompare.CompareType.IN, new string[] { "zhang aa", "li bb", "wang cc" }); //

            Users user = new Users();

            OQL q6 = OQL.From(user)
                     .Select()
                     .Where(cmpResult)
                     .END;

            Console.WriteLine("OQL by OQLCompareFunc<T1>  Test:\r\n{0}", q6);
            Console.WriteLine(q6.PrintParameterInfo());
            q6.Dispose();
        }
Exemple #19
0
        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());
        }
Exemple #20
0
        void TestMapOql()
        {
            Users user = new Users()
            {
                NickName = "pdf.net", RoleID = RoleNames.Admin
            };

            //插入一个数据便于测试
            EntityQuery <Users> .Instance.Insert(user);

            OQL             q  = OQL.From(user).Select().Where(user.RoleID).END;
            EntityContainer ec = new EntityContainer(q);
            //第一种映射方式,适用于OQL单实体类查询:
            var list = ec.MapToList(user, u => new
            {
                P1 = u.ID,
                P2 = u.NickName
            }
                                    );

            UserRoles role = new UserRoles();
            OQL       q2   = OQL.From(user)
                             .Join(role).On(user.RoleID, role.ID)
                             .Select()
                             .Where(cmp => cmp.Comparer(role.ID, "=", RoleNames.User))
                             .OrderBy(role.RoleName)
                             .END;

            q2.Limit(10, 2);
            q2.PageWithAllRecordCount = 0;



            EntityContainer ec2 = new EntityContainer(q2);
            //第二种映射方式,推荐用于OQL多个实体类关联查询的情况:
            var list2 = ec2.MapToList(() => new {
                P1 = user.ID,
                P2 = user.NickName,
                P3 = role.ID,
                P4 = role.Description
            });

            Console.WriteLine("OQL TestMapOql Query:\r\n{0}\r\n", q2);
            Console.WriteLine(q2.PrintParameterInfo());
        }
Exemple #21
0
        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());
        }
Exemple #22
0
        static void TestInChild()
        {
            p_hege_detail detailObj = new p_hege_detail();

            p_hege phegeObj = new p_hege();
            OQL    phegeOq  = new OQL(phegeObj);

            phegeOq.Select(phegeObj.id).OrderBy(phegeObj.id, "DESC");
            phegeOq.TopCount = 1;

            OQL detailOq = new OQL(detailObj);

            detailOq.Select(detailObj.coName, detailObj.coType, detailObj.coMessage)
            .Where(cmp => cmp.Comparer(detailObj.coType, "=", "Status") & cmp.Comparer(detailObj.hegeID, "in", phegeOq))         //
            .OrderBy(detailObj.id, "DESC");

            Console.WriteLine("SQL:\r\n{0}\r\n,{1}", detailOq.ToString(), detailOq.PrintParameterInfo());
        }
Exemple #23
0
        static void OqlInTest()
        {
            LT_UserRoles roles = new LT_UserRoles()
            {
                NickName = "Role1"
            };
            LT_Users users = new LT_Users();
            OQL      qRole = OQL.From(roles).Select(roles.ID).Where(
                cmp => cmp.Comparer(roles.NickName, "like", "123%")
                ).END;

            OQL qUser = new OQL(users);

            qUser.Select().Where(qUser.Condition
                                 .AND(users.LastLoginTime, ">=", DateTime.Now.AddDays(-10))
                                 .NotIn(users.RoleID, qRole));
            Console.WriteLine("OQL to SQL:\r\n{0},\r\n{1}", qUser, qUser.PrintParameterInfo());
        }
Exemple #24
0
        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());
        }
Exemple #25
0
        private static void TestOQL()
        {
            SalesOrder order = new SalesOrder();
            //model.iOrderTypeID = "123";
            BCustomer customer = new BCustomer();

            //请注意方法 GetCondtion1,GetCondtion2,GetCondtion3 中变量 iCityID 的不同而带来的构造条件语句的不同
            OQLCompareFunc <BCustomer, SalesOrder> cmpFun = GetCondtion1();

            OQL q = OQL.From(order)
                    .LeftJoin(customer).On(order.iCustomerID, customer.ISID)
                    .Select()
                    .Where(cmpFun)
                    .OrderBy(order.iBillID, "desc")
                    .END;

            Console.WriteLine(q);
            Console.WriteLine(q.PrintParameterInfo());
            //此OQL 可以由 EntityContainer 对象的方法执行
        }
Exemple #26
0
        static void UpdateTest()
        {
            LT_Users userCmp = new LT_Users()
            {
                Authority = "admin", IsEnable = true, Remarks = "add"
            };
            // LT_Users userQ = new LT_Users();
            //OQLCompare cmp = new OQLCompare(userCmp);
            //OQL q = new OQL(userQ);
            OQL        q   = new OQL(userCmp);
            OQLCompare cmp = new OQLCompare(q);

            cmp = cmp.Comparer(userCmp.ID, "in", new int[] { 1, 2, 3 })
                  & cmp.Comparer(userCmp.LastLoginIP, "=", "127.0.0.1");
            //------分界线-------

            q.Update(userCmp.Authority, userCmp.IsEnable, userCmp.Remarks).Where(cmp);

            Console.WriteLine("update test:{0}\r\n{1}", q, q.PrintParameterInfo());
        }
Exemple #27
0
        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());
        }
Exemple #28
0
        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);
        }
Exemple #29
0
        void Test1()
        {
            Users user = new Users()
            {
                NickName = "pdf.net", RoleID = RoleNames.Admin
            };
            UserRoles roles = new UserRoles()
            {
                RoleName = "role1"
            };
            //测试字段直接比较
            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 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);
        }
Exemple #30
0
 public GOQL <T> Print(out string sqlInfo)
 {
     sqlInfo = string.Format("SQL:{0}\r\n{1}", currentOQL.ToString(), currentOQL.PrintParameterInfo());
     return(this);
 }