Post
Ask Question

Display Data In ASP.NET Using jQuery DataTables Plugin

This article gives a walk-through of jQuery Datatables plugin to display the data stored in database using ASP.NET web services. 
 
Below is the screenshot of the final output that we want to achieve at the end of this tutorial.

So, the above is a table that allows almost all functionalities that a user wants to view data, filter data, sort data etc. This is achieved using jQuery datatables plugin.

Step 1

First we need to create some sample data in our SQL database. Below is the script that I have used to generate test data. You can use the following script or create your own data.

  1. Create Database Test  
  2.    
  3. Use Test  
  4.    
  5. Create Table Students   
  6. (  
  7. iD int primary key not null identity(1,1),  
  8. firstName nvarchar(MAX) not null,  
  9. lastName nvarchar(MAX) not null,  
  10. feesPaid int not null,  
  11. gender nvarchar(MAX) not null,  
  12. emailId nvarchar(MAX) not null,  
  13. telephoneNumber nvarchar(MAX) not null,  
  14. dateOfBirth date not null,  
  15. isActive bit not null,  
  16. creationDate datetime not null,  
  17. lastModifiedDate datetime not null  
  18. )  
  19. GO  
  20.    
  21. INSERT INTO Students VALUES ('Ankit','Bansal',45000, 'Male', '[email protected]','2564210000','20/May/1993',1,GETDATE(),GETDATE())  
  22. GO  
  23. INSERT INTO Students VALUES ('Raj','Kumar',25000, 'Male', '[email protected]','25400123000','20/June/1993',1,GETDATE(),GETDATE())  
  24. GO  
  25. INSERT INTO Students VALUES ('Badrish','Joshi',32000, 'Male', '[email protected]','10213330120','25/May/1989',1,GETDATE(),GETDATE())  
  26. GO  
  27. INSERT INTO Students VALUES ('Rajesh','Negi',56000, 'Male', '[email protected]','89546332021','18/July/1978',1,GETDATE(),GETDATE())  
  28. GO  
  29. INSERT INTO Students VALUES ('Rajkishor','Singh',46000, 'Male', '[email protected]','8546795555','11/August/1985',1,GETDATE(),GETDATE())  
  30. GO  
  31. INSERT INTO Students VALUES ('Neha','Arora',98652, 'Female', '[email protected]','96542231000','02/May/1996',1,GETDATE(),GETDATE())  
  32. GO  
  33. INSERT INTO Students VALUES ('Sanjay','Sharma',65421, 'Male', '[email protected]','99876653222','25/Jan/1998',1,GETDATE(),GETDATE())  
  34. GO  
  35. INSERT INTO Students VALUES ('Glenn','Block',65210, 'Male', '[email protected]','54210000021','27/Feb/1990',1,GETDATE(),GETDATE())  
  36. GO  
  37. INSERT INTO Students VALUES ('Sara','Silver',78900, 'Female', '[email protected]','89778930099','10/Sep/1989',1,GETDATE(),GETDATE())  
  38. GO  
  39. INSERT INTO Students VALUES ('Lucy','Bane',23444, 'Female', '[email protected]','2564210000','20/May/1993',1,GETDATE(),GETDATE())  
  40. GO  
  41. INSERT INTO Students VALUES ('Suresh','Goswami',65321, 'Male', '[email protected]','9876576489','21/Nov/1981',1,GETDATE(),GETDATE())  
  42. GO  
  43. INSERT INTO Students VALUES ('Dinesh','Gola',76544, 'Male', '[email protected]','9899785412','15/March/1984',1,GETDATE(),GETDATE())  
  44. GO  
  45. INSERT INTO Students VALUES ('Shivansh','Manaktala',87555, 'Male', '[email protected]','6755439000','17/Oct/1994',1,GETDATE(),GETDATE())  
  46. GO  
  47. INSERT INTO Students VALUES ('Mahendra','Dhoni',65789, 'Male', '[email protected]','9856421003','15/March/1987',1,GETDATE(),GETDATE())  
  48. GO  
  49.    
  50. Create Procedure getStudents  
  51. As   
  52. Begin  
  53.    Select * from Students Where isActive=1  
  54. End   
Step 2

Download jQuery Datatables plugin core files from its website. Go to http://datatables.net/download/index and follow the below steps.
 
 
 

Step 3

Create an empty ASP.NET web application project and add the above downloaded files to it.

Step 4

