You can't bind the data in one model.
After binding each customer record you need to make ajax call and bind the Order data.
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
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>
<title>Nested Html Table Paging</title>
<script type="text/javascript" src="https://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="https://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: "Home/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: "Home/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>
<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>
</body>
</html>
Namespaces
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
Code
C#
private static int PageSize = 5;
public 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();
}
public 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 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;
}
}
}
}
Screenshot
