예제 #1
0
        /// <summary>
        /// 获取指定商品名称下面的商品信息
        /// </summary>
        /// <param name="goodsName">商品名称</param>
        /// <returns></returns>
        public List <GoodsBaseInfoVM> GetGoodsBaseInfoWhithGoodsName(string goodsName)
        {
            GoodsBaseInfo info = new GoodsBaseInfo();

            info.GoodsName = goodsName;
            OQL q = new OQL(info);

            q.Select(info.SerialNumber, info.GoodsName, info.Manufacturer, info.CanUserMonth)
            .Where(info.GoodsName)
            .OrderBy(info.GoodsName, "asc");
            return(GetGoodsBaseInfoList(q));
        }
예제 #2
0
        /// <summary>
        /// 获取指定页的联系人信息
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageNumber"></param>
        /// <param name="allCount"></param>
        /// <returns></returns>
        public List <CustomerContactInfo> GetContactInfoList(int pageSize, int pageNumber, int allCount)
        {
            CustomerContactInfo info = new CustomerContactInfo();
            OQL q = new OQL(info);

            q.Select().OrderBy(info.CustomerName, "asc");
            q.Limit(pageSize, pageNumber);
            q.PageWithAllRecordCount = allCount;

            //return EntityQuery<CustomerContactInfo>.QueryList(q);
            return(q.ToList <CustomerContactInfo>());
        }
예제 #3
0
        /// <summary>
        /// 获取所有的可售商品总数
        /// </summary>
        /// <returns></returns>
        public int GetGoodsStockCount()
        {
            GoodsStock stock = new GoodsStock();
            OQL        q     = new OQL(stock);

            q.Select()
            .Count(stock.Stocks, "库存数量")
            .Where(q.Condition.AND(stock.Stocks, ">", 0));

            stock = EntityQuery <GoodsStock> .QueryObject(q);

            return(stock.Stocks);
        }
예제 #4
0
 public static List <UserModels> GetUser()
 {
     try
     {
         UserModels model = new UserModels();
         OQL        q     = new OQL(model);
         return(EntityQuery <UserModels> .QueryList(q.Select().END));
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }
예제 #5
0
        public static List <V_UserModels> GetAllList(int pageSzie, int pageNum)
        {
            V_UserModels model = new V_UserModels();
            OQL          q     = new OQL(model);

            q.PageEnable             = true;
            q.PageNumber             = pageNum;
            q.PageSize               = pageSzie;
            q.PageWithAllRecordCount = GetCount();
            List <V_UserModels> list = EntityQuery <V_UserModels> .QueryList(q.Select().END);

            return(list);
        }
예제 #6
0
        /// <summary>
        /// 获取商品名称分组
        /// </summary>
        /// <returns></returns>
        public List <string> GetAllGoodsNames()
        {
            GoodsBaseInfo info = new GoodsBaseInfo();
            OQL           q    = new OQL(info);

            q.Select(info.GoodsName)
            .GroupBy(info.GoodsName)
            .OrderBy(info.GoodsName, "asc");
            List <GoodsBaseInfo> list = EntityQuery <GoodsBaseInfo> .QueryList(q);

            var result = list.ConvertAll <string>(p => p.GoodsName);

            return(result);
        }
예제 #7
0
        void TestOQL()
        {
            //假如GoodsBaseInfo 对象的实例info 是长生命周期的,那么应该使用下面的方式使用OQL和OQLCompare
            GoodsBaseInfo info = new GoodsBaseInfo();

            using (OQL q = new OQL(info))
            {
                OQLCompare cmp = new OQLCompare(info);
                q.Select(info.GoodsName).Where(cmp.Comparer(info.SerialNumber, "=", "123456"));
                string sql = q.ToString();
                cmp.Dispose();
            }

            DisplayMemory();
        }
예제 #8
0
        /// <summary>
        /// 获取列表数据
        /// </summary>
        /// <param name="cp">where条件,没有可传人null</param>
        /// <param name="orders">排序字段对象</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="pageIndex">当前页</param>
        /// <returns></returns>
        public static List <T> GetList <T>(OQLCompareFunc <T> cp, OQLOrder orders, int pageSize, int pageIndex) 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);
            if (pageSize > 0 && pageIndex > 0 && q.PageWithAllRecordCount > 0)
            {
                q.Limit(pageSize, pageIndex);
            }
            string s = q.PrintParameterInfo();

            return(EntityQuery <T> .QueryList(q));
        }
