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:
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