Efficient paging implementation code based on Jquery + Ajax + Json _ jquery-js tutorial

Paging I believe that everyone is familiar with the paging of stored procedures, and ajax is even more familiar, let alone our json. If you haven't used these things, I believe this blog post will be helpful to you. If you have any questions you don't understand or have bugs, feel free to contact me,
At the same time, you are also welcome to give more advice. I do not recommend that you grow up in the spray.
My QQ: 364175837
Preface
I believe many of my friends have used Jquery's paging plug-in. I used jquery. paper before. If you are interested, leave QQ. I will send you a simple source code of the instance.
This code was completed in a rush at night, so it was not optimized, but it was mainly used as an example to combine this knowledge. Okay, let's talk about the code.
Vs2010 + sql2005express
Body
First, we create a general processing program to read the content in the database and obtain the returned value.
Create a file, GetData. ashx.
I am using a stored procedure, and the stored procedure will be stuck below. As for data, it is only an instance. You can read data as needed.
The Code is as follows:

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 = KUSE \ SQLEXPRESS; Initial Catalog = bookshop; Integrated Security = True ";
// Determine whether the current index does not exist. If not, obtain the total number of records.
If (string. IsNullOrEmpty (pageIndex ))
{
// Obtain 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 based on 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 = "*"; // obtain all fields
Parms [1]. Value = pageIndex; // index of the current page
Parms [2]. Value = 10; // page size
Parms [3]. Value = 0; // sort in ascending order
Parms [4]. Value = ""; // Condition Statement
Parms [5]. Value = "ID"; // sorting 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 for data, based on jquery
Create a page show.htm

The Code is as follows:

/* Display data content */

/* Display pagination bar */

Js 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
// Convert the index on the current page to the int type
Var intPageIndex = parseInt (pageIndex );
// Obtain the table with the displayed data
Var table = $ ("# content ");
// Clear the table content
$ ("# 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
+"")
);
} //
// Create a page
// Obtain the total page number based on 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 page p
$ ("# 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 a 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 page format. You can complete the desired results as needed.
Var interval = parseInt (spanInterval); // set the interval
Var start = Math. max (1, intPageIndex-interval); // set the start page
Var end = Math. min (intPageIndex + interval, pageS) // sets 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 ();
} // Else
} //
// Previous 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

Paging Style ---- if you are interested, I have more than 20 sets of paging Styles, you can leave QQ

The Code is as follows:

Paging stored procedure --- PAGINATION

The Code is as follows:

Create procedure [dbo]. [PAGINATION]
@ Feilds varchar (1000), -- the field to be displayed
@ PAGE_INDEX INT, -- current page number
@ PAGE_SIZE INT, -- page size
@ Ordertype bit, -- if it is 0, it is desc. If it is 1, asc
@ Andwhere varchar (1000) = '', -- where statement does not need to add where
@ Orderfeild varchar (100) -- Sort Field
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 -- run directly when the page number is the first page, improving the speed
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 -- when the subquery result is used as a new table, the table name alias must be used.
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) -- parentheses are added here
END

Run

Supplement:

Finally, it's not easy! You may not understand the notes.

PageClick (1, total, 3); the first parameter of this function is the current page number, and the first call is the first page. this parameter is not required. total: indicatesTotal number of records, The third parameter indicates the interval between the current index and the next page.

OK. So far today, I wrote Dongdong for the first time. I am not good at writing and have limited technical skills. I am sorry to forget to read this blog.