I have tried this from two links
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
var pageIndex = 1;
var pageCount;
$(window).scroll(function () {
if ($(window).scrollTop() == $(document).height() - $(window).height()) {
pageIndex++;
GetRecords(pageIndex);
}
});
$("[id*=txtSearch]").live("keyup", function () {
GetRecords(1);
});
function SearchTerm() {
return jQuery.trim($("[id*=txtSearch]").val());
};
function GetRecords(pageIndex) {
if (pageIndex <= 2 || pageIndex <= pageCount) {
$("#loader").show();
$.ajax({
type: "POST",
url: "CS.aspx/GetCustomers",
data: '{searchTerm: "' + SearchTerm() + '", pageIndex: ' + pageIndex + '}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.d);
},
error: function (response) {
alert(response.d);
}
});
}
}
function OnSuccess(response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
pageCount = parseInt(xml.find("PageCount").eq(0).find("PageCount").text());
var customers = xml.find("Customers");
customers.each(function () {
var customer = $(this);
var table = $("#dvCustomers table").eq(0).clone(true);
$(".name", table).html(customer.find("ContactName").text());
$(".city", table).html(customer.find("City").text());
$(".postal", table).html(customer.find("PostalCode").text());
$(".country", table).html(customer.find("Country").text());
$(".phone", table).html(customer.find("Phone").text());
$(".fax", table).html(customer.find("Fax").text());
$("#dvCustomers").append(table).append("<br />");
});
$("#loader").hide();
}
</script>
</head>
<body style="font-family: Arial; font-size: 10pt">
<form id="form1" runat="server">
Search:
<asp:TextBox ID="txtSearch" runat="server" />
<table>
<tr>
<td class="td">
<div id="dvCustomers">
<asp:Repeater ID="rptCustomers" runat="server">
<ItemTemplate>
<table cellpadding="2" cellspacing="0" border="1" style="width: 200px; height: 100px;
border: dashed 2px #04AFEF; background-color: #B0E2F5">
<tr>
<td>
<b><u><span class="name">
<%# Eval("ContactName") %></span></u></b>
</td>
</tr>
<tr>
<td>
<b>City: </b><span class="city">
<%# Eval("City") %></span><br />
<b>Postal Code: </b><span class="postal">
<%# Eval("PostalCode") %></span><br />
<b>Country: </b><span class="country">
<%# Eval("Country")%></span><br />
<b>Phone: </b><span class="phone">
<%# Eval("Phone")%></span><br />
<b>Fax: </b><span class="fax">
<%# Eval("Fax")%></span><br />
</td>
</tr>
</table>
<br />
</ItemTemplate>
</asp:Repeater>
</div>
</td>
<td valign="bottom">
<img id="loader" alt="" src="loading.gif" style="display: none" />
</td>
</tr>
</table>
</form>
</body>
</html>
Namespace
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
using System.Configuration;
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
rptCustomers.DataSource = GetCustomersData("", 1);
rptCustomers.DataBind();
}
}
public static DataSet GetCustomersData(string searchTerm, int pageIndex)
{
string query = "[GetCustomers_Pager]";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SearchTerm", searchTerm);
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", 10);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
return GetData(cmd);
}
private static DataSet GetData(SqlCommand cmd)
{
string strConnString = ConfigurationManager.ConnectionStrings["constr"].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("PageCount");
dt.Columns.Add("PageCount");
dt.Rows.Add();
dt.Rows[0][0] = cmd.Parameters["@RecordCount"].Value;
ds.Tables.Add(dt);
return ds;
}
}
}
}
[WebMethod]
public static string GetCustomers(string searchTerm, int pageIndex)
{
return GetCustomersData(searchTerm, pageIndex).GetXml();
}
SQL
I have used northwindth Database
SP
--[dbo].[GetCustomers_Pager] 'a',1,10,null
CREATE PROCEDURE [dbo].[GetCustomers_Pager]
@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]
,[City]
,[Country]
,[PostalCode]
,[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
