Efficient paging implementation code based on Jquery Ajax Json_jquery

If you have never used these things, I believe that reading this blog post will be helpful to you. If you have any questions or bugs, please feel free to contact me at any time.
Experts are also welcome Give me some advice, I don't recommend growing up among trolls.
My QQ: 364175837
Foreword
I believe many friends have used it. Jquery’s paging plug-in. I used jquery.paper before. If you are interested, you can leave it on QQ and I will send you a simple example. Source code for you.
This code was completed in a hurry at night, so it was not optimized very much, but it was mainly used as an example to combine the comprehensive application of this knowledge. Okay, without further ado, let’s get straight to the code.
vs2010 sql2005express
Text
First we create a general handler to read the contents of the database and get the return value.
Create a file, GetData.ashx.
I am using a stored procedure here , the stored procedure will be pasted below. As for the data, it is just an example. You can read the data according to your needs
The code is as follows:

Copy the code The code is as follows:

<%@ 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";
//Determine whether the current index exists, and if it does not exist, get the total number of records.
if (string.IsNullOrEmpty(pageIndex))
{
//Sql statement to get 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 getting data according to 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 = "";//Conditional statement
parms[5].Value = "ID";//Sort field
List list = new List();
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());//Convert to 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; }
}
　

Display Data page----Asynchronous request to obtain data, create page Show.htm based on jquery

Copy code The code is as follows:

/*Display data content*/

/*Show paging bar*/

js code

Copy code The code is as follows:

$(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 starts from 1
//Index the current page Convert to int type
var intPageIndex = parseInt(pageIndex);
//Get the table displaying data
var table = $("#content");
//Clear the content in the table
$("#content tr").remove();
//Add content to the table
for (var i = 0; i < data.length; i ) {
table.append (
$(""
data[i].Title
""
data[i].Auhor
""
data[i].PublishDate
""
data[i].ISBN
"< /td>")
);
} //for
//Create paging
//Get the total number of pages from the total number of records
var pageS = total
if (pageS % 10 == 0) pageS = pageS / 10;
else pageS = parseInt(total / 10) 1;
var $pager = $("#pager");
//Clear the content in the paging div
$("#pager span").remove();
$("#pager a").remove();
//Add the first page
if (intPageIndex == 1)
$pager.append("first page");
else {
var first = $("First page").click(function () {
PageClick($(this ).attr('first'), total, spanInterval);
return false;
});
$pager.append(first);
}
//Add the previous page
if (intPageIndex == 1)
$pager.append("Previous page");
else {
var pre = $("Previous page").click(function () {
PageClick($(this).attr('pre'), total, spanInterval);
return false;
});
$pager.append(pre);
}
/ /Set the paging format. Here you can complete the results you want according to your needs
var interval = parseInt(spanInterval); //Set the interval
var start = Math.max(1, intPageIndex - interval); //Set Start page
var end = Math.min(intPageIndex interval, pageS)//Set the 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 number
for (var j = start; j < end 1; j ) {
if (j == intPageIndex) {
var spanSelectd = $("" j "");
$pager.append(spanSelectd);
} //if
else {
var a = $("" j "").click(function () {
PageClick($(this).text( ), total, spanInterval);
return false;
});
$pager.append(a);
} //else
} //for
//on One page
if (intPageIndex == total) {
$pager.append("next page");
}
else {
var next = $("Next page").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 = $("Last page" ).click(function () {
PageClick($(this).attr("last"), total, spanInterval);
return false;
});
$pager.append( last);
}
} //sucess
}); //ajax
}; //function
}); //ready

pagination Styles----If you are interested, I have more than 20 sets of paging styles here, you can leave QQ

Copy codeThe code is as follows:

　分页存储过程---PAGINATION

复制代码 代码如下:

CREATE PROCEDURE [dbo].[PAGINATION]
@FEILDS VARCHAR(1000),--要显示的字段
@PAGE_INDEX INT,--当前页码
@PAGE_SIZE INT,--页面大小
@ORDERTYPE BIT,--当为0时 则为 desc 当为1 时 asc
@ANDWHERE VARCHAR(1000)='',--where语句 不用加where
@ORDERFEILD VARCHAR(100) --排序的字段
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 --当页码是第一页时直接运行，提高速度
BEGIN
IF @ANDWHERE=''
SET @EXECSQL = 'SELECT TOP ' STR(@PAGE_SIZE) ' ' @FEILDS ' FROM [books] ' @ORDERBY
ELSE
SET @EXECSQL = 'SELECT TOP ' STR(@PAGE_SIZE) ' ' @FEILDS ' FROM [books] WHERE ' @ANDWHERE ' ' @ORDERBY
END
ELSE
BEGIN
IF @ANDWHERE=''
BEGIN --以子查询结果当做新表时 要给表名别名才能用
SET @EXECSQL = 'SELECT TOP' STR(@PAGE_SIZE) ' ' @FEILDS ' FROM [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 ' FROM [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)--这里要加括号
END

运行效果图

补充：

终于，大功告成，不容易啊！有几个地方忘记给注释说明了，大家可能不理解。

PageClick(1, total, 3); 这个函数，第一个参数是当前页码，第一调用为第一页，这个不用管，total：表示总记录数,第三个参数表示：当前索引和旁边个间隔几页

OK,今天到此为止，第一次写东东，写的不好，技术含量也有限，忘读此博文者见谅。