使用的分页存储过程:
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