Efficient paging implementation code based on Jquery+ajax+json

Last Update:2017-01-13 Source: Internet

Author: User

Tags prev

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

The code is as follows

Copy Code

<%@ WebHandler language= "C #" class= "GetData"%>
Using System;
Using System.Web;
Using System.Data.SqlClient;
Using System.Data;
Using System.Collections.Generic;
Using System.Web.Script.Serialization;
public class Getdata:ihttphandler {
public void ProcessRequest (HttpContext context) {
Context. Response.ContentType = "Text/plain";
var PageIndex = context. request["PageIndex"];
String connectionString = @ "Data source=kusesqlexpress;initial catalog=bookshop;integrated security=true";
Determines whether the current index exists, and if it does not exist, gets the total number of records.
if (string. IsNullOrEmpty (PageIndex))
{
Get the SQL statement for the total number of query records
String sql = "SELECT count ( -1) from books";
int count = 0;
Int. TryParse (Sqlhelper.executescalar (connectionString, System.Data.CommandType.Text, SQL, NULL). ToString (), out count);
Context. Response.Write (count);
Context. Response.End ();
}
When data is obtained from the index
Else
{
int currentpageindex = 1;
Int. TryParse (PageIndex, out CurrentPageIndex);
sqlparameter[] parms = new sqlparameter[] {
New SqlParameter ("@FEILDS", sqldbtype.nvarchar,1000),
New SqlParameter ("@PAGE_INDEX", sqldbtype.int,10),
New SqlParameter ("@PAGE_SIZE", sqldbtype.int,10),
New SqlParameter ("@ORDERTYPE", sqldbtype.int,2),
New SqlParameter ("@ANDWHERE", sqldbtype.varchar,1000),
New SqlParameter ("@ORDERFEILD", sqldbtype.varchar,100)
};
Parms[0]. Value = "*";//Get all fields
PARMS[1]. Value = pageindex;//Current page index
PARMS[2]. Value = 10;//Page size
PARMS[3]. Value = 0;//Ascending order
PARMS[4]. Value = "";//Condition statement
PARMS[5]. Value = "ID";//Sort field
list<book> list = new list<book> ();
using (SqlDataReader SDR = Sqlhelper.executereader (connectionString, CommandType.StoredProcedure, "pagination", parms ))
{
while (SDR. Read ())
{
List. ADD (new book {Title = sdr[2]. ToString (), Auhor = sdr[2]. ToString (), publishdate = Sdr[4]. ToString (), ISBN = Sdr[5]. ToString ()});
}
}
Context. Response.Write (New JavaScriptSerializer (). Serialize (list). ToString ());//JSON format
}
}
public bool IsReusable {
get {
return false;
}
}
}
public class Book
{
public string Title {get; set;}
public string Auhor {get; set;}
public string Publishdate {get; set;}
public string ISBN {get; set;}
}

Show Page

