AJAX+JQUERRY实现分页

============== Windows Phone 7手机开发、.Net培训、期待与您交流！ =============

JQERRY代码如下：

var where = "";
var ajaxurl = "";

$().ready(function () {
    var indexs = 1;

    if ($("#hdwhere").val() == null) {

    }
    else {
        where = $("#hdwhere").val();
    }
    ajaxurl = "ashx/Pages.ashx";
    Init(indexs);
    $(".UpPage").click(function () {
        if (indexs > 1) {
            indexs--
        }
        Init(indexs);
    });
    $(".DownPage").click(function () {
        if (indexs < $("#lbPages").text()) {//判断小于总页数
            indexs++;
        }
        Init(indexs);
    });
    $(".FirstPage").click(function () {
        Init(1);
    });
    $(".LastPage").click(function () {
        Init($("#lbPages").text());
    });
});
function Init(ind) {
    $.ajax({
        type: "GET",
        dataType: "json",
        url: ajaxurl, //目标地址(页面代码会在下面呈上)
        data: { "pageIndex": ind, "where": where }, //要发送的数据 
        beforeSend: function () {
            //            alert("准备发送");
        },
        success: function (json) {
            var result = json.ShowData;
            var tbody = "";
            //            $(".block_topic_content").html("");
            var $str;
            $(".block_topic_content").remove();
            $.each(result, function (i, n) {
                alert(i);
                $str = $("<tr class='block_topic_content'><td class='persontablerow'>" + n.EmployeeID + "</td><td class='persontablerow'>" + n.FName + "</td><td class='persontablerow'>" + n.FDepartName + "</td><td class='persontablerow'>" + n.FPosition + "</td><td class='persontablerow'>" + n.FPhoneNum + "</td><td class='persontablerow'><a href='#'>查看</a> | <a href='#'>修改</a> | <a href='#'>删除</a></td></tr>");
                $(".block_topic_title").after($str);
            });
            $("#lbNowPage").text(ind);
        },
        complete: function (data, textStatus) {
            //HideLoading();
        },
        error: function (data, textStatus) {
            //请求出错处理
        }
    });
}

HTML页面：

<table width="96%" border="0" cellspacing="0" cellpadding="0">
  <tr class="block_topic_title">
    <td width="15%" class="persontable">员工编号</td>
    <td width="15%" class="persontable">姓名</td>
    <td width="15%" class="persontable">部门</td>
    <td width="15%" class="persontable">职务</td>
    <td width="20%" class="persontable">联系方式</td>
    <td width="20%" class="persontable">操作</td>
  </tr>
  <tr>
    <td class="persontablebottom" colspan="6><div style="float:left;margin-left:20px;"><a href="javascript:void(0);" class="FirstPage">首页</a></div><div style="float:left;margin-left:20px;"><a href="javascript:void(0);" class="UpPage">上一页</a></div><div style="float:left;margin-left:20px;"><a href="javascript:void(0);" class="DownPage">下一页</a></div><div style="float:left;margin-left:20px;"><a href="javascript:void(0);" class="LastPage">尾页</a></div></td>
  </tr>
</table>

ashx页面代码：

