Efficient paging implementation code based on Jquery+ajax+json

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 ("<span class= ' disabled ' > first page </span>"); 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 ("<span class= ' disabled ' > Prev </span>"); 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 = $ ("<span class= ' current ' >" + j + "</span>"); $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 ("<span class= ' disabled ' > next page </span>"); } 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 ("<span class= ' disabled ' > last page </span>"); } 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