예제 #9
0
        //该方法已经废弃,合并在 GetOverDays 中
        private int GetStockCount(int goodsID)
        {
            GoodsStock stock = new GoodsStock();

            stock.GoodsID = goodsID;
            OQL q = new OQL(stock);

            q.Select(stock.Stocks)
            .Where(stock.GoodsID);

            stock = EntityQuery <GoodsStock> .QueryObject(q);

            int stockCount = stock.Stocks;

            return(stockCount);
        }
예제 #10
0
        //测试OQL的统计功能
        private int GetStockCount(string serialNumber)
        {
            SuperMarketDAL.Entitys.GoodsStock stock = new SuperMarketDAL.Entitys.GoodsStock();
            stock.SerialNumber = serialNumber;
            OQL q = new OQL(stock);

            q.Select()
            .Count(stock.Stocks, "")
            .Where(stock.SerialNumber);

            stock = EntityQuery <SuperMarketDAL.Entitys.GoodsStock> .QueryObject(q);

            int stockCount = stock.Stocks;

            return(stockCount);
        }
예제 #11
0
 public static List <UserModels> GetUser(int page, int num)
 {
     try
     {
         UserModels model = new UserModels();
         OQL        q     = new OQL(model);
         q.PageEnable             = true;
         q.PageNumber             = num;
         q.PageSize               = page;
         q.PageWithAllRecordCount = UserModels.GetCount();
         return(EntityQuery <UserModels> .QueryList(q.Select().END));
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }
예제 #12
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());
        }
예제 #13
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());
        }
예제 #14
0
        /// <summary>
        /// 获取用户权限菜单
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List <MenuViewModel> GetMenuListByAccountId(long accountId, long menuType)
        {
            AdminRole role  = new AdminRole();
            OQL       qRole = OQL.From(role)
                              .Select()
                              .OrderBy(role.ID, "asc")
                              .END;
            StringBuilder sbMenuIds = new StringBuilder();

            foreach (AdminRole item in qRole.ToList <AdminRole>())
            {
                sbMenuIds.Append(item.MenuIds);
            }

            JsonRsp <MenuViewModel> rsp = new JsonRsp <MenuViewModel>();
            AdminMenu model             = new AdminMenu();
            OQL       q = new OQL(model);

            q.Select()
            .Where(q.Condition.AND(model.MenuType, "=", menuType))
            .OrderBy(model.ID, "asc");

            List <AdminMenu> list = q.ToList <AdminMenu>();//使用OQL扩展

            return(list.ConvertAll <MenuViewModel>(o =>
            {
                return new MenuViewModel()
                {
                    ID = o.ID,
                    ParentID = o.ParentID,
                    MenuKey = o.MenuKey,
                    MenuName = o.MenuName,
                    MenuUrl = o.MenuUrl,
                    MenuType = o.MenuType,
                    IDPath = o.IDPath,
                    Remark = o.Remark,
                    Sort = o.Sort,
                    Status = o.Status,
                    CreateTIme = o.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
                };
            }
                                                   ));
        }
