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); }
public async Task <IActionResult> WithPaging([FromBody] DynamicQueryPath model) { var res = extractDbService.ExecQuery(model); return(Json(new ResultBase <DynamicQueryResult>() { success = true, data = res })); }