基于Jquery+Ajax+Json+高效分页

朴灿烈づ我的快乐病毒、 2022-01-06 15:39 248阅读 0赞

首先我们创建一般处理程序，来读取数据库中内容，得到返回值.

创建文件，GetData.ashx.

我这里是用的存储过程，存储过程会再下面粘出来，至于数据只是实例，你们可根据需求自行读取数据

**代码如下**：

<%@ 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;
    publicclass GetData : IHttpHandler {
       
    
        publicvoid 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";
            //判断当前索引存不存在，如果不存在则获取记录的总数。
    if (string.IsNullOrEmpty(pageIndex))
            {
       
                //获取查询记录总数的sql语句
    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();
            }
                //当根据索引获取数据
    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 ="*";//获取所有的字段
                parms[1].Value = pageIndex;//当前页面索引
                parms[2].Value =10;//页面大小
                parms[3].Value =0;//升序排列
                parms[4].Value ="";//条件语句
                parms[5].Value ="ID";//排序字段
                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格式
            }
        }
    
        publicbool IsReusable {
       
            get {
       
                returnfalse;
            }
        }
    
    }
    
    publicclass Book
    {
       
    
        publicstring Title { get; set; }
    
        publicstring Auhor { get; set; }
    
        publicstring PublishDate { get; set; }
    
        publicstring ISBN { get; set; }
    }

**显示数据页面----异步请求获取数据，基于jquery**

创建页面Show.htm

<body>

<div >

<table id="content">/\*显示数据内容\*/

</table>

<div id="pager" class="yahoo2"></div>/\*显示分页条\*/

</div>

</body>

**js代码**

$(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) {
       
    
                            //索引从1开始
                            //将当前页索引转为int类型
                            var intPageIndex = parseInt(pageIndex);
    
                            //获取显示数据的表格
                            var table = $("#content");
                            //清楚表格中内容
                            $("#content tr").remove();
    
                            //向表格中添加内容
    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
    
                            //创建分页
                            //将总记录数结果 得到 总页码数
                            var pageS = total
                            if (pageS %10==0) pageS = pageS /10;
                            else pageS = parseInt(total /10) +1;
                            var $pager = $("#pager");
    
                            //清楚分页div中的内容
                            $("#pager span").remove();
                            $("#pager a").remove();
    
                            //添加第一页
    if (intPageIndex ==1)
                                $pager.append("<span class='disabled'>第一页</span>");
                            else {
       
                                var first = $("<a href='javascript:void(0)' first='"+1+"'>第一页</a>").click(function () {
       
                                    PageClick($(this).attr('first'), total, spanInterval);
                                    returnfalse;
                                });
                                $pager.append(first);
                            }
    
    
                            //添加上一页
    if (intPageIndex ==1)
                                $pager.append("<span class='disabled'>上一页</span>");
                            else {
       
                                var pre = $("<a href='javascript:void(0)' pre='"+ (intPageIndex -1) +"'>上一页</a>").click(function () {
       
                                    PageClick($(this).attr('pre'), total, spanInterval);
                                    returnfalse;
                                });
                                $pager.append(pre);
                            }
    
                            //设置分页的格式  这里可以根据需求完成自己想要的结果
                            var interval = parseInt(spanInterval); //设置间隔
                            var start = Math.max(1, intPageIndex - interval); //设置起始页
                            var end = Math.min(intPageIndex + interval, pageS)//设置末页
    
                            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);
    
                            }
    
    
                            //生成页码
    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);
                                        returnfalse;
                                    });
                                    $pager.append(a);
                                } //else
                            } //for
    
                            //上一页
    if (intPageIndex == total) {
       
                                $pager.append("<span class='disabled'>下一页</span>");
    
                            }
                            else {
       
    
                                var next = $("<a href='javascript:void(0)' next='"+ (intPageIndex +1) +"'>下一页</a>").click(function () {
       
                                    PageClick($(this).attr("next"), total, spanInterval);
                                    returnfalse;
                                });
                                $pager.append(next);
                            }
    
                            //最后一页
    if (intPageIndex == pageS) {
       
                                $pager.append("<span class='disabled'>最后一页</span>");
    
                            }
                            else {
       
                                var last = $("<a href='javascript:void(0)' last='"+ pageS +"'>最后一页</a>").click(function () {
       
                                    PageClick($(this).attr("last"), total, spanInterval);
                                    returnfalse;
                                });
                                $pager.append(last);
                            }
    
                        } //sucess
    
                    }); //ajax
    
                }; //function
    
            });   //ready

**分页样式---**

**分页存储过程---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

**运行效果图**

![2011082822564495.jpg][]

**补充：**

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

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

转载自博文

[![20150108120130.png][]][20150108120130.png 1]

[欧西][20150108120130.png 1]  
[关注 - 1][- 1]  
[粉丝 - 33][- 33]

请勿用于商业用途，仿冒必究，谢谢

转载于:https://www.cnblogs.com/fannylovo/p/4842435.html

[2011082822564495.jpg]: /images/20211227/4401ebce423b41ac9a20420302c4516b.png
[20150108120130.png]: /images/20211227/b3d685ca8f2c4decb23a26f340b3797f.png
[20150108120130.png 1]: http://home.cnblogs.com/u/Jusoc/
[- 1]: http://home.cnblogs.com/u/Jusoc/followees
[- 33]: http://home.cnblogs.com/u/Jusoc/followers