ASP.NET

jQuery Datatable With Server Side Data

In this article we will learn how to work with jQuery Datatables with server side data. Here we are going to use a 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 under MVC Tips. jQuery Datatable is a client side grid control which is lightweight and easy to use. But when it comes to a 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.

You can always download the source code here:

Create a MVC application

Click File, New, then Project and 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
  • jQuery

You can add all the items mentioned above from NuGet. Right click on your project name and select Manage NuGet packages. 

Manage NuGet Package Window
                                    Figure: Manage NuGet Package Window 

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 look like below.

  1. <!DOCTYPE html>  
  2. <html>  
  3.   
  4. <head>  
  5.     <meta charset="utf-8" />  
  6.     <meta name="viewport" content="width=device-width, initial-scale=1.0">  
  7.     <title>@ViewBag.Title - My ASP.NET Application</title>  
  8.     <link href="~/Content/Site.css" rel="stylesheet" type="text/css" />  
  9.     <link href="~/Content/bootstrap.min.css" rel="stylesheet" type="text/css" />  
  10.     <link href="~/Content/DataTables/css/jquery.dataTables.min.css" rel="stylesheet" />  
  11.     <script src="~/Scripts/modernizr-2.6.2.js"></script>  
  12.     <script src="~/scripts/jquery-2.2.0.min.js"></script>  
  13.     <script src="~/scripts/jquery-ui-1.10.2.min.js"></script>  
  14.     <script src="~/scripts/DataTables/jquery.dataTables.min.js"></script>  
  15.     <script src="~/scripts/MyScripts.js"></script>  
  16.     <script src="~/Scripts/bootstrap.min.js"></script>  
  17. </head>  
  18.   
  19. <body>  
  20.     <div class="navbar navbar-inverse navbar-fixed-top">  
  21.         <div class="container">  
  22.             <div class="navbar-header">  
  23.                 <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">  
  24. <span class="icon-bar"></span>  
  25. <span class="icon-bar"></span>  
  26. <span class="icon-bar"></span>  
  27. </button> @Html.ActionLink("jQuery Datatable With Server Side Data", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })  
  28.             </div>  
  29.             <div class="navbar-collapse collapse">  
  30.                 <ul class="nav navbar-nav">  
  31.                 </ul>  
  32.             </div>  
  33.         </div>  
  34.     </div>  
  35.   
  36.     <div class="container body-content">  
  37.         @RenderBody()  
  38.         <hr />  
  39.         <footer>  
  40.             <p>© @DateTime.Now.Year - <a href="http://sibeeshpassion.com">Sibeesh Passion</a></p>  
  41.         </footer>  
  42.     </div>  
  43. </body>  
  44.   
  45. </html>  
Here MyScripts.js is the JavaScript file where we are going to write our own scripts.

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.
  1. public ActionResult Index()  
  2. {  
  3.    return View();  
  4. }  
