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

Jose Daniel Navarro Brito 61 Reputation points
2023-04-05T06:43:12.46+00:00

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


     [HttpPost]
        [Route("Plots/Postdata")]
        public  async Task<ActionResult> 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 Task<ActionResult>Getdata([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<Progressreport>().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

 <script>
        $(document).ready(function () {
            bindDatatable();
        });

        function bindDatatable() {
            datatable = $('#tblStudent')
                .DataTable({
                    "sAjaxSource": "Plots/Postdata,
                    "bServerSide": true,
                    "bProcessing": true,
                    "type":"POST",
                     "datatype": "json",
                    "bSearchable": true,
                    "order": [[1, 'asc']],
                    "language": {
                        "emptyTable": "No record found.",
                        "processing":
                            '<i class="fa fa-spinner fa-spin fa-3x fa-fw" style="color:#2a2b2b;"></i><span class="sr-only">Loading...</span> '
                    },
                    "columns": [
                        {
                            "data": "plot",
                            "autoWidth": true,
                            "searchable": true
                        },
                        {
                            "data": "status",
                            "autoWidth": true,
                            "searchable": true
                        },
                        {
                            "data": "contractor",
                            "autoWidth": true,
                            "searchable": true
                        },
                        {
                            "data": "inspector",
                            "autoWidth": true,
                            "searchable": true
                        }, 
                    ]
                });
        }
    </script>

Thanks in advance

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,623 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Zhi Lv - MSFT 32,456 Reputation points Microsoft Vendor
    2023-04-06T03:21:06.77+00:00

    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<Employee, string> 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<Employee> 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";
    }
    
    <h1>Index</h1>
    <div class="row">
        <div class="col-sm-12">
            <table class="table table-bordered table-striped" id="tblStudent">
                <thead>
                    <tr>
                        <th scope="col">Name</th>
                        <th scope="col">Position</th>
                        <th scope="col">Location</th>
                        <th scope="col">Age</th>
                        <th scope="col">Start Date</th>
                        <th scope="col">Salary</th>
                    </tr>
                </thead>
            </table>
        </div>
    </div>
    
    @section Scripts{
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
        <link href="https://cdn.datatables.net/1.10.20/css/dataTables.bootstrap.min.css" rel="stylesheet" />
        <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
        <script src="https://cdn.datatables.net/1.10.20/js/dataTables.bootstrap.min.js"></script>
        <script>
            $(document).ready(function () {
                bindDatatable();
            });
    
            function bindDatatable() {
                datatable = $('#tblStudent')
                    .DataTable({
                        "sAjaxSource": "/Employee/GetData",
                        "bServerSide": true,
                        "bProcessing": true,
                        "bSearchable": true,
                        "order": [[1, 'asc']],
                        "language": {
                            "emptyTable": "No record found.",
                            "processing":
                                '<i class="fa fa-spinner fa-spin fa-3x fa-fw" style="color:#2a2b2b;"></i><span class="sr-only">Loading...</span> '
                        },
                        "columns": [
                            {
                                "data": "name",
                                "autoWidth": true,
                                "searchable": true
                            },
                            {
                                "data": "position",
                                "autoWidth": true,
                                "searchable": true
                            },
                            {
                                "data": "location",
                                "autoWidth": true,
                                "searchable": true
                            },
                            {
                                "data": "age",
                                "autoWidth": true,
                                "searchable": true
                            }, {
                                "data": "startDateString",
                                "autoWidth": true,
                                "searchable": true
                            }, {
                                "data": "salary",
                                "autoWidth": true,
                                "searchable": true
                            },
                        ]
                    });
            }
        </script>
    }
    

    The result as below: image1


    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

    1 person found this answer helpful.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.