一、使用JQuery插件pagination实现AJax无刷新分页:
以下只列出主要代码及注释,详细请参考最后源码:
我们需要用到的文件有:jquery.js,jquery.pagination.js,pagination.css
以及用于将DataTable转换为Json的插件Newtonsoft.Json.Net20.dll,文件在包含在最下面的源文件中
分页存储过程:
create PROCEDURE [dbo].[proc_FenYe] |
@strFields varchar (1000) = '*' , |
@strOrder varchar (255)= '' , |
@strOrderType varchar (10)= 'ASC' , |
@strWhere varchar (1500) = '' |
declare @strSQL varchar (5000) |
set @strWhere= ' where ' +@strWhere |
'SELECT ROW_NUMBER() OVER (ORDER BY ' +@strOrder+ ' ' +@strOrderType+ ') AS pos,' +@strFields+ ' ' + |
'FROM ' +@tblName+ ' ' +@strWhere+ |
') AS sp WHERE pos BETWEEN ' +str((@PageIndex-1)*@PageSize+1)+ ' AND ' +str(@PageIndex*@PageSize) |
数据访问层主要函数:取出分页数据及计算总条数(主要看传的什么参数即可)
/// <summary>分页获取数据列表(所有字段+类名)</summary> |
/// <param name="order">排序字段</param> |
/// <param name="ordertype">排序类型:desc或asc</param> |
/// <param name="PageSize">页大小</param> |
/// <param name="PageIndex">页索引</param> |
/// <param name="strWhere">条件</param> |
public DataSet GetList( string order, string ordertype, int PageSize, int PageIndex, string strWhere) |
Database db = DatabaseFactory.CreateDatabase(); |
DbCommand dbCommand = db.GetStoredProcCommand( "proc_FenYe" ); |
db.AddInParameter(dbCommand, "tblName" , DbType.AnsiString, "News" ); |
db.AddInParameter(dbCommand, "strFields" , DbType.AnsiString, "*" ); |
db.AddInParameter(dbCommand, "PageSize" , DbType.Int32, PageSize); |
db.AddInParameter(dbCommand, "PageIndex" , DbType.Int32, PageIndex); |
db.AddInParameter(dbCommand, "strOrder" , DbType.String, order); |
db.AddInParameter(dbCommand, "strOrderType" , DbType.String, ordertype); |
db.AddInParameter(dbCommand, "strWhere" , DbType.AnsiString, strWhere); |
return db.ExecuteDataSet(dbCommand); |
/// <summary>计算记录数</summary> |
/// <param name="strWhere">条件</param> |
public int CalcCount( string strWhere) |
string sql = "select count(1) from News" ; |
if (! string .IsNullOrEmpty(strWhere)) |
sql += " where " + strWhere; |
Database db = DatabaseFactory.CreateDatabase(); |
DbCommand dbCommand = db.GetSqlStringCommand(sql); |
return int .Parse(db.ExecuteScalar(dbCommand).ToString()); |
下面为主要代码:
1、页面引入JS及CSS
< script src = "js/jquery-1.4.2.js" type = "text/javascript" ></ script > |
< script src = "js/jquery.pagination.js" type = "text/javascript" ></ script > |
< link href = "Css/pagination.css" rel = "stylesheet" type = "text/css" /> |
< script src = "js/NewsAjaxPager.js" type = "text/javascript" ></ script > |
2、页面Html代码
< ul id = "PageContain" class = "newslist" > |
< li id = "loadImg" style = " width:100%; height:60px;text-align:center; margin-top:50px;" >< img src = "images/loading1.gif" ></ li > |
< div id = "Pagination" class = "digg" ></ div > |
3、一般处理程序页面GetNewsList.ashx,根据条件取出分页数据及总条数
<%@ WebHandler Language= "C#" Class= "GetNewsList" %> |
/// 获取新闻列表,用于列表页AJAX分页及总条数 |
public class GetNewsList : IHttpHandler |
DAl.NewsDAO newsDao = new DAl.NewsDAO(); |
DAl.ProductCategoryDAO catDao = new DAl.ProductCategoryDAO(); |
public void ProcessRequest(HttpContext context) |
context.Response.ContentType = "text/plain" ; |
string action = context.Request[ "action" ]; |
string order = context.Request[ "order" ].ToString().Trim(); |
string orderType = context.Request[ "orderType" ].ToString().Trim(); |
string pageSizeStr = context.Request[ "pageSize" ].ToString().Trim(); |
int .TryParse(pageSizeStr, out pageSize); |
string pageIndexStr = context.Request[ "pageIndex" ].ToString().Trim(); |
int .TryParse(pageIndexStr, out pageIndex); |
DataTable dt = newsDao.GetList(order, orderType, pageSize, pageIndex, StrWhere(context)).Tables[0]; |
string jsonDate = Common.WebHelper.DataTableToJSON(dt, "ListData" ); |
context.Response.Write(jsonDate); |
else if (action == "pageCount" ) |
context.Response.Write(newsDao.CalcCount(StrWhere(context))); |
private string StrWhere(HttpContext context) |
string strWhere = "IsShow='1'" ; |
string Id = context.Request[ "cid" ].ToString().Trim(); |
if (! string .IsNullOrEmpty(Id)&&Common.WebHelper.IsPosInt(Id)) |
catDao.GetAllChildCatId(Id, ref Id); |
strWhere += "and CategoryID in (" + Id + ")" ; |
DataTable转换为Json的函数
using System.Collections.Generic; |
using System.Security.Cryptography; |
using System.Text.RegularExpressions; |
using System.Web.UI.WebControls; |
/// <summary>网站帮助类</summary> |
/// <summary>将DateTable转为Json格式数据</summary> |
/// <param name="dt">要转换的DataTable名称</param> |
/// <param name="dtName">应该是转换后的Key如用Json.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.WritePropertyName(dtName); |
foreach (DataRow dr in dt.Rows) |
foreach (DataColumn dc in dt.Columns) |
jw.WritePropertyName(dc.ColumnName); |
ser.Serialize(jw, dr[dc].ToString()); |
4、★主要的JS代码:(Ajax取出分页数据、总条数、绑定分页控件,讲分页数据HTML输出到前台)
var CatId = GetQueryString( "cid" ); |
url: "Handler/GetNewsList.ashx" , |
data: "action=pageCount&cid=" + CatId, |
success: function (data) { |
InitData(CatId, "CreateDate" , "Desc" , 20, 0, pageCount); |
function pageselectCallback(page_id, jq) { |
InitData(CatId, "CreateDate" , "Desc" , 20, page_id, pageCount); |
function InitData(CatId, order, ordertype, PageSize, pageindx, pageCount) { |
url: "Handler/GetNewsList.ashx" , |
data: "action=pager&cid=" + CatId + "&order=" + order + "&orderType=" + ordertype + "&pageSize=" + PageSize + "&pageIndex=" + (pageindx + 1), |
beforeSend: function () { $( "#loadImg" ).show(); $( "#Pagination" ).hide() }, |
complete: function () { $( "#loadImg" ).hide(); $( "#Pagination" ).show() }, |
success: function (json) { |
var listDate = json.ListData; |
$.each(listDate, function (i, n) { |
html += "<li class=\"b\"><span class=\"title\"><a href=\"newscontent.aspx?id=" + n.Id + "%>\" class=\"typelink\" target=\"_blank\">" + n.LongTitle + "</a></span><span class=\"date\">" + n.CreateDate + "</span></li>" ; |
$( "#PageContain" ).html(html); |
$( "#Pagination" ).pagination(pageCount, { |
callback: pageselectCallback, |
items_per_page: PageSize, |
![](https://i-blog.csdnimg.cn/blog_migrate/a92ea22c997bda3da372ab033a150d77.png)
实例完整源码:源码与上面所写稍有不同
jQueryPager(JQuery分页插件pagination实现Ajax分页).rar
二、使用aspnetpager分页控件实现分页:
控件下载及演示地址:http://www.webdiyer.com/
存储过程和数据访问层所用的代码和上面的一样
1、添加AspNetPager控件到Vs控件工具箱,方便以后随时调用(如这里添加到“常规”一栏)
点击“常规”→右键“选择项”→“.Net Framework组件”→“浏览”→选择下载文件中的“AspNetPager.dll”
添加成功就会在常规工具箱出现“AspNetPager”控件了
2、引用AspNetPager控件(方法和其他一样,拖动到指定位置即可)
讲分页控件拖动到页面时头部就会自动添加
<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>
分页区域,控件代码:(可在属性栏设置相应属性)
<webdiyer:aspnetpager CssClass= "pages" CurrentPageButtonClass= "cpb" ID= "anp" |
CustomInfoHTML= "当前第%CurrentPageIndex%/%PageCount%页,每页%PageSize%条,共%RecordCount%条" |
FirstPageText= "|<<" LastPageText= ">>|" |
PageIndexBoxType= "TextBox" PageSize= "20" ShowCustomInfoSection= "Left" |
ShowPageIndexBox= "Never" NumericButtonCount= "5" |
LayoutType= "Div" CustomInfoClass= "pages_left" onpagechanged= "anp_PageChanged" |
CustomInfoSectionWidth= "40%" UrlPaging= "True" > |
3、显示分页数据
这里用repeater绑定数据
<asp:Repeater ID= "repList" runat= "server" > |
<a href= "newscontent.aspx?id=<%#Eval(" Id ")%>" class = "typelink" target= "_blank" ><%#Eval( "LongTitle" )%></a></span> |
<span class = "date" ><%#Eval( "CreateDate" )%></span> |
后台.cs代码
using System.Collections.Generic; |
using System.Web.UI.WebControls; |
public partial class newslist : System.Web.UI.Page |
DAl.NewsDAO newsDao = new DAl.NewsDAO(); |
protected void Page_Load( object sender, EventArgs e) |
anp.RecordCount = newsDao.CalcCount( "" ); |
/// <summary>绑定列表数据</summary> |
private void repListBind() |
repList.DataSource = newsDao.GetList( "CreateDate" , "desc" , anp.PageSize, anp.CurrentPageIndex, "" ); |
protected void anp_PageChanged( object sender, EventArgs e) |
A:分页之前必须计算数据总条数,并赋值给AspNetPager的RecordCount属性
B:将分页控件的PageSize(页大小)属性、CurrentPageIndex(当前页索引)属性,传递给提取分页数据的函数
C:设置分页控件的 onpagechanged 事件,即当页码改变时,重新绑定分页数据列表