一、前端准备工作
1、之前我写到过《Asp.net中引用AspNetPager.dll进行数据分页》这种分页方式只能在前台将数据分页，而每次点击查询时对目标数据库还是全查询，这样不仅会消耗数据库资源，还会加长等待时间，所以本文我将介绍如何通过存储过程对数据进行真分页。
2、参考前文将基础搭建完成。
二、分页存储过程。
1、在真分页中，我们要将每一页的数据量，起始行这些发送给后端，后端接收到指令后则按照这个区间进行查询数据，后端数据分页方式在前文中我有详细介绍过。
2、后端分页：《SQL Server的两种数据分页方式简析》,参考此文的分页方式选择合适自己后端分页方法，后面我使用的是存储过程分页-GetPageRecords。
三、页面代码
PS:在AspNetPager.dl开发者官网,也有其它调用方式和用法，大家可以看看有没有新的思路，对于分页控件样式，网上有一些CSS样式可以参考，建议看看，因为原格式实在有些单调。
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" PageSize="25" HorizontalAlign="Center" Width="100%" meta:resourceKey="AspNetPager1" Style="font-size: 14px" AlwaysShow="false" FirstPageText="首页" LastPageText="尾页" NextPageText="后页" PrevPageText="前页" SubmitButtonText="Go" SubmitButtonClass="submitBtn" CustomInfoStyle="font-size:14px;text-align:left;" InputBoxStyle="width:25px; border:1px solid #999999; text-align:center; " TextBeforeInputBox="转到第" TextAfterInputBox="页 " TextAfterPageIndexBox="页" TextBeforePageIndexBox="转到" Font-Size="14px" CustomInfoHTML="共有%RecordCount%条记录,共%PageCount%页,每页%PageSize%行" ShowCustomInfoSection="Left" CustomInfoSectionWidth="30%" CssClass="paginator" PagingButtonSpacing="3px" OnPageChanged="AspNetPager1_PageChanged" ShowBoxThreshold="25"> </webdiyer:AspNetPager
常用自定义项：PageSize:页面显示的行数；
CustomInfoHTML：自定义项（这里可以任意新增删除）
四、后台代码
PS:本文中的代码是从我的测试项目中扣出来的，可根据自己的项目灵活应用，如果有更好的方式和思路欢迎留言。
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack)//判断是否回发 { RowDatasBind();//调用函数 } } //拼接页面查询条件 public string Search() { string SQL = "select PCB_CODE,WORK_ORDER_CODE,PRODUCTION_ORDER_ID,TEST_CODE,PLANT_CODE,CREATE_TIME,DEVICE_NO,(case CONCLUSION when 0 then '通过' when 1 then '不通过' else '其他' end) CONCLUSION from [dbo].[T_Aoi_TEST] WITH (NOLOCK) where 1=1"; if (!string.IsNullOrEmpty(txtWORK_ORDER_CODE.Text)) { SQL = SQL + " and WORK_ORDER_CODE like'" + "%" + txtWORK_ORDER_CODE.Text.Trim() + "%" + "'"; } if (!string.IsNullOrEmpty(txtPCB_CODE.Text)) { SQL = SQL + " and PCB_CODE like'" + "%" + txtPCB_CODE.Text.Trim() + "%" + "'"; } if (!string.IsNullOrEmpty(txtPRODUCTION_ORDER_ID.Text)) { SQL = SQL + " and PRODUCTION_ORDER_ID like'" + "%" + txtPRODUCTION_ORDER_ID.Text.Trim() + "%" + "'"; } if (!string.IsNullOrEmpty(txtBeginTime.Text) && !string.IsNullOrEmpty(txtEndTime.Text)) { SQL = SQL + " and CHECK_TIME>='" + txtBeginTime.Text + "'" + " and CHECK_TIME<='" + txtEndTime.Text + "'"; } if (ddlCon.Text != "9") { SQL = SQL + " and CONCLUSION ='" + ddlCon.Text + "'"; } //按创建时间倒序查询 SQL = SQL + "order by CREATE_TIME desc"; return SQL; } //服务器端数据分页 private void RowDatasBind() { string SearchConditions = "1=1";//搜索条件 string SQLgetcount = "select count(1) from T_Aoi_TEST where 1=1"; if (!string.IsNullOrEmpty(txtWORK_ORDER_CODE.Text)) { SearchConditions = SearchConditions + " and WORK_ORDER_CODE like''" + "%" + txtWORK_ORDER_CODE.Text.Trim() + "%" + "''"; SQLgetcount = SQLgetcount + " and WORK_ORDER_CODE like'" + "%" + txtWORK_ORDER_CODE.Text.Trim() + "%" + "'"; } if (!string.IsNullOrEmpty(txtPCB_CODE.Text)) { SearchConditions = SearchConditions + " and PCB_CODE like''" + "%" + txtPCB_CODE.Text.Trim() + "%" + "''"; SQLgetcount = SQLgetcount + " and PCB_CODE like'" + "%" + txtPCB_CODE.Text.Trim() + "%" + "'"; } if (!string.IsNullOrEmpty(txtPRODUCTION_ORDER_ID.Text)) { SearchConditions = SearchConditions + " and PRODUCTION_ORDER_ID like''" + "%" + txtPRODUCTION_ORDER_ID.Text.Trim() + "%" + "''"; SQLgetcount = SQLgetcount + " and PRODUCTION_ORDER_ID like'" + "%" + txtPRODUCTION_ORDER_ID.Text.Trim() + "%" + "'"; } if (!string.IsNullOrEmpty(txtBeginTime.Text) && !string.IsNullOrEmpty(txtEndTime.Text)) { SearchConditions = SearchConditions + " and CHECK_TIME>=''" + txtBeginTime.Text + "''" + " and CHECK_TIME<=''" + txtEndTime.Text + "''"; SQLgetcount = SQLgetcount + " and CHECK_TIME>='" + txtBeginTime.Text + "'" + " and CHECK_TIME<='" + txtEndTime.Text + "'"; } if (ddlCon.Text != "9") { SearchConditions = SearchConditions + " and CONCLUSION =''" + ddlCon.Text + "''"; SQLgetcount = SQLgetcount + " and CONCLUSION ='" + ddlCon.Text + "'"; } int StartRow = (AspNetPager1.CurrentPageIndex * AspNetPager1.PageSize) - AspNetPager1.PageSize;//计算起始行 string SQLGetPage = "EXEC [Common_GetPageRecords] @StartRow=" + StartRow + ",@MaxRows = " + AspNetPager1.PageSize + ",@TableName = N'T_Aoi_TEST',@PrimaryKey = N'TEST_CODE',@GetFields = N'PCB_CODE,WORK_ORDER_CODE,PRODUCTION_ORDER_ID,TEST_CODE,PLANT_CODE,CREATE_TIME,DEVICE_NO,(case CONCLUSION when 0 then ''通过'' when 1 then ''不通过'' else ''其他'' end) CONCLUSION',@SearchConditions ='" + SearchConditions + "',@SortExpression = N'TEST_CODE desc'"; //计算总行数 DataSet dss = DBHelper.GetDataSetMis(SQLgetcount); DataTable fileNameDt = dss.Tables[0]; this.AspNetPager1.RecordCount = Convert.ToInt32(fileNameDt.Rows[0][0].ToString());//总行数 //分页后数据填充 DataSet ds = DBHelper.GetDataSetMis(SQLGetPage); PagedDataSource pds = new PagedDataSource(); pds.AllowPaging = true;//是否开启分页 pds.AllowServerPaging = true;//是否开启服务器端分页 pds.CurrentPageIndex = AspNetPager1.CurrentPageIndex;//当前页的页码 pds.PageSize = AspNetPager1.PageSize;//每页显示的行数 int aaa = ds.DefaultViewManager.DataSet.Tables.Count; if (ds.DefaultViewManager.DataSet.Tables.Count == 0)//修复当查询结果为空时“无法找到表 0。”的错误 { pds.DataSource = null; } else { pds.DataSource = ds.Tables[0].DefaultView; if (ds.Tables[0].Rows.Count != AspNetPager1.PageSize)//修复最后一页因为剩余尾数和页数不对应出现索引错误的问题 { pds.PageSize = ds.Tables[0].Rows.Count; } } this.GridView1.DataSource = pds; this.GridView1.DataBind(); } /// <summary> /// 绑定数据到分页控件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void AspNetPager1_PageChanged(object sender, EventArgs e) { RowDatasBind(); } /// <summary> /// 条件查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnQuery_Click(object sender, EventArgs e) { RowDatasBind(); }
五、总结
到这里真假分页以及分页存储过程都已经完成了，页面加载时再也不用漫长的等待。如果你有更好方法建议欢迎吐槽、留言~