private string constr = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["OADBConnectionString"].ConnectionString;//数据库连接字符串
        string where = "";
        public void ProcessRequest(HttpContext context)
        {
            //去掉页面缓存
            context.Response.Buffer = true;
            context.Response.ExpiresAbsolute = DateTime.Now.AddDays(-1);
            context.Response.AddHeader("pragma", "no-cache");
            context.Response.AddHeader("cache-control", "");
            context.Response.CacheControl = "no-cache";
            context.Response.ContentType = "text/plain";
            context.Response.Charset = "UTF-8";

            where = context.Request.Params["where"].ToString();
            int pageindex = string.IsNullOrEmpty(context.Request.Params["pageIndex"].ToString()) ? 1 : Convert.ToInt32(context.Request.Params["pageIndex"].ToString());

            DataSet ds = GetList(pageindex, where);
            string jsonData = DataTableToJSON(ds.Tables[0], "ShowData");
            //输入json格式数据
            context.Response.Write(jsonData);
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        private DataSet GetList(int pageindex, string where)
        {
            //创建数据库连接池
            SqlConnection co = new SqlConnection(constr);
            //打开连接池
            co.Open();
            SqlCommand commands;
            commands = new SqlCommand("select count(*) from T_Persons", co);
            //数据总记录数
            int totalcounts = Convert.ToInt32(commands.ExecuteScalar());
            //自定义每页大小为5条数据
            int pagesize = Convert.ToInt32(ConfigurationManager.AppSettings["pagesize"].ToString()); ;
            //总记录数
            int totalpages;
            if (totalcounts % pagesize > 0)
                totalpages = totalcounts / pagesize + 1;
            else
                totalpages = totalcounts / pagesize;
            if (pageindex > totalpages)
                pageindex = totalpages;
            DataSet ds = new DataSet();
            commands = new SqlCommand("P_Page " + pageindex + ", " + pagesize + ", 'FDepartName=" + where + "','T_Persons', true,'EmployeeID'", co);
            SqlDataAdapter adapter = new SqlDataAdapter(commands);
            adapter.Fill(ds, "ds");
            co.Dispose();
            return ds;
        }
        /// <summary>
        /// 数据表转换成JSON字符
        /// </summary>
        /// <param name="dt">数据表对象</param>
        /// <param name="dtName">数据表名称</param>
        public static string DataTableToJSON(DataTable dt, string dtName)
        {
            StringBuilder sb = new StringBuilder();
            StringWriter sw = new StringWriter(sb);

            using (JsonWriter jw = new JsonTextWriter(sw))
            {
                JsonSerializer ser = new JsonSerializer();
                jw.WriteStartObject();
                jw.WritePropertyName(dtName);
                jw.WriteStartArray();

                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        jw.WriteStartObject();

                        foreach (DataColumn dc in dt.Columns)
                        {
                            jw.WritePropertyName(dc.ColumnName);
                            
                            ser.Serialize(jw, dr[dc].ToString());

                        }

                        jw.WriteEndObject();
                    }
                }

                jw.WriteEndArray();
                jw.WriteEndObject();

                sw.Close();
                jw.Close();

            }

            return sb.ToString();
        }

注意JSON数据处理引用的是：Newtonsoft.Json.dll。

分页存储过程：

ALTER PROCEDURE [dbo].[P_Page] 
( 
    @startIndex INT,        --当前页码
    @pageSize INT,            --每页多少条数据
    @strSql varchar(5000),    ---查询条件不用加where,例：id>10
    @TableName varchar(50),    --表名
    @DoCount AS bit=1,        -- 0值返回记录总数, 非 0 值则返回记录 
    @keyword varchar(50)    --排序字段
) 
AS 
begin tran 
    IF @DoCount=0 
        Goto GetCount 
    Else 
    Goto GetSearch 

    GetCount: --返回记录总数 
        DECLARE @SearchSql AS Nvarchar(4000) 
        SET @SearchSql= 'SELECT Count(*) AS Total FROM '+@TableName+' WHERE ' +@strsql
        exec sp_executesql @SearchSql 
        --print @SearchSql 
        COMMIT TRAN 
        return 
    GetSearch: --返回记录 
        DECLARE @SqlQuery varchar(4000) 
        SET @SqlQuery='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY O.'+@keyword+' ) Row, * from '+@TableName+' O Where '+@strsql+') as temp WHERE Row BETWEEN '+cast((@startIndex-1)*@pageSize+1 as varchar) +' and '+cast(@startIndex*@pageSize as varchar)+' and '+ @strsql +' order by '+@keyword + ' desc'
        --print @SqlQuery 
        execute(@SqlQuery) 
        COMMIT TRAN

=============== Windows Phone 7手机开发、.Net培训、期待与您交流！ ==================