how use web api pagination, sorting and search in jquery datatable

Question

Hi:

I want to provide paging, sorting and search to a jquery datatable via a WEB API in Asp.net core. I came acrossed with the link 1 which is what I want to do with the only difference that I don't want to put an API Controller inside my Web Application. On the contrary.......I want to develop a WEB API similar like the project in link 2 that can be used by any application. including the jquery datatable.
My problem is that I don't know how to pass the parameters to the WEB API when I post the datatable data, search and sort parameters in my web application.

I have been searching for a while but I haven't found how an application, in this case, a jquery datatable consumes a WEB API during posting

link1=> https://www.freecodespot.com/blog/datatable-server-side-processing-in-asp-net-core/#VIII_Create_an_API_Controller

link2=> https://codewithmukesh.com/blog/pagination-in-aspnet-core-webapi/

Thanking you in advance.

Answer 1

if you read the datatable docs:

https://datatables.net/manual/server-side

you will see the sample code assumed only one column at a time could be sorted at a time, but the data tables supports multiple columns. so in the above code, you need to add an order by for each column in the order list, rather than assuming 1 or zero entries.

Answer 2

Hi @Jose Daniel Navarro Brito ,

link1=> https://www.freecodespot.com/blog/datatable-server-side-processing-in-asp-net-core/#VIII_Create_an_API_Controller

My problem is that I don't know how to pass the parameters to the WEB API when I post the datatable data, search and sort parameters in my web application.

From the link, we can see that when using JQuery DataTable to sort or search, it will transfer the search and sort parameters via the Request.Form. This seems like the JQuery DataTable default behavior, because in the JS script, we can't find any script to set the parameter:

So, in your web API action method, you can also use the same method to get the search and sort parameters from the Request Form.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion

Answer 3

Hi, VageJoe:

Thanks for your response, but your input doesn't help me. As previously said, my Web API and the application aren't in the same project. Let's go bit by bit:

Firstly, I have to write a method in my web application ( the usser) to post the information from the jquery datatable to my web api (the server) My understanding is that I have to bind the DataTableAjaxPostModel to the FromForm attribute to "load" the data from the datatable ...right? . The code below is incomplete as I don't know what the server

[HttpPost]
        [Route("Plots/Postdata")]
        public  async Task<JsonResult> Postdata([FromForm] DataTableAjaxPostModel model)
        {

            
            using (var client = new HttpClient())
            {
                client.DefaultRequestHeaders.Clear();
                client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
                HttpResponseMessage Res = await client.PostAsync("https://localhost:7193/api/Erven",don't know !!!);

                
                {
                    //Storing the response details recieved from web api
                    var EmpResponse = Res.Content.ReadAsStringAsync().Result;
                    //Deserializing the response recieved from web api and storing into the Employee list
                    users = JsonConvert.DeserializeObject<List<ProgressreportReadonly>>(EmpResponse);
                }


            }

            return => don't know 
        }
    }

Secondly, in the another end, my external web api will receive this request

Answer 4

I recommend using model binding in .NET Core Web API.

    public class DataTableAjaxPostModel  
    {  
        public int? draw { get; set; }  
        public int? start { get; set; }  
        public int? length { get; set; }  
        public List<Column> columns { get; set; }  
        public Search search { get; set; }  
        public List<Order> order { get; set; }  
    }  
  
    public class Column  
    {  
        public string? data { get; set; }  
        public string? name { get; set; }  
        public bool? searchable { get; set; }  
        public bool? orderable { get; set; }  
        public Search? search { get; set; }  
    }  
  
    public class Search  
    {  
        public string? value { get; set; }  
        public string? regex { get; set; }  
    }  
  
    public class Order  
    {  
        public int column { get; set; }  
        public string dir { get; set; }  
    }  

The Web API action will have the following pattern.

[HttpPost]  
public async Task<DataTableResponse> SearchPage([FromForm] DataTableAjaxPostModel model)  
{  
    return await _service.GetDocumentStore(model);  
}  

Service method pattern

public async Task<DataTableResponse> GetDocumentStore(DataTableAjaxPostModel model)  
{  
    int? draw = model.draw;  
    int? start = model.start;  
    int? length = model.length;  
    string? sortColumn = model?.columns[model.order[0].column]?.name;  
    string? sortColumnDirection = model?.order?[0].dir;  
    string? searchValue = model?.search?.value;  
    int pageSize = length != null ? Convert.ToInt32(length) : 0;  
    int skip = start != null ? Convert.ToInt32(start) : 0;  
              
    int recordsTotal = _context.FilePaths.Count();  
    int filteredRecords = 0;  
  
    IQueryable<FilePath> query = _context.FilePaths.OrderBy(sortColumn + " " + sortColumnDirection);  
  
    if (searchValue != null)  
    {  
        query = query.Where(m => m.Name.Contains(searchValue));  
    }  
        
    var results = await query.Skip(skip).Take(pageSize).ToListAsync();  
    filteredRecords = results.Count();  
  
    DataTableResponse response = new DataTableResponse()  
    {  
        draw = draw.HasValue ? draw.Value : 0,  
        recordsFiltered = searchValue == null ? recordsTotal : filteredRecords,  
        recordsTotal = recordsTotal,  
        data = results  
    };  
    return response;  
}