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);
        }
Beispiel #2
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);
        }
Beispiel #3
0
        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"));
        }
Beispiel #4
0
        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);
        }
Beispiel #5
0
        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);
        }
Beispiel #6
0
        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);
        }
Beispiel #7
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);
        }
Beispiel #8
0
 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);
 }