jQuery getJSON() + .ashx 实现分页

来源：互联网发布：深港通数据编辑：程序博客网时间：2024/04/29 01:37

参考了：http://www.cnblogs.com/xingshao/archive/2009/09/11/1564938.html

改进的地方：

1、ashx返回json数据，减少传输数据量，html页面样式控制也比较灵活；

2、改写html页的jQuery代码；

3、把3个ashx文件简化为1个。

一、创建表的测试数据：

create table test(id int identity,title varchar(36))
declare @index int;
set @index = 1;
while(@index < 8888)
begin
insert test(title) values (newid())
set @index = @index + 1
end

二、.html页

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title></title>
     <script type="text/javascript" src="jquery-1.4.2.min.js"></script>
     <script type="text/javascript">
         $(function() {
             Init();
         });
         function Init() {
             $("#Content").html("");
             $("#pageIndex").val(0);
             $("#pageInfo").append("当前第1页");
             $.getJSON("Handler.ashx", { type: 'first' }, function(data) {
                 $("#Content").append("<tr><th style＝'width:130px'>id</th><th style＝'width:150px'>title</th></tr>");
                 $.each(data, function(i) {
                     $("#Content").append("<tr><td>" + data[i].id + "</td><td>" + data[i].title + "</td></tr>");
                 })
             })
         }
         function Pre() {
             var currIndex = Number($("#pageIndex").val()) - 1;
             Go('pre', currIndex);
         }
         function Next() {
             var currIndex = Number($("#pageIndex").val()) + 1;
             Go('next', currIndex);
         }
         function Go(type, index) {
             $("#Content").html("");
             $("#pageInfo").html("");
             if (index == 0 || index == -1) { Init(); return; }
             $.getJSON("Handler.ashx", { type: type, index: index }, function(data) {
                 $("#Content").append("<tr><th style＝'width:130px'>id</th><th style＝'width:150px'>title</th></tr>");
                 $.each(data, function(i) {
                     $("#Content").append("<tr><td>" + data[i].id + "</td><td>" + data[i].title + "</td></tr>");
                 })
                 $("#pageInfo").append("当前第 " + (index + 1) + " 页");
                 $("#pageIndex").val(index);
             });
         }
     </script>
</head>
<body>
    <form id="form1" runat="server">
        <div style="width: 100%">
           <table id="Content" >
           </table>
        </div>
        <div id="PagePanel" style="margin-left:20px">
            <label id="pageInfo"></label>
            <a href="#" onclick="Pre()">上一页</a>   
            <a href="#" onclick="Next()">下一页</a>
        </div>
        <input type="hidden" value="0" id="pageIndex" />
    </form>
</body>
</html>

三、.ashx页

public class Handler : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            StringBuilder tb = new StringBuilder();
            DataBase db = new DataBase();
            int pageSize = 10;
            int pageIndex = 0;

            string type = context.Request.Params["type"];
            switch (type)
            {
                case "first":
                    DataTable dt1 = db.GetDataSet("select top 10 * from test", null).Tables[0];
                    tb.Append(Common.DataTableToJSON(dt1, true)); //DataTable转为JSON
                    break;
                case "next":
                    pageIndex = Convert.ToInt32(context.Request.Params["index"]);
                    DataTable dt2 = db.GetDataSet("select top " + pageSize.ToString() + " * from test where id> (select max(id) from (select top " + (pageSize * pageIndex).ToString() + " id from test) t)", null).Tables[0];
                    tb.Append(Common.DataTableToJSON(dt2, true));
                    break;
                case "pre":
                    pageIndex = Convert.ToInt32(context.Request.Params["index"]);
                    DataTable dt3 = db.GetDataSet("select top " + pageSize.ToString() + " * from test where id> (select max(id) from (select top " + (pageSize * pageIndex).ToString() + " id from test) t)", null).Tables[0];
                    tb.Append(JSONHelper.DataTableToJSON(dt));
                    break;
            }

            context.Response.Write(tb.ToString());
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }

四、效果

idtitle1112937A34-8EFA-4703-946A-FDE12BC3F5AB12DAF9D2AC-3BEE-4BF6-955A-CA57EC2D128113F9DB3478-AE84-42AB-9A49-F02363EEECE8145E67126F-20B8-40BF-9E38-A45665E2B247156F25D12F-CF24-43B0-A1A6-ED917B8466C8169464242D-B0C6-407C-8779-4C15BD64E47417AB7A7DB3-A117-41B4-8EF2-00F01ACF1058189A332F56-9C23-45B1-B49B-4E62095870ED19829D803D-5288-4B71-AB35-3C6A32352F2920307E8ABA-D975-4F01-AC4F-25F50A4CA52A

--------------------------------------------------------------------------------------------------------------------

备注(2010-7-10)：

用sql2005 row_number()分页方法，.ashx页面代码可简化为

public class Handler : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";

            DataBase db = new DataBase();
            int pageSize = 10;
            int pageIndex = 0;
            int.TryParse(context.Request.Params["index"], out pageIndex);
            string type = context.Request.Params["type"];

            string sql = string.Format("select * from ( select row_number() over (order by id) as rowNum,* from test) as t "
                + " where rowNum>{0} and rowNum<={1}", pageIndex * pageSize, (pageIndex+1) * pageSize);

            DataTable dt = db.GetDataSet(sql, null).Tables[0];

            context.Response.Write(JSONHelper.DataTableToJSON(dt));
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }

备注：

其中JSONHelper.DataTableToJSON(dt)方法为DataTable解析成JSON，见另一篇文章JSONHelper 帮助类