aspnetpager+repeater+oracle实现分页功能

一、设计原理阐述

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

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>
上一篇:电脑里的记事本文件永久删除怎么办


下一篇:java-reflect反射