Right click on the controller, and click add view, that will create a View for you. Now we will change the view as follows.
  1. @{  
  2.     ViewBag.Title = "jQuery Datatable With Server Side Data";  
  3. }  
  4.    
  5. <h2>jQuery Datatable With Server Side Data</h2>  
  6.    
  7. <table id="myGrid" class="table">  
  8.     <thead>  
  9.         <tr>  
  10.             <th>SalesOrderID</th>  
  11.             <th>SalesOrderDetailID</th>  
  12.             <th>CarrierTrackingNumber</th>  
  13.             <th>OrderQty</th>  
  14.             <th>ProductID</th>  
  15.             <th>UnitPrice</th>  
  16.         </tr>  
  17.     </thead>  
  18.     <tfoot>  
  19.         <tr>  
  20.             <th>SalesOrderID</th>  
  21.             <th>SalesOrderDetailID</th>  
  22.             <th>CarrierTrackingNumber</th>  
  23.             <th>OrderQty</th>  
  24.             <th>ProductID</th>  
  25.             <th>UnitPrice</th>  
  26.         </tr>  
  27.     </tfoot>  
  28. </table>  
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.
  1. USE [master]  
  2. GO  
  3.    
  4. /****** Object:  Database [TrialsDB]    Script Date: 17-Feb-16 10:21:17 PM ******/  
  5. CREATE DATABASE [TrialsDB]  
  6.  CONTAINMENT = NONE  
  7.  ON  PRIMARY  
  8. ( NAME = N'TrialsDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TrialsDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )  
  9.  LOG ON  
  10. ( NAME = N'TrialsDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TrialsDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)  
  11. GO  
  12.    
  13. ALTER DATABASE [TrialsDB] SET COMPATIBILITY_LEVEL = 110  
  14. GO  
  15.    
  16. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))  
  17. begin  
  18. EXEC [TrialsDB].[dbo].[sp_fulltext_database] @action = 'enable'  
  19. end  
  20. GO  
  21.    
  22. ALTER DATABASE [TrialsDB] SET ANSI_NULL_DEFAULT OFF  
  23. GO  
  24.    
  25. ALTER DATABASE [TrialsDB] SET ANSI_NULLS OFF  
  26. GO  
  27.    
  28. ALTER DATABASE [TrialsDB] SET ANSI_PADDING OFF  
  29. GO  
  30.    
  31. ALTER DATABASE [TrialsDB] SET ANSI_WARNINGS OFF  
  32. GO  
  33.    
  34. ALTER DATABASE [TrialsDB] SET ARITHABORT OFF  
  35. GO  
  36.    
  37. ALTER DATABASE [TrialsDB] SET AUTO_CLOSE OFF  
  38. GO  
  39.    
  40. ALTER DATABASE [TrialsDB] SET AUTO_CREATE_STATISTICS ON  
  41. GO  
  42.    
  43. ALTER DATABASE [TrialsDB] SET AUTO_SHRINK OFF  
  44. GO  
  45.    
  46. ALTER DATABASE [TrialsDB] SET AUTO_UPDATE_STATISTICS ON  
  47. GO  
  48.    
  49. ALTER DATABASE [TrialsDB] SET CURSOR_CLOSE_ON_COMMIT OFF  
  50. GO  
  51.    
  52. ALTER DATABASE [TrialsDB] SET CURSOR_DEFAULT  GLOBAL  
  53. GO  
  54.    
  55. ALTER DATABASE [TrialsDB] SET CONCAT_NULL_YIELDS_NULL OFF  
  56. GO  
  57.    
  58. ALTER DATABASE [TrialsDB] SET NUMERIC_ROUNDABORT OFF  
  59. GO  
  60.    
  61. ALTER DATABASE [TrialsDB] SET QUOTED_IDENTIFIER OFF  
  62. GO  
  63.    
  64. ALTER DATABASE [TrialsDB] SET RECURSIVE_TRIGGERS OFF  
  65. GO  
  66.    
  67. ALTER DATABASE [TrialsDB] SET  DISABLE_BROKER   
  68. GO  
  69.    
  70. ALTER DATABASE [TrialsDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF  
  71. GO  
  72.    
  73. ALTER DATABASE [TrialsDB] SET DATE_CORRELATION_OPTIMIZATION OFF  
  74. GO  
  75.    
  76. ALTER DATABASE [TrialsDB] SET TRUSTWORTHY OFF  
  77. GO  
  78.    
  79. ALTER DATABASE [TrialsDB] SET ALLOW_SNAPSHOT_ISOLATION OFF  
  80. GO  
  81.    
  82. ALTER DATABASE [TrialsDB] SET PARAMETERIZATION SIMPLE   
  83. GO  
  84.    
  85. ALTER DATABASE [TrialsDB] SET READ_COMMITTED_SNAPSHOT OFF  
  86. GO  
  87.    
  88. ALTER DATABASE [TrialsDB] SET HONOR_BROKER_PRIORITY OFF  
  89. GO  
  90.    
  91. ALTER DATABASE [TrialsDB] SET RECOVERY FULL  
  92. GO  
  93.    
  94. ALTER DATABASE [TrialsDB] SET  MULTI_USER   
  95. GO  
  96.    
  97. ALTER DATABASE [TrialsDB] SET PAGE_VERIFY CHECKSUM    
  98. GO  
  99.    
  100. ALTER DATABASE [TrialsDB] SET DB_CHAINING OFF  
  101. GO  
  102.    
  103. ALTER DATABASE [TrialsDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )   
  104. GO  
  105.    
  106. ALTER DATABASE [TrialsDB] SET TARGET_RECOVERY_TIME = 0 SECONDS   
  107. GO  
  108.    
  109. ALTER DATABASE [TrialsDB] SET  READ_WRITE   
  110. GO  
Now we will create a table.

Create table in database

Below is the query to create table in database.
  1. USE [TrialsDB]  
  2. GO  
  3.    
  4. /****** Object:  Table [dbo].[SalesOrderDetail]    Script Date: 19-Feb-16 12:30:55 PM ******/  
  5. SET ANSI_NULLS ON  
  6. GO  
  7.    
  8. SET QUOTED_IDENTIFIER ON  
  9. GO  
  10.    
  11. CREATE TABLE [dbo].[SalesOrderDetail](  
  12.     [SalesOrderID] [int] NOT NULL,  
  13.     [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,  
  14.     [CarrierTrackingNumber] [nvarchar](25) NULL,  
  15.     [OrderQty] [smallint] NOT NULL,  
  16.     [ProductID] [int] NOT NULL,  
  17.     [SpecialOfferID] [int] NOT NULL,  
  18.     [UnitPrice] [money] NOT NULL,  
  19.     [UnitPriceDiscount] [money] NOT NULL,  
  20.     [LineTotal]  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),  
  21.     [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,  
  22.     [ModifiedDate] [datetime] NOT NULL,  
  23.  CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED   
  24. (  
  25.     [SalesOrderID] ASC,  
  26.     [SalesOrderDetailID] ASC  
  27. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  28. ) ON [PRIMARY]  
  29.    
  30. GO  
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 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.
  1. USE [TrialsDB]  
  2. GO  
  3.    
  4.     INSERT INTO [dbo].[SalesOrderDetail]  
  5.            ([SalesOrderID]  
  6.            ,[CarrierTrackingNumber]  
  7.            ,[OrderQty]  
  8.            ,[ProductID]  
  9.            ,[SpecialOfferID]  
  10.            ,[UnitPrice]  
  11.            ,[UnitPriceDiscount]  
  12.            ,[rowguid]  
  13.            ,[ModifiedDate])  
  14.      VALUES  
  15.            (<SalesOrderID, int,>  
  16.            ,<CarrierTrackingNumber, nvarchar(25),>  
  17.            ,<OrderQty, smallint,>  
  18.            ,<ProductID, int,>  
  19.            ,<SpecialOfferID, int,>  
  20.            ,<UnitPrice, money,>  
  21.            ,<UnitPriceDiscount, money,>  
  22.            ,<rowguid, uniqueidentifier,>  
  23.            ,<ModifiedDate, datetime,>)  
  24. GO  
Along with this, we can create a new stored procedure which will fetch the data. The following is the query to create the stored procedure.
  1. USE [TrialsDB]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[usp_Get_SalesOrderDetail]    Script Date: 19-Feb-16 12:33:43 PM ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. -- =============================================  
  9. -- Author:      <Author,Sibeesh Venu>  
  10. -- Create date: <Create Date, 18-Feb-2016>  
  11. -- Description: <Description,To fetch SalesOrderDetail>  
  12. -- =============================================  
  13. ALTER PROCEDURE [dbo].[usp_Get_SalesOrderDetail]   
  14. AS  
  15. BEGIN  
  16.     -- SET NOCOUNT ON added to prevent extra result sets from  
  17.     -- interfering with SELECT statements.  
  18.     SET NOCOUNT ON;  
  19.    
  20.     -- Select statements for procedure here  
  21.     SELECT top(100) SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID,UnitPrice,ModifiedDate from dbo.SalesOrderDetail  
  22. END  
Next thing we are going to do is creating an 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 request later. Once you add the Jsonresult action, I hope your controller will look like this.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6. using jQuery_Datatable_With_Server_Side_Data.Models;  
  7. namespace jQuery_Datatable_With_Server_Side_Data.Controllers  
  8. {  
  9.     public class HomeController: Controller  
  10.     {  
  11.         TrialsDBEntities tdb;  
  12.         Sales sa = new Sales();  
  13.         public ActionResult Index()  
  14.         {  
  15.             return View();  
  16.         }  
  17.         public JsonResult GetGata()  
  18.         {  
  19.             try  
  20.             {  
  21.                 using(tdb = new TrialsDBEntities())  
  22.                 {  
  23.                     var myList = sa.GetSales(tdb);  
  24.                     return Json(myList, JsonRequestBehavior.AllowGet);  
  25.                 }  
  26.             }  
  27.             catch (Exception)  
  28.             {  
  29.                 throw;  
  30.             }  
  31.         }  
  32.     }  
  33. }  
Here TrialsDBEntities is our entity class. Please note that to use the model classes in your controller, you must add the reference as follows.
  1. using jQuery_Datatable_With_Server_Side_Data.Models;  
I know all of you are familiar with this, I am just saying! Now can we create a function GetSales in our model class Sales ?.
  1. public object GetSales(TrialsDBEntities tdb)  
  2. {  
  3.     try  
  4.     {  
  5.         var myList = ((from l in tdb.SalesOrderDetails select new  
  6.         {  
  7.             SalesOrderID = l.SalesOrderID,  
  8.                 SalesOrderDetailID = l.SalesOrderDetailID,  
  9.                 CarrierTrackingNumber = l.CarrierTrackingNumber,  
  10.                 OrderQty = l.OrderQty,  
  11.                 ProductID = l.ProductID,  
  12.                 UnitPrice = l.UnitPrice  
  13.         }).OrderBy(l => l.SalesOrderID)).Take(100).ToList();  
  14.         return myList;  
  15.   
  16.     }  
  17.     catch (Exception)  
  18.     {  
  19.   
  20.         throw new NotImplementedException();  
  21.     }  
  22.   
  23. }  
We use 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.
  1. public List < SalesOrderDetail > GetSalesSP(TrialsDBEntities tdb)  
  2. {  
  3.     try  
  4.     {  
  5.         var myList = tdb.Database.SqlQuery < SalesOrderDetail > ("EXEC usp_Get_SalesOrderDetail").ToList();  
  6.         return myList;  
  7.   
  8.     }  
  9.     catch (Exception)  
  10.     {  
  11.   
  12.         throw new NotImplementedException();  
  13.     }  
  14.   
  15. }  
Now the only thing pending is to call our controller JsonResult action, right? We will do some code in our MyScript.js file.
  1. $(document).ready(function() {  
  2.     $('#myGrid').DataTable({  
  3.         "ajax": {  
  4.             "url": "../Home/GetGata/",  
  5.             "dataSrc": ""  
  6.         },  
  7.         "columns": [{  
  8.             "data": "SalesOrderID"  
  9.         }, {  
  10.             "data": "SalesOrderDetailID"  
  11.         }, {  
  12.             "data": "CarrierTrackingNumber"  
  13.         }, {  
  14.             "data": "OrderQty"  
  15.         }, {  
  16.             "data": "ProductID"  
  17.         }, {  
  18.             "data": "UnitPrice"  
  19.         }]  
  20.     });  
  21. });  
Here “dataSrc”: “” should be used if you have a plain JSON data. The sample data can be seen below.
  1. [{"SalesOrderID":43659,"SalesOrderDetailID":2,"CarrierTrackingNumber":"4911-403C-98","OrderQty":1,"ProductID":776,"UnitPrice":2024.994},{"SalesOrderID":43659,"SalesOrderDetailID":3,"CarrierTrackingNumber":"4911-403C-98","OrderQty":3,"ProductID":777,"UnitPrice":2024.994},{"SalesOrderID":43659,"SalesOrderDetailID":4,"CarrierTrackingNumber":"4911-403C-98","OrderQty":1,"ProductID":778,"UnitPrice":2024.994},{"SalesOrderID":43659,"SalesOrderDetailID":5,"CarrierTrackingNumber":"4911-403C-98","OrderQty":1,"ProductID":771,"UnitPrice":2039.994},{"SalesOrderID":43659,"SalesOrderDetailID":6,"CarrierTrackingNumber":"4911-403C-98","OrderQty":1,"ProductID":772,"UnitPrice":2039.994},{"SalesOrderID":43659,"SalesOrderDetailID":7,"CarrierTrackingNumber":"4911-403C-98","OrderQty":2,"ProductID":773,"UnitPrice":2039.994},{"SalesOrderID":43659,"SalesOrderDetailID":8,"CarrierTrackingNumber":"4911-403C-98","OrderQty":1,"ProductID":774,"UnitPrice":2039.994},{"SalesOrderID":43659,"SalesOrderDetailID":9,"CarrierTrackingNumber":"4911-403C-98","OrderQty":3,"ProductID":714,"UnitPrice":28.8404},{"SalesOrderID":43659,"SalesOrderDetailID":10,"CarrierTrackingNumber":"4911-403C-98","OrderQty":1,"ProductID":716,"UnitPrice":28.8404}]  
We have done everything!. Can we see the output now?

Output

jQuery Datatable With Server Side Data
                                                   Figure: jQuery Datatable With Server Side Data

jQuery Datatable With Server Side Data Search
                                          Figure: jQuery Datatable With Server Side Data Search
Please see this article in my blog here 
 
Conclusion 

Did I miss anything that you may think is needed? Did you use jQuery Datatables in your application? Have you ever wanted to do this requirement? Did you find this post useful? I hope you liked this article. Please sharewith  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.
 
Read more articles on ASP.NET:

 

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs