前台代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="AjaxPage.aspx.cs" Inherits="XML操作.AjaxPage" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="jquery-3.2-vsdoc2.js" type="text/javascript"></script>
<script src="jquery.pagination.js" type="text/javascript"></script>
<link href="pagination.css" rel="stylesheet" type="text/css" />
<script language="javascript" type="text/javascript">
var pageIndex = 0; //页面索引初始值
var pageSize = 15; //每页显示条数初始化，修改显示条数，修改这里即可
$(function () {
InitTable(0); //Load事件，初始化表格数据，页面索引为0（第一页）
//分页，PageCount是总条目数，这是必选参数，其它参数都是可选
$("#Pagination").pagination(<%=pcount%>, {
callback: PageCallback, //PageCallback() 为翻页调用次函数。
prev_text: "« 上一页",
next_text: "下一页 »",
items_per_page:pageSize,
num_edge_entries: 2, //两侧首尾分页条目数
num_display_entries: 6, //连续分页主体部分分页条目数
current_page: pageIndex, //当前页索引
});
//翻页调用
function PageCallback(index, jq) {
InitTable(index);
}
//请求数据
function InitTable(pageIndex) {
$.ajax({
type: "POST",
dataType: "text",
url: 'AjaxPage.ashx', //提交到一般处理程序请求数据
data: "pageIndex=" + (pageIndex) + "&pageSize=" + pageSize, //提交两个参数：pageIndex(页面索引)，pageSize(显示条数)
success: function(data) {
$("#Result tr:gt(0)").remove(); //移除Id为Result的表格里的行，从第二行开始（这里根据页面布局不同页变）
$("#Result").append(data); //将返回的数据追加到表格
}
});
}
}); </script>
</head> <body>
<form id="form1" runat="server">
<table id="Result" cellspacing="0" cellpadding="0">
<tr>
<th>商品编号</th>
<th>商品名称</th>
</tr>
</table>
<div id="Pagination" class="right flickr"></div>
<%-- 分页使用的样式（可选）
<div id="Pagination" class="meneame"></div>
<div id="Pagination" class="black"></div>
<div id="Pagination" class="quotes"></div>
<div id="Pagination" class="scott"></div> --%>
</form>
</body>
</html>
后台代码：
protected string pcount=string.Empty; //总条数
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
pcount = SQLHelper.GetDataSet("select count(*) from K_SysModuleNode").Rows[][].ToString();
}
}
Ajax处理程序：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;
using KingTop.Common;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Web.Script.Serialization; namespace XML操作
{
/// <summary>
/// AjaxPage1 的摘要说明
/// </summary>
public class AjaxPage1 : IHttpHandler
{ public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
String str = string.Empty; if (context.Request["pageIndex"] != null && context.Request["pageIndex"].ToString().Length > )
{
int pageIndex; //具体的页面数
int.TryParse(context.Request["pageIndex"], out pageIndex);
if (context.Request["pageSize"] != null && context.Request["pageSize"].ToString().Length > )
{
//页面显示条数
string pageSize = context.Request["pageSize"].ToString();
string data = BindSource(pageSize,pageIndex.ToString());
context.Response.Write(data);
context.Response.End();
}
}
} public bool IsReusable
{
get
{
return false;
}
} #region 绑定
/// <summary>
/// 剧院之窗列表
/// </summary>
private string BindSource(string pageSize, string pageIndex)
{
string sql = string.Format("select NodeID,NodeCode,NodeName from K_SysModuleNode");
int pagesize = int.Parse(pageSize);
int pageindex = int.Parse(pageIndex)+; string order = " NodeCode desc ";
SqlParameter[] sqlparam = new SqlParameter[] {
new SqlParameter("@NewPageIndex",pageindex),
new SqlParameter("@PageSize",pagesize),
new SqlParameter("@order",order),
new SqlParameter("@strSql",sql)
};
StringBuilder text = new StringBuilder();
DataSet ds = SQLHelper.ExecuteDataSet(SQLHelper.ConnectionStringLocalTransaction,
CommandType.StoredProcedure, "proc_Pager", sqlparam);
if (ds != null && ds.Tables.Count > )
{
foreach (DataRow row in ds.Tables[].Rows)
{
text.Append("<tr><td>");
text.Append(row["NodeID"]);
text.Append("</td><td>");
text.Append(row["NodeCode"]);
text.Append("</td><td>");
text.Append(row["NodeName"]);
text.Append("</td></tr>");
} }
return text.ToString(); }
}
#endregion
}
所使用的方法：
/// <summary>
/// 分页存储过程
/// </summary>
/// <param name="connectionString">连接语句</param>
/// <param name="cmdType">sql语句类型</param>
/// <param name="cmdText">存储过程名称</param>
/// <param name="commandParameters">参数名</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
DataSet ds = new DataSet();
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
return ds;
}
catch
{
conn.Close();
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">SqlCommand object</param>
/// <param name="conn">SqlConnection object</param>
/// <param name="trans">SqlTransaction object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">SqlParameters to use in the command</param>
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
} cmd.Connection = conn;
cmd.CommandText = cmdText; if (trans != null)
{
cmd.Transaction = trans;
} cmd.CommandType = cmdType; if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
分页存储过程：
ALTER PROC [dbo].[proc_Pager]
@NewPageIndex int,--当前页码
@PageSize int,--分页条数
@order varchar(64),
@strSql nvarchar(max) --sql语句
as
DECLARE @str varchar(8000)
BEGIN
SET @str = 'SELECT COUNT(1) FROM ('+@strSql+')T'
SET @str = @str+' SELECT * FROM (SELECT row_number() over(order by '+@order+') as Rownum,* FROM('+@strSql+')T1)T '
END
SET @str=@str+' where Rownum between '+cast((@NewPageIndex-1)*@PageSize+1 as varchar(20))+' and '+cast(@NewPageIndex*@PageSize as varchar(20))
--IF LEN(@order)<>0
--SET @str=@str+' order by '+@order
EXEC(@str)