如果用SqlServer则可以用它的存储过程分页,代码如下:
USE
[
OMS_NET
]
GO
/**/ /****** 对象: StoredProcedure [dbo].[mp_DivPageBySql] 脚本日期: 02/12/2007 09:55:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [ dbo ] . [ mp_DivPageBySql ]
@strSql varchar ( 8000 ), -- sql语句,比如select * from tUser或exec mp_StoreProcedure
@nPageSize int , -- 表示每页的条数
@nPageCount int -- 表示返回哪一页
as
SET NOCOUNT ON
DECLARE @P1 INT ,
@nRowCount INT
-- //注意:@scrollopt = 1 会取得Select的时候的总行数
EXEC sp_cursoropen @P1 OUTPUT, @strSql , @scrollopt = 2 , @ccopt = 335873 , @rowcount = @nRowCount OUTPUT
IF ( @P1 != 0 )
BEGIN
-- SELECT @nRowCount AS nRecordCount, ceiling(1.0 * @nRowCount / @nPageSize) AS nPageCount, @nPageCount AS nPage
SET @nPageCount = ( @nPageCount - 1 ) * @nPageSize + 1
EXEC sp_cursorfetch @P1 , 32 , @nPageCount , @nPageSize
EXEC sp_cursorclose @P1
END
GO
/**/ /****** 对象: StoredProcedure [dbo].[mp_DivPageBySql] 脚本日期: 02/12/2007 09:55:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [ dbo ] . [ mp_DivPageBySql ]
@strSql varchar ( 8000 ), -- sql语句,比如select * from tUser或exec mp_StoreProcedure
@nPageSize int , -- 表示每页的条数
@nPageCount int -- 表示返回哪一页
as
SET NOCOUNT ON
DECLARE @P1 INT ,
@nRowCount INT
-- //注意:@scrollopt = 1 会取得Select的时候的总行数
EXEC sp_cursoropen @P1 OUTPUT, @strSql , @scrollopt = 2 , @ccopt = 335873 , @rowcount = @nRowCount OUTPUT
IF ( @P1 != 0 )
BEGIN
-- SELECT @nRowCount AS nRecordCount, ceiling(1.0 * @nRowCount / @nPageSize) AS nPageCount, @nPageCount AS nPage
SET @nPageCount = ( @nPageCount - 1 ) * @nPageSize + 1
EXEC sp_cursorfetch @P1 , 32 , @nPageCount , @nPageSize
EXEC sp_cursorclose @P1
END
如果用Excel等没有这个存储过程的数据库,我们还可以用下面的方法分页:
首先自己写一个填充Datalist的过程,注意此时不要把获取数据的select放到AccessDataSource里了。
/**/
/// <summary>
/// 普通获取数据
/// </summary>
public void BuildGrid()
{
OleDbConnection myCon = new OleDbConnection(ConfigurationManager.AppSettings["ConnectionString"]);
OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT * FROM vPingLun order by id", myCon);
DataSet ds = new DataSet();
int startRecord = (CurrentPage - 1) * PageItem;//其中CurrentPage是当前页码,可以用QueryString传入;PageItem是每页的条数
myAdapter.Fill(ds, startRecord, PageItem, "Paging");
dataLst.DataSource = ds.Tables["Paging"].DefaultView;
dataLst.DataBind();
myCon.Close();
}
/// 普通获取数据
/// </summary>
public void BuildGrid()
{
OleDbConnection myCon = new OleDbConnection(ConfigurationManager.AppSettings["ConnectionString"]);
OleDbDataAdapter myAdapter = new OleDbDataAdapter("SELECT * FROM vPingLun order by id", myCon);
DataSet ds = new DataSet();
int startRecord = (CurrentPage - 1) * PageItem;//其中CurrentPage是当前页码,可以用QueryString传入;PageItem是每页的条数
myAdapter.Fill(ds, startRecord, PageItem, "Paging");
dataLst.DataSource = ds.Tables["Paging"].DefaultView;
dataLst.DataBind();
myCon.Close();
}
这样只需在PageLoad的时候执行一下BuildGrid();就可以了。下面要做的就是生成页码的html代码:
生成分页
#region 生成分页
protected string MakePage()
{
int currentPage = Convert.ToInt32(Request.QueryString[0]);//当前页
int iCount = Convert.ToInt32(DB.ExecuteSchaler("select count(id) from PingLun");//总条数
int allPage = iCount / PageItem;//allPage是总的页数,PageItem是每页要显示的条数
if (allPage * PageItem != iCount) allPage += 1;//呵呵,烦琐
string outputStr = "[共"+iCount+"条回复,分"+allPage+"页] ";
if (allPage == 1)
{
outputStr = "<font color=red>共一页</font>";
}
else
{
for (int i = 1; i < allPage + 1; i++)
{
if (i == currentPage)
{
outputStr += "<font color=red>" + i.ToString() + "</font> ";
}
else
{
outputStr += "<a class='op' href='PopView.aspx?page=" + i.ToString() + "'>" + i.ToString() + "</a> ";
}
}
}
return outputStr;
}
#endregion
protected string MakePage()
{
int currentPage = Convert.ToInt32(Request.QueryString[0]);//当前页
int iCount = Convert.ToInt32(DB.ExecuteSchaler("select count(id) from PingLun");//总条数
int allPage = iCount / PageItem;//allPage是总的页数,PageItem是每页要显示的条数
if (allPage * PageItem != iCount) allPage += 1;//呵呵,烦琐
string outputStr = "[共"+iCount+"条回复,分"+allPage+"页] ";
if (allPage == 1)
{
outputStr = "<font color=red>共一页</font>";
}
else
{
for (int i = 1; i < allPage + 1; i++)
{
if (i == currentPage)
{
outputStr += "<font color=red>" + i.ToString() + "</font> ";
}
else
{
outputStr += "<a class='op' href='PopView.aspx?page=" + i.ToString() + "'>" + i.ToString() + "</a> ";
}
}
}
return outputStr;
}
#endregion