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
Procedures
-- GetCustomersPageWise 'm',1,5,100
CREATE PROCEDURE [dbo].[GetCustomersPageWise]
@SearchTerm VARCHAR(100) = ''
,@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [CustomerID] ASC
)AS RowNumber
,[CustomerID]
,[CompanyName]
,[ContactName]
,[ContactTitle]
,[Address]
,[City]
,[Region]
,[PostalCode]
,[Country]
,[Phone]
,[Fax]
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
GO
-- GetOrdersPageWiseBasedOnCustomerId 'TOMSP',1,5,100
CREATE PROCEDURE [dbo].[GetOrdersPageWiseBasedOnCustomerId]
@Id VARCHAR(10)
,@PageIndex INT = 1
,@PageSize INT = 5
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER (ORDER BY [CustomerID] ASC) AS RowNumber,*
INTO #Results
FROM [Orders]
WHERE CustomerID = @Id
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>Nested Html Table Paging</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/json2/20130526/json2.min.js"></script>
<script type="text/javascript">
$(function () {
$("#txtSearch").focus();
GetCustomers(1, '');
$("#txtSearch").keyup(function () {
GetCustomers(1, $(this).val());
});
$("body").on("click", "[src*=plus]", function () {
$(this).closest("tr").after("<tr><td></td><td colspan = '999'>" + $(this).next().html() + "</td></tr>")
$(this).attr("src", "images/minus.png");
GetOrders(1, $(this).closest("tr").find('td').eq(1).text(), $(this).closest("tr").find('#dvChild'));
});
$("body").on("click", "[src*=minus]", function () {
$(this).attr("src", "images/plus.png");
$(this).closest("tr").next().remove();
$(this).closest("tr").find('#dvChild').empty();
});
});
$(".Pager .page").live("click", function () {
GetCustomers(parseInt($(this).attr('page')), $("#txtSearch").val());
});
$(".PagerChild .page").live("click", function () {
var customerId = $(this).closest("tr").find('#hfCustomerId').val();
GetOrders(parseInt($(this).attr('page')), customerId, $(this).closest("tr").find('#dvChild'));
});
var row;
function GetCustomers(pageIndex, name) {
var obj = {};
obj.pageIndex = $.trim(pageIndex);
obj.name = $.trim(name);
$.ajax({
type: "POST",
url: "Default.aspx/GetCustomers",
data: JSON.stringify(obj),
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
var customers = xml.find("Customers");
if (row == null) {
row = $("[id*=gvCustomers] tr:last-child").clone(true);
}
$("[id*=gvCustomers] tr").not($("[id*=gvCustomers] tr:first-child")).remove();
$.each(customers, function () {
var customer = $(this);
$("td", row).find('#hfCustomerId').val($(this).find("CustomerID").text());
$("td", row).find('#id').html($(this).find("CustomerID").text());
$("td", row).find('#name').html($(this).find("ContactName").text());
$("td", row).find('#country').html($(this).find("City").text());
$("[id*=gvCustomers]").append(row);
row = $("[id*=gvCustomers] tr:last-child").clone(true);
});
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())
});
}
});
}
function GetOrders(pageIndex, id, ele) {
$(ele).empty();
var obj = {};
obj.pageIndex = $.trim(pageIndex);
obj.id = $.trim(id);
$.ajax({
type: "POST",
url: "Default.aspx/GetOrders",
data: JSON.stringify(obj),
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
var orders = xml.find("Customers");
var html = '<table><tr><th>Order Id</th><th>Freight</th><th>Ship City</th></tr>';
$.each(orders, function () {
html += '<tr><td>' + $(this).find("OrderID").text() +
'</td><td>' + $(this).find("Freight").text() +
'</td><td>' + $(this).find("ShipCity").text() +
'</td></tr>';
});
html += '</table><br/><div class="PagerChild"></div>';
$(ele).append(html);
var pager = xml.find("Pager");
$(".PagerChild").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" />
<table id="gvCustomers">
<tr>
<th> </th>
<th>CustomerID</th>
<th>Contact Name</th>
<th>City</th>
</tr>
<tr>
<td valign="top">
<input type="hidden" id="hfCustomerId" />
<img alt="" style="cursor: pointer" src="images/plus.png" />
<div id="dvChild">
</div>
</td>
<td valign="top"><span id="id"></span></td>
<td valign="top"><span id="name"></span></td>
<td valign="top"><span id="country"></span></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.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
Code
C#
private static int PageSize = 5;
[WebMethod]
public static string GetCustomers(int pageIndex, string name)
{
// Get Customers PageWise.
string query = "[GetCustomersPageWise]";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SearchTerm", name);
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
return GetData(cmd, pageIndex).GetXml();
}
[WebMethod]
public static string GetOrders(int pageIndex, string id)
{
// Get Orders PageWise.
string query = "[GetOrdersPageWiseBasedOnCustomerId]";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", id);
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
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 = 5
<WebMethod>
Public Shared Function GetCustomers(ByVal pageIndex As Integer, ByVal name As String) As String
Dim query As String = "[GetCustomersPageWise]"
Dim cmd As SqlCommand = New SqlCommand(query)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@SearchTerm", name)
cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
cmd.Parameters.AddWithValue("@PageSize", PageSize)
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
Return GetData(cmd, pageIndex).GetXml()
End Function
<WebMethod>
Public Shared Function GetOrders(ByVal pageIndex As Integer, ByVal id As String) As String
Dim query As String = "[GetOrdersPageWiseBasedOnCustomerId]"
Dim cmd As SqlCommand = New SqlCommand(query)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Id", id)
cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
cmd.Parameters.AddWithValue("@PageSize", PageSize)
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
