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));
}
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);
}