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
}));
}