working-with-jquery-datatables-in-asp-dot-net

05-Jan-2014 6 Comments paras

Working with tabular data in jquery could be quite a work because you have to manually handle every trivia involving editing, deleting, sorting, paging and so on. Moreover if you are a "typical" asp.net web forms developer with good experience at using server controls like gridview, the whole task of creating a table with jquery could be a nightmare.
This is where jquery datatables come to rescue; with a proper server datasource which gives json formatted data with a certain specific format,  the tabular representation of data with jquery becomes a lot easier.
To start with, lets create a website/web application project in Visual Studio and install jquery and jquery datatable via Nuget Package Manager.


For the data source, I have used an XML file, Northwind.xml file, which is the xml representation of the popular Microsoft’s sample Northwind database.
First, I created a DAL which reads the list of employees from the XML file, here I have used a bit of Linq to XML to build the list.


In the next step I created a handler that returns the formatted json data to the client side upon request. Here we need to keep in mind that datatable expects the json data wrapped in a certain format where fields like iTotalRecords, iTotalDisplayRecords are important to keep track of pagination and the data is contained in a single variable “aData” usually. However there are options to customize the names while inititializing the datatables as well.
Add a handler “datatablehandler.ashx” in your project and include the following logic.


Now we have a handler ready to provide the necessary data. Lets move to the client side and implement the datatables for real.
In the page reference the necessary js and css files, jquery, datatable js and a basic datatable css.
Then we create a table markup with the necessary columns to display the data.


To initialize datatable without any configuration it is simple, all we need to do is call $(‘selector’).dataTable(). But since we are trying to fetch data from server we have a few more things to keep in mind.


Here, the parameter “bProcessing” displays the “processing” message while we are loading the data. The parameter “bServerSide” set to true means we shall be getting data from the server everytime we search or change the page. We could do it otherwise by simply loading all the data at first and then initializing the datatable so that the data won’t be fetched from the server everytime we search or change page. However, while handling huge rows of data, this might not be quite practical which is why the server side logic is helpful. The final output would look something like this.


You can download the project from here
 

Comments
  • sohaib

    Hi. Nice sharing. Can you please guide that how to do the same work by getting data from Database, either in datatable or in List? Since IEnumerable data won't be filled in Generic List or Data Table. Thanks!

  • Sajal

    Project is not working properly due to datatablehandler.ashx will able to send or update in download zip

  • Mukhtar

    Paging, Search and navigation not working in case when there are more than 10 records

  • Manoj

    Hey, Very nice.. I have a query, Search isn't working for me in your source code. Please help

  • Rohit Patil

    Hi Paras. I really liked your logic and i have downloaded your code. But the project has one missing file datatablehandler.ashx due to which the project fails to work and gives error "datatablehandler.ashx not found"

  • Raj

    Good info