コード例 #1
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);
        }
コード例 #2
0
 public void TestJoinCond()
 {
     JoinCond j = JoinCond.Fields("a");
 }
コード例 #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);
        }