asp.net+ajax+json来实现无刷新分页功能

2019独角兽企业重金招聘Python工程师标准>>>

现在做网站就是尽可能的提高用户体验，用户浏览网站尽可能不要刷新，响应速度尽可能的快，就是加载速度不好，也要告诉用户你已经很努力的在加载了(loading.....),下面来看看如何实现此功能

USE [WineShop]
GO
/****** Object:  StoredProcedure [dbo].[GetRecordFromPage2005]    Script Date: 06/26/2012 13:52:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetRecordFromPage2005]
  @fieldlist nvarchar(200) = '*',
  @datasrc nvarchar(200),
  @filter nvarchar(200) = '',
  @orderBy nvarchar(200),
  @pageNum int = 1,
  @pageSize int = NULL
AS
  SET NOCOUNT ON
  DECLARE
     @STMT nvarchar(max)         -- SQL to execute
    ,@recct int                  -- total # of records (for GridView paging interface)

  IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1'
  IF @pageSize IS NULL BEGIN
    SET @STMT =  'SELECT   ' + @fieldlist + 
                 'FROM     ' + @datasrc +
                 'WHERE    ' + @filter + 
                 'ORDER BY ' + @orderBy
    EXEC (@STMT)                 -- return requested records 
  END ELSE BEGIN
    SET @STMT =  'SELECT   @recct = COUNT(*)
                  FROM     ' + @datasrc + '
                  WHERE    ' + @filter
    EXEC sp_executeSQL @STMT, @params  = N'@recct INT OUTPUT', @recct = @recct OUTPUT
    --SELECT @recct AS recct       -- return the total # of records

    DECLARE
      @lbound int,
      @ubound int

    SET @pageNum = ABS(@pageNum)
    SET @pageSize = ABS(@pageSize)
    IF @pageNum < 1 SET @pageNum = 1
    IF @pageSize < 1 SET @pageSize = 1
    SET @lbound = ((@pageNum - 1) * @pageSize)
    SET @ubound = @lbound + @pageSize + 1
    IF @lbound >= @recct BEGIN
      SET @ubound = @recct + 1
      SET @lbound = @ubound - (@pageSize + 1) -- return the last page of records if                                               -- no records would be on the
                                              -- specified page
    END
    SET @STMT =  'SELECT  ' + @fieldlist + '
                  FROM    (
                            SELECT  ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, *
                            FROM    ' + @datasrc + '
                            WHERE   ' + @filter + '
                          ) AS tbl
                  WHERE
                          row > ' + CONVERT(varchar(9), @lbound) + ' AND
                          row < ' + CONVERT(varchar(9), @ubound)
    EXEC (@STMT)                 -- return requested records 
  END

上面的分页的存储过程

后台方法我们应该如何调用呢

存储过程参数说明： 

fieldlist：你要显示的字段，datasrc:表名，filter：过滤条件，orderBy：排序，pageNum：页数，pagesize：每页显示几条记录

 public static IList<WineComment> WineCommentQueryAll(int pageindex, string orderfiled)
    {
        using (SqlConnection cn = new SqlConnection(SQLHelp.Conn))
        {
            cn.Open();
            string sql = "GetRecordFromPage2005";
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@fieldlist", "commentid,commentuser,commenttext,commenttime");
            cmd.Parameters.AddWithValue("@datasrc", "tb_WineComment");
            cmd.Parameters.AddWithValue("@filter", " ");
            cmd.Parameters.AddWithValue("@orderBy", orderfiled);
            cmd.Parameters.AddWithValue("@pageNum", pageindex);
            cmd.Parameters.AddWithValue("@pagesize", 5);
            SqlDataReader dr = cmd.ExecuteReader();
            IList<WineComment> list = new List<WineComment>();
            while (dr.Read())
            {
                WineComment wc = new WineComment();
                if (dr["CommentID"] != DBNull.Value)
                {
                    wc.CommentID = (int)dr["CommentID"];
                }
                if (dr["CommentUser"] != DBNull.Value)
                {
                    wc.CommentUser = (string)dr["CommentUser"];
                }
                if (dr["CommentText"] != DBNull.Value)
                {
                    wc.CommentText = (string)dr["CommentText"];
                }
                if (dr["CommentTime"] != DBNull.Value)
                {
                    //wc.CommentTime = (DateTime)dr["CommentTime"];
                    wc.CommentTime = ((DateTime)dr["CommentTime"]).ToString("yyyy-MM-dd HH:mm:ss");
                }
                list.Add(wc);
            }
            dr.Close();
            return list;
        }
    }

前台页面的js代码首先要引用

    <script src="js/jquery.1.3.2.js" type="text/javascript"></script>
    <script src="jqueryPager/jquery.pagination.js" type="text/javascript"></script>
    <link href="jqueryPager/pagination.css" rel="stylesheet" type="text/css" />

 

<script type="text/javascript"> 

$().ready(function() {
     InitWineComment(0);               
  });
        function pageselectCallback(page_id, jq) {
            InitWineComment(page_id);
        }
        function InitWineComment(pageindx)
        {
            var tbody = "";         
            $.ajax({
               type: "GET",
               dataType:"json",   //返回的格式
               url:"handler/handler.ashx",  //处理的页面
               data: { action: "GetComments", p: (pageindx+1)},  //附带的参数
               beforeSend:function(){$("#divload").show();$("#Pagination").hide();},
               complete:function(){$("#divload").hide();$("#Pagination").show()},
               success:function(json) {           
                    $("#comment tr:gt(0)").remove();
                    $.each(json, function(index, array) {//解析json
                        var trs = "";
                        trs += "<tr><td>" + ((index+1)+5*(pageindx-0))+ "</td><td>"   +array['CommentUser'] + "</td><td>" + array['CommentText'] +                        "</td><td>"+array['CommentTime']                       +                        "</td></tr>";                           
                        tbody += trs;
                    });
                    $("#comment").append(tbody);
                    $("#comment tr:gt(0):odd").attr("class", "odd");
                    $("#comment tr:gt(0):even").attr("class", "enen");                                            
                    $("#comment tr:gt(0)").hover(function(){
                        $(this).addClass('mouseover');
                    },function(){
                        $(this).removeClass('mouseover');
                    });
                }});
           
                $("#Pagination").pagination(<%=pagecount %>, {//pagecount为评论的总条数
                    callback: pageselectCallback, //页数改变触发的事件
                    prev_text: '上一页',
                    next_text: '下一页',
                    items_per_page:5,  //每页显示5条
                    num_display_entries:6,
                    current_page:pageindx,  //当前页
                    num_edge_entries:2
                });
        }      

</script>

Handler.ashx处理页面的代码为

 case "GetComments":
                int pi=int.Parse(context.Request.QueryString["p"].ToString());
                string isorder = "commentid desc";
                if (pi == 0)
                    pi = 1;
                IList<WineComment> comment= WineCommentManager.WineCommentQueryAll(pi,isorder);
                Content = JavaScriptConvert.SerializeObject(comment);
                break;

context.Response.Write(Content);

 

前台页面的html的代码很简单

 

<div class="box">
   <div class="box-title">网友评论</div>
   <div id="divload" style="top: 50%; right: 50%; position:absolute; padding: 0px; margin: 0px; z-index: 999">
       <img src="image/spinner3-greenie.gif" />
   </div>
   <div>
       <table cellpadding="5" cellspacing="1" id="comment">
            <tr>
                <th style="width: 40px">ID</th>
                <th style="width: 100px"> 评论者</th>
                <th style="width: 500px">评论内容</th>
                <th style="width: 200px">评论时间</th>
            </tr>
       </table>
   </div>
   <div id="Pagination" class="digg">
   </div>
</div>

运行效果如下：

转载于:https://my.oschina.net/hellokitty/blog/63840