예제 #15
0
        /// <summary>
        /// 简单查询
        /// </summary>
        /// <returns></returns>
        public List <SODUser> getRegionList(string logName)
        {
            var outlist = new List <SODUser>();

            try
            {
                SODUser model = new SODUser()
                {
                    LogName = logName
                };
                OQL q = new OQL(model);
                q.Select().Where(model.LogName);
                outlist = new EntityQuery <SODUser>(CurrentDataBase).GetList(q);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return(outlist);
        }
예제 #16
0
        public static void 分页查询()
        {
            SODUser user = new SODUser();

            OQL q = new OQL(user);

            OQLCompareFunc resultFunc = cmp =>
            {
                OQLCompare resultCmp = cmp.Comparer(user.ID, ">", 0);
                resultCmp = resultCmp & cmp.Comparer(user.LogName, "like", "%zhang%");
                return(resultCmp);
            };

            q.Select().Where(resultFunc).OrderBy(user.ID);
            //分页
            q.PageEnable             = true;
            q.PageWithAllRecordCount = 10;
            q.Limit(10, 1, true);
            var outlist = EntityQuery <SODUser> .QueryList(q);

            int pageCount = q.PageWithAllRecordCount;
        }
예제 #17
0
        private void btnChange_Click(object sender, EventArgs e)
        {
            TblTestModel model = new TblTestModel();

            model.Title      = "测试标题";
            model.ArtContent = "测试内容";
            model.CreateTime = DateTime.Now;
            EntityQuery <TblTestModel> .Instance.Insert(model);

            OQL q = new OQL(model);

            q.Select().OrderBy(model.CreateTime, "desc");
            model = EntityQuery <TblTestModel> .QueryList(q).ToList <TblTestModel>()[0];

            model.Title      = "已修改" + model.ID.ToString();
            model.CreateTime = DateTime.Now;
            EntityQuery <TblTestModel> .Instance.Update(model);

            dataGV1.DataSource = EntityQuery <TblTestModel> .QueryList(q).ToList <TblTestModel>();

            dataGV1.Refresh();
        }
예제 #18
0
        private static void TestTransaction()
        {
            AdoHelper db = MyDB.GetDBHelper();
            EntityQuery <AuctionOperationLog> query = new EntityQuery <AuctionOperationLog>(db);

            AuctionOperationLog optLog = new AuctionOperationLog();

            optLog.OperaterID = 1000;
            optLog.Module     = "Login";
            optLog.Operation  = "登录成功1";
            optLog.LogSource  = "PC";

            db.BeginTransaction();
            try
            {
                query.Insert(optLog);

                //必须设置为全部属性已经修改,否则仅会更新 Operation 字段
                optLog.ResetChanges(true);
                optLog.Operation = "退出登录";
                query.Insert(optLog);

                //optLog.Module = "Login";
                //OQL q = OQL.From(optLog).Select().Where(optLog.Module).END;
                OQL q = new OQL(optLog);
                //q.Select().Where(q.Condition.AND(optLog.Operation, "like", "%登录%"));

                q.Select().Count(optLog.OperaterID, "");//使用空字符串参数,这样统计的值将放到 OperaterID 属性中
                //必须指定db参数,否则不再一个事务中,无法进行统计查询
                optLog = EntityQuery <AuctionOperationLog> .QueryObject(q, db);

                int allCount = optLog.OperaterID;

                //optLog 已经使用过,在生成OQL的查询前,必须使用新的实体对象,
                //       否则下面的查询仅会使用OperaterID 字段从而导致分页查询出错
                optLog = new AuctionOperationLog();
                q      = new OQL(optLog);

                q.Select().OrderBy(optLog.Module, "asc").OrderBy(optLog.AtDateTime, "desc");
                q.Limit(10, 2);
                q.PageEnable             = true;
                q.PageWithAllRecordCount = allCount;

                //查询列表并更新到数据库
                List <AuctionOperationLog> list = EntityQuery <AuctionOperationLog> .QueryList(q, db);

                foreach (AuctionOperationLog logItem in list)
                {
                    logItem.AtDateTime = DateTime.Now;
                }
                query.Update(list);

                db.Commit();

                Console.WriteLine("事务操作成功。");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error:" + ex.Message);
                db.Rollback();
            }
        }
예제 #19
0
        static void Main(string[] args)
        {
            Console.WriteLine("====**************** PDF.NET SOD 控制台测试程序 **************====");
            Assembly coreAss = Assembly.GetAssembly(typeof(AdoHelper));//获得引用程序集

            Console.WriteLine("框架核心程序集 PWMIS.Core Version:{0}", coreAss.GetName().Version.ToString());
            Console.WriteLine();
            Console.WriteLine("  应用程序配置文件默认的数据库配置信息:\r\n  当前使用的数据库类型是:{0}\r\n  连接字符串为:{1}\r\n  请确保数据库服务器和数据库是否有效且已经初始化过建表脚本(项目下的2个sql脚本文件),\r\n继续请回车,退出请输入字母 Q ."
                              , MyDB.Instance.CurrentDBMSType.ToString(), MyDB.Instance.ConnectionString);
            Console.WriteLine("=====Power by Bluedoctor,2015.2.8 http://www.pwmis.com/sqlmap ====");
            string read = Console.ReadLine();

            if (read.ToUpper() == "Q")
            {
                return;
            }

            //写入10000条日志,有缓存,可能不会写完
            Console.WriteLine("测试日志写入10000 条信息...");
            CommandLog loger = new CommandLog();

            for (int t = 0; t <= 100; t++)
            {
                System.Threading.Thread thread = new System.Threading.Thread(new System.Threading.ParameterizedThreadStart(WriteLog));
                thread.Name = "thread" + t;
                thread.Start(loger);
            }

            loger.Flush();
            Console.WriteLine("按任意键继续");
            Console.ReadLine();

            EntityUser  etu = new EntityUser();
            ITable_User itu = etu.AsEntity();
            DateTime    dtt = itu.Birthday;



            //测试 AdoHelper的并发能力
            //for (int i = 0; i < 100; i++)
            //{
            //    System.Threading.Thread t = new System.Threading.Thread(
            //        new System.Threading.ParameterizedThreadStart(TestDataSetAndOQL));
            //    t.Name = "thread "+i;
            //    t.Start();

            //}

            //测试生成列的脚本
            EntityCommand ecmd         = new EntityCommand(new Table_User(), new SqlServer());
            string        table_script = ecmd.CreateTableCommand;

            Console.Write("1,测试 OpenSession 长连接数据库访问...");
            TestDataSetAndOQL(null);
            Console.WriteLine("OK");
            //
            Console.WriteLine("2,测试OQL 转SQL...");
            RoadTeam.Model.CS.TbCsEvent CsEvent = new RoadTeam.Model.CS.TbCsEvent();
            CsEvent.EventID = 1;
            OQL oql = OQL.From(CsEvent)
                      .Select(CsEvent.EventCheck, CsEvent.EventCheckInfo, CsEvent.EventCheckor, CsEvent.EventCheckTime)
                      .Where(CsEvent.EventID)
                      .END;

            Console.WriteLine(oql.ToString());
            Console.WriteLine("-----------------------");

            RoadTeam.Model.CS.TbCsEvent CsEvent2 = new RoadTeam.Model.CS.TbCsEvent();
            CsEvent.EventID = 1;
            OQL oql2 = OQL.From(CsEvent2)
                       .Select(true, CsEvent2.EventCheck, CsEvent2.EventCheckInfo, CsEvent2.EventCheckor, CsEvent2.EventCheckTime)
                       .Where(CsEvent2.EventID)
                       .END;

            Console.WriteLine(oql2.ToString());
            Console.WriteLine("-----------------------");
            Console.WriteLine("OK");
            //
            Console.Write("3,测试实体类动态增加虚拟属性...");
            UserModels um1 = new UserModels();

            um1.AddPropertyName("TestName");
            um1["TestName"] = 123;
            int testi = (int)um1["TestName"];

            um1["TestName"] = "abc";
            string teststr = (string)um1["TestName"];

            Console.WriteLine("OK");
            //
            Console.Write("4,测试缓存...");
            var cache = PWMIS.Core.MemoryCache <EntityBase> .Default;

            cache.Add("UserModels", um1);
            var cacheData = cache.Get("UserModels");

            cache.Remove("UserModels");
            Console.WriteLine("OK");
            //
            Console.Write("5,测试自动创建实体类数据库表...");
            AutoCreateEntityTable <LT_Users>();
            AutoCreateEntityTable <LT_UserRoles>();
            Console.WriteLine("OK");

            Console.WriteLine("------------测试暂时停止,回车继续运行------");
            Console.ReadLine();
            //return;

            Console.Write("6,测试实体类的外键查询...");
            TestEntityFK();
            Console.WriteLine("OK");
            Console.Write("7,测试实体类批量插入...");
            OqlInTest();
            Console.WriteLine("OK");

            Console.WriteLine("8,测试SOD POCO实体类性能...");
            Console.WriteLine("SELECT top 100000 UID,Sex,Height,Birthday,Name FROM Table_User");
            for (int i = 0; i < 10; i++)
            {
                Console.WriteLine("-------------Testt No.{0}----------------", i + 1);
                TestPocoQuery();
            }
            Console.WriteLine("--------OK---------------");

            Console.Write("9,测试OQL IN 子查询...");
            TestInChild();
            Console.WriteLine("OK");
            //TestFun(1, 2, 3);

            Console.WriteLine("10,测试泛型 OQL --GOQL");
            TestGOQL();
            Console.WriteLine("OK");

            Console.WriteLine("11,测试OQL 批量更新(带条件更新)...");
            UpdateTest();
            Console.WriteLine("OK");

            Console.WriteLine("12,测试批量数据插入性能....");
            //InsertTest();



            Console.WriteLine("13,OQL 自连接...");
            OqlJoinTest();
            //
            Console.Write("14,根据接口类型,自动创建实体类测试...");
            TestDynamicEntity();
            Console.WriteLine("OK");

            //
            Console.WriteLine("15,Sql 格式化查询测试( SOD 微型ORM功能)...");
            AdoHelper dbLocal = new SqlServer();

            dbLocal.ConnectionString = MyDB.Instance.ConnectionString;
            //DataSet ds = dbLocal.ExecuteDataSet("SELECT * FROM Table_User WHERE UID={0} AND Height>={1:5.2}", 1, 1.80M);

            /*
             * 下面的写法过时
             * var dataList = dbLocal.GetList(reader =>
             * {
             *  return new
             *  {
             *      UID=reader.GetInt32(0),
             *      Name=reader.GetString(1)
             *  };
             * }, "SELECT UID,Name FROM Table_User WHERE Sex={0} And Height>={0:5.2}",1, 1.60);
             */
            var dataList = dbLocal.ExecuteMapper("SELECT UID,Name FROM Table_User WHERE Sex={0} And Height>={1:5.2}", 1, 1.60)
                           .MapToList(reader => new
            {
                UID  = reader.GetInt32(0),
                Name = reader.GetString(1)
            });

            Console.WriteLine("OK");

            //
            Console.Write("16,测试属性拷贝...");
            V_UserModels vum = new V_UserModels();

            vum.BIGTEAM_ID   = 123;//可空属性,如果目标对象不是的话,无法拷贝
            vum.REGION_ID    = 456;
            vum.SMALLTEAM_ID = 789;

            UserModels um = vum.CopyTo <UserModels>();

            Console.WriteLine("OK");

            //
            Console.Write("17,测试【自定义查询】的实体类...");
            UserPropertyView up = new UserPropertyView();
            OQL      q11        = new OQL(up);
            OQLOrder order      = new OQLOrder(q11);

            q11.Select()
            .Where(q11.Condition.AND(up.PropertyName, "=", "总成绩").AND(up.PropertyValue, ">", 1000))
            .OrderBy(order.Asc(up.UID));
            AdoHelper db11   = MyDB.GetDBHelperByConnectionName("local");
            var       result = EntityQuery <UserPropertyView> .QueryList(q11, db11);

            //下面2行不是必须
            q11.Dispose();
            Console.WriteLine("OK");

            //EntityContainer ec = new EntityContainer(q11);
            //var ecResult = ec.MapToList(() => {
            //    return new { AAA = ec.GetItemValue<int>(0), BBB = ec.GetItemValue<string>(1) };
            //});

            /////////////////////////////////////////////////////
            Console.WriteLine("18,测试实体类【自动保存】数据...");

            TestAutoSave();

            /////////////////测试事务////////////////////////////////////
            Console.WriteLine("19,测试测试事务...");
            TestTransaction();
            TestTransaction2();
            Console.WriteLine("事务测试完成!");
            Console.WriteLine("-------PDF.NET SOD 测试全部完成-------");

            Console.ReadLine();
        }
예제 #20
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);
        }
