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);
}
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);
}
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");
}
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"));
}
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 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"));
}
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());
}
}
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());
}
}
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);
}
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);
}
}
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);
}
IEnumerable <Customer> GetAll(AnyDbConnection con)
{
Select sel = Qb.Select("*").From("customer");
return(con.Query <Customer>(sel));
}