Code for efficient paging Based on Jquery + Ajax + Json

Paging I believe everyone is familiar with the paging of stored procedures, and ajax is even more familiar, let alone our json, and so on.
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:

<% @ 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 connectionStr Ing = @ "Data Source = KUSE \ SQLEXPRESS; Initial Catalog = bookshop; Integrated Security = True"; // determines whether the current index does not exist. If not, the total number of records is obtained. If (string. isNullOrEmpty (pageIndex) {// SQL statement for retrieving 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 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 the fields parms [1]. value = pageIndex; // the current page index parms [2]. value = 10; // page size parms [3]. value = 0; // parms [4] in ascending order. value = ""; // Condition Statement parms [5]. value = "ID"; // List of sorting Fields
 
  
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

 
 
 
 
  
  
/* Display data content */
 
  
/* Display pagination bar */
 

Js 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 starts from 1 // convert the current page index to int type var intPageIndex = parseInt (pageIndex ); // obtain the table var table of the displayed data =$ ("# 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 +"");} // For // create a page // obtain 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 pagination p $ ("# pager span "). remove (); $ ("# pager "). 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 page format. Here you can complete the expected result var interval = parseInt (spanInterval) as needed; // set the interval var start = Math. max (1, intPageIndex-interval); // set the start page var end = Math. min (intPageIndex + interval, pageS) // set 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 the 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 // 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);} // The 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.

Paging stored procedure --- PAGINATION

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 the value is 0, it is desc. When the value is 1, asc @ andwhere varchar (1000) = ''. -- The where statement does not need to add where @ orderfeild varchar (100) -- Sort the 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, increase the speed of 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 -- use the subquery result as a new table only the table name alias can be 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 exec (@ EXECSQL) -- END must be enclosed in parentheses.

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

For more articles about efficient paging implementation code based on Jquery + Ajax + Json, refer to PHP Chinese website!