Пример #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);
        }
Пример #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);
        }
Пример #3
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);
        }