|
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.
|