Add a class file named students.cs with some auto-implemented properties. You can copy and paste the following code if you are using SQL script.

  1. using System;  
  2.    
  3. namespace dataTablesPlugin  
  4. {  
  5.     class Students  
  6.     {  
  7.         public int iD { get; set; }  
  8.         public string firstName { get; set; }  
  9.         public string lastName { get; set; }  
  10.         public int feesPaid { get; set; }  
  11.         public string gender { get; set; }  
  12.         public string emailId { get; set; }  
  13.         public string telephoneNumber { get; set; }  
  14.         public DateTime dateOfBirth { get; set; }  
  15.     }  
  16. }  

Explanation: We have created properties in the class file with the same name as that of our database table columns. This helps us to retrieve data from SQL database.

Step 5

Create a connection string in the Web.config file.

  1. <connectionStrings>  
  2.     <add connectionString="Data Source=.;Initial Catalog=Test;Integrated Security=True" name="dbcs" providerName="System.Data.SqlClient"/>  
  3.   </connectionStrings>  

The above string is used to connect to Database using .NET sql client.

Step 6

Add a ASP.NET web service named StudentService.asmx and add the following code to it.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Web.Script.Serialization;  
  7. using System.Web.Services;  
  8.    
  9. namespace dataTablesPlugin  
  10. {  
  11.     [WebService(Namespace = "http://tempuri.org/")]  
  12.     [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]  
  13.     [System.ComponentModel.ToolboxItem(false)]  
  14.     [System.Web.Script.Services.ScriptService]  
  15.     public class StudentService : System.Web.Services.WebService  
  16.     {  
  17.         [WebMethod]  
  18.         public void GetStudents()  
  19.         {  
  20.             var cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;  
  21.             var students = new List<Students>();  
  22.             using (var con = new SqlConnection(cs))  
  23.             {  
  24.                 var cmd = new SqlCommand("getStudents", con) {CommandType = CommandType.StoredProcedure};  
  25.                 con.Open();  
  26.                 var dr = cmd.ExecuteReader();  
  27.                 while (dr.Read())  
  28.                 {  
  29.                     var student = new Students  
  30.                     {  
  31.                         iD = Convert.ToInt32(dr[0].ToString()),  
  32.                         firstName = dr[1].ToString(),  
  33.                         lastName = dr[2].ToString(),  
  34.                         feesPaid = Convert.ToInt32(dr[3].ToString()),  
  35.                         gender = dr[4].ToString(),  
  36.                         emailId = dr[5].ToString(),  
  37.                         telephoneNumber = dr[6].ToString(),  
  38.                         dateOfBirth = Convert.ToDateTime(dr[7].ToString())  
  39.                     };  
  40.                     students.Add(student);  
  41.                 }  
  42.             }  
  43.             var js = new JavaScriptSerializer();  
  44.             Context.Response.Write(js.Serialize(students));  
  45.         }  
  46.     }  
  47. }  

The above code is a simple ADO.NET code that create a list of Students and retrieves data from database stored procedure and reads each property value from it and stores it in our class properties. At the last of this code, JavaScriptSerializer class is used to convert the received data in JSON format so that our Datatables plugin can consume that data using AJAX call.

Now let us quickly test our web service if it is returning the correct data or not. Press CTRL + F5.

 
 
Step 7

Add a webform to the project.

 

Step 8

Add the following references in the head section of the HTML.
  1.    <script src="DataTables/jQuery-2.2.0/jquery-2.2.0.js"></script>  
  2.    <script src="DataTables/DataTables-1.10.11/js/jquery.dataTables.js"></script>  
  3.    <link href="DataTables/DataTables-1.10.11/css/jquery.dataTables.css" rel="stylesheet" />  
  4.    <link href="DataTables/Bootstrap-3.3.6/css/bootstrap.css" rel="stylesheet" />  

The above references are of core jQuery, datatables plugin jquery, datatables plugin css and bootstrap css.

Step 9

