Ajax+Asp.Net无刷新分页
1、新建解决方案，并建立四个项目BLL，DAL,Model,PagerTest，如图所示：
2、Model代码
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace Model { public class Person { /// <summary> /// 编号 /// </summary> public int Id { get; set; } /// <summary> /// 名字 /// </summary> public string Name { get; set; } } }
3、DAL代码
SQlHelper.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data.SqlClient; using System.Data; namespace DAL { public class SQlHelper { /// <summary> /// 执行非查询 /// </summary> /// <param name="connectionString">连接字符串</param> /// <param name="cmdType">命令类型</param> /// <param name="cmdText">命令的文本</param> /// <param name="commandParameters">命令的参数</param> /// <returns>返回影响的结果集行数</returns> public static int ExcuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { PrePareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } /// <summary> /// 执行非查询 /// </summary> /// <param name="connection">数据库连接</param> /// <param name="cmdType">命令的类型</param> /// <param name="cmdText">命令的文本</param> /// <param name="commandParameters">命令的参数</param> /// <returns>返回影响的结果集行数</returns> public static int ExcuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrePareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// 执行非查询 /// </summary> /// <param name="trans">事务</param> /// <param name="cmdType">命令的类型</param> /// <param name="cmdText">命令的文本</param> /// <param name="commandParameters">命令的参数</param> /// <returns>返回影响的结果集行数</returns> public static int ExcuteNonQuery(SqlTransaction tran, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrePareCommand(cmd, tran.Connection, tran, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// 执行查询 /// </summary> /// <param name="connectionString">练级字符串</param> /// <param name="cmdType">命令的类型</param> /// <param name="cmdText">命令的文本</param> /// <param name="commandParameters">命令的参数数组</param> /// <returns>返回结果集</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connectionString); try { PrePareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return sdr; } catch { conn.Close(); throw; } } /// <summary> /// 执行查询 /// </summary> /// <param name="connectionString">练级字符串</param> /// <param name="cmdType">命令的类型</param> /// <param name="cmdText">命令的文本</param> /// <param name="commandParameters">命令的参数数组</param> /// <returns>返回结果集</returns> public static SqlDataReader ExecuteReader(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); try { PrePareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.Default); cmd.Parameters.Clear(); return sdr; } catch { throw; } } /// <summary> /// 读取数据 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="cmdType">数据命令类型</param> /// <param name="cmdText">数据命令的文本</param> /// <param name="cmd">需要被执行的数据命令</param> /// <param name="commandParameters">数据命令的参数</param> /// <returns>返回影响的结果集行数</returns> public static SqlDataReader ExecuteReader(string connectiongStr, CommandType cmdType, string cmdText, out SqlCommand cmd, params SqlParameter[] commandParameters) { cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connectiongStr); try { PrePareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return sdr; } catch { throw; } } /// <summary> /// 匹配参数 /// </summary> /// <param name="cmd">需要被执行的数据命令</param> /// <param name="conn">数据库连接</param> /// <param name="trans">事务</param> /// <param name="cmdType">数据命令类型</param> /// <param name="cmdText">数据命令的文本</param> /// <param name="cmdParms">数据命令的参数</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 param in cmdParms) { cmd.Parameters.Add(param); } } } } }
PersonDAL.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data.SqlClient; using System.Data; using System.ComponentModel; namespace DAL { public class PersonDAL { private static readonly string connStr = ConfigurationManager.ConnectionStrings["PagerTestConnectionString"].ToString(); /// <summary> /// 获取总数 /// </summary> /// <returns>返回一个总数</returns> public int GetPersonCount() { int i = 0; using (SqlDataReader sdr = SQlHelper.ExecuteReader(connStr, CommandType.Text, "select * from Person", null)) { while (sdr.Read()) { i++; } sdr.Close(); return i; } } /// <summary> /// 分页数据列表 /// </summary> /// <param name="strGetFields">需要返回的列 </param> /// <param name="fldName">排序的字段名</param> /// <param name="PageSize">页尺寸</param> /// <param name="PageIndex">页码</param> /// <param name="doCount">返回记录总数, 非 0 值则返回</param> /// <param name="OrderType">设置排序类型, 非 0 值则降序</param> /// <param name="strWhere">查询条件 (注意: 不要加 where)</param> /// <returns></returns> public BindingList<Model.Person> Getlist(string strGetFields, string fldName, int PageSize, int PageIndex, int doCount, int OrderType, string strWhere) { SqlParameter[] parameters = { new SqlParameter("@tblName", SqlDbType.VarChar, 500), new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000), new SqlParameter("@fldName", SqlDbType.VarChar, 255), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@PageIndex", SqlDbType.Int), new SqlParameter("@doCount", SqlDbType.Bit), new SqlParameter("@OrderType", SqlDbType.Bit), new SqlParameter("@strWhere", SqlDbType.VarChar,1500), }; parameters[0].Value = "dbo.Person"; parameters[1].Value = strGetFields; parameters[2].Value = fldName; parameters[3].Value = PageSize; parameters[4].Value = PageIndex; parameters[5].Value = doCount; parameters[6].Value = OrderType; parameters[7].Value = strWhere; SqlDataReader sdr= SQlHelper.ExecuteReader(connStr, CommandType.StoredProcedure, "dbo.sp_Common_GetListByPage", parameters); BindingList<Model.Person> list = new BindingList<Model.Person>(); while (sdr.Read()) { list.Add(createRpCcInfoByReader(sdr)); } return list; } /// <summary> /// 根据reader生成Person实体 /// </summary> /// <param name="reader"></param> /// <returns></returns> private Model.Person createRpCcInfoByReader(SqlDataReader reader) { Model.Person p = new Model.Person(); p.Id = Convert.ToInt32(reader["Id"]); ; p.Name = Convert.ToString(reader["Name"]); return p; } } }
4、BLL代码
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.ComponentModel; namespace BLL { public class PersonBLL { /// <summary> /// 获取总数 /// </summary> /// <returns>返回一个总数</returns> public int GetPersonCount() { DAL.PersonDAL p = new DAL.PersonDAL(); return p.GetPersonCount(); } /// <summary> /// 分页数据列表 /// </summary> /// <param name="strGetFields">需要返回的列 </param> /// <param name="fldName">排序的字段名</param> /// <param name="PageSize">页尺寸</param> /// <param name="PageIndex">页码</param> /// <param name="doCount">返回记录总数, 非 0 值则返回</param> /// <param name="OrderType">设置排序类型, 非 0 值则降序</param> /// <param name="strWhere">查询条件 (注意: 不要加 where)</param> /// <returns></returns> public BindingList<Model.Person> Getlist(string strGetFields, string fldName, int PageSize, int PageIndex, int doCount, int OrderType, string strWhere) { DAL.PersonDAL p = new DAL.PersonDAL(); return p.Getlist(strGetFields, fldName, PageSize, PageIndex, doCount, OrderType, strWhere); } } }
5、PagerTest代码,
PagerHandler.ashx
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.ComponentModel; using System.Text; namespace PagerTest.Handler { /// <summary> /// PagerHandler 的摘要说明 /// </summary> public class PagerHandler : IHttpHandler { public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; string str = string.Empty; //具体的页面数 int pageIndex; int.TryParse(context.Request["pageIndex"], out pageIndex); //页面显示条数 int size = Convert.ToInt32(context.Request["pageSize"]); if (pageIndex == 0) { pageIndex = 1; } int count; BindingList<Model.Person> list = new BLL.PersonBLL().Getlist( "*", "Id", size, pageIndex, 0, 1, "" ); StringBuilder sb = new StringBuilder(); foreach (Model.Person p in list) { sb.Append("<tr><td>"); sb.Append(p.Id.ToString()); sb.Append("</td><td>"); sb.Append(p.Name); sb.Append("</td></tr>"); } str = sb.ToString(); context.Response.Write(str); } public bool IsReusable { get { return false; } } } }
PageWeb.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="PageWeb.aspx.cs" Inherits="PagerTest.PageWeb" %> <!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="Scripts/jquery-1.4.1.js" type="text/javascript"></script> <script src="Scripts/jquery.pagination.js" type="text/javascript"></script> <link href="Style/pagination.css" rel="stylesheet" type="text/css" /> <link href="Style/tablecloth.css" rel="stylesheet" type="text/css" /> <script type="text/javascript"> var pageIndex = 0; //页面索引初始值 var pageSize = 10; //每页显示条数初始化，修改显示条数，修改这里即可 $(function() { InitTable(0); //Load事件，初始化表格数据，页面索引为0（第一页） //分页，PageCount是总条目数，这是必选参数，其它参数都是可选 $("#Pagination").pagination(<%=pageCount %>, { callback: PageCallback, prev_text: '上一页', //上一页按钮里text next_text: '下一页', //下一页按钮里text items_per_page: pageSize, //显示条数 num_display_entries: 1, //连续分页主体部分分页条目数 current_page: pageIndex, //当前页索引 num_edge_entries: 2 //两侧首尾分页条目数 }); //翻页调用 function PageCallback(index, jq) { InitTable(index); } //请求数据 function InitTable(pageIndex) { $.ajax({ type: "POST", dataType: "text", url: 'Handler/PagerHandler.ashx', //提交到一般处理程序请求数据 data: "pageIndex=" + (pageIndex + 1) + "&pageSize=" + pageSize, //提交两个参数：pageIndex(页面索引)，pageSize(显示条数) success: function(data) { $("#Result tr:gt(0)").remove(); //移除Id为Result的表格里的行，从第二行开始（这里根据页面布局不同页变） $("#Result").append(data); //将返回的数据追加到表格 } }); } }); </script> </head> <body> <div align="center"> <h1>Posrchev----无刷新分页</h1> </div> <div id="container" > <table id="Result" cellspacing="0" cellpadding="0"> <tr> <th>编号</th> <th>名称</th> </tr> </table> <div id="Pagination"></div> </div> </body> </html>
PageWeb.aspx.cs
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace PagerTest { public partial class PageWeb : System.Web.UI.Page { public string pageCount = string.Empty; //总条目数 protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { pageCount = new BLL.PersonBLL().GetPersonCount().ToString(); } } } }
运行效果：
备注：本例用了jquery.pagination.js
|
参数名 |
描述 |
参数值 |
|
maxentries |
总条目数 |
必选参数，整数 |
|
items_per_page |
每页显示的条目数 |
可选参数，默认是10 |
|
num_display_entries |
连续分页主体部分显示的分页条目数 |
可选参数，默认是10 |
|
current_page |
当前选中的页面 |
可选参数，默认是0，表示第1页 |
|
num_edge_entries |
两侧显示的首尾分页的条目数 |
可选参数，默认是0 |
|
link_to |
分页的链接 |
字符串，可选参数，默认是"#" |
|
prev_text |
“前一页”分页按钮上显示的文字 |
字符串参数，可选，默认是"Prev" |
|
next_text |
“下一页”分页按钮上显示的文字 |
字符串参数，可选，默认是"Next" |
|
ellipse_text |
省略的页数用什么文字表示 |
可选字符串参数，默认是"…" |
|
prev_show_always |
是否显示“前一页”分页按钮 |
布尔型，可选参数，默认为true，即显示“前一页”按钮 |
|
next_show_always |
是否显示“下一页”分页按钮 |
布尔型，可选参数，默认为true，即显示“下一页”按钮 |
|
callback |
回调函数 |
当点击链接的时候此函数被调用，此函数接受两个参数，新一页的id和pagination容器（一个DOM元素）。如果回调函数返回false，则pagination事件停止执行 |
Css 代码：
pagination.css
.pagination { width:300px; margin:20px 0 20px 210px; } .pagination a { text-decoration:none; border: solid 1px #AAE; color: #15B; } .pagination a, .pagination span { display: block; float: left; padding: 0.3em 0.5em; margin-right: 5px; margin-bottom: 5px; min-width: 1em; text-align: center; } .pagination .current { background: #328aa4; color: #fff; border: solid 1px #AAE; } .pagination .current.prev { color: #999;width:40px; display:block; border-color: #999; background: #fff; } .pagination .current.next { color: #999;width:40px; display:block; border-color: #999; background: #fff; }
tablecloth.css
body{ margin:0; padding:0; background:#f1f1f1; font:70% Arial, Helvetica, sans-serif; color:#555; line-height:150%; text-align:left; } h1{ font-size:140%; margin:0 20px; line-height:80px; color:White; background-color:#328aa4; } #container{ margin:0 auto; width:680px; background:#fff; padding-bottom:50px; } table, td{ font:100% Arial, Helvetica, sans-serif; } table{width:100%;border-collapse:collapse;margin:1em 0;} th, td{text-align:left;padding:.5em;border:1px solid #fff;} th{background:#328aa4 url(tr_back.gif) repeat-x;color:#fff;} td{background:#e5f1f4;} /* tablecloth styles */ tr.even td{background:#e5f1f4;} tr.odd td{background:#f8fbfc;} th.over, tr.even th.over, tr.odd th.over{background:#4a98af;} th.down, tr.even th.down, tr.odd th.down{background:#bce774;} th.selected, tr.even th.selected, tr.odd th.selected{} td.over, tr.even td.over, tr.odd td.over{background:#ecfbd4;} td.down, tr.even td.down, tr.odd td.down{background:#bce774;color:#fff;} td.selected, tr.even td.selected, tr.odd td.selected{background:#bce774;color:#555;} /* use this if you want to apply different styleing to empty table cells*/ td.empty, tr.odd td.empty, tr.even td.empty{background:#fff;}
