Hi SajidHussa,
Check this example. Now please take its reference and correct your code.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
SQL
CREATE PROCEDURE [dbo].[GetCustomersPageWise_Sort]
@SearchTerm VARCHAR(100) = ''
,@PageIndex INT = 1
,@PageSize INT = 10
,@SortDirection VARCHAR(4) = 'ASC'
,@SortColumn VARCHAR(40) = ''
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER ( ORDER BY
CASE WHEN @SortColumn = 'CustomerID' AND @SortDirection = 'ASC' THEN CustomerID END ASC,
CASE WHEN @SortColumn = 'CustomerID' AND @SortDirection = 'DESC' THEN CustomerID END DESC,
CASE WHEN @SortColumn = 'CompanyName' AND @SortDirection = 'ASC' THEN CompanyName END ASC,
CASE WHEN @SortColumn = 'CompanyName' AND @SortDirection = 'DESC' THEN CompanyName END DESC,
CASE WHEN @SortColumn = 'ContactName' AND @SortDirection = 'ASC' THEN ContactName END ASC,
CASE WHEN @SortColumn = 'ContactName' AND @SortDirection = 'DESC' THEN ContactName END DESC,
CASE WHEN @SortColumn = 'ContactTitle' AND @SortDirection = 'ASC' THEN ContactTitle END ASC,
CASE WHEN @SortColumn = 'ContactTitle' AND @SortDirection = 'DESC' THEN ContactTitle END DESC,
CASE WHEN @SortColumn = 'Address' AND @SortDirection = 'ASC' THEN Address END ASC,
CASE WHEN @SortColumn = 'Address' AND @SortDirection = 'DESC' THEN Address END DESC,
CASE WHEN @SortColumn = 'City' AND @SortDirection = 'ASC' THEN City END ASC,
CASE WHEN @SortColumn = 'City' AND @SortDirection = 'DESC' THEN City END DESC,
CASE WHEN @SortColumn = 'Region' AND @SortDirection = 'ASC' THEN Region END ASC,
CASE WHEN @SortColumn = 'Region' AND @SortDirection = 'DESC' THEN Region END DESC,
CASE WHEN @SortColumn = 'PostalCode' AND @SortDirection = 'ASC' THEN PostalCode END ASC,
CASE WHEN @SortColumn = 'PostalCode' AND @SortDirection = 'DESC' THEN PostalCode END DESC,
CASE WHEN @SortColumn = 'Country' AND @SortDirection = 'ASC' THEN Country END ASC,
CASE WHEN @SortColumn = 'Country' AND @SortDirection = 'DESC' THEN Country END DESC,
CASE WHEN @SortColumn = 'Phone' AND @SortDirection = 'ASC' THEN Phone END ASC,
CASE WHEN @SortColumn = 'Phone' AND @SortDirection = 'DESC' THEN Phone END DESC,
CASE WHEN @SortColumn = 'Fax' AND @SortDirection = 'ASC' THEN Fax END ASC,
CASE WHEN @SortColumn = 'Fax' AND @SortDirection = 'DESC' THEN Fax END DESC
) AS RowNumber,*
INTO #Results
FROM [Customers]
WHERE [ContactName] LIKE @SearchTerm + '%' OR @SearchTerm = ''
SELECT @RecordCount = COUNT(*)
FROM #Results
SELECT *
FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Html Table Paging Sorting</title>
<style type="text/css">
.Pager span
{
text-align: center;
color: #999;
display: inline-block;
width: 20px;
background-color: #A1DCF2;
margin-right: 3px;
line-height: 150%;
border: 1px solid #3AC0F2;
}
.Pager a
{
text-align: center;
display: inline-block;
width: 20px;
background-color: #3AC0F2;
color: #fff;
border: 1px solid #3AC0F2;
margin-right: 3px;
line-height: 150%;
text-decoration: none;
}
</style>
<script type="text/javascript" src="https://code.jquery.com/jquery-3.1.1.min.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
var pagerIndex = 1;
var direction = 'ASC';
var column = 'CustomerID';
$(function () {
GetCustomers(1, '', 'ASC', column);
$("#txtSearch").on('keyup', function () {
GetCustomers(1, $(this).val(), direction, column);
});
$("#tblCustomers th a").on('click', function () {
direction = direction == 'ASC' ? 'DESC' : 'ASC';
column = $(this).data('sort');
GetCustomers(pagerIndex, $("#txtSearch").val(), direction, column);
});
$('.Pager').on("click", ".page", function () {
pagerIndex = parseInt($(this).attr('page'));
GetCustomers(pagerIndex, $("#txtSearch").val());
});
});
function GetCustomers(pageIndex, searchTerm, sortDirection, sortColumn) {
var obj = {};
obj.pageIndex = $.trim(pageIndex);
obj.searchTerm = $.trim(searchTerm);
obj.sortDirection = $.trim(sortDirection);
obj.sortColumn = $.trim(sortColumn);
$.ajax({
type: "POST",
url: "Default.aspx/GetCustomers",
data: JSON.stringify(obj),
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.d);
},
error: function (response) {
alert(response.d);
}
});
}
var row;
function OnSuccess(response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
var customers = xml.find("Customers");
if (row == null) {
row = $("[id*=tblCustomers] tr:last-child").clone(true);
}
$("[id*=tblCustomers] tr").not($("[id*=tblCustomers] tr:first-child")).remove();
if (customers.length > 0) {
$.each(customers, function () {
var customer = $(this);
$("td", row).eq(0).html($(this).find("CustomerID").text());
$("td", row).eq(1).html($(this).find("ContactName").text());
$("td", row).eq(2).html($(this).find("City").text());
$("[id*=tblCustomers]").append(row);
row = $("[id*=tblCustomers] tr:last-child").clone(true);
});
} else {
var empty_row = row.clone(true);
$("td:first-child", empty_row).attr("colspan", $("td", row).length);
$("td:first-child", empty_row).attr("align", "center");
$("td:first-child", empty_row).html("No records found for the search criteria.");
$("td", empty_row).not($("td:first-child", empty_row)).remove();
$("[id*=tblCustomers]").append(empty_row);
}
var pager = xml.find("Pager");
$(".Pager").ASPSnippets_Pager({
ActiveCssClass: "current",
PagerCssClass: "pager",
PageIndex: parseInt(pager.find("PageIndex").text()),
PageSize: parseInt(pager.find("PageSize").text()),
RecordCount: parseInt(pager.find("RecordCount").text())
});
};
</script>
</head>
<body>
<form id="form1" runat="server">
<center>
Name : <input type="text" name="name" id="txtSearch" /><br /><br />
<table id="tblCustomers" width="25%">
<tr>
<th><a href="#" data-sort="CustomerID">CustomerID</a></th>
<th><a href="#" data-sort="ContactName">Contact Name</a></th>
<th><a href="#" data-sort="City">City</a></th>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
</tr>
</table>
<br /><div class="Pager"></div>
</center>
</form>
</body>
</html>
Namespaces
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
VB.Net
Imports System.Data.SqlClient
Imports System.Data
Imports System.Web.Services
Code
C#
private static int PageSize = 10;
[WebMethod]
public static string GetCustomers(int pageIndex, string searchTerm, string sortDirection, string sortColumn)
{
string query = "[GetCustomersPageWise_Sort]";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SearchTerm", searchTerm);
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.AddWithValue("@SortDirection", sortDirection);
cmd.Parameters.AddWithValue("@SortColumn", sortColumn);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
return GetData(cmd, pageIndex).GetXml();
}
private static DataSet GetData(SqlCommand cmd, int pageIndex)
{
string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds, "Customers");
DataTable dt = new DataTable("Pager");
dt.Columns.Add("PageIndex");
dt.Columns.Add("PageSize");
dt.Columns.Add("RecordCount");
dt.Rows.Add();
dt.Rows[0]["PageIndex"] = pageIndex;
dt.Rows[0]["PageSize"] = PageSize;
dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;
ds.Tables.Add(dt);
return ds;
}
}
}
}
VB.Net
Private Shared PageSize As Integer = 10
<WebMethod()>
Public Shared Function GetCustomers(ByVal pageIndex As Integer, ByVal searchTerm As String, ByVal sortDirection As String, ByVal sortColumn As String) As String
Dim query As String = "[GetCustomersPageWise_Sort]"
Dim cmd As SqlCommand = New SqlCommand(query)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@SearchTerm", searchTerm)
cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
cmd.Parameters.AddWithValue("@PageSize", PageSize)
cmd.Parameters.AddWithValue("@SortDirection", sortDirection)
cmd.Parameters.AddWithValue("@SortColumn", sortColumn)
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
Return GetData(cmd, pageIndex).GetXml()
End Function
Private Shared Function GetData(ByVal cmd As SqlCommand, ByVal pageIndex As Integer) As DataSet
Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Using con As SqlConnection = New SqlConnection(strConnString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using ds As DataSet = New DataSet()
sda.Fill(ds, "Customers")
Dim dt As DataTable = New DataTable("Pager")
dt.Columns.Add("PageIndex")
dt.Columns.Add("PageSize")
dt.Columns.Add("RecordCount")
dt.Rows.Add()
dt.Rows(0)("PageIndex") = pageIndex
dt.Rows(0)("PageSize") = PageSize
dt.Rows(0)("RecordCount") = cmd.Parameters("@RecordCount").Value
ds.Tables.Add(dt)
Return ds
End Using
End Using
End Using
End Function
Screenshot
