Repeater自定义分页(1)

Repeater自定义分页，使用的存储过程如下

view plain copy to clipboard print ?

1. --取得总记录数
2. ifexists(select1fromsys.objectswherename='GetProductsCount'andtype='P')
3. dropprocGetProductsCount
4. go
5. CREATEPROCEDUREGetProductsCount
6. as
7. selectcount(*)fromproducts
8. go
9. --使用row_number函数
10. --SQLServer2005的新特性,它可以将记录根据一定的顺序排列,每条记录和一个等级相关这个等级可以用来作为每条记录的rowindex.
11. ifexists(select1fromsys.objectswherename='GetProductsByPage'andtype='P')
12. dropprocGetProductsByPage
13. go
14. CREATEPROCEDUREGetProductsByPage
15. @PageNumberint,
16. @PageSizeint
17. AS
18. selectProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued
19. from
20. (selectrow_number()Over(orderbyproductid)asrow,ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued
21. fromproducts)asProductsWithRowNumber
22. whererowbetween(@PageNumber-1)*@PageSize+1and@PageNumber*@PageSize
23. go
25. --execGetProductsByPage1,10
26. --execGetProductsByPage5,10

--取得总记录数 if exists(select 1 from sys.objects where name = 'GetProductsCount' and type = 'P') drop proc GetProductsCount go CREATE PROCEDURE GetProductsCount as select count(*) from products go --使用row_number函数 --SQL Server 2005的新特性,它可以将记录根据一定的顺序排列,每条记录和一个等级相关 这个等级可以用来作为每条记录的row index. if exists(select 1 from sys.objects where name = 'GetProductsByPage' and type = 'P') drop proc GetProductsByPage go CREATE PROCEDURE GetProductsByPage @PageNumber int, @PageSize int AS select ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued from (select row_number() Over (order by productid) as row,ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued from products) as ProductsWithRowNumber where row between (@PageNumber - 1) * @PageSize + 1 and @PageNumber * @PageSize go --exec GetProductsByPage 1, 10 --exec GetProductsByPage 5, 10

页面代码如下:

view plain copy to clipboard print ?

1. <asp:linkbuttonid="lbtnFirst"runat="server"commandname="First"oncommand="lbtnPage_Command">|<</asp:linkbutton>
2. <asp:linkbuttonid="lbtnPrevious"runat="server"commandname="Previous"oncommand="lbtnPage_Command"><<</asp:linkbutton>
3. <asp:labelid="lblMessage"runat="server">
4. <asp:linkbuttonid="lbtnNext"runat="server"commandname="Next"oncommand="lbtnPage_Command">>></asp:linkbutton>
5. <asp:linkbuttonid="lbtnLast"runat="server"commandname="Last"oncommand="lbtnPage_Command">>|</asp:linkbutton>
6. 转到第<asp:dropdownlistid="dropPage"runat="server"autopostback="True"onselectedindexchanged="dropPage_SelectedIndexChanged"></asp:dropdownlist>页
7. </asp:label>

<linkbutton id="lbtnFirst" runat="server" commandname="First" oncommand="lbtnPage_Command">|&lt;</linkbutton><linkbutton id="lbtnPrevious" runat="server" commandname="Previous" oncommand="lbtnPage_Command">&lt;&lt;</linkbutton><linkbutton id="lbtnNext" runat="server" commandname="Next" oncommand="lbtnPage_Command">&gt;&gt;</linkbutton><linkbutton id="lbtnLast" runat="server" commandname="Last" oncommand="lbtnPage_Command">&gt;|</linkbutton> 转到第<dropdownlist id="dropPage" runat="server" autopostback="True" onselectedindexchanged="dropPage_SelectedIndexChanged"></dropdownlist>页

后台代码如下:

view plain copy to clipboard print ?