The code is as follows	Copy Code
<body> <div > <table id= "Content" >/* Display data content * * </table> <div id= "Pager" class= "Yahoo2" ></div>/* display the paging bar * * </div> </body>

JS Code

The code is as follows

Copy Code

$ (function () {
$.post ("Getdata.ashx", NULL, function (data) {
var total = data;
Pageclick (1, Total, 3);
});
Pageclick = function (PageIndex, total, spaninterval) {
$.ajax ({
URL: "Getdata.ashx",
Data: {"PageIndex": PageIndex},
Type: "Post",
DataType: "JSON",
Success:function (data) {
Index starting from 1
Convert current page index to int type
var intpageindex = parseint (PageIndex);
Get a table showing data
var table = $ ("#content");
Clear the contents of the table
$ ("#content tr"). Remove ();
Add content to a table
for (var i = 0; i < data.length; i++) {
Table.append (
$ ("<tr><td>" +
Data[i]. Title
+ "</td><td>" +
Data[i]. Auhor
+ "</td><td>" +
Data[i]. Publishdate
+ "</td><td>" +
Data[i]. Isbn
+ "</td></tr>")
);
}//for
Create a paging
Total number of total records results
var PageS = Total
if (pages% = 0) pages = PAGES/10;
else PageS = parseint (TOTAL/10) + 1;
var $pager = $ ("#pager");
Clear the contents of the page div
$ ("#pager span"). Remove ();
$ ("#pager a"). Remove ();
Add first page
if (Intpageindex = 1)
$pager. Append (" first page ");
else {
var first = $ ("<a href= ' javascript:void (0) ' first= '" + 1 + "' > Page One </a>"). Click (function () {
Pageclick ($ (this). attr (' a '), total, spaninterval);
return false;
});
$pager. Append (a);
}
Add Previous Page
if (Intpageindex = 1)
$pager. Append (" Prev ");
else {
var pre = $ ("<a href= ' javascript:void (0) ' pre= '" + (intPageIndex-1) + "' > Prev </a>"). Click (function () {
Pageclick ($ (this). attr (the ' pre '), total, spaninterval);
return false;
});
$pager. Append (pre);
}
Format pagination Here's what you want to do with your needs.
var interval = parseint (spaninterval); Set interval
var start = Math.max (1, intpageindex-interval); Set Start Page
var end = Math.min (Intpageindex + interval, PageS)/set last page
if (Intpageindex < interval + 1) {
End = (2 * interval + 1) > PageS? PageS: (2 * interval + 1);
}
if ((Intpageindex + interval) > PageS) {
Start = (PageS-2 * interval) < 1? 1: (pageS-2 * interval);
}
Generate page numbers
for (var j = start, J < end + 1; j + +) {
if (j = = Intpageindex) {
var spanselectd = $ ("" + j + "");
$pager. Append (SPANSELECTD);
}//if
else {
var a = $ ("<a href= ' javascript:void (0) ' >" + j + "</a>"). Click (function () {
Pageclick ($ (this). Text (), total, spaninterval);
return false;
});
$pager. Append (a);
}//else
}//for
Previous page
if (Intpageindex = total) {
$pager. Append (" next page ");
}
else {
var next = $ ("<a href= ' javascript:void (0) ' next= '" + (Intpageindex + 1) + "' > Next </a>"). Click (function () {
Pageclick ($ (this). attr ("Next"), Total, spaninterval);
return false;
});
$pager. Append (next);
}
Last page
if (Intpageindex = = PageS) {
$pager. Append (" last page ");
}
else {
var last = $ ("<a href= ' javascript:void (0) ' last= '" + pages + "' > Final page </a>"). Click (function () {
Pageclick ($ (this). attr (' last '), total, spaninterval);
return false;
});
$pager. append (last);
}
}//sucess
}); Ajax
}; function
}); Ready

SQL statement

The code is as follows

Copy Code

CREATE PROCEDURE [dbo]. [Pagination]
@FEILDS VARCHAR (1000),--the field to display
@PAGE_INDEX INT,--Current page number
@PAGE_SIZE INT,--Page size
@ORDERTYPE BIT,--when 0 is desc when 1 o'clock ASC
@ANDWHERE VARCHAR (1000) = ',--where statement without adding where
@ORDERFEILD VARCHAR (100)--sorted fields
As
DECLARE @EXECSQL VARCHAR (2000)
DECLARE @ORDERSTR VARCHAR (100)
DECLARE @ORDERBY VARCHAR (100)
BEGIN
Set NOCOUNT on
IF @ORDERTYPE = 1
BEGIN
SET @ORDERSTR = ' > (SELECT MAX ([' + @ORDERFEILD + ']) '
SET @ORDERBY = ' ORDER BY ' + @ORDERFEILD + ' ASC '
End
ELSE
BEGIN
SET @ORDERSTR = ' < (SELECT MIN ([' + @ORDERFEILD + ']) '
SET @ORDERBY = ' ORDER BY ' + @ORDERFEILD + ' DESC '
End
If @PAGE_INDEX = 1--Runs directly when the page number is the first page, increasing speed
BEGIN
IF @ANDWHERE = '
SET @EXECSQL = ' SELECT top ' +str (@PAGE_SIZE) + ' + @FEILDS + ' to [books] ' + @ORDERBY
ELSE
SET @EXECSQL = ' SELECT top ' +str (@PAGE_SIZE) + ' + @FEILDS + ' from [books] WHERE ' + @ANDWHERE + ' + @ORDERBY
End
ELSE
BEGIN
IF @ANDWHERE = '
BEGIN--alias to table name to be used when the subquery results as a new table
SET @EXECSQL = ' SELECT top ' +str (@PAGE_SIZE) + ' + @FEILDS + ' to [books] WHERE ' + @ORDERFEILD +
@ORDERSTR + ' from (SELECT top ' +str (@PAGE_SIZE * (@PAGE_INDEX-1)) + ' + @ORDERFEILD +
' From [books] ' + @ORDERBY + ') as TEMP ' + @ORDERBY
End
ELSE
BEGIN
SET @EXECSQL = ' SELECT top ' +str (@PAGE_SIZE) + ' + @FEILDS + ' to [books] WHERE ' + @ORDERFEILD +
@ORDERSTR + ' from (SELECT top ' + STR (@PAGE_SIZE * (@PAGE_INDEX-1)) + ' + @ORDERFEILD +
' From [books] WHERE ' + @ANDWHERE + ' + @ORDERBY + ') as TEMP) and ' + @ANDWHERE + ' + @ORDERBY
End
End
EXEC (@EXECSQL)--Here are parentheses
End

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More