Thursday, December 22

jQuery DataTable - Paging, Custom Styling and Disable Sorting of a column

In my previous article jQuery Datatable using webservice, i explained the basic binding of jQuery Datatable with default sorting.
In this article I will explore below features of jQuery DataTable.
1. Paging.
2. Custom style on paging
3. Hide page numbers when number of rows is less than rows specified on a page
4. Specify width of a column
5. Disable sorting in a particular column.
Figure 1: Default Pagin


jQueryDataTableAllPaging

Figure 2: Paging is visible for one page.


jQueryDataTableOnlyOnePage

Figure 3: Paging is not visible when there is only one page


jQueryDataTableWithoutPaging

Let's see how we can achieve this:
Step 1:  Download jQuery 1.4.2 and jQuery Datatable

Step 2: Add jquery-1.4.2.min.js and jquery.dataTables.min.js in the page

<script src="jquery-1.4.2.min.js" type="text/javascript"></script><script src="jquery.dataTables.min.js" type="text/javascript"></script>

Step 3: Add the below style in the page.


<style type="text/css">
      
.myGrid      {
            
width: 100%;
            
margin: 0px 0 0px 0;
            
border: solid 1px #525252;
            
border-collapse: collapse;
            
width: 600px;
      }

      .myGrid td      {
            
padding: 2px;
            
border: solid 1px #c1c1c1;
      
      color: Black;
            
font-family: Arial,Helvetica,sans-serif;
            
font-size: 0.9em;
      }

      .myGrid th      {
            
color: #fff;
            
background: url(images/grid_header.png) repeat-x top;
            
font-family: Arial,Helvetica,sans-serif;
            
font-size: 0.9em;
            
cursor: pointer;
      }

      .dataTables_paginate
      
{
            
width: 600px;
            
background: url(images/grid_pager.png) repeat-x top;
            
text-align: center;
      
}

      
.paging_full_numbers
      
{
            
width: 600px;
            
height: 24px;
            
padding: 4px 0px 2px 0px;
            
vertical-align: middle;
            
line-height: 22px;
      }

      .paging_full_numbers span.paginate_button, .paging_full_numbers span.paginate_active      {
            
border: 1px solid #aaa;
            
padding: 2px 5px;
            
margin: 0 3px;
            
cursor: pointer;
            
cursor: hand;
      }

      .paging_full_numbers span.paginate_button      {
            
background-color: #fff;
      }

      .paging_full_numbers span.paginate_button:hover      {
            
background-color: #ccc;
      }

      .paging_full_numbers span.paginate_active      {
            
background-color: #99B3FF;
      }

</style>

Step 4: Add below html content inside body tag

<table id="grid" class="myGrid">
      
<thead
>
            
<tr
>
                  
<th
>                        By
                  </th
>
                  
<th
>
                        
Recipie Name
                  </th
>
      
            <th
>
                        
Preparation Time
                  </th
>
                  
<th
>
                        
Cooking Time
                  </th
>
            
</tr
>
      
</thead
>
      
<tbody
>
            
<tr
>
                  
<td colspan
="5">
                        
Loading....
                  </td
>
            
</tr
>
      
</tbody
></table><div id="errorMessage"></div>


Step 5: Add below javascript in the page. fnDrawCallback allows you to hide paging when number of rows is less than rows specified in one page.

<script language="javascript" type="text/javascript">
      
$(document).ready(function
() {
            function
 renderTable(result) {
                  var
 dtData = [];
                  $.each(result, function
() {
                        dtData.push([
                              this
.by,
                              this
.Recipiename,
                              this
.preparationtime,
                              this
.cookingtime
                        ]);
            });
            
            $('#grid').dataTable({ //grid is the id of the table                  'aaData': dtData,
                  'bPaginate': true
,
                  'bInfo': false
,
                  'bFilter': false
,
                  'bLengthChange': false
,
                  "aoColumns"
: [
                        { "bSortable": false, sWidth: '80px'
},
                        { sWidth: '180px'
},
                        { sWidth: '170px'
},
                        { sWidth: '170px'
}
                  ],
                  'sPaginationType': 'full_numbers'
,
                  'iDisplayLength'
: 5,
                  "fnDrawCallback": function
() {
                        if ($("#grid").find("tr:not(.ui-widget-header)"
).length <= 5) {
                              $('div.dataTables_paginate')[0].style.display = "none"
;
                        }
                        else
{
                              $('div.dataTables_paginate')[0].style.display = "block"
;
                        }
                  }
            });
      }

      $.ajax({
            type: "GET"
,
            url: "JsonWebService.asmx/GetRecipie"
,
            contentType: "application/json; charset=utf-8"
,
            dataType: "json"
,
            success: function
(response) {
                  renderTable(response.d);
            },
            failure: function
(errMsg) {
                  $('#errorMessage'
).text(errMsg);  //errorMessage is id of the div
            }
      });
});
</script>

Step 6: Create an asmx file and create below class.
public class Recipie{
      public string
 by;
      public string
 Recipiename;
      public string
 preparationtime;
      public string
 cookingtime;
}

Step 7: Add below name space in the asmx.cs file

using System.Web.Script.Services;
using
 System.Data;
using System.Data.SqlClient;

Step 8: Create below webmethod in the asmx.cs file to get data from database and return List to jQuery
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = true
)]
public List<Recipie
> GetRecipie()
{
      string strQuery = "SELECT * FROM Recipie"
;
      DataTable dtRecipie = null
;
      Recipie
 objRecipie;
      SqlConnection con = GetConnection("Data Source=(local);Initial Catalog=DataTable;Integrated Security=SSPI"
);
      using
(con)
      {
            con.Open();
            using (SqlDataAdapter sqlAdapter = new SqlDataAdapter
(strQuery, con))
            {
                  dtRecipie = new DataTable
();
                  sqlAdapter.Fill(dtRecipie);
            }
      }
      List<Recipie> drlist = new List<Recipie
>();
      foreach (DataRow row in
 dtRecipie.Rows)
      {
            objRecipie = new Recipie
();
            objRecipie.by = row["by"
].ToString();
            objRecipie.Recipiename = row["Recipiename"
].ToString();
            objRecipie.preparationtime = row["preparationtime"
].ToString();
            objRecipie.cookingtime = row["cookingtime"
].ToString();
            drlist.Add(objRecipie);
      }
      return
 drlist;
}

Step 9 : Add below method to get SqlConnection
private SqlConnection GetConnection(string m_conString)
{
      SqlConnection con = new SqlConnection
(m_conString);
      return
 con;
}

This ends the article of creating custom datatable using jquery and webservice.


Like us if you find this post useful. Thanks! 
Download Code
 

3 comments:

Please don't spam, spam comments is not allowed here.

ShibashishMnty
shibashish mohanty