Example #1
0
        void TestPaging()
        {
            Select sel = Qb.Select("*")
                         .From("AE", "ae")
                         .OrderBy("AE_ID")
                         .Page(2, 10);

            Renderer.MySqlRenderer my = new Renderer.MySqlRenderer();
            string sql = my.RenderSelect(sel);


            From u = From.Table("unit", "u", "nsi");

            sel = Qb.Select("*")
                  .From(u)
                  .OrderBy("id")
                  .Page(2, 10);
            Renderer.PostgreSqlRenderer pg = new Renderer.PostgreSqlRenderer();
            sql = pg.RenderSelect(sel);

            Renderer.SqLiteRenderer lite = new Renderer.SqLiteRenderer();
            sql = lite.RenderSelect(sel);

            Renderer.SqlServerRenderer ms = new Renderer.SqlServerRenderer();
            sql = ms.RenderSelect(sel);

            Renderer.OracleRenderer ora = new Renderer.OracleRenderer();
            sql = ora.RenderSelect(sel);
        }
Example #2
0
        void TestNotIn()
        {
            Select sel = Qb.Select("*")
                         .From("AE", "ae")
                         .Where(Cond.NotIn(Expr.Field("f"), 1, 2, 3, 4));

            Renderer.PostgreSqlRenderer pg = new Renderer.PostgreSqlRenderer();
            string sql = pg.RenderSelect(sel);
        }
Example #3
0
        public void TestFrom()
        {
            From f = From.Table("a", "b", "dbo");

            f = From.SubQuery(Qb.Select("a"), "t");
            Union u = Qb.Union();

            f = From.Union(u, "a");
        }
Example #4
0
        public void TestExpr()
        {
            Expr e = Expr.Field("a");

            e = Expr.Field("a", From.Table("tab"));
            e = Expr.Function(AggFunc.Max, Expr.Field("a"));
            e = Expr.IfNull(Expr.Field("a"), Expr.Param("p"));
            e = Expr.SubQuery(Qb.Select("a"));
        }
Example #5
0
        public void TestJoin()
        {
            Select sel = Qb.Select("a").From("lt").Join("rt", JoinCond.Fields("id_0"));

            From  lt = From.Table("lt");
            From  rt = From.Table("rt");
            Logic l  = Logic.And(Cond.Equal(Expr.Field("id_0", lt), Expr.Field("id_0", rt)));

            sel = Qb.Select("a").From("lt").Join(JoinType.Left, lt, rt, l);
        }
Example #6
0
        public void TestCond()
        {
            Cond c = Cond.Equal(Expr.Field("a"), Expr.Param("p"));

            c = Cond.Like(Expr.Field("a"), Expr.Param("p"), '!');
            c = Cond.Like("a", "%");
            c = Cond.In(Expr.Field("a"), Qb.Select("a"));
            c = Cond.IsNull(Expr.Field("a"));
            c = Cond.Between(Expr.Field("a"), Expr.Param("p1"), Expr.Param("p2"));
            c = Cond.Exists(Qb.Select("a"));
        }
Example #7
0
        public void SelectTest()
        {
            Select sel = Qb.Select("*")
                         .From("customer").OrderBy("id");

            using (AnyDbConnection con = _factory.OpenConnection())
            {
                var res = con.Query(sel);
                Assert.Equal(100, res.Count());
            }
        }
Example #8
0
        public void GetPageSqlTest()
        {
            Select sel = Qb.Select("*")
                         .From("customer").OrderBy("id").Page(1, 10);
            string sql = _factory.GetSql(sel);

            using (AnyDbConnection con = _factory.OpenConnection())
            {
                var res = con.Query(sel);
                Assert.Equal(10, res.Count());
            }
        }
Example #9
0
        void TestLong()
        {
            Select sel = Qb.Select("*")
                         .From("tab")
                         .Where(Cond.Equal("col", DateTime.Now.Ticks));
            AnyDbFactory factory = new AnyDbFactory(new AnyDbSetting());
            string       sql     = factory.GetSql(sel);
            Update       upd     = Qb.Update("tab")
                                   .Values(
                Value.New("col", DateTime.Now.Ticks)
                );

            sql = factory.GetSql(upd);
        }
Example #10
0
        public void ExecuteAsyncTest()
        {
            using (AnyDbConnection con = _factory.OpenConnection())
            {
                Insert ins = Qb.Insert("customer")
                             .Values(
                    Value.New("first_name", "123"),
                    Value.New("last_name", "456")
                    );
                con.ExecuteAsync(ins).Wait();
                Assert.Equal(101, GetAll(con).Count());

                ins = Qb.Insert("customer")
                      .Values(
                    Value.New("first_name", "321"),
                    Value.New("last_name", "654")
                    );
                con.ExecuteAsync(ins).Wait();
                Assert.Equal(102, GetAll(con).Count());


                Select sel_0 = Qb.Select("*").From("customer").Page(0, 200);
                sel_0 = sel_0.OrderBy("first_name");
                var page = con.QueryAsync(sel_0).Result;
                Assert.Equal(102, page.Count());

                Update upd = Qb.Update("customer")
                             .Values(
                    Value.New("first_name", "XXX")
                    );
                int res = con.ExecuteAsync(upd).Result;
                Assert.Equal(102, res);

                Select sel = Qb.Select("*")
                             .From("customer");
                IEnumerable <Customer> en = con.QueryAsync <Customer>(sel).Result;
                Assert.Equal(102, en.Count());

                Delete del = Qb.Delete("customer");
                res = con.ExecuteAsync(del).Result;
                Assert.Equal(102, res);
            }
        }
