-- =============================================
-- Description: 分页,用到了ROW_NUMBER()
-- =============================================
create PROCEDURE [dbo].[proc_SplitPage]
@tblName varchar(255), -- 表名
@strFields varchar(1000) = '*', -- 需要返回的列,默认*
@strOrder varchar(255)='', -- 排序的字段名,必填
@strOrderType varchar(10)='ASC', -- 排序的方式,默认ASC
@PageSize int = 10, -- 页尺寸,默认10
@PageIndex int = 1, -- 页码,默认1
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL nvarchar(4000)
if @strWhere !=''
set @strWhere=' where '+@strWhere
set @strSQL=
'SELECT *,(select count(1) from '+@tblName+') as counts FROM ('+
'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)
print @strSQL
EXEC sp_executesql @strSQL
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace repeaterDAO
{
public class MessageDAO
{
private SQLHelper sqlHelper = null;
private SqlCommand cmd = null;
public MessageDAO()
{
sqlHelper = new SQLHelper(); /* SQLHelper 助手类*/
}
/*
* 获取分页数据
* tblName:表名 strOrder:以哪个字段排序 strOrderType:数据显示顺序类型如desc
* PageSize:一页显示的数据数目 PageIndex当前页码索引 strWhere:查询条件
*/
<pre name="code" class="csharp"> public List<AirBtc.Entity.Sys_News> GetList(string tblName, string strOrder, string strOrderType, int PageSize, int PageIndex, string strWhere)
{
List<AirBtc.Entity.Sys_News> list = new List<AirBtc.Entity.Sys_News>();
string cmdText = "proc_SplitPage";
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@tblName", tblName),
new SqlParameter("@strOrder", strOrder),
new SqlParameter("@strOrderType", strOrderType),
new SqlParameter("@PageSize", PageSize),
new SqlParameter("@PageIndex", PageIndex),
new SqlParameter("@strWhere", strWhere)
};
using (SqlDataReader sdr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, cmdText, paras))
{
while (sdr.Read())
{
list.Add(DataReaderToEntity(sdr));
list.Capacity = Convert.ToInt32(sdr["counts"]);
}
};
return list;
}
增加anp控件方法: vs20008/vs2010 左侧->工具箱->常规->选项->浏览->选择AspNetPage.dll
把控件拉入前台页面如:default.aspx 想要显示AspNetPage处。
AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication2._Default" %>
<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>
<!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>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Repeater ID="rep" runat="server">
<HeaderTemplate>
<table>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td>
<%# Eval("newsTitle") %> <!-- 数据列绑定 -->
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
<webdiyer:AspNetPager ID="anp" runat="server" onpagechanged="anp_PageChanged"> <!-- AspNetPage控件 -->
</webdiyer:AspNetPager>
</div>
</form>
</body>
</html>
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using AirBtc.Entity;
using AirBtc.BLL;
using System.Collections.Generic;
namespace WebApplication2
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
RepBind();
}
}
/* repeater控件绑定方法 */
private void RepBind()
{
Sys_News_Rule rule=Sys_News_Rule.GetInstance();
List<AirBtc.Entity.Sys_News> list = rule.GetList("News", "newsAddTime", "desc", anp.PageSize, anp.CurrentPageIndex, "");
rep.DataSource = list;
rep.DataBind();
anp.RecordCount = Convert.ToInt32(list.Capacity);
}
/// <summary>
/// 分页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void anp_PageChanged(object sender, EventArgs e)
{
RepBind();
}
}}
以上是非常原始的样式,非常难看,所以,如果要达到良好的用户体验,还得加上以下的样式代码。
<img src="http://images.cnblogs.com/cnblogs_com/zengxiangzhan/WindowsLiveWriter/AspNetPager_111AB/ok_3.png" alt="曾祥展" />
/*拍拍风格*/
.paginator { font: 11px Arial, Helvetica, sans-serif;padding:10px 20px 10px 0; margin: 0px;}
.paginator a {padding: 5px 10px; border: solid 1px #ddd; background: #fff; text-decoration: none;margin-right:2px}
.paginator a:visited {padding: 1px 6px; border: solid 1px #ddd; background: #fff; text-decoration: none;}
.paginator .cpb {padding: 1px 6px;font-weight: bold; font-size: 13px;border:none}
.paginator a:hover {color: #fff; background: #ffa501;border-color:#ffa501;text-decoration: none;}
/*淘宝风格*/.paginator { font: 12px Arial, Helvetica, sans-serif;padding:10px 20px 10px 0; margin: 0px;}
.paginator a {border:solid 1px #ccc;color:#0063dc;cursor:pointer;text-decoration:none;}
.paginator a:visited {padding: 1px 6px; border: solid 1px #ddd; background: #fff; text-decoration: none;}
.paginator .cpb {border:1px solid #F50;font-weight:700;color:#F50;background-color:#ffeee5;}
.paginator a:hover {border:solid 1px #F50;color:#f60;text-decoration:none;}
.paginator a,.paginator a:visited,.paginator .cpb,.paginator a:hover {float:left;height:16px;line-height:16px;min-width:10px;_width:10px;margin-right:5px;text-align:center; white-space:nowrap;font-size:12px;font-family:Arial,SimSun;padding:0 3px;}
在分页控件应用这个paginator样式就可以了。UrlPaging="True",用url传值会好一点,个人觉得,因为就算不用它也会autoPostBlack,一样会跳动。另外,要使得这个控件实现无刷新的效果,就必须将他放置在updatePannel里面。暂时没能找到更好的方法。但是发现很多都是没有实现无刷新的,那就先这样吧。
补充:
在实践过程中,发现有一个页面,当自己点击第二页的时候,虽然地址栏上面是第二页的值,但是页面数据没有发生改变。通过调试,发现CurrentPageIndex仍为第一页,
后来用expertMore_PageChanging事件的e.NewPageIndex才解决。
/// <summary>
/// 分页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void expertMore_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
{
this.expertMore.CurrentPageIndex = e.NewPageIndex;
DataBind();
}
实现Ajax的关键是,设置AspNetPager的UrlPaging=false。天呐!搞了好久,也没个人告诉我一下,浪费了一天几个小时的时间。
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="subjectAdmin.aspx.cs" Inherits="WebUI.SystemPage.subjectAdmin" %>
<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>
<!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>
<link href="../commonCss/common.css" rel="stylesheet" type="text/css" />
<link href="css/subjectAdmin.css" rel="stylesheet" type="text/css" />
<link href="../artDialog4.1.6/skins/simple.css" rel="stylesheet" type="text/css" />
<script src="js/jquery-1.7.2.min.js" type="text/javascript"></script>
<script src="js/aubjectAdmin.js" type="text/javascript"></script>
<script src="../artDialog4.1.6/artDialog.source.js" type="text/javascript"></script>
<script src="../artDialog4.1.6/plugins/iframeTools.source.js" type="text/javascript"></script>
</head>
<body>
<form id="form1" runat="server">
<div id="list" class="box">
<a href="javascript:void(0)"><div>添加科目</div></a>
</div>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<div style="width:100%; margin-top:10px;" class="box">
<table id="subjectTabel" style="width:100%; border-collapse:collapse; border:1px solid #71B90B" border="1">
<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate>
<tr style="background-color:#71B90B; color:White; font-weight:bold;">
<th>编号</th>
<th>科目名称</th>
<th>操作</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><%#Eval("SID") %></td>
<td><%#Eval("SName") %></td>
<td>dsf</td>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" CssClass="paginator" CustomInfoHTML="共%PageCount%页,当前为第%CurrentPageIndex%页,每页%PageSize%条"
ShowCustomInfoSection="Right"
CustomInfoSectionWidth="100%"
onpagechanged="AspNetPager1_PageChanged" ToolTip="分页按钮,点击即可分页">>
</webdiyer:AspNetPager>
</div>
</ContentTemplate>
</asp:UpdatePanel>
</form>
</body>
</html>