예제 #21
0
 public GOQL1 <T> Select()
 {
     return(new GOQL1 <T>(this, currentOQL.Select()));
 }
예제 #22
0
		public bool HasPassword(string identity, string @namespace)
		{
			var entity = new UserEntity();
			var oql = new OQL(entity);
			var comparer = new Comparer(oql);
			var dataAccess = this.EnsureService<IDataAccess>();

			comparer = Utility.GetUserIdentityComparer(identity, @namespace, comparer, entity);

			comparer = comparer & comparer.IsNotNull(entity.Password);

			oql.Select(entity.UserId)
			   .Where(comparer)
			   .OrderBy(p => p.Desc(entity.UserId))
			   .End();

			return dataAccess.Exists(oql);
		}
예제 #23
0
		public bool Exists(string identity, string @namespace)
		{
			if(string.IsNullOrWhiteSpace(identity))
				return false;

			var entity = new UserEntity();
			var oql = new OQL(entity);
			var comparer = new Comparer(oql);
			var dataAccess = this.EnsureService<IDataAccess>();

			Utility.UserIdentityType identityType;

			comparer = Utility.GetUserIdentityComparer(identity, @namespace, comparer, entity, out identityType);

			// 确保用户名是审核通过的
			if(identityType == Utility.UserIdentityType.Name)
				this.Censor(identity);

			oql.Select(entity.UserId)
			   .Where(comparer)
			   .OrderBy(p => p.Desc(entity.UserId))
			   .End();

			return dataAccess.Exists(oql);
		}
