一、设计原理阐述

数据查询分页，这个功能相信大家都很熟悉，通过数据库或其它数据源进行查询操作后，将获得的数据显示到界面上，但是由于数据量太大，不能一次性完全的显示出来，就有了数据分页的需求。这个需求在实际开发过程中还是普遍存在的，也给出了不同的实现，正常的的几种思路有：

1、一次性将所有要查询的数据查询出来，然后在客户端处理，分页显示相应的数据。

2、每次只取我所需要的那部分数据，在服务器端分页完成后，再发送到客户端显示。

在asp.net中，GridView控件自带的分页功能，就是引用了第一种思路，但是这个方法有很大的弊端，每次请求都是将所有的数据，一次性的读入到DataTable或DataSet或其它集合当中，然后再分区间显示出来，显然造成网络资源的紧张。而如果在服务器端将数据已经分好页，再传送，网络资源显然压力大幅度减少，这样子就提高了查询的效率。

如果在思路2的基础上，再进行优化，还可以有以下几种思路：

1）通过SQL语句分页

2）引用动态SQL语句进行分页

3）引用存储过程进行数据分页

显然这里推荐第3种解决方案，理由是：存储过程由于在服务器端数据库已经预先编译好，不需要运行时编译，应该可以直接运行，本身的运行效率比SQL要高；安全性高，防止SQL注入式攻击（带参数的SQL语句比简单的SQL拼接要安全）；便于代码管理和复用，完全可以直接将存储过程代码保存起来，在下一个项目中直接引用。

至此，数据查询分页原理已经设计完毕，再进一步，可以引用web页面静态化技术（不是伪静态，就是真的生成html文件），定期的将动态html生成静态html，这样可以有效提高页面的访问速度，显然已经不需要每次再到数据库中查询了，省去了数据库查询和网络数据传送的时间。

二、代码实例

不论是asp、asp.net、jsp、php还是其它web开发语言，涉及到分页这一块，原理都是想通的，这里仅以asp.net为例，完整的设计一个数据查询分页功能。

1、完全自己写分页代码

这个无非就是利用sql语句的分页功能实现分页，C#后台拼接各种字符串，这里不想过多阐述。

2、引用第三方分页控件，比如aspnetpager等

首先新建一个分页实体类：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
public  class  MPage
{
     private  string  pagesql; //产生的sql语句
     private  int  pagesize; //每页显示的条数
     private  int  pageindex; //显示页的索引
                            
     /// <summary>
     /// 产生分页的sql语句 sql要求必须包含rownum字段且其别名为rn,如例子:select rownum rn,t.* from test t
     /// </summary>
     public  string  Pagesql
     {
         get  {  return  pagesql; }
         set  { pagesql = value; }
     }
                                
     /// <summary>
     /// 每页显示的条数
     /// </summary>
     public  int  Pagesize
     {
         get  {  return  pagesize; }
         set  { pagesize = value; }
     }
                            
     /// <summary>
     /// 显示页的索引 从0开始
     /// </summary>
     public  int  Pageindex
     {
         get  {  return  pageindex; }
         set  { pageindex = value; }
     }
}

分页查询类：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
/// <summary>
/// 获取分页的记录
/// </summary>
/// <param name="page">封装的页面对象</param>
/// <param name="result">反馈的结果</param>
/// <returns>结果集的表</returns>
public  DataTable GetPageRecord(MPage page,  out  ArrayList result)
{
     OracleParameter[] oracleParameter =  new  OracleParameter[6];
                       
     oracleParameter[0] =  new  OracleParameter( "p_pagesql" , OracleType.VarChar);
     oracleParameter[0].Direction = ParameterDirection.Input;
     oracleParameter[0].Value = page.Pagesql;
                       
     oracleParameter[1] =  new  OracleParameter( "p_pagesize" , OracleType.Number);
     oracleParameter[1].Direction = ParameterDirection.Input;
     oracleParameter[1].Value = page.Pagesize;
                       
     oracleParameter[2] =  new  OracleParameter( "p_pageindex" , OracleType.Number);
     oracleParameter[2].Direction = ParameterDirection.Input;
     oracleParameter[2].Value = page.Pageindex;
                       
     oracleParameter[3] =  new  OracleParameter( "p_totalcount" , OracleType.Number);
     oracleParameter[3].Direction = ParameterDirection.Output;
                       
     oracleParameter[4] =  new  OracleParameter( "p_pagecount" , OracleType.Number);
     oracleParameter[4].Direction = ParameterDirection.Output;
                       
     oracleParameter[5] =  new  OracleParameter( "p_currentpagedata" , OracleType.Cursor);
     oracleParameter[5].Direction = ParameterDirection.Output;
                       
     DataAccess da =  new  DataAccess();
     return  da.ExecuteProcedureWithTable(DBConn.sb,  ref  oracleParameter,  "p_app_page" ,  out  result);
}

