How to model binding jquery datatable request to an external ASP.NET CORE WEB API

Question

Hi: I'm struggling with: I have a Database-WEB API -Web application tier. My web application has a jquery datatable which post its request to the following method in the WEB API

 public class DataTableAjaxPostModel
    {
        public int? draw { get; set; }
        public int? start { get; set; }
        public int? length { get; set; }
        public List columns { get; set; }
        public Search search { get; set; }
        public List 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; }
    }


     [HttpPost]
        [Route("Plots/Postdata")]
        public  async Task Postdata([FromForm] DataTableAjaxPostModel model)
        {     
            using (var client = new HttpClient())
            {
                client.DefaultRequestHeaders.Clear();
                client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));            
                HttpContent body = new StringContent(JsonConvert.SerializeObject(model), Encoding.UTF8, "application/json");              
                HttpResponseMessage Res = await client.PostAsync("https://localhost:7193/api/Erven",body);
                
                if (Res.IsSuccessStatusCode)
                {
                   
                    var EmpResponse = Res.Content.ReadAsStringAsync().Result;
                   
               
                return Ok(EmpResponse);
                  }
                }
            return null;
        }

First question: Does the datatable parameters bind automatically to the DataTableAjaxPostmodel ? Second question: Is the above code correct ? It looks a bit clumsy for me ( I'm not a developer but a civil engineer who likes coding) The PostAsync of HttpResponseMessage targets the following WEB API method which is "outside"the WEB application project.

  [HttpPost]
        public async TaskGetdata([FromBody] DataTableAjaxPostModel model)
        {
            string? searchValue = model?.search?.value;
          
               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;             
               int pageSize = length != null ? Convert.ToInt32(length) : 0;
               int skip = start != null ? Convert.ToInt32(start) : 0;
               int filteredRecords = 0;
            
            var query =this._UoW.Repository().Query();

            

           if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))
            {
                query = query.OrderBy(sortColumn + " " + sortColumnDirection);
            }


            if (!string.IsNullOrEmpty(searchValue))
            {
                query = query.Where( p => p.Inspector.ToLower().Contains(searchValue.ToLower()) ||
                                     p.Projectname.ToLower().Contains(searchValue.ToLower()) ||
                                     p.Plot.ToString().ToLower().Contains(searchValue.ToLower()) ||
                                     p.Status.ToLower().Contains(searchValue.ToLower()));             
            }
            
             int recordsTotal = await query.CountAsync();

            
            var data = await query.Skip(skip).Take(pageSize).ToListAsync();


            filteredRecords =  data.Count();

          
           
            var jsonData = new { draw = draw, recordsFiltered = filteredRecords, recordsTotal = recordsTotal, data = data };

            return Ok(jsonData);

        }

Third question; Is this coding correct? Is the JSonData return right ? Lastly, the Jquery datatable script

Thanks in advance

Answer

Hi @Jose Daniel Navarro Brito

From the JQuery DataTable document, we can see that when using server-side processing and sAjaxSource, the returned object must include the parameter aaData which is the data source for the table. So, using your code, the table might be empty. To solve this issue, in the Getdata() method, you should change the returned object's property name from data to aaData.

  
        var jsonData = new { draw = draw, recordsFiltered = filteredRecords, recordsTotal = recordsTotal, aaData = data };

Besides, here is a sample about using jquery DataTable with sAjaxSource, you can refer to it. Class :

public class JqueryDatatableParam
{
    public string sEcho { get; set; }
    public string sSearch { get; set; }
    public int iDisplayLength { get; set; }
    public int iDisplayStart { get; set; }
    public int iColumns { get; set; }
    public int iSortCol_0 { get; set; }
    public string sSortDir_0 { get; set; }
    public int iSortingCols { get; set; }
    public string sColumns { get; set; }
}
public class Employee
{
    public string Name { get; set; }
    public string Position { get; set; }
    public string Location { get; set; }
    public int Age { get; set; }
    public DateTime StartDate { get; set; }
    public string StartDateString { get; set; }
    public int Salary { get; set; }
}

Employee Controller:

public class EmployeeController : Controller
{
    public IActionResult Index()
    {
        return View();
    }
    public ActionResult GetData(JqueryDatatableParam param, string iSortCol_0, string sSortDir_0)
    {
        var employees = GetEmployees();

        employees.ToList().ForEach(x => x.StartDateString = x.StartDate.ToString("dd'/'MM'/'yyyy"));

        if (!string.IsNullOrEmpty(param.sSearch))
        {
            employees = employees.Where(x => x.Name.ToLower().Contains(param.sSearch.ToLower())
                                          || x.Position.ToLower().Contains(param.sSearch.ToLower())
                                          || x.Location.ToLower().Contains(param.sSearch.ToLower())
                                          || x.Salary.ToString().Contains(param.sSearch.ToLower())
                                          || x.Age.ToString().Contains(param.sSearch.ToLower())
                                          || x.StartDate.ToString("dd'/'MM'/'yyyy").ToLower().Contains(param.sSearch.ToLower())).ToList();
        }

        var sortColumnIndex = Convert.ToInt32(iSortCol_0);
        var sortDirection = sSortDir_0;

        if (sortColumnIndex == 3)
        {
            employees = sortDirection == "asc" ? employees.OrderBy(c => c.Age) : employees.OrderByDescending(c => c.Age);
        }
        else if (sortColumnIndex == 4)
        {
            employees = sortDirection == "asc" ? employees.OrderBy(c => c.StartDate) : employees.OrderByDescending(c => c.StartDate);
        }
        else if (sortColumnIndex == 5)
        {
            employees = sortDirection == "asc" ? employees.OrderBy(c => c.Salary) : employees.OrderByDescending(c => c.Salary);
        }
        else
        {
            Func orderingFunction = e => sortColumnIndex == 0 ? e.Name :
                                                           sortColumnIndex == 1 ? e.Position :
                                                           e.Location;

            employees = sortDirection == "asc" ? employees.OrderBy(orderingFunction) : employees.OrderByDescending(orderingFunction);
        }

        var displayResult = employees.Skip(param.iDisplayStart)
            .Take(param.iDisplayLength).ToList();
        var totalRecords = employees.Count();

        return Json(new
        {
            param.sEcho,
            //iTotalRecords = totalRecords,
            //iTotalDisplayRecords = totalRecords,
            recordsFiltered = totalRecords,
            recordsTotal = totalRecords,
            aaData = displayResult
        });

    }

    private IEnumerable GetEmployees()
    {
        return Enumerable.Range(1, 30).Select(index => new Employee
        {
            Name = $"Name {index}",
            Position = $"Position {index}",
            Location = $"Location {index}",  
            StartDate = DateTime.Now.AddDays(index),
            StartDateString = DateTime.Now.AddDays(index).ToShortDateString(),
            Age = Random.Shared.Next(20, 60),
            Salary=  Random.Shared.Next(10, 30),
        });
    }
}

Index.cshtml: Note, when set the columns data (such as: name, position), the first character of the value should be lowercase.

@{
    ViewData["Title"] = "Index";
}

Index

    
        
            
                
                    Name
                    Position
                    Location
                    Age
                    Start Date
                    Salary
                
            
        
    


@section Scripts{
    
    
    
    
    
}

The result as below:

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

Share via

How to model binding jquery datatable request to an external ASP.NET CORE WEB API

1 answer