存储过程:
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int,--每页显示记录条数
@currentpage int,--第几页
@orderid nvarchar(50),--主键排序
@sort int,--排序方式,1表示升序,0表示降序排列
@rowcount int output,--总记录数,共有几条信息
@pagecount int output--总页数,共有多少页
AS
declare @countsql nvarchar(50)
declare @sql nvarchar(200)
declare @subsql nvarchar(100)--not in子sql语句
declare @tmpOrderid nvarchar(50)
--返回总记录数,并赋值给输出参数@rowcount
set @countsql='select @totalcount=count(*) from '+@tablename
exec sp_executesql @countsql,N'@totalcount int out',@rowcount output
--判断字段名是否为空
if @fieldname is null or @fieldname=''
set @fieldname=' * '
--判断是否排序及排序方式
if @orderid is null or @orderid=''
set @tmpOrderid=' '
else
begin
if @sort=0
set @tmpOrderid='order by '+@orderid+' desc'
else
set @tmpOrderid='order by '+@orderid+' asc'
end
--计算页数
if @rowcount%@pagesize>0
set @pagecount =(@rowcount/@pagesize)+1;
else
set @pagecount=@rowcount/@pagesize;
--分页算法实现
set @subsql='select top'+str(@pagesize*(@currentpage-1))+' '+@orderid+' from '+@tablename+' '+@tmpOrderid
set @sql='select top'+str(@pagesize)+' '+@fieldname+' from '+@tablename+' where '+@orderid+' not in ('+@subsql+')'+@tmpOrderid
exec(@sql)
.CS文件:
{
int CurrentPage,RowCount,PageCount;
int PageSize = 2 ;
if (Request[ " CurrentPage " ] == null || Convert.ToInt32(Request[ " CurrentPage " ]) < 1 )
{
CurrentPage = 1 ;
}
else
{
CurrentPage = Convert.ToInt32(Request[ " CurrentPage " ]);
}
// 数据库操作
SqlConnection sqlconn = new SqlConnection(ConfigurationManager.AppSettings[ " ConnStr " ].ToString());
SqlCommand cmd = new SqlCommand( " pagelist " , sqlconn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlconn;
SqlParameter[] prams = {
new SqlParameter( " @tablename " ,SqlDbType.NVarChar, 50 ),
new SqlParameter( " @fieldname " ,SqlDbType.NVarChar, 50 ),
new SqlParameter( " @pagesize " ,SqlDbType.Int),
new SqlParameter( " @currentpage " ,SqlDbType.Int),
new SqlParameter( " @orderid " ,SqlDbType.NVarChar, 50 ),
new SqlParameter( " @sort " ,SqlDbType.Int),
new SqlParameter( " @rowcount " ,SqlDbType.Int),
new SqlParameter( " @pagecount " ,SqlDbType.Int)};
prams[ 0 ].Value = " news " ; // 表名
prams[ 1 ].Value = " * " ; // 字段名
prams[ 2 ].Value = PageSize; // 每页显示条数
prams[ 3 ].Value = CurrentPage; // 当前页数
prams[ 4 ].Value = " id " ; // 主键
prams[ 5 ].Value = 1 ; // 排序方式,0表示降序,1表示升序
prams[ 6 ].Direction = ParameterDirection.Output; // 总记录数
prams[ 7 ].Direction = ParameterDirection.Output; // 总页数
foreach (SqlParameter pram in prams)
{
cmd.Parameters.Add(pram);
}
sqlconn.Open();
SqlDataAdapter sda = new SqlDataAdapter();
DataSet ds = new DataSet();
sda.SelectCommand = cmd;
sqlconn.Close();
sda.Fill(ds);
// 数据库操作结束
RowCount = ( int )cmd.Parameters[ " @rowcount " ].Value;
PageCount = ( int )cmd.Parameters[ " @pagecount " ].Value;
if (CurrentPage > PageCount)
{
Response.Redirect( " CutPage.aspx?CurrentPage= " + Convert.ToString(PageCount));
Response.End();
}
this .lblCurrent.Text = Convert.ToString(CurrentPage);
this .lblPageTotal.Text = Convert.ToString(PageCount);
this .lblRowsTotal.Text = Convert.ToString(RowCount);
this .lblPageSize.Text = Convert.ToString(PageSize);
this .hlFirst.NavigateUrl = " CutPage.aspx?CurrentPage=1 " ;
this .hlPrev.NavigateUrl = " CutPage.aspx?CurrentPage= " + Convert.ToString(CurrentPage - 1 );
this .hlNext.NavigateUrl = " CutPage.aspx?CurrentPage= " + Convert.ToString(CurrentPage + 1 );
this .hlLast.NavigateUrl = " CutPage.aspx?CurrentPage= " + Convert.ToString(PageCount);
if (Convert.ToInt32(CurrentPage) == 1 )
{
this .hlPrev.Enabled = false ;
this .hlFirst.Enabled = false ;
}
if (Convert.ToInt32(CurrentPage) == PageCount)
{
this .hlNext.Enabled = false ;
this .hlLast.Enabled = false ;
}
GridView1.DataSource = ds.Tables[ 0 ];
GridView1.DataBind();
}
aspx文件:
</ asp:GridView >
</ div >
第 < asp:Label ID ="lblCurrent" runat ="server" ></ asp:Label > 页 | 每页 < asp:Label ID ="lblPageSize"
runat ="server" ></ asp:Label > 条 | 共有 < asp:Label ID ="lblPageTotal" runat ="server" ></ asp:Label > 页 | 共有 < asp:Label ID ="lblRowsTotal"
runat ="server" ></ asp:Label > 条信息 |
< asp:HyperLink ID ="hlFirst" runat ="server" > 首页 </ asp:HyperLink >
|
< asp:HyperLink ID ="hlPrev" runat ="server" > 上一页 </ asp:HyperLink >
|
< asp:HyperLink ID ="hlNext" runat ="server" > 下一页 </ asp:HyperLink >
|
< asp:HyperLink ID ="hlLast" runat ="server" > 尾页 </ asp:HyperLink >< br />