新建用户控件pageno.ascx
Code<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="PageNo.ascx.cs" Inherits="Web.Admin.UserControl.PageNo1" %>
总记录数:
<asp:Label ID="txt_pageCount" runat="server"></asp:Label>
页码:<asp:Label ID="txt_begin" runat="server"></asp:Label>/<asp:Label ID="txt_pageAll" runat="server"></asp:Label>
<asp:HyperLink ID="hyFirst" runat="server">首页</asp:HyperLink>
<asp:HyperLink ID="hyLast" runat="server">上一页</asp:HyperLink>
<asp:HyperLink ID="hyNext" runat="server">下一页</asp:HyperLink>
<asp:HyperLink ID="hyFoot" runat="server">末页</asp:HyperLink>
<asp:TextBox ID="txt_PageNum" runat="server" Width="30px" onkeypress="if(((event.keyCode>=48)&&(event.keyCode <=57))) {event.returnValue=true;} else{event.returnValue=false;}"></asp:TextBox>
<asp:LinkButton ID="btn_go" runat="server" OnClick="btn_go_Click">GO</asp:LinkButton>
后台pageno.ascx.cs
Codeusing System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
namespace Web.Admin.UserControl
{
public partial class PageNo1 : System.Web.UI.UserControl
{
private string _where;
private string _strurl; //链接字符串
private int _pagecount; //每页的记录数
private int _pageindex; //当前页
private int _intcount; //总记录数
private int _pagesize = 12; //每页显示记录数量
public int _intUpPage; //上一页
public int _intNextPage; //下一页
public int _intLastPage; //末页
private string _fldName; //排序字段
private string _strGetFields; //返回列
private string _table; //查询的表
//public string strFromTo;
protected void Page_Load(object sender, EventArgs e)
{
}
/// <summary>
/// where条件
/// </summary>
public string Where
{
get { return _where; }
set { _where = value; }
}
/// <summary>
/// 查询的表
/// </summary>
public string Table
{
get { return _table; }
set { _table = value; }
}
/// <summary>
/// 当前页
/// </summary>
public int Pageindex
{
get { return _pageindex; }
set { _pageindex = value; }
}
/// <summary>
/// 每页显示记录数量
/// </summary>
public int PageSize
{
get { return _pagesize; }
set { _pagesize = value; }
}
/// <summary>
/// 排序字段
/// </summary>
public string FldName
{
get { return _fldName; }
set { _fldName = value; }
}
/// <summary>
/// 返回列
/// </summary>
public string StrGetFields
{
get { return _strGetFields; }
set { _strGetFields = value; }
}
/// <summary>
/// 当前页面
/// </summary>
public string Strurl
{
get { return _strurl; }
set { _strurl = value; }
}
/// <summary>
/// 分页函数 得到总记录数
/// </summary>
public int CountTable(string strWhere, string tableName)
{
DataTable table = SDW.BLL.PageList.GetDataTable(strWhere, tableName);
return Convert.ToInt32(table.Rows[0]["Total"]);
}
/// <summary>
///
/// </summary>
public void BindDate()
{
//strFromTo = strFromTo.Substring(0, strFromTo.IndexOf(".aspx")) + ".aspx";
int flag = CountTable(_where, _table);
if (flag == 0)
return;
_intcount = flag;
if (_intcount != 0)
{
if (_intcount % _pagesize == 0)
_pagecount = _intcount / _pagesize;
else
_pagecount = _intcount / _pagesize + 1;
if (_pageindex >= 1 && _pageindex <= _pagecount)
{
txt_PageNum.Text = _pageindex.ToString();
txt_begin.Text = _pageindex.ToString();
}
else
{
txt_begin.Text = "1";
txt_PageNum.Text = "1";
_pageindex = 1;
}
if ((_pageindex - 1) < 1)
_intUpPage = 1;
else
_intUpPage = _pageindex - 1;
if ((_pageindex + 1) > _pagecount)
_intNextPage = _pagecount;
else
{
_intNextPage = _pageindex + 1;
}
_intLastPage = _pagecount;
txt_pageCount.Text = _intcount.ToString();
txt_pageAll.Text = _pagecount.ToString();
if (_pageindex == 1)
{
this.hyFirst.Enabled = false;
this.hyLast.Enabled = false;
}
else
{
this.hyFirst.Enabled = true;
this.hyFirst.NavigateUrl = _strurl + "&page=1";
this.hyLast.Enabled = true;
this.hyLast.NavigateUrl = _strurl + "&page=" + _intUpPage + "";
}
if (_pageindex == (_intcount % _pagesize == 0 ? _intcount / _pagesize : (_intcount / _pagesize) + 1))
{
this.hyNext.Enabled = false;
this.hyFoot.Enabled = false;
}
else
{
this.hyNext.Enabled = true;
this.hyNext.NavigateUrl = _strurl + "&page=" + _intNextPage + "";
this.hyFoot.Enabled = true;
this.hyFoot.NavigateUrl = _strurl + "&page=" + _intLastPage + "";
}
}
else
{
return;
}
}
/// <summary>
/// 返回 查询的表数据
/// </summary>
public DataTable BindView()
{
BindDate();
DataTable dtView = SDW.BLL.PageList.GetDataTable(_where, _table, 1, _fldName, _strGetFields, _pagesize, _pageindex, 0);
return dtView;
}
/// <summary>
/// 返回 查询的表数据 降序 第三个参数 1 表示降序 要是 0就是升序
/// </summary>
public DataTable BindViewDesc()
{
BindDate();
DataTable dtView = SDW.BLL.PageList.GetDataTable(_where, _table, 1, _fldName, _strGetFields, _pagesize, _pageindex, 0);
return dtView;
}
protected void btn_go_Click(object sender, EventArgs e)
{
int page = 1;
try
{
page = Convert.ToInt32(txt_PageNum.Text.Trim());
}
catch
{
page = 1;
}
int sumCount;
if (txt_pageAll.Text.Trim() == "" && txt_pageAll.Text.Trim() == null)
sumCount = 1;
else
sumCount = Convert.ToInt32(txt_pageAll.Text.Trim());
if (page <= sumCount && page > 0)
{
txt_PageNum.Text = Convert.ToString(page);
}
else
{
if (page > sumCount)
{
txt_PageNum.Text = Convert.ToString(sumCount);
}
else
{
txt_PageNum.Text = "1";
}
}
string strURl = Request.RawUrl;
strURl = strURl.Substring(0, strURl.IndexOf(".aspx")) + ".aspx" + "?Z=0&page=" + txt_PageNum.Text.Trim();
if (Request.QueryString["s"] != null)
{
strURl=strURl + "&s=" + Request.QueryString["s"].ToString() ;
}
if (Request.QueryString["x"] != null)
{
strURl=strURl + "&x=" + Request.QueryString["x"].ToString();
}
if (Request.QueryString["channelname"] != null)
{
strURl = strURl + "&channelname=" + Request.QueryString["channelname"].ToString();
} if (Request.QueryString["id"] != null)
{
strURl = strURl + "&id=" + Request.QueryString["id"].ToString();
}
Response.Redirect(strURl);
Response.End();
}
}
}
BLL层PageList.cs
Codeusing System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//请先添加引用
namespace SDW.BLL
{
/// <summary>
/// PageList 的摘要说明
/// </summary>
public class PageList
{
/// <summary>
/// 分页数据库操作函数
/// </summary>
/// <param name="strwhere">查询条件(注意: 不要加where)</param>
/// <param name="tblName">表名</param>
/// <param name="OrderType">设置排序类型,非 0 值则降序</param>
/// <param name="fldName">排序的字段名</param>
/// <param name="strGetFields">需要返回的列</param>
/// <param name="PageSize">页尺寸</param>
/// <param name="PageIndex">页码</param>
/// <param name="doCount">,[0:返回查询的表数据,非0:值则返回记录总数]</param>
/// <returns>返回一个表</returns>
public static DataTable GetDataTable(string strwhere, string tblName, int OrderType, string fldName, string strGetFields, int PageSize, int PageIndex, int doCount)
{
return SDW.DAL.PageList.GetDataTable(strwhere, tblName, OrderType, fldName, strGetFields, PageSize, PageIndex, doCount);
}
/// <summary>
/// 分页导航获取总页数
/// </summary>
/// <param name="strwhere">查询条件</param>
/// <param name="tblName">表、视图</param>
/// <returns>Total:字段名(总的记录数)</returns>
public static DataTable GetDataTable(string strwhere, string tblName)
{
return SDW.DAL.PageList.GetDataTable(strwhere, tblName);
}
}
}
DAL层PageList.cs
Codeusing System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Maticsoft.DBUtility;
namespace SDW.DAL
{
/// <summary>
/// PageList 的摘要说明
/// </summary>
public class PageList
{
/// <summary>
/// 分页数据库操作函数
/// </summary>
/// <param name="strwhere">查询条件(注意: 不要加where)</param>
/// <param name="tblName">表名</param>
/// <param name="OrderType">设置排序类型,非 0 值则降序</param>
/// <param name="fldName">排序的字段名</param>
/// <param name="strGetFields">需要返回的列</param>
/// <param name="PageSize">页尺寸</param>
/// <param name="PageIndex">页码</param>
/// <param name="doCount">,[0:返回查询的表数据,非0:值则返回记录总数]</param>
/// <returns>返回一个表</returns>
public static DataTable GetDataTable(string strwhere, string tblName, int OrderType, string fldName, string strGetFields, int PageSize, int PageIndex, int doCount)
{
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@strwhere", strwhere), //查询条件(注意: 不要加where)
new SqlParameter("@tblName", tblName), //表名
new SqlParameter("@OrderType", OrderType), //设置排序类型,非 0 值则降序
new SqlParameter("@fldName", fldName), //排序的字段名
new SqlParameter("@strGetFields", strGetFields), //需要返回的列
new SqlParameter("@PageSize", PageSize), //页尺寸
new SqlParameter("@PageIndex", PageIndex), //页码
new SqlParameter("@doCount", doCount) //返回记录总数,非 0 值则返回
};
return DbHelperSQL.RunProcedure("SP_Pagination", param, "PageList").Tables[0];
}
/// <summary>
/// 分页导航获取总页数
/// </summary>
/// <param name="strwhere">查询条件</param>
/// <param name="tblName">表、视图</param>
/// <returns>Total:字段名(总的记录数)</returns>
public static DataTable GetDataTable(string strwhere, string tblName)
{
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@strwhere", strwhere), //查询条件(注意: 不要加where)
new SqlParameter("@tblName", tblName), //表名
new SqlParameter("@doCount", 1) //返回记录总数,非 0 值则返回
};
return DbHelperSQL.RunProcedure("SP_Pagination", param, "PageList").Tables[0];
}
/// <summary>
/// 分页函数
/// </summary>
/// <param name="page">要连接的页</param>
/// <param name="pageSize">每页显示的记录数</param>
/// <param name="Url">链接路径</param>
/// <param name="strWhere">查询条件</param>
/// <param name="tblName">要查询的表、视图</param>
/// <returns>返回分页导航栏</returns>
public static string GoToPager(int page, int pageSize, string Url, string strWhere, string tblName)
{
if (tblName == null) goto Err;
DataTable tb = GetDataTable(strWhere, tblName);
int Count = Convert.ToInt32(tb.Rows[0]["Total"]); //取得总的记录数
StringBuilder strHtml = new StringBuilder();
int prevPage = page - 1;
int nextPage = page + 1;
int startPage;
int pageCount = (int)Math.Ceiling((double)Count / pageSize);
//strHtml.Append(@"总记录:");
//strHtml.Append(Count);
//strHtml.Append(@"  页码:");
//strHtml.Append(page);
//strHtml.Append(@"/");
//strHtml.Append(pageCount);
//strHtml.Append(@" ");
//strHtml.Append(@"");
if (prevPage < 1)
{
strHtml.Append("首页 ");
strHtml.Append("上一页 ");
}
else
{
strHtml.Append(@"<a href='" + Url + "=1'>首页</a> ");
strHtml.Append(@"<a href='" + Url + "=" + prevPage + "'>上一页</a> ");
}
if (page % 10 == 0)
{
startPage = page - 9;
}
else
{
startPage = page - page % 10 + 1;
}
if (startPage > 10)
{
strHtml.Append(@"<a href='");
strHtml.Append(Url);
strHtml.Append(@"=");
strHtml.Append(startPage - 1);
strHtml.Append(@"'>【←前10页</a>");
}
for (int i = startPage; i < startPage + 10; i++)
{
if (i > pageCount) break;
if (i == page)
{
strHtml.Append(@" <span class=""page_b"">" + i + "</span>");
}
else
{
strHtml.Append(@" <a href='" + Url + "=" + i + "'>" + i + "</a> ");
}
}
if (pageCount >= startPage + 10) strHtml.Append(@"<a href='" + Url + "=" + (startPage + 10) + "'>后10页→】</a>");
if (nextPage > pageCount)
{
strHtml.Append(@" 下一页 ");
strHtml.Append(@"末页 ");
}
else
{
strHtml.Append(@" <a href='" + Url + "=" + nextPage + "'>下一页</a> ");
strHtml.Append(@"<a href='" + Url + "=" + pageCount + "'>末页</a> ");
}
return strHtml.ToString();
Err:
return "缺少数据表或视图";
}
/// <summary>
/// 分页函数
/// </summary>
/// <param name="page">要连接的页</param>
/// <param name="pageSize">每页显示的记录数</param>
/// <param name="strWhere">查询条件</param>
/// <param name="tblName">要查询的表、视图</param>
/// <returns>返回分页导航栏</returns>
public static string GoToPager(int page, int pageSize, string strWhere, string tblName)
{
if (tblName == null) goto Err;
DataTable tb = GetDataTable(strWhere, tblName);
int Count = Convert.ToInt32(tb.Rows[0]["Total"]); //取得总的记录数
StringBuilder strHtml = new StringBuilder();
int prevPage = page - 1;
int nextPage = page + 1;
int startPage;
int pageCount = (int)Math.Ceiling((double)Count / pageSize);
strHtml.Append(@"总记录:");
strHtml.Append(Count);
strHtml.Append(@"  页码:");
strHtml.Append(page);
strHtml.Append(@"/");
strHtml.Append(pageCount);
strHtml.Append(@" ");
strHtml.Append(@"");
if (prevPage < 1)
{
strHtml.Append("首页 ");
strHtml.Append("上一页 ");
}
else
{
strHtml.Append(@"<span class='ddee' onclick=""redirection('1','tt')"">首页</span> ");
strHtml.Append(@"<span class='ddee' onclick=""redirection('" + prevPage + "','tt')\">上一页</span> ");
}
if (page % 10 == 0)
{
startPage = page - 9;
}
else
{
startPage = page - page % 10 + 1;
}
if (startPage > 10)
{
strHtml.Append(@"<span onclick=""redirection('" + (startPage - 1) + "','tt')\">【←前10页</span> ");
//strHtml.Append(@"<a href='");
//strHtml.Append(Url);
//strHtml.Append(@"=");
//strHtml.Append(startPage - 1);
//strHtml.Append(@"'>【←前10页</a>");
}
for (int i = startPage; i < startPage + 10; i++)
{
if (i > pageCount) break;
if (i == page)
{
strHtml.Append(@"<font color='#ff0000'>[" + i + "]</font> ");
}
else
{
strHtml.Append(@"<span class='ddee' onclick=""redirection('" + i + " ','tt')\">[" + i + "]</span> ");
//strHtml.Append(@" <a href='" + Url + "=" + i + "'>[" + i + "]</a> ");
}
}
if (pageCount >= startPage + 10)
strHtml.Append(@"<span onclick=""redirection('" + (startPage + 10) + " ','tt')\">后10页→】</span> ");
//strHtml.Append(@"<a href='" + Url + "=" + (startPage + 10) + "'>后10页→】</a>");
if (nextPage > pageCount)
{
strHtml.Append(@" 下一页 ");
strHtml.Append(@"末页 ");
}
else
{
strHtml.Append(@"<span class='ddee' onclick=""redirection('" + nextPage + " ','tt')\">下一页</span> ");
//strHtml.Append(@" <a href='" + Url + "=" + nextPage + "'>下一页</a> ");
strHtml.Append(@"<span class='ddee' onclick=""redirection('" + pageCount + " ','tt')\">末页</span> ");
//strHtml.Append(@"<a href='" + Url + "=" + pageCount + "'>末页</a> ");
}
return strHtml.ToString();
Err:
return "缺少数据表或视图";
}
}
}
Default.aspx
Code<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="YPEN.Web.Default" %>
<%@ Register Src="pageno.ascx" TagName="pageno" TagPrefix="uc1" %>
<!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:DataList ID="DataList1" runat="server">
<HeaderTemplate>
<table>
<tr>
<th>
id
</th>
<th>
city
</th>
<th>
countryid
</th>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table>
<tr>
<td>
<%#Eval("id") %>
</td>
<td>
<%#Eval("city") %>
</td>
<td>
<%#Eval("countryid") %>
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
<br />
<uc1:pageno ID="pageno1" runat="server" />
</div>
</form>
</body>
</html>
Default.aspx.cs
代码
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
namespace YPEN.Web
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
Bind();
}
}
private void Bind()
{
string strURl = Request.RawUrl;
strURl = strURl.Substring(0, strURl.IndexOf(".aspx")) + ".aspx";
strURl += "?Z=0";
int pageSize = 2;//这里设置分页尺寸
int pageIndex = 1;
string strWhere = "1=1 ";
if (Request.QueryString["page"] != null)
{
pageIndex = Convert.ToInt32(Request.QueryString["page"].ToString());
}
pageno1.Strurl = strURl; //当前页url
pageno1.FldName = "id"; //排序字段
pageno1.Table = "city";//表名
pageno1.StrGetFields = "*";//查询字段
pageno1.PageSize = pageSize; //页面数目
pageno1.Where = strWhere; //过滤条件
pageno1.Pageindex = pageIndex;
DataTable dt = pageno1.BindView();
this.DataList1.DataSource = dt.DefaultView;
this.DataList1.DataBind();
}
}
}
分页所用存储过程
代码
if object_id('[SP_Pagination]') is not null drop procedure [SP_Pagination]
go
USE [ypentest]
GO
/****** 对象: StoredProcedure [dbo].[SP_Pagination] 脚本日期: 01/30/2010 11:08:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_Pagination]
@tblName varchar(255), -- 表名 .
@strGetFields varchar(1000) ='* ', -- 需要返回的列
@fldName varchar(255)= ' ', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数,非 0 值则返回
@OrderType int = 0, -- 设置排序类型,非 0 值则降序
@strWhere varchar(1500) =' ' , -- 查询条件(注意: 不要加where)
@keyDate varchar(1500) =' '
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @fldNames varchar(100) -- 排序字段,如果两张表,只查询单张表的数据,去掉前面的表名
declare @str varchar(100)
if @doCount != 0
begin
if @strWhere != ' '
set @strSQL = 'select count(*) as Total from ' +@tblName+ ' where '+@strWhere
else
set @strSQL = 'select count(*) as Total from ' +@tblName+ ' '
end
--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况:
else
begin
declare @length int
declare @str1 varchar(100)
set @str1=@fldName;
if charindex(',',@fldName,0)>0
begin
set @str=SUBSTRING(@fldName,CharIndex(',',@fldName,0)+1,len(@fldName))
if charindex('.',@str,0)>0
begin
set @length=CharIndex('.',@str,0)
set @fldNames=SUBSTRING(@str,@length+1,len(@str))
end
else
begin
set @fldNames=@str
end
end
else
begin
if charindex('.',@fldName,0)>0
begin
set @length=CharIndex('.',@fldName,0)
set @fldNames=SUBSTRING(@fldName,@length+1,len(@fldName))
end
else
begin
set @fldNames=@fldName
end
end
if charindex(',',@fldName,0)>0
begin
set @str=SUBSTRING(@fldName,0,CharIndex(',',@fldName,0))+','
set @fldName=SUBSTRING(@fldName,CharIndex(',',@fldName,0)+1,len(@fldName))
end
else
begin
set @str=''
end
if @OrderType = 1
begin
set @strTmp = ' <(select min '
set @strOrder =' order by '+ @str +@fldName+ ' desc '
--如果@OrderType不是0,就执行降序,这句很重要!
end
else if @OrderType = 0
begin
set @strTmp = '> (select max '
set @strOrder =' order by '+ @str +@fldName+ ' asc '
end
else
begin
set @strTmp = 'NOT IN (SELECT TOP '
set @strOrder =' order by '+ @str +@fldName+ ' asc '
end
if @PageIndex = 1
begin
if @strWhere != ' '
set @strSQL = 'select top ' + str(@PageSize)+ ' ' +@strGetFields+ ' from ' +@tblName+ ' where ' +@strWhere+ ' ' +@strOrder
else
set @strSQL = 'select top ' + str(@PageSize)+ ' ' +@strGetFields+ ' from ' +@tblName+ ' ' +@strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' +str(@PageSize)+ ' ' +@strGetFields+ ' from ' +@tblName+ ' where ' +@fldName+ ' ' +@strTmp+ '( ' +@fldNames+ ') from (select top ' +str((@PageIndex-1)*@PageSize)
+ ' ' +@fldName+ ' from ' +@tblName+ ' ' +@strOrder+ ') as tblTmp) ' +@strOrder
if @strWhere != ' '
if(charindex(',',@str1,0)>0)
set @strSQL = 'select top ' +str(@PageSize)+ ' ' +@strGetFields+ ' from ' +@tblName+ ' where ' +@strWhere+ ' and ' +@keyDate+ ' not in (select top ' +str((@PageIndex-1)*@PageSize)
+ ' '+ @keyDate+ ' from ' +@tblName+ ' where ' +@strWhere+ ' ' +@strOrder+ ') ' +@strOrder
else
set @strSQL = 'select top ' +str(@PageSize)+ ' ' +@strGetFields+ ' from ' +@tblName+ ' where ' +@fldName+ ' ' +@strTmp + '( ' +@fldNames+ ') from (select top ' +str((@PageIndex-1)*@PageSize)
+ ' ' +@fldName+ ' from ' +@tblName+ ' where ' +@strWhere+ ' ' +@strOrder+ ') as tblTmp) and ' +@strWhere+ ' ' +@strOrder
end
end
exec (@strSQL)