Exemplo n.º 1
0
        public DynamicQueryResult ExecQuery(DynamicQueryPath model)
        {
            var query       = "SELECT ";
            var pagingQuery = "";

            if (model.PageIndex.HasValue && model.PageSize.HasValue)
            {
                query      += "totalCount = COUNT(*) OVER(),";
                pagingQuery = " OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY";
                model.Paramaters.Add(new ParamInfo()
                {
                    Name = "@PageSize", Value = model.PageSize
                });
                model.Paramaters.Add(new ParamInfo()
                {
                    Name = "@PageNumber", Value = model.PageIndex
                });
            }
            query += model.Select;
            if (model.OrderPaths.Count > 0)
            {
                var order = " Order By " + model.OrderPaths[0].Name + " " + (model.OrderPaths[0].Order == OrderType.ASC ? "ASC " : "DESC ");
                for (int i = 1; i < model.OrderPaths.Count; i++)
                {
                    order += " AND " + model.OrderPaths[i].Name + " " + (model.OrderPaths[i].Order == OrderType.ASC ? "ASC " : "DESC ");
                }
                query += order;
            }
            else
            {
                query += " Order By Id desc ";
            }
            if (!string.IsNullOrWhiteSpace(model.WhereCondition))
            {
                query += (" where " + model.WhereCondition);
            }
            query += pagingQuery;
            var res    = ExecQuery(query, model.Paramaters);
            var result = new DynamicQueryResult()
            {
                List   = res,
                Paging = new Framework.Data.PagingInfo(model.PageSize.HasValue ? model.PageSize.Value : 0, model.PageIndex.HasValue ? model.PageIndex.Value : 1)
            };

            if (!string.IsNullOrWhiteSpace(pagingQuery))
            {
                if (res.Rows.Count == 0)
                {
                    result.Paging.RowsCount = 0;
                }
                else
                {
                    result.Paging.RowsCount = Convert.ToInt32(res.Rows[0]["totalCount"]);
                }
            }
            return(result);
        }
Exemplo n.º 2
0
        public async Task <IActionResult> WithPaging([FromBody] DynamicQueryPath model)
        {
            var res = extractDbService.ExecQuery(model);

            return(Json(new ResultBase <DynamicQueryResult>()
            {
                success = true, data = res
            }));
        }