用aspnetpager控件和repeater控件结合oracle存储过程绑定：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
/// <summary>
/// 用aspnetpager控件和repeater控件结合oracle存储过程绑定
/// </summary>
/// <param name="aspnetpager">aspnetpager控件</param>
/// <param name="bindingtarget">repeater控件</param>
/// <param name="pagesql">执行的sql语句</param>
public  void  BindingRepeaterWithAspNetPager(AspNetPager aspnetpager, Repeater bindingtarget,  string  pagesql)
{
     int  recordcount = 0;
     ArrayList result =  null ;
     MPage page =  new  MPage();
     page.Pagesize = aspnetpager.PageSize; //每页显示的条数
     page.Pageindex = aspnetpager.StartRecordIndex / aspnetpager.PageSize; //显示页的索引 从0开始
     page.Pagesql = pagesql; //产生分页的sql语句 sql要求必须包含rownum字段且其别名为rn,如例子:select rownum rn,t.* from test t
                  
     bindingtarget.DataSource = GetPageRecord(page,  out  result);
     bindingtarget.DataBind();
                  
     if  (result !=  null  && result.Count > 0)
     {
         if  ( int .TryParse(result[3].ToString(),  out  recordcount))
         {
             aspnetpager.RecordCount = recordcount;
         }
         else
         {
             aspnetpager.RecordCount = 0;
         }
     }
}

aspnetpager控件和repeater控件直接绑定datatable：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/// <summary>
/// aspnetpager控件和repeater控件直接绑定Datatable
/// </summary>
/// <param name="aspnetpager">aspnetpager控件</param>
/// <param name="bindingtarget">repeater控件</param>
/// <param name="dt">Datatable</param>
public  void  BindingRepeaterWithAspNetPagerByDataTable(AspNetPager aspnetpager, Repeater bindingtarget, DataTable dt)
{
     PagedDataSource pds =  new  PagedDataSource();
     pds.AllowPaging =  true ;
     pds.PageSize = aspnetpager.PageSize;
     pds.CurrentPageIndex = aspnetpager.CurrentPageIndex - 1;
     pds.DataSource = dt.DefaultView;
     aspnetpager.RecordCount = pds.DataSourceCount;
             
     bindingtarget.DataSource = pds;
     bindingtarget.DataBind();
}

前台代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
< asp:Repeater  ID = "Repeater1"  runat = "server" >
                < HeaderTemplate >
                    < table  cellpadding = "0"  cellspacing = "0"  border = "1"  width = "100%" >
                        < thead  align = "center" >
                            < tr >
                                < td  style = "width: 5%;" >
                                    < label >
                                        < b >序号</ b ></ label >
                                </ td >
                                < td  style = "width: 10%;" >
                                    < label >
                                        < b >单位代码</ b ></ label >
                                </ td >
                                < td  style = "width: 25%;" >
                                    < label >
                                        < b >单位名称</ b ></ label >
                                </ td >
                                < td  style = "width: 8%;" >
                                    < label >
                                        < b >单位申报所属期</ b ></ label >
                                </ td >
                                < td  style = "width: 8%;" >
                                    < label >
                                        < b >单位申报基数</ b ></ label >
                                </ td >
                                < td  style = "width: 8%;" >
                                    < label >
                                        < b >单位申报个人基数</ b ></ label >
                                </ td >
                                < td  style = "width: 8%;" >
                                    < label >
                                        < b >单位应缴总金额</ b ></ label >
                                </ td >
                                < td  style = "width: 8%;" >
                                    < label >
                                        < b >单位到帐总金额</ b ></ label >
                                </ td >
                                < td  style = "width: 8%;" >
                                    < label >
                                        < b >单位到帐时间</ b ></ label >
                                </ td >
                            </ tr >
                        </ thead >
                </ HeaderTemplate >
                < ItemTemplate >
                    < tr  align = "center"  style='background-color: <%#(Container.ItemIndex%2==0)?"#eeeeee":"#ffffff"%>'
                        onmouseover="this.style.background='#ddeeff'" onmouseout="this.style.background='<%#(Container.ItemIndex%2==0)?"#eeeeee":"#ffffff"%>'">
                        < td >
                            < label >
                                <%#Eval("rn") %></ label > <!--序号-->
                        </ td >
                        < td >
                            < label >
                                <%#Eval("dwdm") %></ label > <!--单位代码-->
                        </ td >
                        < td >
                            < label >
                                <%#Eval("dwmc") %></ label > <!--单位名称-->
                        </ td >
                        < td >
                            < label >
                                <%#Eval("dwsbssq") %></ label > <!--单位申报所属期-->
                        </ td >
                        < td >
                            < label >
                                <%#Eval("dwsbjs") %></ label > <!--单位申报基数-->
                        </ td >
                        < td >
                            < label >
                                <%#Eval("dwsbgrjs") %></ label > <!--单位申报个人基数-->
                        </ td >
                        < td >
                            < label >
                                <%#Eval("dwyjzje") %></ label > <!--单位应缴总金额-->
                        </ td >
                        < td >
                            < label >
                                <%#Eval("dwdzzje") %></ label > <!--单位到帐总金额-->
                        </ td >
                        < td >
                            < label >
                                <%#Eval("dwdzsj") %> <!--单位到帐时间 --> </ label >
                        </ td >
                    </ tr >
                </ ItemTemplate >
                < FooterTemplate >
                    </ table >
                </ FooterTemplate >
            </ asp:Repeater >
            < webdiyer:AspNetPager  ID = "AspNetPager1"  runat = "server"  AlwaysShow = "True"  Font-Size = "10pt"
                Font-Names = "gb2312"  CustomInfoHTML = "第%CurrentPageIndex%页 共%RecordCount%条记录"  ShowCustomInfoSection = "Right"
                SubmitButtonText = "跳转"  TextAfterPageIndexBox = "页"  OnPageChanged = "AspNetPager1_PageChanged"
                CurrentPageButtonPosition = "Center"  CustomInfoSectionWidth = "45%"  CustomInfoTextAlign = "Center"
                PageSize = "25"  ShowPageIndex = "True"  Width = "92%" >
            </ webdiyer:AspNetPager >