继SqlPager之后推出一款可用于前台页面的分页控件--UrlPager
使用方法其实和SqlPager一样，用的存储过程也是同一个，只是SqlPager更是个用于后台管理和B/S应用系统中，因为他是采用的Postback实现的翻页；而今天推出的Sqlpager是使用的通过在Url地址中传递参数(page=1...)实现的。闲话不多说了，转入正题。（使用环境：WindowsServer2003(IIS6.0)+.Net Framework2.0+MSSQL2000）
使用方法其实和SqlPager一样，用的存储过程也是同一个，只是SqlPager更是个用于后台管理和B/S应用系统中，因为他是采用的Postback实现的翻页；而今天推出的Urlpager是使用的通过在Url地址中传递参数(page=1...)实现的。闲话不多说了，转入正题。（使用环境：WindowsServer2003(IIS6.0)+.Net Framework2.0+MSSQL2000.
使用的分页存储过程:
CREATE PROC [dbo].[sp_PageView]
@tbname varchar(4000), --要分页显示的表名
@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC 用于指定排序顺序
@Where nvarchar(1000)='' --查询条件
--@PageCount int OUTPUT --总页数
AS
SET NOCOUNT ON
--检查对象是否有效
--IF OBJECT_ID(@tbname) IS NULL
--BEGIN
--RAISERROR(N'对象"%s"不存在',1,16,@tbname)
-- RETURN
--END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END
--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
RETURN
END
--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
--IF @PageCount IS NULL
--BEGIN
-- DECLARE @sql nvarchar(4000)
-- SET @sql=N'SELECT @PageCount=COUNT(*)'
-- +N' FROM '+@tbname
-- +N' '+@Where
-- EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
-- SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
--END
--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=(@PageCurrent-1)*@PageSize
--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' as xx '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
--处理别名
IF @FieldShow=N'*'
SET @FieldShow=N' tempPage.*'
--生成主键(惟一键)处理条件
DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),
@s nvarchar(1000),@Field sysname
SELECT @Where1=N'',@Where2=N'',@s=@FieldKey
WHILE CHARINDEX(N',',@s)>0
SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),
@s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
@Where1=@Where1+N' AND tempPage.'+@Field+N'=tempPageb.'+@Field,
@Where2=@Where2+N' AND tempPageb.'+@Field+N' IS NULL',
@Where=REPLACE(@Where,@Field,N'tempPage.'+@Field),
@FieldOrder=REPLACE(@FieldOrder,@Field,N'tempPage.'+@Field),
@FieldShow=REPLACE(@FieldShow,@Field,N'tempPage.'+@Field)
SELECT @Where=REPLACE(@Where,@s,N'tempPage.'+@s),
@FieldOrder=REPLACE(@FieldOrder,@s,N'tempPage.'+@s),
@FieldShow=REPLACE(@FieldShow,@s,N'tempPage.'+@s),
@Where1=STUFF(@Where1+N' AND tempPage.'+@s+N'=tempPageb.'+@s,1,5,N''),
@Where2=CASE
WHEN @Where='' THEN N'WHERE ('
ELSE @Where+N' AND ('
END+N'tempPageb.'+@s+N' IS NULL'+@Where2+N')'
--执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' tempPage LEFT JOIN(SELECT TOP '+@TopN1
+N' '+@FieldKey
+N' FROM '+@tbname
+N' tempPage '+@Where
+N' '+@FieldOrder
+N')tempPageb ON '+@Where1
+N' '+@Where2
+N' '+@FieldOrder)
END
GO
@tbname varchar(4000), --要分页显示的表名
@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC 用于指定排序顺序
@Where nvarchar(1000)='' --查询条件
--@PageCount int OUTPUT --总页数
AS
SET NOCOUNT ON
--检查对象是否有效
--IF OBJECT_ID(@tbname) IS NULL
--BEGIN
--RAISERROR(N'对象"%s"不存在',1,16,@tbname)
-- RETURN
--END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END
--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
RETURN
END
--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
--IF @PageCount IS NULL
--BEGIN
-- DECLARE @sql nvarchar(4000)
-- SET @sql=N'SELECT @PageCount=COUNT(*)'
-- +N' FROM '+@tbname
-- +N' '+@Where
-- EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
-- SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
--END
--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=(@PageCurrent-1)*@PageSize
--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' as xx '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
--处理别名
IF @FieldShow=N'*'
SET @FieldShow=N' tempPage.*'
--生成主键(惟一键)处理条件
DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),
@s nvarchar(1000),@Field sysname
SELECT @Where1=N'',@Where2=N'',@s=@FieldKey
WHILE CHARINDEX(N',',@s)>0
SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),
@s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
@Where1=@Where1+N' AND tempPage.'+@Field+N'=tempPageb.'+@Field,
@Where2=@Where2+N' AND tempPageb.'+@Field+N' IS NULL',
@Where=REPLACE(@Where,@Field,N'tempPage.'+@Field),
@FieldOrder=REPLACE(@FieldOrder,@Field,N'tempPage.'+@Field),
@FieldShow=REPLACE(@FieldShow,@Field,N'tempPage.'+@Field)
SELECT @Where=REPLACE(@Where,@s,N'tempPage.'+@s),
@FieldOrder=REPLACE(@FieldOrder,@s,N'tempPage.'+@s),
@FieldShow=REPLACE(@FieldShow,@s,N'tempPage.'+@s),
@Where1=STUFF(@Where1+N' AND tempPage.'+@s+N'=tempPageb.'+@s,1,5,N''),
@Where2=CASE
WHEN @Where='' THEN N'WHERE ('
ELSE @Where+N' AND ('
END+N'tempPageb.'+@s+N' IS NULL'+@Where2+N')'
--执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' tempPage LEFT JOIN(SELECT TOP '+@TopN1
+N' '+@FieldKey
+N' FROM '+@tbname
+N' tempPage '+@Where
+N' '+@FieldOrder
+N')tempPageb ON '+@Where1
+N' '+@Where2
+N' '+@FieldOrder)
END
GO
(注：这个分页存储过程是借鉴的一位同行的，具体哪位已经记不清楚了)
使用方法：
//// <summary>
/// 绑定UrlPager数据 存储过程的分页
/// </summary>
/// <param name="sqlPager">UrlPager控件ID</param>
/// <param name="sort">排序方式</param>
/// <param name="sortfied">排序字段</param>
/// <param name="FieldKey">关键字段(主键)</param>
/// <param name="ConnectString">数据库链接字符串</param>
/// <param name="ControlName">绑定的网格控件</param>
/// <param name="CommandText">SQL语句</param>
/// <param name="TableName">数据表表名(可以是复杂的查询)</param>
/// <param name="Fields">查询字段</param>
/// <param name="SqlCondition">Where条件</param>
public void BindSqlPager(UrlPagerControl.UrlPager urlPager, UrlPager.SortMode sort,
string sortfied, string FieldKey, string ConnectString, string ControlName, string CommandText,
string TableName, string Fields, string SqlCondition)
{
urlPager.ConnectionString = ConnectString;
urlPager.ControlToPaginate = ControlName;
urlPager.SelectCommand = CommandText;
urlPager.SortMode = sort;
urlPager.FieldKey = FieldKey;
urlPager.SortField = sortfied;
urlPager.TableName = TableName;
urlPager.Fields = Fields;
urlPager.SQLCondition = SqlCondition;
urlPager.DataBind();
}
调用方法：
this.BindSqlPager(this.UrlPager1, sortmode, sortfield, "ID", this.GetDbConnectString(), "DataList1", this.CommandSQL, "(" + this.CommandSQL + ")", "*", "ID>0");
其中CommandSQL为你自己的复杂的查询语句，比如select a.*,b.* from A as a left join B as b where a.id=b.id
控件的关键代码：
/**//// <summary>
/// 呈现
/// </summary>
/// <param name="writer"></param>
protected override void Render(HtmlTextWriter writer)
{
string curPage = "";
string query = "";
if (Context != null)
{
query = Page.Request.Url.Query.Replace('?', '&');
}
curPage = query == "" ? "&page=1" : query;
query = RX.Replace(query, String.Empty, -1);
query = "<a href='?page={0}" + query + "'>{1}</a> ";
// Prepare the necessary number
int page = CurrentPage;
int count = PageCount;
int nums = NumberCount - 1;
int center = nums / 2;
int beginIndex = 1;
// Calculate the first page number in the pagger bar
if (count > nums && page > center)
{
beginIndex = page - center;
if ((count - beginIndex) <= nums)
beginIndex = count - nums;
}
// Calculate the last page number in the pagger bar
int endIndex = beginIndex + nums;
if (endIndex > count)
endIndex = count;
// Render the pagger bar
writer.AddAttribute(HtmlTextWriterAttribute.Id, ClientID);
if (!String.IsNullOrEmpty(_class))
{
writer.AddAttribute(HtmlTextWriterAttribute.Class, _class);
}
else
{
}
writer.RenderBeginTag(HtmlTextWriterTag.Div);
writer.Write(String.Format(query, page > 1 ? (page - 1) : 1, PrevText));
writer.Write(String.Format(query, 1, "1")+FirstText);
for (int i = beginIndex; i <= endIndex; i++)
{
if (page == i)
{
string _tempUrl = query.Replace("'>", "' class='" + this.VisitedCss + "'>");
writer.Write(String.Format(_tempUrl, i, i));
}
else
{
writer.Write(String.Format(query, i, i));
}
}
writer.Write(LastText + String.Format(query, count, count.ToString()));
writer.Write(String.Format(query, page < count ? (page + 1) : page, NextText));
writer.RenderEndTag();
}
/**//// <summary>
/// 重载数据绑定 取得，填充数据
/// </summary>
public override void DataBind()
{
//this.SelectCommand=string.Format("exec sp_PageView '{0}','{1}','{2}',{3},{4},{5},{6},'{7}'",this.TableName,this.Fields,this.SortField,this.ItemsPerPage,(this.CurrentPageIndex+1),0,(this.SortMode==LJHSqlPager2.SortMode.DESC?"1":"0"),this.SQLCondition);
object[] param ={
this.TableName,
this.FieldKey,
this.CurrentPage,
this.PageSize,
this.Fields,
this.SortField+(this.SortMode==SortMode.DESC?" DESC":" ASC"),
this.SQLCondition
};
this.SelectCommand = string.Format("exec sp_PageView '{0}','{1}',{2},{3},'{4}','{5}','{6}'", param);
// 触发数据绑定事件
base.DataBind();
// 控件必须在数据绑定后重新创建
ChildControlsCreated = false;
// 确定数据容器存在并且为列表控件(list control)
if (ControlToPaginate == "")
return;
_controlToPaginate = Page.FindControl(ControlToPaginate);
if (_controlToPaginate == null)
return;
if (!(_controlToPaginate is BaseDataList
|| _controlToPaginate is ListControl
|| _controlToPaginate is CompositeDataBoundControl
|| _controlToPaginate is Repeater))
return;
// 确定数据库连接字符串有效且查询命令已指定
if (ConnectionString == "" || SelectCommand == "")
return;
// 取得数据
//if (PagingMode == PagingMode.Cached)
//FetchAllData();
//else
//{
// //if (SortField == "")
// // return;
FetchPageData();
//}
// Bind data to the buddy control
// 绑定数据到数据容器
BaseDataList baseDataListControl = null;
ListControl listControl = null;
if (_controlToPaginate is BaseDataList)
{
baseDataListControl = (BaseDataList)_controlToPaginate;
baseDataListControl.DataSource = _dataSource;
baseDataListControl.DataBind();
return;
}
if (_controlToPaginate is ListControl)
{
listControl = (ListControl)_controlToPaginate;
listControl.Items.Clear();
listControl.DataSource = _dataSource;
listControl.DataBind();
return;
}
if (_controlToPaginate is CompositeDataBoundControl)
{
GridView gv = (GridView)_controlToPaginate;
gv.DataSource = _dataSource;
gv.DataBind();
return;
}
if (_controlToPaginate is Repeater)
{
Repeater rep = (Repeater)_controlToPaginate;
rep.DataSource = _dataSource;
rep.DataBind();
return;
}
}
/// 绑定UrlPager数据 存储过程的分页
/// </summary>
/// <param name="sqlPager">UrlPager控件ID</param>
/// <param name="sort">排序方式</param>
/// <param name="sortfied">排序字段</param>
/// <param name="FieldKey">关键字段(主键)</param>
/// <param name="ConnectString">数据库链接字符串</param>
/// <param name="ControlName">绑定的网格控件</param>
/// <param name="CommandText">SQL语句</param>
/// <param name="TableName">数据表表名(可以是复杂的查询)</param>
/// <param name="Fields">查询字段</param>
/// <param name="SqlCondition">Where条件</param>
public void BindSqlPager(UrlPagerControl.UrlPager urlPager, UrlPager.SortMode sort,
string sortfied, string FieldKey, string ConnectString, string ControlName, string CommandText,
string TableName, string Fields, string SqlCondition)
{
urlPager.ConnectionString = ConnectString;
urlPager.ControlToPaginate = ControlName;
urlPager.SelectCommand = CommandText;
urlPager.SortMode = sort;
urlPager.FieldKey = FieldKey;
urlPager.SortField = sortfied;
urlPager.TableName = TableName;
urlPager.Fields = Fields;
urlPager.SQLCondition = SqlCondition;
urlPager.DataBind();
}
调用方法：
this.BindSqlPager(this.UrlPager1, sortmode, sortfield, "ID", this.GetDbConnectString(), "DataList1", this.CommandSQL, "(" + this.CommandSQL + ")", "*", "ID>0");
其中CommandSQL为你自己的复杂的查询语句，比如select a.*,b.* from A as a left join B as b where a.id=b.id
控件的关键代码：
/**//// <summary>
/// 呈现
/// </summary>
/// <param name="writer"></param>
protected override void Render(HtmlTextWriter writer)
{
string curPage = "";
string query = "";
if (Context != null)
{
query = Page.Request.Url.Query.Replace('?', '&');
}
curPage = query == "" ? "&page=1" : query;
query = RX.Replace(query, String.Empty, -1);
query = "<a href='?page={0}" + query + "'>{1}</a> ";
// Prepare the necessary number
int page = CurrentPage;
int count = PageCount;
int nums = NumberCount - 1;
int center = nums / 2;
int beginIndex = 1;
// Calculate the first page number in the pagger bar
if (count > nums && page > center)
{
beginIndex = page - center;
if ((count - beginIndex) <= nums)
beginIndex = count - nums;
}
// Calculate the last page number in the pagger bar
int endIndex = beginIndex + nums;
if (endIndex > count)
endIndex = count;
// Render the pagger bar
writer.AddAttribute(HtmlTextWriterAttribute.Id, ClientID);
if (!String.IsNullOrEmpty(_class))
{
writer.AddAttribute(HtmlTextWriterAttribute.Class, _class);
}
else
{
}
writer.RenderBeginTag(HtmlTextWriterTag.Div);
writer.Write(String.Format(query, page > 1 ? (page - 1) : 1, PrevText));
writer.Write(String.Format(query, 1, "1")+FirstText);
for (int i = beginIndex; i <= endIndex; i++)
{
if (page == i)
{
string _tempUrl = query.Replace("'>", "' class='" + this.VisitedCss + "'>");
writer.Write(String.Format(_tempUrl, i, i));
}
else
{
writer.Write(String.Format(query, i, i));
}
}
writer.Write(LastText + String.Format(query, count, count.ToString()));
writer.Write(String.Format(query, page < count ? (page + 1) : page, NextText));
writer.RenderEndTag();
}
/**//// <summary>
/// 重载数据绑定 取得，填充数据
/// </summary>
public override void DataBind()
{
//this.SelectCommand=string.Format("exec sp_PageView '{0}','{1}','{2}',{3},{4},{5},{6},'{7}'",this.TableName,this.Fields,this.SortField,this.ItemsPerPage,(this.CurrentPageIndex+1),0,(this.SortMode==LJHSqlPager2.SortMode.DESC?"1":"0"),this.SQLCondition);
object[] param ={
this.TableName,
this.FieldKey,
this.CurrentPage,
this.PageSize,
this.Fields,
this.SortField+(this.SortMode==SortMode.DESC?" DESC":" ASC"),
this.SQLCondition
};
this.SelectCommand = string.Format("exec sp_PageView '{0}','{1}',{2},{3},'{4}','{5}','{6}'", param);
// 触发数据绑定事件
base.DataBind();
// 控件必须在数据绑定后重新创建
ChildControlsCreated = false;
// 确定数据容器存在并且为列表控件(list control)
if (ControlToPaginate == "")
return;
_controlToPaginate = Page.FindControl(ControlToPaginate);
if (_controlToPaginate == null)
return;
if (!(_controlToPaginate is BaseDataList
|| _controlToPaginate is ListControl
|| _controlToPaginate is CompositeDataBoundControl
|| _controlToPaginate is Repeater))
return;
// 确定数据库连接字符串有效且查询命令已指定
if (ConnectionString == "" || SelectCommand == "")
return;
// 取得数据
//if (PagingMode == PagingMode.Cached)
//FetchAllData();
//else
//{
// //if (SortField == "")
// // return;
FetchPageData();
//}
// Bind data to the buddy control
// 绑定数据到数据容器
BaseDataList baseDataListControl = null;
ListControl listControl = null;
if (_controlToPaginate is BaseDataList)
{
baseDataListControl = (BaseDataList)_controlToPaginate;
baseDataListControl.DataSource = _dataSource;
baseDataListControl.DataBind();
return;
}
if (_controlToPaginate is ListControl)
{
listControl = (ListControl)_controlToPaginate;
listControl.Items.Clear();
listControl.DataSource = _dataSource;
listControl.DataBind();
return;
}
if (_controlToPaginate is CompositeDataBoundControl)
{
GridView gv = (GridView)_controlToPaginate;
gv.DataSource = _dataSource;
gv.DataBind();
return;
}
if (_controlToPaginate is Repeater)
{
Repeater rep = (Repeater)_controlToPaginate;
rep.DataSource = _dataSource;
rep.DataBind();
return;
}
}
主要的核心代码就是呈现数据和绑定数据,大家一看代码就知道了，我也不用多说，不过要提醒的一点是显示的页面是可以自己设置的。
效果图片：
源码下载：UrlPager
如有问题可留言，我会抽时间答复！谢谢大家支持。
如有问题可留言，我会抽时间答复！谢谢大家支持。
