Client Side Paging Using JQuery in Asp.net MVC
Introduction:
Today in this article, I have explained Client Side Paging Using JQuery in Asp.net MVC. I’ve been using the jQuery paging plugins. You don’t need to know jQuery to make this work… You need to know little CSS to create a nice design for your table, what you will see in this article is how to attach this plugin to your pure HTML table and a div for pagination and make your table with pagination features.
Follow these steps in order to implement “Client Side Paging Using JQuery in Asp.net MVC”
Step1: Create New Project.
Go to File > New > Project > Web > Asp.net MVC web project > Enter Application Name > Select your project location > click to add button > It will show new dialog window for select template > here we will select empty project > then click to ok
Step2: Create a table and Stored procedure in the database.
In this example, I have used following table for display record and pagination.
CREATE TABLE [dbo].[employee](
[id] [int] IDENTITY(1,1) primary key NOT NULL,
[name] [nvarchar](50) NULL,
[email] [nvarchar](max) NULL,
[address] [nvarchar](max) NULL,
)
Now create store procedure for display record
CREATE proc Sp_Employee_All
as
begin
Select * from Employee
end
Run above script in MS SQL Server and click to execute button
Step3: Add Connection string in web.config file
Here I have added connection string in ‘web.config’ file under Configuration section as follows,
<connectionStrings>
<add name="con" connectionString="Data Source=.;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
Step4: Create a controller.
Go to Solutions Explorer > right click on controller folder > Add Controller > Enter Controller name > Select template “empty MVC Controller” > Add.
Here I have created a controller “HomeController”
Step5: Add new action into the controller for display data from the database.
Here I have to add ‘Index’ into ‘HomeController’. It has following code.
public ActionResult Index()
{
return View();
}
Write following code for fetch record from database in Index action
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
public ActionResult Index()
{
SqlCommand com = new SqlCommand("Sp_Employee_All", con);
com.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
ViewBag.record = ds.Tables[0];
return View();
}
Now entire HomeController.cs as follows:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace JqueryPaging.Controllers
{
public class HomeController : Controller
{
//
// GET: /Home/
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
public ActionResult Index()
{
SqlCommand com = new SqlCommand("Sp_Employee_All", con);
com.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
ViewBag.record = ds.Tables[0];
return View();
}
}
}
Step6: Add view for action in controller & design.
Right Click on Action Method > Add View > Enter View Name > Select View Engine (Razor) > Add.
It has following code and design
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<style>
.tableData {
border-left: solid 1px #D8C3C3;
border-top: solid 1px #D8C3C3;
}
.tableData tr {
}
.tableData td, .tableData th {
border-right: solid 1px #D8C3C3;
border-bottom: solid 1px #D8C3C3;
text-align: left;
padding: 5px;
}
.tableData td {
}
.tableData th {
background-color: #FAFAFA;
padding: 7px 5px;
border-bottom-color: #9C9C9C;
}
</style>
<script src="~/Resource/jquery-1.7.1.min.js"></script>
<script src="~/Resource/jquery-ui.min.js"></script>
<script src="~/Resource/paging.js"></script>
<link href="~/Resource/paging.css" rel="stylesheet" />
</head>
<body>
<div>
<table id="tbldata" class="tableData">
<tr>
<th>Sr no</th>
<th>Name</th>
<th>Email</th>
<th>Address</th>
</tr>
@{
foreach (System.Data.DataRow dr in ViewBag.record.Rows)
{
<tr>
<td>@dr["id"]</td>
<td>@dr["name"]</td>
<td>@dr["email"]</td>
<td>@dr["address"]</td>
</tr>
}
}
</table>
</div>
<script>
$(function () {
$('#tbldata').paging({ limit: 10 });
})
</script>
</body>
</html>
In above code, I have to use ViewBag for binding data from the database and loop it through foreach loop and limit is the total number of records that display per page.
Paging.js: It contain following code for pagination
(function($) {
$(function() {
$.widget("zpd.paging", {
options: {
limit: 5,
rowDisplayStyle: 'block',
activePage: 0,
rows: []
},
_create: function() {
var rows = $("tbody", this.element).children();
this.options.rows = rows;
this.options.rowDisplayStyle = rows.css('display');
var nav = this._getNavBar();
this.element.after(nav);
this.showPage(0);
},
_getNavBar: function() {
var rows = this.options.rows;
var nav = $('<div>', {class: 'paging-nav'});
for (var i = 0; i < Math.ceil(rows.length / this.options.limit); i++) {
this._on($('<a>', {
href: '#',
text: (i + 1),
"data-page": (i)
}).appendTo(nav),
{click: "pageClickHandler"});
}
//create previous link
this._on($('<a>', {
href: '#',
text: '<<',
"data-direction": -1
}).prependTo(nav),
{click: "pageStepHandler"});
//create next link
this._on($('<a>', {
href: '#',
text: '>>',
"data-direction": +1
}).appendTo(nav),
{click: "pageStepHandler"});
return nav;
},
showPage: function(pageNum) {
var num = pageNum * 1; //it has to be numeric
this.options.activePage = num;
var rows = this.options.rows;
var limit = this.options.limit;
for (var i = 0; i < rows.length; i++) {
if (i >= limit * num && i < limit * (num + 1)) {
$(rows[i]).css('display', this.options.rowDisplayStyle);
} else {
$(rows[i]).css('display', 'none');
}
}
},
pageClickHandler: function(event) {
event.preventDefault();
$(event.target).siblings().attr('class', "");
$(event.target).attr('class', "selected-page");
var pageNum = $(event.target).attr('data-page');
this.showPage(pageNum);
},
pageStepHandler: function(event) {
event.preventDefault();
//get the direction and ensure it's numeric
var dir = $(event.target).attr('data-direction') * 1;
var pageNum = this.options.activePage + dir;
//if we're in limit, trigger the requested pages link
if (pageNum >= 0 && pageNum < this.options.rows.length) {
$("a[data-page=" + pageNum + "]", $(event.target).parent()).click();
}
}
});
});
})(jQuery);
Paging.css: It contains following code for design of pager
.paging-nav {
/*text-align: right;*/
padding-top: 2px;
}
.paging-nav a {
margin: auto 1px;
text-decoration: none;
display: inline-block;
padding: 1px 7px;
background: #5b6167;
color: white;
border-radius: 3px;
font-size: 15px;
}
.paging-nav .selected-page {
background: #187ed5;
font-weight: bold;
}
.paging-nav {
width: 400px;
/*margin: 0 auto;*/
font-family: Arial, sans-serif;
}
Step 7: Run Application.
We have done all steps, now it’s time to run the application.