예제 #24
0
        /// <summary>
        /// 获取用户权限菜单
        /// </summary>
        /// <param name="accountId"></param>
        /// <param name="menuType"></param>
        /// <returns></returns>
        public List <MenuViewModel> GetMenuListByAccountId(long accountId, long menuType)
        {
            AdminRole        role = new AdminRole();
            AdminAccountRole ar   = new AdminAccountRole();

            ar.AccountID = accountId;
            OQL joinQ = OQL.From(role)
                        .Join(ar).On(role.ID, ar.RoleID)
                        .Select(role.MenuIds)
                        .Where(ar.AccountID)
                        .OrderBy(role.Sort, "desc")
                        .END;

            PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
            EntityContainer  ec       = new EntityContainer(joinQ, db);
            List <AdminRole> roleList = (List <AdminRole>)ec.MapToList <AdminRole>(() => new AdminRole()
            {
                MenuIds = role.MenuIds,
            });

            StringBuilder sbMenuIds = new StringBuilder();

            foreach (AdminRole item in roleList)
            {
                sbMenuIds.Append(item.MenuIds);
            }

            //long[] roleArr = Array.ConvertAll(sbMenuIds.ToString().Split(','), long.Parse);


            JsonRsp <MenuViewModel> rsp = new JsonRsp <MenuViewModel>();
            AdminMenu model             = new AdminMenu();
            OQL       q = new OQL(model);

            if (menuType > 0)
            {
                q.Select()
                .Where(q.Condition.AND(model.MenuType, "=", menuType).AND(model.Status, ">", -1))
                .OrderBy(model.Sort, "desc");
            }
            else
            {
                q.Select()
                .Where(q.Condition.AND(model.Status, ">", -1))
                .OrderBy(model.Sort, "desc");
            }

            List <AdminMenu> list = q.ToList <AdminMenu>();//使用OQL扩展

            return(list.ConvertAll <MenuViewModel>(o =>
            {
                return new MenuViewModel()
                {
                    ID = o.ID,
                    ParentID = o.ParentID,
                    MenuKey = o.MenuKey,
                    MenuName = o.MenuName,
                    MenuUrl = o.MenuUrl,
                    MenuType = o.MenuType,
                    Sort = o.Sort,
                    Status = o.Status,
                    CreateTime = o.CreateTime,
                };
            }
                                                   ));
        }