Add the following HTML code to the Body tag.

  1. <body>  
  2.     <form id="form1" runat="server">  
  3.         <div style="padding: 10px; border: 5px solid black; margin-top: 50px" class="container-fluid">  
  4.             <div>  
  5.                 <b class="label label-danger" style="padding: 8.5px">Click to Show or Hide Column:</b>  
  6.                 <div class="btn-group btn-group-sm">  
  7.                     <a class="showHide btn btn-primary" data-columnindex="0">ID</a>  
  8.                     <a class="showHide btn btn-primary" data-columnindex="1">FirstName</a>  
  9.                     <a class="showHide btn btn-primary" data-columnindex="2">LastName</a>  
  10.                     <a class="showHide btn btn-primary" data-columnindex="3">FeesPaid</a>  
  11.                     <a class="showHide btn btn-primary" data-columnindex="4">Gender</a>  
  12.                     <a class="showHide btn btn-primary" data-columnindex="5">Email</a>  
  13.                     <a class="showHide btn btn-primary" data-columnindex="6">TelephoneNumber</a>  
  14.                     <a class="showHide btn btn-primary" data-columnindex="7">Date of Birth</a>  
  15.                 </div>  
  16.             </div>  
  17.             <br />  
  18.             <table id="studentTable" class="table table-responsive table-hover">  
  19.                 <thead>  
  20.                     <tr>  
  21.                         <th>ID</th>  
  22.                         <th>First Name</th>  
  23.                         <th>Last Name</th>  
  24.                         <th>Fees Paid</th>  
  25.                         <th>Gender</th>  
  26.                         <th>Email</th>  
  27.                         <th>Telephone Number</th>  
  28.                         <th>Date of Birth</th>  
  29.                     </tr>  
  30.                 </thead>  
  31.                 <tfoot>  
  32.                     <tr>  
  33.                         <th>ID</th>  
  34.                         <th>First Name</th>  
  35.                         <th>Last Name</th>  
  36.                         <th>Fees Paid</th>  
  37.                         <th>Gender</th>  
  38.                         <th>Email</th>  
  39.                         <th>Telephone Number</th>  
  40.                         <th>Date of Birth</th>  
  41.                     </tr>  
  42.                 </tfoot>  
  43.             </table>  
  44.         </div>  
  45.     </form>  
  46. </body>  

We have just declared a table with header and footer. The main content will be displayed using jQuery AJAX call using our web service.

Step 10

Add the custom CSS to the head section.

  1. <style>  
  2.     .showHide {  
  3.         cursor: pointer;  
  4.     }  
  5. </style>  
Step 11

Add the following jQuery code to the head section.
  1. <script type="text/javascript">  
  2.      $(document).ready(function () {  
  3.          $.ajax({  
  4.              type: "POST",  
  5.              dataType: "json",  
  6.              url: "studentService.asmx/GetStudents",  
  7.              success: function (data) {  
  8.                  var datatableVariable = $('#studentTable').DataTable({  
  9.                      data: data,  
  10.                      columns: [  
  11.                          { 'data': 'iD' },  
  12.                          { 'data': 'firstName' },  
  13.                          { 'data': 'lastName' },  
  14.                          {  
  15.                              'data': 'feesPaid', 'render': function (feesPaid) {  
  16.                                  return '$ ' + feesPaid;  
  17.                              }  
  18.                          },  
  19.                          { 'data': 'gender' },  
  20.                          { 'data': 'emailId' },  
  21.                          { 'data': 'telephoneNumber' },  
  22.                          {  
  23.                              'data': 'dateOfBirth', 'render': function (date) {  
  24.                                  var date = new Date(parseInt(date.substr(6)));  
  25.                                  var month = date.getMonth() + 1;  
  26.                                  return date.getDate() + "/" + month + "/" + date.getFullYear();  
  27.                              }  
  28.                          }]  
  29.                  });  
  30.                  $('#studentTable tfoot th').each(function () {  
  31.                      var placeHolderTitle = $('#studentTable thead th').eq($(this).index()).text();  
  32.                      $(this).html('<input type="text" class="form-control input input-sm" placeholder = "Search ' + placeHolderTitle + '" />');  
  33.                  });  
  34.                  datatableVariable.columns().every(function () {  
  35.                      var column = this;  
  36.                      $(this.footer()).find('input').on('keyup change', function () {  
  37.                          column.search(this.value).draw();  
  38.                      });  
  39.                  });  
  40.                  $('.showHide').on('click', function () {  
  41.                      var tableColumn = datatableVariable.column($(this).attr('data-columnindex'));  
  42.                      tableColumn.visible(!tableColumn.visible());  
  43.                  });  
  44.              }  
  45.          });  
  46.   
  47.      });  
  48.  </script>  
Explanation

  1. We use the jquery ajax function to call our web service by mentioning its URL in the URL option.

  2. If the call to the service is successful, then we call DataTable function of the datatables plugin API which converts our table into a multi-purpose table which can help to show data, filter data, sort data, paging of data, hiding / showing columns etc.

  3. We specify the columns that we want to display after getting data from the AJAX call.

  4. To allow searching on each column, we use the each function of jQuery and append an input element which displays a textbox at the bottom of each column.

  5. To show/hide columns, we are using visible function of jQuery and data-columnindex attribute. 

Step 12

Press Ctrl + F5 together and you will see the following output and this is what we want to achieve.


Similar Articles