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);
}
public void TestJoinCond()
{
JoinCond j = JoinCond.Fields("a");
}
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);
}
