.jpg)
Paging, Sorting, Searching In ASP.NET by JQuery DataTable
--Database.............................
Nowadays it is most popular way to apply client side
searching, sorting and pagination (client/server side) through ajax/html request
from any tabular format.
--Database.............................
USE [NORTHWND]
GO
/****** Object: Table
[dbo].[Customers] Script Date:
26-11-2016 21:37:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE TABLE [dbo].[Customers](
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL,
[Phone] [nvarchar](24) NULL,
[Fax] [nvarchar](24) NULL,
CONSTRAINT
[PK_Customers] PRIMARY KEY
CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE
= OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
First we will take WebForm in
Asp.net Application which name is Default.aspx
Code for Default.aspx.....................
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>JQuery Data Table in Asp.Net</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<link href="Style/bootstrap-theme.css" rel="stylesheet" />
<link href="Style/dataTables.jqueryui.css" rel="stylesheet" />
<link href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" rel="stylesheet" />
<script type="text/javascript">
$(document).ready(function () {
// Setup - add a text input to each footer cell
$('#example tfoot th').each(function () {
var title = $(this).text();
$(this).html('<input type="text"
placeholder="Search ' + title + '" />');
});
// DataTable
var table = $('#example').DataTable();
// Apply the search
table.columns().every(function () {
var that = this;
$('input', this.footer()).on('keyup
change', function () {
if (that.search() !== this.value) {
that
.search(this.value)
.draw();
}
});
});
});
</script>
<style type="text/css">
.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>
</head>
<body>
<form id="form1" runat="server">
<div style ="margin-top:30px">
<table class="table table-striped table-bordered " style="font-family: Serif;"
border="1px" id="example" >
<thead>
<tr>
<th>SrNo</th>
<th>CustomerID</th>
<th>CompanyName</th>
<th>Contact Name</th>
<th>Address</th>
<th>City</th>
<%-- <th> Region</th>
<th>PostalCode</th>
<th>Country</th>--%>
<th>Actions</th>
</tr>
</thead>
<tfoot>
<tr>
<th>SrNo</th>
<th>CustomerID</th>
<th>CompanyName</th>
<th>Contact Name</th>
<th>Address</th>
<th>City</th>
<%--<th> Region</th>
<th>PostalCode</th>
<th>Country</th>--%>
<th>Actions</th>
</tr>
</tfoot>
<tbody id="tlist" runat="server">
</tbody>
</table>
</div>
</form>
</body>
</html>
Code for Default.aspx.cs.....................
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindDataTable();
}
}
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString);
public void BindDataTable()
{
con.Open();
SqlCommand cmd = new SqlCommand("SELECT
[CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax]FROM[dbo].[Customers]", con);
SqlDataReader dr =
cmd.ExecuteReader();
String result = "";
Int32 i = 1;
while (dr.Read())
{
result += "<tr>";
result += " <td>" +i.ToString() +"</td>";
result += " <td>" + dr["CustomerID"] + "</td>";
result += " <td>" + dr["CompanyName"] + "</td>";
result += " <td>" + dr["ContactName"] + "</td>";
result += " <td>" + dr["Address"] + "</td>";
result += " <td>" + dr["City"] + "</td>";
//result += " <td
class=\"center\">" + dr["Region"] +
"</td>";
//result += " <td
class=\"center\">" + dr["PostalCode"] +
"</td>";
//result += " <td
class=\"center\">" + dr["Country"] +
"</td>";
result += " <td
class=\"center\">";
result += " <a class=\"btn btn-info\"
href=\"Edit.aspx?ID=" + dr[0] + "\">";
result += " <i class=\"icon-edit
icon-white\"></i> ";
result += " Edit ";
result += " </a>";
result += " <a class=\"btn btn-info\"
href=\"Delete.aspx?ID="
+ dr[0] + "\">";
result += " <i class=\"icon-edit
icon-white\"></i> ";
result += " Delete ";
result += " </a>";
result += " </td>";
result += " </tr>";
tlist.InnerHtml = result;
i++;
}
con.Close();
}
}
Result
<!DOCTYPE html>
<html>
<head>
<title></title>
<meta charset="utf-8" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" rel="stylesheet" />
<style type="text/css">
tfoot input {
width: 100%;
padding: 3px;
box-sizing: border-box;
}
</style>
<script type="text/javascript">
$(document).ready(function () {
// Setup - add a text input to each footer cell
$('#example tfoot th').each(function () {
var title = $(this).text();
$(this).html('<input type="text" placeholder="Search ' + title + '" />');
});
// DataTable
var table = $('#example').DataTable();
// Apply the search
table.columns().every(function () {
var that = this;
$('input', this.footer()).on('keyup change', function () {
if (that.search() !== this.value) {
that
.search(this.value)
.draw();
}
});
});
});
</script>
</head>
<body>
<table id="example" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</tfoot>
<tbody>
<tr>
<td>Kush Tiwari</td>
<td>.Net Developer</td>
<td>Hard Shell Pvt Ltd</td>
<td>29</td>
<td>2016/11/24</td>
<td>50000</td>
</tr>
<tr>
<td>Udayan Maiti</td>
<td>Sr .Net Developer</td>
<td>Hard Shell Pvt Ltd</td>
<td>31</td>
<td>2015/11/24</td>
<td>67000</td>
</tr>
<html>
<head>
<title></title>
<meta charset="utf-8" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" rel="stylesheet" />
<style type="text/css">
tfoot input {
width: 100%;
padding: 3px;
box-sizing: border-box;
}
</style>
<script type="text/javascript">
$(document).ready(function () {
// Setup - add a text input to each footer cell
$('#example tfoot th').each(function () {
var title = $(this).text();
$(this).html('<input type="text" placeholder="Search ' + title + '" />');
});
// DataTable
var table = $('#example').DataTable();
// Apply the search
table.columns().every(function () {
var that = this;
$('input', this.footer()).on('keyup change', function () {
if (that.search() !== this.value) {
that
.search(this.value)
.draw();
}
});
});
});
</script>
</head>
<body>
<table id="example" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</tfoot>
<tbody>
<tr>
<td>Kush Tiwari</td>
<td>.Net Developer</td>
<td>Hard Shell Pvt Ltd</td>
<td>29</td>
<td>2016/11/24</td>
<td>50000</td>
</tr>
<tr>
<td>Udayan Maiti</td>
<td>Sr .Net Developer</td>
<td>Hard Shell Pvt Ltd</td>
<td>31</td>
<td>2015/11/24</td>
<td>67000</td>
</tr>
........................................................
............................................
</tbody>
</table>
</body>
</html>
</tbody>
</table>
</body>
</html>
