存储过程 http://www.cnblogs.com/hejunrex/archive/2011/10/26/2224895.html
==DAL==
///
<summary>
/// 获取分页数据
/// </summary>
/// <param name="tblName"> 表名、视图、表连接 如:'xtest' </param>
/// <param name="strGetFields"> 需要返回的列如:'xname,xdemo' </param>
/// <param name="strWhere"> 查询条件(注意:不要加where) </param>
/// <param name="strOrder"> 排序的字段名如:'order by id desc' </param>
/// <param name="pageIndex"> 开始记录位置 </param>
/// <param name="pageSize"> 每页记录数 </param>
/// <param name="doCount"> 记录总数 </param>
/// <param name="recordCount"> 非0则统计,为0则不统计 </param>
/// <returns></returns>
public DataTable GetPageData( string tblName, string strGetFields, string strWhere, string strOrder, int pageIndex, int pageSize, int doCount, out int recordCount)
{
SqlParameter[] prams ={
new SqlParameter( " @tblName ",SqlDbType.VarChar),
new SqlParameter( " @strGetFields ",SqlDbType.VarChar),
new SqlParameter( " @strWhere ",SqlDbType.VarChar),
new SqlParameter( " @strOrder ",SqlDbType.VarChar),
new SqlParameter( " @pageIndex ",SqlDbType.Int),
new SqlParameter( " @pageSize ",SqlDbType.Int),
new SqlParameter( " @doCount ",SqlDbType.Int),
new SqlParameter( " @recordCount ",SqlDbType.Int)
};
prams[ 0].Value = tblName;
prams[ 1].Value = strGetFields;
prams[ 2].Value = strWhere;
prams[ 3].Value = strOrder;
prams[ 4].Value = pageIndex;
prams[ 5].Value = pageSize;
prams[ 6].Value = doCount;
prams[ 7].Direction = ParameterDirection.Output;
DataTable dt = DbHelperSQL.RunProcedure( " xp_GetDataPaged_2005 ", prams, " PageData ").Tables[ 0];
recordCount = ( int)prams[ 7].Value;
return dt;
}
/// 获取分页数据
/// </summary>
/// <param name="tblName"> 表名、视图、表连接 如:'xtest' </param>
/// <param name="strGetFields"> 需要返回的列如:'xname,xdemo' </param>
/// <param name="strWhere"> 查询条件(注意:不要加where) </param>
/// <param name="strOrder"> 排序的字段名如:'order by id desc' </param>
/// <param name="pageIndex"> 开始记录位置 </param>
/// <param name="pageSize"> 每页记录数 </param>
/// <param name="doCount"> 记录总数 </param>
/// <param name="recordCount"> 非0则统计,为0则不统计 </param>
/// <returns></returns>
public DataTable GetPageData( string tblName, string strGetFields, string strWhere, string strOrder, int pageIndex, int pageSize, int doCount, out int recordCount)
{
SqlParameter[] prams ={
new SqlParameter( " @tblName ",SqlDbType.VarChar),
new SqlParameter( " @strGetFields ",SqlDbType.VarChar),
new SqlParameter( " @strWhere ",SqlDbType.VarChar),
new SqlParameter( " @strOrder ",SqlDbType.VarChar),
new SqlParameter( " @pageIndex ",SqlDbType.Int),
new SqlParameter( " @pageSize ",SqlDbType.Int),
new SqlParameter( " @doCount ",SqlDbType.Int),
new SqlParameter( " @recordCount ",SqlDbType.Int)
};
prams[ 0].Value = tblName;
prams[ 1].Value = strGetFields;
prams[ 2].Value = strWhere;
prams[ 3].Value = strOrder;
prams[ 4].Value = pageIndex;
prams[ 5].Value = pageSize;
prams[ 6].Value = doCount;
prams[ 7].Direction = ParameterDirection.Output;
DataTable dt = DbHelperSQL.RunProcedure( " xp_GetDataPaged_2005 ", prams, " PageData ").Tables[ 0];
recordCount = ( int)prams[ 7].Value;
return dt;
}
==BLL==
///
<summary>
/// 获取分页数据
/// </summary>
/// <param name="tblName"> 表名、视图、表连接 如:'xtest' </param>
/// <param name="strGetFields"> 需要返回的列如:'xname,xdemo' </param>
/// <param name="strWhere"> 查询条件(注意:不要加where) </param>
/// <param name="strOrder"> 排序的字段名如:'order by id desc' </param>
/// <param name="pageIndex"> 开始索引位置 </param>
/// <param name="pageSize"> 每页记录数 </param>
/// <param name="doCount"> 记录总数 </param>
/// <param name="recordCount"> 非0则统计,为0则不统计 </param>
/// <returns></returns>
public DataTable GetPageData( string tblName, string strGetFields, string strWhere, string strOrder, int pageIndex, int pageSize, int doCount, out int recordCount)
{
SteelMachining.DAL.AspNetPager AspNetPagerDAL = new SteelMachining.DAL.AspNetPager();
DataTable dt= AspNetPagerDAL.GetPageData(tblName, strGetFields, strWhere, strOrder, pageIndex, pageSize, doCount, out recordCount);
return dt;
}
/// 获取分页数据
/// </summary>
/// <param name="tblName"> 表名、视图、表连接 如:'xtest' </param>
/// <param name="strGetFields"> 需要返回的列如:'xname,xdemo' </param>
/// <param name="strWhere"> 查询条件(注意:不要加where) </param>
/// <param name="strOrder"> 排序的字段名如:'order by id desc' </param>
/// <param name="pageIndex"> 开始索引位置 </param>
/// <param name="pageSize"> 每页记录数 </param>
/// <param name="doCount"> 记录总数 </param>
/// <param name="recordCount"> 非0则统计,为0则不统计 </param>
/// <returns></returns>
public DataTable GetPageData( string tblName, string strGetFields, string strWhere, string strOrder, int pageIndex, int pageSize, int doCount, out int recordCount)
{
SteelMachining.DAL.AspNetPager AspNetPagerDAL = new SteelMachining.DAL.AspNetPager();
DataTable dt= AspNetPagerDAL.GetPageData(tblName, strGetFields, strWhere, strOrder, pageIndex, pageSize, doCount, out recordCount);
return dt;
}
==Web==
private
void Bind()
{
int recordCount = 0;
DataTable dt = ( new SteelMachining.BLL.AspNetPager()).GetPageData( " companyInfo ", " id ", "", " order by id desc ", AspNetPager1.CurrentPageIndex, AspNetPager1.PageSize, 1, out recordCount);
AspNetPager1.RecordCount = recordCount;
Repeater1.DataSource = dt;
Repeater1.DataBind();
}
protected void AspNetPager1_PageChanged( object sender, EventArgs e)
{
Bind();
}
{
int recordCount = 0;
DataTable dt = ( new SteelMachining.BLL.AspNetPager()).GetPageData( " companyInfo ", " id ", "", " order by id desc ", AspNetPager1.CurrentPageIndex, AspNetPager1.PageSize, 1, out recordCount);
AspNetPager1.RecordCount = recordCount;
Repeater1.DataSource = dt;
Repeater1.DataBind();
}
protected void AspNetPager1_PageChanged( object sender, EventArgs e)
{
Bind();
}
简单记录下...
其中分页每次算总数可以使用单独方法只计算一次.....