public IEnumerable <DocumentViewModel> FilterFilesByGuid(string currentSid)
{
_logger.LogDebug($"Return files from database by current sid");
Select select = Qb.Select(nameof(Document.path), nameof(Document.upload_time_stamp), nameof(Document.short_name), nameof(Document.picture), nameof(Document.doc_id))
.From(Tables.Document)
.Where(
Cond.Equal(nameof(Document.user_sid), currentSid),
Cond.Equal(nameof(Document.type), Convert.ToByte(IO.FileExtension.Extensions.Pdf))
);
IEnumerable <DocumentViewModel> documents;
try
{
using (AnyDbConnection connection = _factory.OpenConnection())
{
return(documents = connection.Query <DocumentViewModel>(select));
}
}
catch (Exception ex)
{
_logger.LogError(ex.Message);
}
_logger.LogDebug($"FileDbService.FilterFilesByGuid....OK");
return(null);
}
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 TestDelete()
{
Delete del = Qb.Delete("Customers")
.Where(Cond.Equal("Id", 20));
Renderer.SqlServerRenderer renderer = new Renderer.SqlServerRenderer();
string sql = renderer.RenderDelete(del);
del = Qb.Delete("Customers", "nsi")
.Where(Cond.Equal("Id", 20));
sql = renderer.RenderDelete(del);
}
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 TestUpdate()
{
Update upd = Qb.Update("Customers")
.Values(
Value.New("LastName", "Pavlov")
)
.Where(Cond.Equal("FirstName", "Pavel"));
Renderer.SqlServerRenderer renderer = new Renderer.SqlServerRenderer();
string sql = renderer.RenderUpdate(upd);
upd = Qb.Update("Customers", "rem")
.Values(
Value.New("LastName", "Pavlov")
)
.Where(Cond.Equal("FirstName", "Pavel"));
sql = renderer.RenderUpdate(upd);
}
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);
}
public void TestLogic()
{
Logic l0 = Logic.And(Cond.Equal("a", 1), Cond.Greater("b", 1));
Logic l1 = Logic.And(Cond.Equal("a", 1), Cond.Greater("b", 1));
Logic l2 = Logic.And(l0, l1);
}