This repository is a Server Side processor for Jquery DataTables with Asp.Net Core as backend. It provides a quick way to implement dynamic multiple column searching and sorting along with pagination and excel export at the server side. This can be done by decorating your model properties with simple attributes.
Demo Implementation Project URL - Free Download
Note: This tutorial contains example for both AJAX GET and AJAX POST Server Side Configuration.
Warning: If we are RESTful strict, we should use GET Method to get information not POST but I prefer this way to avoid limitations related to form data through the query string, so up to you if you want to use GET. I recommend using AJAX GET only if your DataTable has very less number of columns. As Jquery DataTables AJAX requests produces too large query string which will be rejected by server.
Well... there are lots of different approaches how to get a Jquery DataTables with Asp.Net Core app running. I thought it would be nice for .NET devs to use the ASP.NET Core backend and just decorate the model properties with a pretty simple attributes called [Searchable] and [Sortable]. [DisplayName(“”)] as the name suggests, can be used to change the column name in excel export or display name in the table HTML. I just combine ASP.NET Core & Jquery DataTables for easy server side processing.
[Searchable][SearchableString][SearchableDateTime][SearchableShort][SearchableInt][SearchableLong][SearchableDecimal][SearchableDouble]
[Sortable][Sortable(Default = true)]
[DisplayName(“”)]
The following chart describes the operator compatibility with data types with green as compatible and red as not compatible.
To activate and make Jquery DataTable communicate with asp.net core backend,
PM> Install-Package JqueryDataTables.ServerSide.AspNetCoreWeb> dotnet add package JqueryDataTables.ServerSide.AspNetCoreWebpublic void ConfigureServices(IServiceCollection services)
{
services.AddMvc();
services.AddSession();
services.AddJqueryDataTables();
services.AddAutoMapper(typeof(Startup));
}Please note: services.AddSession() is is required only if you're using excel export functionality in Jquery DataTables.
Add a JqueryDataTables TagHelper reference to your _ViewImports.cshtml file as shown below
@addTagHelper *, JqueryDataTables.ServerSide.AspNetCoreWebI have written
<jquery-datatables>TagHelper to do the heavy lifting works for you.
<jquery-datatables id="fingers10"
class="table table-sm table-dark table-bordered table-hover"
model="@Model"
thead-class="text-center"
enable-searching="true"
search-row-th-class="p-0"
search-input-class="form-control form-control-sm">
</jquery-datatables>id- to add id to the html tableclass- to apply the given css class to the html tablemodel- view model with properties to generate columns for html tablethead-class- to apply the given css class to the<thead>in html tableenable-searching-trueto add search inputs to the<thead>andfalseto remove search inputs from the<thead>search-row-th-class- to apply the given css class to the search inputs row of the<thead>in the html tablesearch-input-class- to apply the given css class to the search input controls added in each column inside<thead>
Add the following code to initialize DataTable. Don't miss to add
orderCellsTop : true. This makes sure to add sorting functionality to the first row in the table header. For other properties refer Jquery DataTables official documentation.
Use
AdditionalValuesto pass extra parameters if required to the server for processing. Configure Column properties and add the required search operator in thenameproperty to perform search based on the operator in thenameproperty. If name property isnullorstring.Empty, the search default's toEqualssearch operation.
Please note: Search Operator must be one among the following eq | co | gt | gte | lt | lte based on the above compatibility chart.
var table = $('#fingers10').DataTable({
language: {
processing: "Loading Data...",
zeroRecords: "No matching records found"
},
processing: true,
serverSide: true,
orderCellsTop: true,
autoWidth: true,
deferRender: true,
lengthMenu: [5, 10, 15, 20],
dom: '<"row"<"col-sm-12 col-md-6"B><"col-sm-12 col-md-6 text-right"l>><"row"<"col-sm-12"tr>><"row"<"col-sm-12 col-md-5"i><"col-sm-12 col-md-7"p>>',
buttons: [
{
text: 'Export to Excel',
className: 'btn btn-sm btn-dark',
action: function (e, dt, node, config) {
window.location.href = "/Home/GetExcel";
},
init: function (api, node, config) {
$(node).removeClass('dt-button');
}
}
],
ajax: {
type: "POST",
url: '/Home/LoadTable/',
contentType: "application/json; charset=utf-8",
async: true,
headers: {
"XSRF-TOKEN": document.querySelector('[name="__RequestVerificationToken"]').value
},
data: function (data) {
let additionalValues = [];
additionalValues[0] = "Additional Parameters 1";
additionalValues[1] = "Additional Parameters 2";
data.AdditionalValues = additionalValues;
return JSON.stringify(data);
}
},
columns: [
{
data: "Id",
name: "eq",
visible: false,
searchable: false
},
{
data: "Name",
name: "eq"
},
{
data: "Position",
name: "co"
},
{
data: "Office",
name: "eq"
},
{
data: "Extn",
name: "eq"
},
{
data: "StartDate",
render: function (data, type, row) {
return window.moment(data).format("DD/MM/YYYY");
},
name: "gt"
},
{
data: "Salary",
name: "lte"
}
]
});For AJAX GET configuration, simply change the ajax and buttons options as follows,
buttons: [
{
text: 'Export to Excel',
className: 'btn btn-sm btn-dark',
action: function (e, dt, node, config) {
var data = table.ajax.params();
var x = JSON.stringify(data, null, 4);
window.location.href = "/Home/GetExcel?" + $.param(data);
},
init: function (api, node, config) {
$(node).removeClass('dt-button');
}
}
],
ajax: {
url: '/Home/LoadTable/',
data: function (data) {
return $.extend({}, data, {
"additionalValues[0]": "Additional Parameters 1",
"additionalValues[1]": "Additional Parameters 2"
});
}
}Add the following script to trigger search only onpress of Enter Key.
table.columns().every(function (index) {
$('#fingers10 thead tr:last th:eq(' + index + ') input')
.on('keyup',
function (e) {
if (e.keyCode === 13) {
table.column($(this).parent().index() + ':visible').search(this.value).draw();
}
});
});Decorate the properties based on their data types
public class Demo
{
public int Id { get; set; }
[SearchableString]
[Sortable(Default = true)]
public string Name { get; set; }
[SearchableString]
[Sortable]
public string Position { get; set; }
[SearchableString]
[Sortable]
public string Office { get; set; }
[SearchableInt]
[Sortable]
[DisplayName("Extension")]
public int Extn { get; set; }
[SearchableDateTime]
[Sortable]
[DisplayName("Start Date")]
public DateTime StartDate { get; set; }
[SearchableLong]
[Sortable]
public long Salary { get; set; }
}On DataTable's AJAX Request,
JqueryDataTablesParameterswill read the DataTable's state andJqueryDataTablesResult<T>will acceptIEnumerable<T>response data to be returned back to table asJsonResult.
[HttpPost]
public async Task<IActionResult> LoadTable([FromBody]JqueryDataTablesParameters param)
{
try
{
// `param` is stored in session to be used for excel export. This is required only for AJAX POST.
// Below session storage line can be removed if you're not using excel export functionality.
HttpContext.Session.SetString(nameof(JqueryDataTablesParameters),JsonConvert.SerializeObject(param));
var results = await _demoService.GetDataAsync(param);
return new JsonResult(new JqueryDataTablesResult<Demo> {
Draw = param.Draw,
Data = results.Items,
RecordsFiltered = results.TotalSize,
RecordsTotal = results.TotalSize
});
} catch(Exception e)
{
Console.Write(e.Message);
return new JsonResult(new { error = "Internal Server Error" });
}
}public async Task<IActionResult> OnPostLoadTableAsync([FromBody]JqueryDataTablesParameters param)
{
try
{
// `param` is stored in session to be used for excel export. This is required only for AJAX POST.
// Below session storage line can be removed if you're not using excel export functionality.
HttpContext.Session.SetString(nameof(JqueryDataTablesParameters),JsonConvert.SerializeObject(param));
var results = await _demoService.GetDataAsync(param);
return new JsonResult(new JqueryDataTablesResult<Demo> {
Draw = param.Draw,
Data = results.Items,
RecordsFiltered = results.TotalSize,
RecordsTotal = results.TotalSize
});
} catch(Exception e)
{
Console.Write(e.Message);
return new JsonResult(new { error = "Internal Server Error" });
}
}public async Task<IActionResult> LoadTable(JqueryDataTablesParameters param)
{
try
{
var results = await _demoService.GetDataAsync(param);
return new JsonResult(new JqueryDataTablesResult<Demo> {
Draw = param.Draw,
Data = results.Items,
RecordsFiltered = results.TotalSize,
RecordsTotal = results.TotalSize
});
} catch(Exception e)
{
Console.Write(e.Message);
return new JsonResult(new { error = "Internal Server Error" });
}
}public async Task<IActionResult> OnGetLoadTableAsync(JqueryDataTablesParameters param)
{
try
{
var results = await _demoService.GetDataAsync(param);
return new JsonResult(new JqueryDataTablesResult<Demo> {
Draw = param.Draw,
Data = results.Items,
RecordsFiltered = results.TotalSize,
RecordsTotal = results.TotalSize
});
} catch(Exception e)
{
Console.Write(e.Message);
return new JsonResult(new { error = "Internal Server Error" });
}
}Inject Automapper
IConfigurationProviderto make use ofProjectTo<T>before returning the data. Inside the Data Access Method, createIQueryable<TEntity>to hold the query. Now, to perform dynamic multiple column searching create a instance of Search Processornew SearchOptionsProcessor<T,TEntity>()and call theApply()function with query and table columns as parameters. Again for dynamic multiple column sorting, create a instance of Sort Processornew SortOptionsProcessor<T,TEntity>()and call theApply()function with query and table as parameters. To implement pagination, make use ofStartandLengthfrom table parameter and return the result asJqueryDataTablesPagedResults.
public class DefaultDemoService:IDemoService
{
private readonly Fingers10DbContext _context;
private readonly IConfigurationProvider _mappingConfiguration;
public DefaultDemoService(Fingers10DbContext context,IConfigurationProvider mappingConfiguration)
{
_context = context;
_mappingConfiguration = mappingConfiguration;
}
public async Task<JqueryDataTablesPagedResults<Demo>> GetDataAsync(JqueryDataTablesParameters table)
{
IQueryable<DemoEntity> query = _context.Demos;
query = new SearchOptionsProcessor<Demo,DemoEntity>().Apply(query,table.Columns);
query = new SortOptionsProcessor<Demo,DemoEntity>().Apply(query,table);
var size = await query.CountAsync();
var items = await query
.AsNoTracking()
.Skip((table.Start / table.Length) * table.Length)
.Take(table.Length)
.ProjectTo<Demo>(_mappingConfiguration)
.ToArrayAsync();
return new JqueryDataTablesPagedResults<Demo> {
Items = items,
TotalSize = size
};
}
}Please note: If you are having DataAccessLogic in a separate project, the create instance of SearchOptionsProcessor and SortOptionsProcessor inside ActionMethod/Handler and pass it as a parameter to Data Access Logic.
To exporting the filtered and sorted data as an excel file, add GetExcel action method in your controller as shown below. Return the data as JqueryDataTablesExcelResult<T> by passing filtered/ordered data, excel sheet name and excel file name. JqueryDataTablesExcelResult Action Result that I have added in the Nuget package. This will take care of converting your data as excel file and return it back to browser.
If you want all the results in excel export without pagination, then please write a separate service method to retrive data without using
Take()andSkip()
public async Task<IActionResult> GetExcel()
{
// Here we will be getting the param that we have stored in the session in server side action method/page handler
// and deserialize it to get the required data.
var param = HttpContext.Session.GetString(nameof(JqueryDataTablesParameters));
var results = await _demoService.GetDataAsync(JsonConvert.DeserializeObject<JqueryDataTablesParameters>(param));
return new JqueryDataTablesExcelResult<Demo>(results.Items,"Demo Sheet Name","Fingers10");
}public async Task<IActionResult> OnGetExcelAsync()
{
// Here we will be getting the param that we have stored in the session in server side action method/page handler
// and deserialize it to get the required data.
var param = HttpContext.Session.GetString(nameof(JqueryDataTablesParameters));
var results = await _demoService.GetDataAsync(JsonConvert.DeserializeObject<JqueryDataTablesParameters>(param));
return new JqueryDataTablesExcelResult<Demo>(results.Items,"Demo Sheet Name","Fingers10");
}public async Task<IActionResult> GetExcel(JqueryDataTablesParameters param)
{
var results = await _demoService.GetDataAsync(param);
return new JqueryDataTablesExcelResult<Demo>(results.Items,"Demo Sheet Name","Fingers10");
}public async Task<IActionResult> OnGetExcelAsync(JqueryDataTablesParameters param)
{
var results = await _demoService.GetDataAsync(param);
return new JqueryDataTablesExcelResult<Demo>(results.Items,"Demo Sheet Name","Fingers10");
}Please note: GetExcel ActionMethod/Handler name must match the name you define in the excel export action click in your Jquery DataTable Initialization script.
JqueryDataTablesServerSide is actively under development and I plan to have even more useful features implemented soon, including:
- Complex Search/Sort
Get in touch if there are any features you feel JqueryDataTablesServerSide needs.
- Asp.Net Core 2.2
- Visual Studio Community 2017
- ClosedXML (0.94.2) - For Generating Excel Files
- Microsoft.AspNetCore.Mvc (2.2.0) - For using MVC stuffs
- Abdul Rahman - Software Developer - from India. Software Consultant, Architect, Freelance Lecturer/Developer and Web Geek.
Feel free to submit a pull request if you can add additional functionality or find any bugs (to see a list of active issues), visit the Issues section. Please make sure all commits are properly documented.
JqueryDataTablesServerSide is release under the MIT license. You are free to use, modify and distribute this software, as long as the copyright header is left intact (specifically the comment block which starts with /*!.
Enjoy!
I'm happy to help you with my Nuget Package. Support this project by becoming a sponsor/backer. Your logo will show up here with a link to your website. Sponsor/Back via
