In this article, we will learn how we can work with jQuery Datatables with server-side data. Here we are going to use an MVC application with jQuery and other required packages installed in it. If you are new to MVC, You can always get the tips/tricks/blogs about that here MVC Tips, Tricks, Blogs. jQuery Datatables is a client-side grid control that is lightweight and easy to use. But when it comes to grid control, it must be usable when it supports the server-side loading of data. This control is perfect for that. I guess, it is enough for the introduction. Now we will start using our grid. I hope you will like this.
Download the source code
You can always download the source code here:
Create a MVC application
Click File-> New-> Project then select MVC application. Before going to start the coding part, make sure that all the required extensions/references are installed. Below are the required things to start with.
- Datatables Package
You can all the items mentioned above from NuGet. Right-click on your project name and select Manage NuGet packages.
Once you have installed those items, please make sure that all the items(jQuery, Datatables JS files) are loaded in your scripts folder.
Using the code
Now let us add the needed references.
Include the references in your _Layout.cshtml
As we have already installed all the packages we need, now we need to add the references, right? After adding the reference, your _Layout.cshtml will looks like below.
Add a normal MVC controller
Now we will add a normal MVC controller in our app. Once you add that you can see an ActionResult is created for us.
public ActionResult Index()
Right-click on the controller, and click add view, that will create a View for you. Now we will change the view as follows.
So we have set the headers and footer for our grid, where we are going to load the grid control in the table myGrid. So far the UI part is done, now it is time to set up our database and entity model. Are you ready?
Create a database
The following query can be used to create a database in your SQL Server.
Now we will create a table 🙂
Create table in database
Below is the query to create table in database.
Insert data to table
To insert the data, I will attach a database script file along with the download file, you can either run that or insert some data by using the below query. By the way, if you would like to know how to generate scripts with data in SQL Server, you can check here.
Along with this, we can create a new stored procedure that will fetch the data. Following is the query to create the stored procedure.
Next thing we are going to do is creating a ADO.NET Entity Data Model.
Create Entity Data Model
Right-click on your model folder and click new, select ADO.NET Entity Data Model. Follow the steps given. Once you have done the processes, you can see the edmx file and other files in your model folder.
Now we will go back to our controller and add a new JsonResult which can be called via a new jQuery Ajax request. No worries, we will create that Ajax requests later. Once you add the Jsonresult action, I hope your controller will look like this.
Here TrialsDBEntities is our entity class. Please be noted that to use the model classes in your controller, you must add the reference as follows.
I know all you are familiar with this, I am just saying!. Now can we create a function GetSales in our model class Sales ?.
We uses normal LINQ queries here, and we take only 100 records to load for now. If you don’t want to use this method you can call our stored procedure which we have created while creating our database. You can call this as explained in the below function.
Now the only thing pending is to call our controller JsonResult action right? We will do some code in our MyScript.js file.
Here “dataSrc”: “” should be used if you have a plain JSON data. The sample data can be find below.
We have done everything!. Can we see the output now?
Have a happy coding.
Did I miss anything that you may think which is needed? Did you use jQuery Datatables in your application? Have you ever wanted to do this requirement? Could you find this post as useful? I hope you liked this article. Please share me your valuable suggestions and feedback.
Your turn. What do you think?
A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on C# Corner, Code Project, Stack Overflow, Asp.Net Forum instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I can.