Code//分页存储过程
CREATE procedure Pager
&＃64;sqlstr nvarchar(4000), --查询字符串
&＃64;currentpage int, --第N页
&＃64;pagesize int --每页行数
as
set nocount on
declare &＃64;P1 int, --P1是游标的id
&＃64;rowcount int,
&＃64;pagecount int
exec sp_cursoropen &＃64;P1 output,&＃64;sqlstr,&＃64;scrollopt&＃61;1,&＃64;ccopt&＃61;1,&＃64;rowcount&＃61;&＃64;rowcount output
select ceiling(1.0*&＃64;rowcount/&＃64;pagesize) as pagecount,&＃64;rowcount as recordcount--,&＃64;currentpage as 当前页
set &＃64;pagecount &＃61; ceiling(1.0*&＃64;rowcount/&＃64;pagesize)
IF &＃64;currentpage>&＃61;&＃64;pagecount
set &＃64;currentpage&＃61;&＃64;pagecount
set &＃64;currentpage&＃61;(&＃64;currentpage-1)*&＃64;pagesize&＃43;1
exec sp_cursorfetch &＃64;P1,16,&＃64;currentpage,&＃64;pagesize
exec sp_cursorclose &＃64;P1
set nocount off
GO
//实现代码:
//执行分页程序
public DataTable ExecPager(string sql, int curPage, int pageSize, out int pageCount , out int recordCount)
{
//Set up a return value
DataTable dt &＃61; new DataTable();
DataSet ds &＃61; null;
//Create a parameter
SqlParameter[] parms &＃61;{
XNCJWC.Utility.Common.MakeInParm("&＃64;sqlstr",SqlDbType.VarChar,4000, sql),
XNCJWC.Utility.Common.MakeInParm("&＃64;currentpage",SqlDbType.Int, 4, curPage),
XNCJWC.Utility.Common.MakeInParm("&＃64;pagesize",SqlDbType.Int, 4, pageSize)
};
try
{
//Execute the query
ds &＃61; SqlHelper.ExecuteReaderDataSet(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Pager", parms);
dt &＃61; ds.Tables[1];
pageCount &＃61; Int32.Parse(((DataRow)dt.Rows[0])[0].ToString());
recordCount &＃61; Int32.Parse(((DataRow)dt.Rows[0])[1].ToString());
}
catch (Exception e)
{
XNCJWC.Utility.Log.Error(e.Message.ToString());
throw e;// new Exception("Query Failed");
}
return ds.Tables[2];
}
------------------------------------------------------------------------------------------------------------
pager类:
public class Pager
{
private int _curPage ; //当前页数
private int _recordCount ;//总记录数
private int _pageSize &＃61; 20; //每页记录数
private int _pageCount; //页数
private string _strSql; //sql语句
private DataTable _dt; //当前页记录集
public Pager()
{
_curPage &＃61; 1 ;
_recordCount &＃61; 0 ;
_pageCount &＃61; 0;
_strSql &＃61; "";
}
public Pager(string sql, int curpage)
{
_recordCount &＃61; 0;
_pageCount &＃61; 0;
_strSql &＃61; sql;
_curPage &＃61; curpage;
}
///
/// 分页
///
public void StartPage()
{
_dt &＃61; ExecPager(this._strSql, this._curPage, this._pageSize, out _pageCount, out _recordCount);
}
public int CurPage
{
get { return this._curPage; }
set { this._curPage &＃61; value; }
}
public int RecordCount
{
get { return this._recordCount; }
set { this._recordCount &＃61; value; }
}
public int PageSize
{
get { return this._pageSize; }
set { this._pageSize &＃61; value; }
}
public int PageCount
{
get { return this._pageCount; }
set { this._pageCount &＃61; value; }
}
public string StrSql
{
get { return this._strSql; }
set { this._strSql &＃61; value; }
}
public DataTable Dt
{
get { return this._dt; }
set { this._dt &＃61; value; }
}
}
京公网安备 11010802041100号