Example #11
0
        public static string GetSqlDemans(List <string> grants, List <string> bans)
        {
            From        d         = From.Table("demand", "d", "dem");
            List <Cond> condGrant = new List <Cond>();
            List <Cond> condBan   = new List <Cond>();

            foreach (string grant in grants)
            {
                condGrant.Add(Cond.Like(Expr.Field(nameof(Demand.OkpCode), d), Expr.String(grant)));
            }
            foreach (string ban in bans)
            {
                condBan.Add(Cond.NotLike(Expr.Field(nameof(Demand.OkpCode), d), Expr.String(ban)));
            }

            Logic logicGrant = Logic.Or(condGrant.ToArray());
            Logic logicBan   = Logic.Or(condBan.ToArray());

            Select sel = Qb.Select("*")
                         .From(d);

            if (condGrant.Count == 0 && condBan.Count == 0)
            {
                return(GetSql(sel));
            }
            if (condGrant.Count != 0 && condBan.Count != 0)
            {
                sel.Where(Logic.And(logicGrant, logicBan));
                return(GetSql(sel));
            }
            if (condBan.Count == 0)
            {
                sel.Where(Logic.And(logicGrant));
                return(GetSql(sel));
            }
            sel.Where(Logic.And(logicBan));
            return(GetSql(sel));
        }
Example #12
0
        public void TestWhere()
        {
            Select sel = Qb.Select("*")
                         .From("tab")
                         .Where(
                Cond.Equal("a", 1),
                Cond.Greater("b", 2)
                );

            Renderer.ISqlOmRenderer renderer = new Renderer.SqlServerRenderer();
            string sql = renderer.RenderSelect(sel);

            Assert.Equal("select * from [tab] where (([a] = 1 and [b] > 2))", sql);

            sel = Qb.Select("*")
                  .From("tab")
                  .Where(
                Cond.NotIn(Expr.Field("a"), 1, 2),
                Cond.NotIn("a", 1, 2),
                Cond.NotIn("b", "bb", "bbb"),
                Cond.NotIn("a", DateTime.Now, DateTime.UtcNow)
                );
            sql = renderer.RenderSelect(sel);

            sel = Qb.Select("*")
                  .From("tab")
                  .Where(Logic.Or(
                             Cond.Equal("a", 1),
                             Cond.Equal("a", 2)
                             ));
            sql = renderer.RenderSelect(sel);


            sel = Qb.Select("*")
                  .From("tab")
                  .Where(
                Logic.And(
                    Cond.Equal("a", 1),
                    Cond.Greater("b", 2)
                    )
                );

            sql = renderer.RenderSelect(sel);
            Assert.Equal("select * from [tab] where (([a] = 1 and [b] > 2))", sql);

            sel = Qb.Select("*")
                  .From("tab")
                  .Where(
                Logic.Or(
                    Cond.Equal("a", 1),
                    Cond.Greater("b", 2)
                    )
                );
            sql = renderer.RenderSelect(sel);
            Assert.Equal("select * from [tab] where (([a] = 1 or [b] > 2))", sql);

            From   customer = From.Table("Customers", "c");
            From   orders   = From.Table("Orders", "o");
            Select inner    = Qb.Select(
                Column.New("FirstName", customer),
                Column.New("LastName", customer),
                Column.New("Count", "sum", orders, AggFunc.Sum)
                )
                              .From(customer)
                              .Join(JoinType.Left, customer, orders, JoinCond.Fields("Id", "CustomerId"))
                              .GroupBy("FirstName", customer)
                              .GroupBy("LastName", customer);

            From t = From.SubQuery(inner, "t");

            sel = Qb.Select(
                Column.New("FirstName", t),
                Column.New("LastName", t),
                Column.New(Expr.IfNull(Expr.Field("sum", t), 0), "total")
                )
                  .From(From.SubQuery(inner, "t"))
                  .Where(Cond.NotLike(Expr.Field("FirstName"), Expr.String("aa$")));

            sql = renderer.RenderSelect(sel);
            Renderer.PostgreSqlRenderer pg = new Renderer.PostgreSqlRenderer();
            sql = pg.RenderSelect(sel);

            List <string> grants = new List <string>()
            {
                "1.$", "2.$"
            };
            List <string> bans = new List <string>()
            {
                "3.$", "4.$"
            };

            string ss = GetSqlDemans(grants, bans);


            sel      = Qb.Select("*").From("tab").Where(Cond.Like("FirstName", "%abc%"));
            sql      = renderer.RenderSelect(sel);
            renderer = new Renderer.PostgreSqlRenderer();
            sql      = renderer.RenderSelect(sel);
        }
Example #13
0
        IEnumerable <Customer> GetAll(AnyDbConnection con)
        {
            Select sel = Qb.Select("*").From("customer");

            return(con.Query <Customer>(sel));
        }