예제 #25
0
		public bool HasPassword(int userId)
		{
			var entity = new UserEntity();
			var oql = new OQL(entity);
			var dataAccess = this.EnsureService<IDataAccess>();

			oql.Select(entity.UserId)
			   .Where(oql.Condition.And(entity.Password, "IS", "NOT NULL"))
			   .OrderBy(p => p.Desc(entity.UserId))
			   .End();

			return dataAccess.Exists(oql);
		}
예제 #26
0
		public User GetUser(string identity, string @namespace)
		{
			var entity = new UserEntity();
			var oql = new OQL(entity);
			var comparer = new Comparer(oql);
			var dataAccess = this.EnsureService<IDataAccess>();

			comparer = Utility.GetUserIdentityComparer(identity, @namespace, comparer, entity);

			entity = dataAccess.Find<UserEntity>(oql.Select().Where(comparer).End());

			return Mapper.Map<UserEntity, User>(entity);
		}
예제 #27
0
		public static bool GetUserId(IDataAccess dataAccess, string identity, string @namespace, out int userId)
		{
			if(dataAccess == null)
				throw new ArgumentNullException("dataAccess");

			if(string.IsNullOrWhiteSpace(identity))
				throw new ArgumentNullException("identity");

			var entity = new UserEntity();
			var oql = new OQL(entity);
			var comparer = new Comparer(oql);

			comparer = GetUserIdentityComparer(identity, @namespace, comparer, entity);

			oql = oql.Select(entity.UserId)
					 .Where(comparer)
					 .End();

			entity = dataAccess.Find<UserEntity>(oql);

			var result = entity != null && entity.UserId > 0;

			userId = 0;

			if(result)
				userId = entity.UserId;

			return result;
		}