Sunday, June 9, 2013

Custom Populate paging on Page Scroll






--Create table

CREATE TABLE [dbo].[Customer](

[ContactName] [varchar](250) NULL,

[City] [varchar](250) NULL,

[PostalCode] [varchar](250) NULL,

[Country] [varchar](250) NULL,

[Phone] [varchar](250) NULL,

[Fax] [varchar](250) NULL,

[CustomerId] [int] IDENTITY(1,1) NOT NULL

) ON [PRIMARY]



--Create procedure

create proc GetCustomersPageWise(@PageIndex int,@PageSize int,@PageCount int out)



as

BEGIN

SET NOCOUNT ON;

SELECT ROW_NUMBER() OVER( ORDER BY [CustomerID] ASC )AS RowNumber,

ContactName,City,PostalCode,Country,Phone,Fax,CustomerId INTO #Results FROM Customer

SELECT @PageCount = COUNT(*) FROM #Results

SELECT * FROM #Results

WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

DROP TABLE #Results



END

--Filling value in table

declare @max int ,@min int

set @max=500

set @min=1

while(@min<@max)



begin

insert into Customer( ContactName,City,PostalCode,Country,Phone,Fax)values(@min,@min,@min,@min,@min,@min)

set @min=@min+1



end

select * from Customer



------------------

//Connection String in Web.config

<connectionStrings>

<add name="constr" connectionString="Data Source=.;Initial Catalog=Test_DB;integrated security=true"/>

</connectionStrings>



---------------------------

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="autopageslide.aspx.cs" Inherits="autopageslide" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<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()) {

GetRecords();

}

});

function GetRecords() {

pageIndex++;

if (pageIndex == 2 || pageIndex <= pageCount) {

$("#loader").show();

$.ajax({

type: "POST",

url: "CS.aspx/GetCustomers",

data: '{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>

<form id="form1" runat="server">

<table>

<tr><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>



-------------------------------------------------------

using System;

using System.Collections.Generic;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.Web.Services;

using System.Configuration;





public partial class autopageslide : System.Web.UI.Page



{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

rptCustomers.DataSource = GetCustomersData(1);

rptCustomers.DataBind();

}

}

public static DataSet GetCustomersData(int pageIndex)

{

string query = "[GetCustomersPageWise]";

SqlCommand cmd = new SqlCommand(query);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@PageIndex", pageIndex);

cmd.Parameters.AddWithValue("@PageSize", 10);

cmd.Parameters.Add("@PageCount", 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["@PageCount"].Value;

ds.Tables.Add(dt);

return ds;

}

}

}

}


[WebMethod]
public static string GetCustomers(int pageIndex)

{

return GetCustomersData(pageIndex).GetXml();

}



}

1 comment: