前言:
公司项目开发,上周的任务是做基础数据的管理。在Sharepoint2010里边内嵌asp.net的aspx页,遇到了各种各样奇葩的问题,因为之前对sharepoint只是有一些了解,但是没有设计到具体的编程工作,这一次算是初次接触吧。其中有一部分基础数据数据量很大,大致有十多万,因为是对基础数据的维护,所以还需要对数据进行列表展示,增删改查什么的,大家都知道Asp.net里边的GridView有自带的分页,但是,那个分页对于少量的数据还好,对于这种数十万的数据量而言,这种分页方式简直就是灾难。网上关于GridView高效分页的东西有很多,找了一个自己改了改。用着效果还不错,和大家分享一下。
这是分页的效果图
下边就讲一下具体的实现,首先声明,这个东西是不是我原创的,只是在此基础上进行了修饰和完善。希望能给各位有所启发。
一、前台布局
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
|
< div >
< div id = "main" >
< div id = "search" >
< table >
< tr >
< td >
< asp:Label ID = "lb" runat = "server" Text = "姓名" ></ asp:Label ></ td >
< td >
< asp:TextBox ID = "SearchName" runat = "server" ></ asp:TextBox >
</ td >
< td >
< asp:Button ID = "btnSearch" runat = "server" Text = "查询" onclick = "PagerBtnCommand_OnClick" CommandName = "search" />
</ td >
< td >
< asp:Button ID = "btnReset" runat = "server" Text = "重置" onclick = "btnReset_Click" />
</ td >
</ tr >
</ table >
</ div >
< div id = "gridView" >
< asp:GridView ID = "UserGridView" runat = "server" AutoGenerateColumns = "false" >
< Columns >
< asp:TemplateField HeaderText = "用户名" >
< ItemTemplate >
< asp:Label ID = "UserName" runat = "server" Text='<%#Eval("username")
%>'></ asp:Label >
</ ItemTemplate >
</ asp:TemplateField >
< asp:TemplateField HeaderText = "单位名称" >
< ItemTemplate >
< asp:Label ID = "DisplayName" runat = "server" Text='<%#Eval("displayname")
%>'></ asp:Label >
</ ItemTemplate >
</ asp:TemplateField >
< asp:TemplateField HeaderText = "组织编码" >
< ItemTemplate >
< asp:Label ID = "OrgCode" runat = "server" Text='<%#Eval("orgcode")
%>'></ asp:Label >
</ ItemTemplate >
</ asp:TemplateField >
< asp:TemplateField HeaderText = "组织名称" >
< ItemTemplate >
< asp:Label ID = "OrgName" runat = "server" Text='<%#Eval("orgname")
%>'></ asp:Label >
</ ItemTemplate >
</ asp:TemplateField >
</ Columns >
</ asp:GridView >
</ div >
< div id = "page" >
< table >
< tr >
< td >
< asp:Label ID = "lbcurrentpage1" runat = "server" Text = "当前页:" ></ asp:Label >
< asp:Label ID = "lbCurrentPage" runat = "server" Text = "" ></ asp:Label >
< asp:Label ID = "lbFenGe" runat = "server" Text = "/" ></ asp:Label >
< asp:Label ID = "lbPageCount" runat = "server" Text = "" ></ asp:Label >
</ td >
< td >
< asp:Label ID = "recordscount" runat = "server" Text = "总条数:" ></ asp:Label >
< asp:Label ID = "lbRecordCount" runat = "server" Text = "" ></ asp:Label >
</ td >
< td >
< asp:Button ID = "Fistpage" runat = "server" CommandName = "" Text = "首页" OnClick = "PagerBtnCommand_OnClick" />
< asp:Button ID = "Prevpage" runat = "server" CommandName = "prev" Text = "上一页"
OnClick = "PagerBtnCommand_OnClick" />
< asp:Button ID = "Nextpage" runat = "server" CommandName = "next" Text = "下一页" OnClick = "PagerBtnCommand_OnClick" />
< asp:Button ID = "Lastpage" runat = "server" CommandName = "last" Text = "尾页"
key = "last" OnClick = "PagerBtnCommand_OnClick" />
</ td >
< td >
< asp:Label ID = "lbjumppage" runat = "server" Text = "跳转到第" ></ asp:Label >
< asp:TextBox ID = "GotoPage" runat = "server" Width = "25px" ></ asp:TextBox >
< asp:Label ID = "lbye" runat = "server" Text = "页" ></ asp:Label >
< asp:Button ID = "Jump" runat = "server" Text = "跳转" CommandName = "jump" OnClick = "PagerBtnCommand_OnClick" />
</ td >
</ tr >
</ table >
</ div >
</ div >
</ div >
|
布局的效果如下:
二、后台的代码实现
我会一点一点向大家讲解清楚,这个分页的原理
Members:这里主要定义几个全局的变量,主要用来记录信息的数量、页的数量和当前页
#region
Members
const int PAGESIZE
= 10;
int PagesCount,
RecordsCount;
int CurrentPage,
Pages, JumpPage;
const string COUNT_SQL
= "select
count(*) from p_user" ;
#endregion
|
Methods:
1、GetRecordsCount:该方法主要用来获取当前信息的总数,有一个sqlSearch参数,默认的为default,即初始化页面时,查询所有信息的总条数,当用户输入要搜索的用户名进行检索时,获取符合用户检索条件的信息的总条数
///
<summary>
///
获取信息总数
///
</summary>
///
<param name="sqlSearch"></param>
///
<returns></returns>
public static int GetRecordsCount( string sqlRecordsCount)
{
string sqlQuery;
if (sqlRecordsCount
== "default" )
{
sqlQuery
= COUNT_SQL;
}
else
{
sqlQuery
= sqlRecordsCount;
}
int RecordCount
= 0;
SqlCommand
cmd = new SqlCommand(sqlQuery,
Conn());
RecordCount
= Convert.ToInt32(cmd.ExecuteScalar());
cmd.Connection.Close();
return RecordCount;
}
|
2、OverPage:该方法主要用来计算剩余页,当前设置的为每页显示10条数据,如何符合条件的数据有11条,则要显示2页
///
<summary>
///
计算余页
///
</summary>
///
<returns></returns>
public int OverPage()
{
int pages
= 0;
if (RecordsCount
% PAGESIZE != 0)
pages
= 1;
else
pages
= 0;
return pages;
}
|
3、ModPage:该方法也是用计算余页,主要用来防止SQL执行溢出
///
<summary>
///
计算余页,防止SQL语句执行时溢出查询范围
///
</summary>
///
<returns></returns>
public int ModPage()
{
int pages
= 0;
if (RecordsCount
% PAGESIZE == 0 && RecordsCount != 0)
pages
= 1;
else
pages
= 0;
return pages;
}
|
4、Conn:该方法用来创建数据连接对象,在使用的时候只需改成自己的数据库名即可
///
<summary>
///
数据连接对象
///
</summary>
///
<returns></returns>
public static SqlConnection
Conn()
{
SqlConnection
conn = new SqlConnection( "data
source=.;initial catalog=DB_GSL_ZCW;Integrated Security=true" );
conn.Open();
return conn;
}
|
5、GridViewDataBind:该方法主要用来数据绑定,如果传入的参数为default则,默认的绑定所有的数据,否则,则绑定过滤过的数据
///
<summary>
///
GridView数据绑定,根据传入参数的不同,进行不同方式的查询,
///
</summary>
///
<param name="sqlSearch"></param>
private void GridViewDataBind( string sqlSearch)
{
CurrentPage
= ( int )ViewState[ "PageIndex" ];
Pages
= ( int )ViewState[ "PagesCount" ];
if (CurrentPage
+ 1 > 1)
{
Fistpage.Enabled
= true ;
Prevpage.Enabled
= true ;
}
else
{
Fistpage.Enabled
= false ;
Prevpage.Enabled
= false ;
}
if (CurrentPage
== Pages)
{
Nextpage.Enabled
= false ;
Lastpage.Enabled
= false ;
}
else
{
Nextpage.Enabled
= true ;
Lastpage.Enabled
= true ;
}
DataSet
ds = new DataSet();
string sqlResult;
if (sqlSearch
== "default" )
{
sqlResult
= "Select
Top " +
PAGESIZE + "user_serialid,username,displayname,orgcode,orgname
from p_user where user_serialid not in(select top " +
PAGESIZE * CurrentPage + "
user_serialid from p_user order by user_serialid asc) order by user_serialid asc" ;
}
else
{
sqlResult
= sqlSearch;
}
SqlDataAdapter
sqlAdapter = new SqlDataAdapter(sqlResult,
Conn());
sqlAdapter.Fill(ds,
"Result" );
UserGridView.DataSource
= ds.Tables[ "Result" ].DefaultView;
UserGridView.DataBind();
lbCurrentPage.Text
= (CurrentPage + 1).ToString();
GotoPage.Text
= (CurrentPage + 1).ToString();
sqlAdapter.Dispose();
}
|
6、Page_Load:页面加载函数,主要是在首次进入页面时,进行初始化,默认的获取所有的信息
protected void Page_Load( object sender,
EventArgs e)
{
if (!IsPostBack)
{
RecordsCount
= GetRecordsCount( "default" );
PagesCount
= RecordsCount / PAGESIZE + OverPage();
ViewState[ "PagesCount" ]
= RecordsCount / PAGESIZE - ModPage();
ViewState[ "PageIndex" ]
= 0;
ViewState[ "JumpPages" ]
= PagesCount;
lbPageCount.Text
= PagesCount.ToString();
lbRecordCount.Text
= RecordsCount.ToString();
if (RecordsCount
<= 10)
{
GotoPage.Enabled
= false ;
}
GridViewDataBind( "default" );
}
}
|
7、PagerBtnCommand_OnClick:该方法主要用来处理设计视图页的“首页”、“下一页”,“上一页”,“尾页”,“查询”按钮的Click事件,主要通过不同按钮的CommandName属性来分别处理,需要在前台为每一个按钮相应的CommandName属性赋值,如果用户点击的是“查询”按钮,这个时候需要对查询的Sql语句进行重写,加入过滤条件,即用户输入的查询的条件
///
<summary>
///
页面按钮Click处理
///
</summary>
///
<param name="sender"></param>
///
<param name="e"></param>
protected void PagerBtnCommand_OnClick( object sender,
EventArgs e)
{
CurrentPage
= ( int )ViewState[ "PageIndex" ];
Pages
= ( int )ViewState[ "PagesCount" ];
Button
btn = sender as Button;
string sqlResult= "default" ;
if (btn
!= null )
{
string cmd
= btn.CommandName;
switch (cmd)
{
case "next" :
CurrentPage++;
break ;
case "prev" :
CurrentPage--;
break ;
case "last" :
CurrentPage
= Pages;
break ;
case "search" :
if (! string .IsNullOrEmpty(SearchName.Text))
{
RecordsCount
= GetRecordsCount( "select
count(*) from p_user where username like '" +
SearchName.Text + "%'" );
PagesCount
= RecordsCount / PAGESIZE + OverPage();
ViewState[ "PagesCount" ]
= RecordsCount / PAGESIZE - ModPage();
ViewState[ "PageIndex" ]
= 0;
ViewState[ "JumpPages" ]
= PagesCount;
lbPageCount.Text
= PagesCount.ToString();
lbRecordCount.Text
= RecordsCount.ToString();
if (RecordsCount
<= 10)
GotoPage.Enabled
= false ;
sqlResult
= "Select
Top " +
PAGESIZE + "user_serialid,username,displayname,orgcode,orgname
from p_user where user_serialid not in(select top " +
PAGESIZE * CurrentPage + "
user_serialid from p_user order by user_serialid asc) and username like '" +
SearchName.Text + "%'
order by user_serialid asc" ;
}
else
{
Response.Write( "请输入您所要查找的用户姓名!" );
}
break ;
case "jump" :
JumpPage
= ( int )ViewState[ "JumpPages" ];
if (Int32.Parse(GotoPage.Text)
> JumpPage || Int32.Parse(GotoPage.Text) <= 0)
Response.Write( "<script>alert('页码范围越界!')</script>" );
else
{
int InputPage
= Int32.Parse(GotoPage.Text.ToString()) - 1;
ViewState[ "PageIndex" ]
= InputPage;
CurrentPage
= InputPage;
sqlResult
= "Select
Top " +
PAGESIZE + "user_serialid,username,displayname,orgcode,orgname
from p_user where user_serialid not in(select top " +
PAGESIZE * CurrentPage + "
user_serialid from p_user order by user_serialid asc) and username like '" +
SearchName.Text + "%'
order by user_serialid asc" ;
}
break ;
default :
CurrentPage
= 0;
break ;
}
ViewState[ "PageIndex" ]
= CurrentPage;
GridViewDataBind(sqlResult);
}
}
|
8、btn_Reset_Click:该方法主要用来进行重置,用户完成一次查询之后,需要重置,才能进行下一次的查询操作
protected void btnReset_Click( object sender,
EventArgs e)
(
RecordsCount
= GetRecordsCount( "default" );
PagesCount
= RecordsCount / PAGESIZE + OverPage();
ViewState[ "PagesCount" ]
= RecordsCount / PAGESIZE - ModPage();
ViewState[ "PageIndex" ]
= 0;
ViewState[ "JumpPages" ]
= PagesCount;
lbPageCount.Text
= PagesCount.ToString();
lbRecordCount.Text
= RecordsCount.ToString();
if (RecordsCount
<= 10)
{
GotoPage.Enabled
= false ;
}
GridViewDataBind( "default" );
}
|
这里的高效分页方法主要用的是select top 10 Id ,Name from tb where Id not in (select top 10*N from tb order by Id asc) order by Id asc
示例中的N代表的是页数,之前原子里也有很多关于高效分页的讨论,这里就不再多说了,我个人觉得这个分页语句效果还不错,当然除此之外还有row_number()函数分页、select Max() 分页等等方法,之前也有总结过,有兴趣的朋友可以看一下我之前写的ListView和Repeater高效分页这篇文章,里边讲述的也很详细,只要你一步一步的照着去操练应该问题不大的。
这里需要解释一下的是为什么没有有数据绑定控件直接进行绑定,因为在sharepoint2010项目里边它支持的数据源控件只有XMLDataSource,所以就只有自己动手实现了。
好了今天就到这里了,希望能给大家带来些帮助吧!还请多多指教!
分享到:
相关推荐
asp.net中GridView高效分页示例,非使用存储过程,简单易用,效率高。
gridview存储过程+分页1
Gridview各种功能+AspNetPager+Ajax实现无刷新存储过程分页
GridView重写+分页改写(小丑)GridView重写+分页改写(小丑)GridView重写+分页改写(小丑)GridView重写+分页改写(小丑)GridView重写+分页改写(小丑)
android Gridview分页实现的一个小例子
gridview存储过程+分页2
gridview的左右滑动, 并且有分页, 动态加载数据
ActivityGroup + GridView + ViewFlipper 实现选项卡源代码.zip ActivityGroup + GridView + ViewFlipper 实现选项卡源代码.zip ActivityGroup + GridView + ViewFlipper 实现选项卡源代码.zip ActivityGroup + ...
ActivityGroup + GridView 实现Tab分页标签
GridView+ScrollView+intent实例
gridview实现自定义分页
既有前辈整理的Repeater + AspNetPager + 分页存储对百万级数据查询(520万),也有小弟实现GridView + DetailsView + AspnetPager + 分页存储 对百万级数据的增删改查。唉,老板只因为不够美观,摒弃了。感觉有些用处...
gridview自带分页源码,及数据分析;gridview自带分页源码,及数据分析;gridview自带分页源码,及数据分析;
GridView高效分页和搜索功能的实现代码,需要的朋友可以参考一下
既有前辈整理的Repeater + AspNetPager + 分页存储对百万级数据查询(520万),也有小弟实现GridView + DetailsView + AspnetPager + 分页存储 对百万级数据的增删改查。 唉,老板只因为不够美观,摒弃了。感觉有些...
1.asp.net gridview实现手写分页 2.GridView数据一键导出Excel
ASP.NET 控件GridView分页 ,最简单的GridView自定义分页
GridView LINQ TO SQL 高效分页 一次只读处当前页的条数
使用ObjectDataSource和Gridview自定义分页,采用Microsoft.Practices.EnterpriseLibrary.Data4.1操作数据库
这是我最近做项目前做的一个用gridview+sql存储过程 实现真分页的小例子。 很适合要用到gridview的一些操作和数据绑定及数据分页读取的学习。里面用到了第三方devexpress的ASPxGridView控件,如果没安装的只要把它用...