Create a new .NET Core application with Identity Framework or take any existing .NET Core application in which Entity Framework is configured.
I will be create a new .NET Core application with Identity Framework as ServerSideDataTableInNetCore
Create a folder named POCO in Data folder and add a new file Employee.cs in it.
ATCSMART
public class Employee
{
`Key`
public int Id { get; set; }
public string Name { get; set; }
public string FirstSurname { get; set; }
public string SecondSurname { get; set; }
public string Street { get; set; }
public string Phone { get; set; }
public string ZipCode { get; set; }
public string Country { get; set; }
public string Notes { get; set; }
}
Open the ApplicationDbContext.cs file and add reference in it.
public class ApplicationDbContext : IdentityDbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
public DbSet<Employee> Employees { get; set; }
}
Open the Package Manager console and upgrade the database.
Create a new file called DatatableModels.cs file in Models folder and add following classes in it. This are basically used as parameter when DataTable sends the request on server.
public class DtParameters
{
public int Draw { get; set; }
public DtColumn`` Columns { get; set; }
public DtOrder`` Order { get; set; }
public int Start { get; set; }
public int Length { get; set; }
public DtSearch Search { get; set; }
public string SortOrder => Columns != null && Order != null && Order.Length > 0 ? (Columns`Order`0`.Column`.Data + (Order`0`.Dir == DtOrderDir.Desc ? " " + Order`0`.Dir : string.Empty)) : null;
public IEnumerable<string> AdditionalValues { get; set; }
}
public class DtColumn
{
public string Data { get; set; }
public string Name { get; set; }
public bool Searchable { get; set; }
public bool Orderable { get; set; }
public DtSearch Search { get; set; }
}
public class DtOrder
{
public int Column { get; set; }
public DtOrderDir Dir { get; set; }
}
public enum DtOrderDir
{
Asc,
Desc
}
public class DtSearch
{
public string Value { get; set; }
public bool Regex { get; set; }
}
//Create a new folder as Extension in project root directory and add following class as //LinqExtensions.cs in it. We will be using it for sorting the data dynamically.
public static class LinqExtensions
{
public static IQueryable<T> OrderByDynamic<T>(this IQueryable<T> query, string orderByMember, DtOrderDir ascendingDirection)
{
var param = Expression.Parameter(typeof(T), "c");
var body = orderByMember.Split('.').Aggregate<string, Expression>(param, Expression.PropertyOrField);
var queryable = ascendingDirection == DtOrderDir.Asc ?
(IOrderedQueryable<T>)Queryable.OrderBy(query.AsQueryable(), (dynamic)Expression.Lambda(body, param)) :
(IOrderedQueryable<T>)Queryable.OrderByDescending(query.AsQueryable(), (dynamic)Expression.Lambda(body, param));
return queryable;
}
}
Now, open the HomeController and add following code in it.
public class HomeController : Controller
{
private readonly ApplicationDbContext _context;
public HomeController(ApplicationDbContext context)
{
_context = context;
}
public async Task<IActionResult> Index()
{
await SeedData();
return View();
}
`HttpPost`
public async Task<IActionResult> LoadTable(`FromBody`DtParameters dtParameters)
{
var searchBy = dtParameters.Search?.Value;
var orderCriteria = string.Empty;
var orderAscendingDirection = true;
if (dtParameters.Order != null)
{
orderCriteria = dtParameters.Columns`dtParameters.Order`0`.Column`.Data;
orderAscendingDirection = dtParameters.Order`0`.Dir.ToString().ToLower() == "asc";
}
else
{
orderCriteria = "Id";
orderAscendingDirection = true;
}
var result = await _context.Employees.ToListAsync();
if (!string.IsNullOrEmpty(searchBy))
{
result = result.Where(r => r.Name != null && r.Name.ToUpper().Contains(searchBy.ToUpper()) ||
r.FirstSurname != null && r.FirstSurname.ToUpper().Contains(searchBy.ToUpper()) ||
r.SecondSurname != null && r.SecondSurname.ToUpper().Contains(searchBy.ToUpper()) ||
r.Street != null && r.Street.ToUpper().Contains(searchBy.ToUpper()) ||
r.Phone != null && r.Phone.ToUpper().Contains(searchBy.ToUpper()) ||
r.ZipCode != null && r.ZipCode.ToUpper().Contains(searchBy.ToUpper()) ||
r.Country != null && r.Country.ToUpper().Contains(searchBy.ToUpper()) ||
r.Notes != null && r.Notes.ToUpper().Contains(searchBy.ToUpper()))
.ToList();
}
result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Asc).ToList() : result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Desc).ToList();
var filteredResultsCount = result.Count();
var totalResultsCount = await _context.Employees.CountAsync();
return Json(new
{
draw = dtParameters.Draw,
recordsTotal = totalResultsCount,
recordsFiltered = filteredResultsCount,
data = result
.Skip(dtParameters.Start)
.Take(dtParameters.Length)
.ToList()
});
}
public async Task SeedData()
{
if (!_context.Employees.Any())
{
for (var i = 0; i < 1000; i++)
{
await _context.Employees.AddAsync(new Employee
{
Name = $"TestName{i}",
FirstSurname = $"TestFirstSurname{i}",
SecondSurname = $"TestSecondSurname{i}",
Street = $"TestStreet{i}",
Phone = $"TestPhone{i}",
ZipCode = $"TestZipCode{i}",
Country = $"TesCountry{i}",
Notes = $"TestNotes{i}"
});
}
await _context.SaveChangesAsync();
}
}
}
We will be adding dummy data it Database if no data exist in the table.
Now, create a new file as app.js in js folder in wwwroot directory and add following code in it.
$(document).ready(function () {
$("#test-registers").DataTable({
autoWidth: true,
processing: true,
serverSide: true,
paging: true,
searching: { regex: true },
ajax: {
url: "/Home/LoadTable",
type: "POST",
contentType: "application/json",
dataType: "json",
data: function (data) {
return JSON.stringify(data);
}
},
columns: `
{ data: "name" },
{ data: "firstSurname" },
{ data: "secondSurname" },
{ data: "street" },
{ data: "phone" },
{ data: "zipCode" },
{ data: "country" },
{
data: "notes",
render: function (data, type, row) {
return `<span style="color: green;">${data}</span>`;
}
}
`
});
});
We will also customizing the last column as to see how it works with customization.
Open the Index.cshtml file from Home folder in Views and add the following code in it.
@model IEnumerable<ServerSideDataTableInNetCore.Data.POCO.Employee>
@{
ViewData`"Title"` = "Index";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<div class="row">
<div class="col-md-12">
<table id="test-registers" class="table">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstSurname)
</th>
<th>
@Html.DisplayNameFor(model => model.SecondSurname)
</th>
<th>
@Html.DisplayNameFor(model => model.Street)
</th>
<th>
@Html.DisplayNameFor(model => model.Phone)
</th>
<th>
@Html.DisplayNameFor(model => model.ZipCode)
</th>
<th>
@Html.DisplayNameFor(model => model.Country)
</th>
<th>
@Html.DisplayNameFor(model => model.Notes)
</th>
</tr>
</thead>
</table>
</div>
</div>
@section Styles{
<link rel="stylesheet" href="~/lib/datatables/css/dataTables.bootstrap4.min.css" asp-append-version="true" />
}
@section Scripts{
<script src="/images/blog/ae3f72f3ce1111f56d82c6f00686258.jpg" asp-append-version="true"></script>
<script src="/images/blog/71266c78f3b6c47d1111ce177d71ec8.jpg" asp-append-version="true"></script>
<script src="/images/blog/0364f57fbff2fabbe941ed20c328ef1.jpg" asp-append-version="true"></script>
}
You can get the DataTable libraries from my GitHub account. I’ll be providing the source code at last.
Open the _Layout.cshtml file and add following replace it with following HTML.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>@ViewData`"Title"` - ServerSideDataTableInNetCore</title>
<environment include="Development">
<link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.css" />
</environment>
<environment exclude="Development">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
asp-fallback-href="~/lib/bootstrap/dist/css/bootstrap.min.css"
asp-fallback-test-class="sr-only" asp-fallback-test-property="position" asp-fallback-test-value="absolute"
crossorigin="anonymous"
integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" />
</environment>
<link rel="stylesheet" href="~/css/site.css" />
@RenderSection("Styles", required: false)
</head>
<body>
<header>
<nav class="navbar navbar-expand-sm navbar-toggleable-sm navbar-light bg-white border-bottom box-shadow mb-3">
<div class="container">
<a class="navbar-brand" asp-area="" asp-controller="Home" asp-action="Index">ServerSideDataTableInNetCore</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target=".navbar-collapse" aria-controls="navbarSupportedContent"
aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="navbar-collapse collapse d-sm-inline-flex flex-sm-row-reverse">
<partial name="_LoginPartial" />
<ul class="navbar-nav flex-grow-1">
<li class="nav-item">
<a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Index">Home</a>
</li>
</ul>
</div>
</div>
</nav>
</header>
<div class="container">
<partial name="_CookieConsentPartial" />
<main role="main" class="pb-3">
@RenderBody()
</main>
</div>
<footer class="border-top footer text-muted">
<div class="container">
© 2020 - ServerSideDataTableInNetCore
</div>
</footer>
<environment include="Development">
<script src="/images/blog/642dbfc47727a4dc6ad66ff87a6e074.jpg"></script>
<script src="/images/blog/4033a28e2ce91066840378763993abc.jpg"></script>
</environment>
<environment exclude="Development">
<script src="/images/blog/7fbbf5c44b32b33abe39f384f059433.jpg"
asp-fallback-src="/images/blog/7fbbf5c44b32b33abe39f384f059433.jpg"
asp-fallback-test="window.jQuery"
crossorigin="anonymous"
integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8=">
</script>
<script src="/images/blog/2c6b80e100220f69134f1cedf3c2f16.jpg"
asp-fallback-src="/images/blog/2c6b80e100220f69134f1cedf3c2f16.jpg"
asp-fallback-test="window.jQuery && window.jQuery.fn && window.jQuery.fn.modal"
crossorigin="anonymous"
integrity="sha384-xrRywqdh3PHs8keKZN+8zzc5TX0GRTLCcmivcbNJWm2rs5C8PRhcEn3czEjhAO9o">
</script>
</environment>
<script src="/images/blog/4be1713d825f5a80ebf1f2ea3efd7a4.jpg" asp-append-version="true"></script>
@RenderSection("Scripts", required: false)
</body>
</html>
You can find the source code from my GitHub account from here.
You can also check the Server side pagination in ASP.NET MVC 5 from here.