1. usingSystem;
2. usingSystem.Collections;
3. usingSystem.Configuration;
4. usingSystem.Data;
5. usingSystem.Web;
6. usingSystem.Web.Security;
7. usingSystem.Web.UI;
8. usingSystem.Web.UI.HtmlControls;
9. usingSystem.Web.UI.WebControls;
10. usingSystem.Web.UI.WebControls.WebParts;
11. usingSystem.Data.SqlClient;
13. publicpartialclassRepeaterPaging:System.Web.UI.Page
14. {
15. //每页显示的最多记录的条数
16. privateintpageSize=10;
17. //当前页号
18. privateintcurrentPageNumber;
19. //显示数据的总条数
20. privatestaticintrowCount;
21. //总页数
22. privatestaticintpageCount;
24. protectedvoidPage_Load(objectsender,EventArgse)
25. {
26. if(!IsPostBack)
27. {
28. SqlConnectioncn=newSqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
30. SqlCommandcmd=newSqlCommand("GetProductsCount",cn);
31. cmd.CommandType=CommandType.StoredProcedure;
32. cn.Open();
33. rowCount=(int)cmd.ExecuteScalar();
34. cn.Close();
35. pageCount=(rowCount-1)/pageSize+1;
36. currentPageNumber=1;
38. for(inti=1;i<=pageCount;i++)
39. {
40. dropPage.Items.Add(newListItem(i.ToString(),i.ToString()));
41. }
42. dropPage.SelectedValue=dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;
43. Query();
44. }
45. }
47. protectedvoidsdsProducts_Selecting(objectsender,SqlDataSourceSelectingEventArgse)
48. {
49. sdsProducts.SelectParameters["PageNumber"].DefaultValue=currentPageNumber.ToString();
50. sdsProducts.SelectParameters["PageSize"].DefaultValue=pageSize.ToString();
51. }
53. protectedvoidsdsProducts_Selected(objectsender,SqlDataSourceStatusEventArgse)
54. {
55. lblMessage.Text="共找到"+rowCount+"条记录,当前第"+currentPageNumber+"/"+pageCount+"页";
56. }
58. protectedvoidlbtnPage_Command(objectsender,CommandEventArgse)
59. {
60. switch(e.CommandName)
61. {
62. case"First":
63. currentPageNumber=1;
64. break;
65. case"Previous":
66. currentPageNumber=(int)ViewState["currentPageNumber"]-1>1?(int)ViewState["currentPageNumber"]-1:1;
67. break;
68. case"Next":
69. currentPageNumber=(int)ViewState["currentPageNumber"]+1<pageCount?(int)ViewState["currentPageNumber"]+1:pageCount;
70. break;
71. case"Last":
72. currentPageNumber=pageCount;
73. break;
74. }
75. dropPage.SelectedValue=dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;
76. Query();
77. }
79. protectedvoiddropPage_SelectedIndexChanged(objectsender,EventArgse)
80. {
81. currentPageNumber=int.Parse(dropPage.SelectedValue);
82. Query();
83. }
85. privatevoidQuery()
86. {
87. ViewState["currentPageNumber"]=currentPageNumber;
88. SetButton(currentPageNumber);
89. sdsProducts.Select(DataSourceSelectArguments.Empty);
90. }
92. privatevoidSetButton(intcurrentPageNumber)
93. {
94. lbtnFirst.Enabled=currentPageNumber!=1;
95. lbtnPrevious.Enabled=currentPageNumber!=1;
96. lbtnNext.Enabled=currentPageNumber!=pageCount;
97. lbtnLast.Enabled=currentPageNumber!=pageCount;
98. }
99. }

using System; using System.Collections; using System.Configuration; using System.Data; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Data.SqlClient; public partial class RepeaterPaging : System.Web.UI.Page { //每页显示的最多记录的条数 private int pageSize = 10; //当前页号 private int currentPageNumber; //显示数据的总条数 private static int rowCount; //总页数 private static int pageCount; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString); SqlCommand cmd = new SqlCommand("GetProductsCount", cn); cmd.CommandType = CommandType.StoredProcedure; cn.Open(); rowCount = (int)cmd.ExecuteScalar(); cn.Close(); pageCount = (rowCount - 1) / pageSize + 1; currentPageNumber = 1; for (int i = 1; i <= pageCount; i++) { dropPage.Items.Add(new ListItem(i.ToString(), i.ToString())); } dropPage.SelectedValue = dropPage.Items.FindByValue(currentPageNumber.ToString()).Value; Query(); } } protected void sdsProducts_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { sdsProducts.SelectParameters["PageNumber"].DefaultValue = currentPageNumber.ToString(); sdsProducts.SelectParameters["PageSize"].DefaultValue = pageSize.ToString(); } protected void sdsProducts_Selected(object sender, SqlDataSourceStatusEventArgs e) { lblMessage.Text = "共找到" + rowCount + "条记录, 当前第" + currentPageNumber + "/" + pageCount + "页"; } protected void lbtnPage_Command(object sender, CommandEventArgs e) { switch (e.CommandName) { case "First": currentPageNumber = 1; break; case "Previous": currentPageNumber = (int)ViewState["currentPageNumber"] - 1 > 1 ? (int)ViewState["currentPageNumber"] - 1 : 1; break; case "Next": currentPageNumber = (int)ViewState["currentPageNumber"] + 1 < pageCount ? (int)ViewState["currentPageNumber"] + 1 : pageCount; break; case "Last": currentPageNumber = pageCount; break; } dropPage.SelectedValue = dropPage.Items.FindByValue(currentPageNumber.ToString()).Value; Query(); } protected void dropPage_SelectedIndexChanged(object sender, EventArgs e) { currentPageNumber = int.Parse(dropPage.SelectedValue); Query(); } private void Query() { ViewState["currentPageNumber"] = currentPageNumber; SetButton(currentPageNumber); sdsProducts.Select(DataSourceSelectArguments.Empty); } private void SetButton(int currentPageNumber) { lbtnFirst.Enabled = currentPageNumber != 1; lbtnPrevious.Enabled = currentPageNumber != 1; lbtnNext.Enabled = currentPageNumber != pageCount; lbtnLast.Enabled = currentPageNumber != pageCount; } }

页面效果如下