|
當(dāng)數(shù)據(jù)庫(kù)的數(shù)據(jù)量比較大,，對(duì)執(zhí)行效率要求比較高的時(shí)候,，我們可以考慮使用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)分頁(yè)，根據(jù)傳入的頁(yè)數(shù)返回需要顯示的數(shù)據(jù)表,，僅僅select出當(dāng)前頁(yè)的數(shù)據(jù),。（這個(gè)比使用PagedDataSource類而言效率要高。）
現(xiàn)在采用Repeater來(lái)實(shí)現(xiàn)一個(gè)數(shù)據(jù)分頁(yè),，數(shù)據(jù)庫(kù)采用SQL server2000,，利用里面的系統(tǒng)表Northwind。
新建存儲(chǔ)過(guò)程如下：
create PROCEDURE dbo.myPaging
(
@pagesize int,
@currentPage int,
@total int output
)
AS
create table #temp
(
ID int identity(1,1),
CustomerID varchar(50),
CompanyName varchar(50),
ContactName varchar(50),
ContactTitle varchar(50),
Phone varchar(50)
)
insert into #temp(CustomerID,CompanyName,ContactName,ContactTitle,Phone)
select CustomerID,CompanyName,ContactName,ContactTitle,Phone
from Customers
select @total=(select count(*) from Customers)
declare @startID int
declare @endID int
set @startID=(@currentpage-1)*@pagesize+1
set @endID=@currentpage*@pagesize
select * from #temp where ID>=@startID and ID<=@endID
GO
若不會(huì)寫(xiě)存儲(chǔ)過(guò)程的話,，可以參照網(wǎng)站在線生成分頁(yè)的存儲(chǔ)過(guò)程：http://www./AspNetPager/utility/sqlspgen.aspx
前臺(tái)代碼：
Code
1<body>
2 <form id="Form1" method="post" runat="server">
3 <FONT face="宋體">
4 <asp:DataList id="DataList1" runat="server" Width="744px" DataKeyField="customerID">
5 <HeaderTemplate>
6 <TABLE id="Table2" cellSpacing="1" cellPadding="0" width="100%" bgColor="#990033" border="0">
7 <TR>
8 <TD bgColor="#ffffff">公司名稱</TD>
9 <TD bgColor="#ffffff">聯(lián)系人名字</TD>
10 <TD bgColor="#ffffff">聯(lián)系標(biāo)題</TD>
11 <TD bgColor="#ffffff">聯(lián)系電話</TD>
12 </TR>
13 </TABLE>
14 </HeaderTemplate>
15 <ItemTemplate>
16 <TABLE id="Table1" cellSpacing="1" cellPadding="0" width="100%" bgColor="#990033" border="0">
17 <TR>
18 <TD bgColor="#ffffff">
19 <asp:Label id="Label1" runat="server" Width="40%">
20 <%# DataBinder.Eval(Container.DataItem,"CompanyName")%>
21 </asp:Label></TD>
22 <TD bgColor="#ffffff">
23 <asp:Label id="Label2" runat="server" Width="20%">
24 <%# DataBinder.Eval(Container.DataItem,"ContactName")%>
25 </asp:Label></TD>
26 <TD bgColor="#ffffff">
27 <asp:Label id="Label3" runat="server" Width="20%">
28 <%# DataBinder.Eval(Container.DataItem,"ContactTitle")%>
29 </asp:Label></TD>
30 <TD bgColor="#ffffff">
31 <asp:Label id="Label4" runat="server" Width="20%">
32 <%# DataBinder.Eval(Container.DataItem,"Phone")%>
33 </asp:Label></TD>
34 </TR>
35 </TABLE>
36 </ItemTemplate>
37 </asp:DataList></FONT>
38 <TABLE id="Table3" cellSpacing="0" cellPadding="1" width="744" border="0">
39 <TR>
40 <TD style="height: 20px"><FONT face="宋體">
41 <P align="center">
42 <asp:LinkButton id="LinkButton1" runat="server"onclick="LinkButton1_Click">第一頁(yè)</asp:LinkButton></P>
43 </FONT>
44 </TD>
45 <TD style="height: 20px">
46 <P align="center">
47 <asp:LinkButton id="LinkButton2" runat="server"onclick="LinkButton2_Click">上一頁(yè)</asp:LinkButton></P>
48 </TD>
49 <TD style="height: 20px">
50 <P align="center">
51 <asp:LinkButton id="LinkButton3" runat="server"onclick="LinkButton3_Click">下一頁(yè)</asp:LinkButton></P>
52 </TD>
53 <TD style="height: 20px">
54 <P align="center">
55 <asp:LinkButton id="LinkButton4" runat="server"onclick="LinkButton4_Click">最后一頁(yè)</asp:LinkButton></P>
56 </TD>
57 </TR>
58 <TR>
59 <TD colSpan="2">
60 <P align="center"><FONT face="宋體">當(dāng)前第
61 <asp:Label id="Label5" runat="server"></asp:Label>頁(yè)</FONT></P>
62 </TD>
63 <TD colSpan="2">
64 <P align="center"><FONT face="宋體">總共
65 <asp:Label id="Label6" runat="server"></asp:Label>頁(yè)</FONT></P>
66 </TD>
67 </TR>
68 </TABLE>
69 </form>
70 </body>
后臺(tái)代碼如下：
Code
1using System;
2using System.Collections;
3using System.ComponentModel;
4using System.Data;
5using System.Drawing;
6using System.Web;
7using System.Web.SessionState;
8using System.Web.UI;
9using System.Web.UI.WebControls;
10using System.Web.UI.HtmlControls;
11using System.Data.SqlClient;
12
13namespace Paging
14{
15 public partial class WebForm1 : System.Web.UI.Page
16 {
17
18 //定義每頁(yè)顯示的長(zhǎng)度
19 int pagesize=5;
20
21 protected void Page_Load(object sender, System.EventArgs e)
22 {
23 int currentpage=1;//設(shè)置當(dāng)前頁(yè)為1
24
25 if(!IsPostBack)
26 {
27 BindCustomers(pagesize,currentpage);
28 }
29 }
30
31 void BindCustomers(int pagesize,int currentpage)
32 {
33 Label5.Text=currentpage.ToString();
34
35 string str="server=.;uid=sa;pwd=;database=Northwind";
36 SqlConnection con=new SqlConnection(str);
37 SqlDataAdapter da=new SqlDataAdapter("myPaging",con);//定義用存儲(chǔ)過(guò)程
38 da.SelectCommand.CommandType=CommandType.StoredProcedure;
39 da.SelectCommand.Parameters.Add("@pagesize",pagesize);//每頁(yè)顯示頁(yè)數(shù)
40 da.SelectCommand.Parameters.Add("@currentpage",currentpage);//當(dāng)前頁(yè)
41 da.SelectCommand.Parameters.Add("@total",SqlDbType.Int);//總共數(shù)據(jù)的條數(shù)
42
43 //指示參數(shù)是輸出,Output參數(shù)是輸出參數(shù)
44 da.SelectCommand.Parameters["@total"].Direction=ParameterDirection.Output;
45 con.Open();
46 DataSet ds=new DataSet();
47 da.Fill(ds);
48 DataList1.DataSource=ds;
49 DataList1.DataBind();
50
51 //獲得總共數(shù)據(jù)數(shù)目
52 int total=Convert.ToInt32(da.SelectCommand.Parameters["@total"].Value);
53 //獲得總頁(yè)數(shù)
54
55 int totalpage=Convert.ToInt32(Math.Ceiling(total*1.0/pagesize));
56
57 Label6.Text=totalpage.ToString();
58
59 //設(shè)置導(dǎo)航按鈕的狀態(tài)
60 if(Label5.Text=="1") //當(dāng)前頁(yè)為第一頁(yè)
61 {
62 LinkButton1.Enabled=false;
63 LinkButton2.Enabled=false;
64 LinkButton3.Enabled=true;
65 LinkButton4.Enabled=true;
66 }
67 else if (Label5.Text == Label6.Text) //當(dāng)前頁(yè)為最后一頁(yè)
68 {
69 LinkButton1.Enabled=true;
70 LinkButton2.Enabled=true;
71 LinkButton3.Enabled=false;
72 LinkButton4.Enabled=false;
73 }
74 else
75 {
76 LinkButton1.Enabled=true;
77 LinkButton2.Enabled=true;
78 LinkButton3.Enabled=true;
79 LinkButton4.Enabled=true;
80 }
81 }
82
83 protected void LinkButton1_Click(object sender, System.EventArgs e) //首頁(yè)
84 {
85 Label5.Text="1";
86 int currentpage=int.Parse(Label5.Text);
87 BindCustomers(pagesize,currentpage);
88 }
89
90 protected void LinkButton4_Click(object sender, System.EventArgs e) //尾頁(yè)
91 {
92 Label5.Text=Label6.Text;
93 int currentpage=int.Parse(Label5.Text);
94 BindCustomers(pagesize,currentpage);
95 }
96
97 protected void LinkButton2_Click(object sender, System.EventArgs e) // 上一頁(yè)
98 {
99 int currentpage=int.Parse(Label5.Text);
100 currentpage--;
101 Label5.Text=currentpage.ToString();
102 BindCustomers(pagesize,currentpage);
103 }
104
105 protected void LinkButton3_Click(object sender, System.EventArgs e) //下一頁(yè)
106 {
107 int currentpage=int.Parse(Label5.Text);
108 currentpage++;
109 Label5.Text=currentpage.ToString();
110 BindCustomers(pagesize,currentpage);
111 }
112 }
113}
114
|
