aspnetpager分页配通用存储过程
存储过程 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();
}
简单记录下...
其中分页每次算总数可以使用单独方法只计算一次.....
