Wednesday, December 18, 2013

Paging by ajax in c# with Jquery.

/////////USER INTERFACE//////////////
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Testpaging.aspx.cs" Inherits="Testpaging" %>

<!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 src="ASPSnippets_Pager.min.js" type="text/javascript"></script>

<script type="text/javascript">

$(function () {



GetCustomers(1);

});
  $(".Pager .page").live("click", function () {

GetCustomers(parseInt($(this).attr('page')));



});
  function GetCustomers(pageIndex) {





$.ajax({
  type: "POST",

url: "Testpaging.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);

var customers = xml.find("Customers");

var 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).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*=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())



});

};
  </script>

<style type="text/css">

body



{
  font-family: Arial;

font-size: 10pt;



}


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

</head>

<body>

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

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" RowStyle-BackColor="#A1DCF2"

HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White">

<Columns>

<asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="CustomerID" />

<asp:BoundField ItemStyle-Width="150px" DataField="ContactName" HeaderText="Contact Name" />

<asp:BoundField ItemStyle-Width="150px" DataField="City" HeaderText="City" />

</Columns>

</asp:GridView>

<br />

<div class="Pager"></div>

</form>

</body>

</html>




///////Code Behind //////////////
using System;

using System.Data;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Web.Services;

using System.Configuration;

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



{
  private static int PageSize = 10;

protected void Page_Load(object sender, EventArgs e)



{
  if (!IsPostBack)



{

BindDummyRow();

}

}
  private void BindDummyRow()



{
  DataTable dummy = new DataTable();

dummy.Columns.Add("CustomerID");

dummy.Columns.Add("ContactName");

dummy.Columns.Add("City");



dummy.Rows.Add();

gvCustomers.DataSource = dummy;

gvCustomers.DataBind();

}
  [WebMethod]

public static string GetCustomers(int pageIndex)



{
  string query = "[GetCustomers_Pager]";

SqlCommand cmd = new SqlCommand(query);

cmd.CommandType = CommandType.StoredProcedure;

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["STVConnectionString"].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;



}

}

}

}

} 

///////////////////////
see below "ASPSnippets_Pager.min.js"
//////////////////////////

function ASPSnippetsPager(a, b) {




  var c = '<a style = "cursor:pointer" class="page" page = "{1}">{0}</a>';

var d = "<span>{0}</span>";

var e, f, g; var g = 5;

var h = Math.ceil(b.RecordCount / b.PageSize);

if (b.PageIndex > h) { b.PageIndex = h }

var i = "";

if (h > 1) {



f = h > g ? g : h;

e = b.PageIndex > 1 && b.PageIndex + g - 1 < g ? b.PageIndex : 1;
  if (b.PageIndex > g % 2)



{
  if (b.PageIndex == 2)



f = 5;
  else



f = b.PageIndex + 2

}
  else {



f = g - b.PageIndex + 1

}
  if (f - (g - 1) > e)



{

e = f - (g - 1)

}
  if (f > h) {



f = h;

e = f - g + 1 > 0 ? f - g + 1 : 1

}
  var j = (b.PageIndex - 1) * b.PageSize + 1;

var k = j + b.PageSize - 1;

if (k > b.RecordCount)



{

k = b.RecordCount

}
  i = "<b>Records " + (j == 0 ? 1 : j) + " - " + k + " of " + b.RecordCount + "</b> ";

if (b.PageIndex > 1) {

i += c.replace("{0}", "<<").replace("{1}", "1");

i += c.replace("{0}", "<").replace("{1}", b.PageIndex - 1)



}
  for (var l = e; l <= f; l++) {

if (l == b.PageIndex) {

i += d.replace("{0}", l)

} else {

i += c.replace("{0}", l).replace("{1}", l)



}

}
  if (b.PageIndex < h) {

i += c.replace("{0}", ">").replace("{1}", b.PageIndex + 1);

i += c.replace("{0}", ">>").replace("{1}", h)



}

}

a.html(i);
  try {

a[0].disabled = false



}
  catch (m) { }



}
  (function (a) {

a.fn.ASPSnippets_Pager = function (b) {

var c = {};

var b = a.extend(c, b);

return this.each(function () {

ASPSnippetsPager(a(this), b) }



) }

}

)(jQuery); 

/////////////////////////
tblCustomer table
///////////////////////

create table [tblCustomer]([CustomerID] int identity(1,1)

,[CompanyName] varchar(250)

,[ContactName] varchar(250)

,[City] varchar(250))

 ///////////////////////
Procedure  "GetCustomers_Pager"
///////////////////
CREATE PROCEDURE [dbo].[GetCustomers_Pager]

@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]=FirstName

,[City]

INTO #Results

FROM [tblCustomer]



 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
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)