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

Figure 2: Paging is visible for one page.
Figure 3: Paging is not visible when there is only one page
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.
|