AspNetPager 的存储过程

最新推荐文章于 2024-10-16 10:33:19 发布

limfungsuen

最新推荐文章于 2024-10-16 10:33:19 发布

阅读量1.1k

点赞数

文章标签：存储 resources server asp border c#

本文链接：https://blog.csdn.net/limfungsuen/article/details/3042051

版权

ALTER PROCEDURE P_GetOrderNumber
AS
select count(orderid) from orders;
RETURN
1. ALTER procedure P_GetPagedOrders2000
2. (@startIndex int,
3. @pageSize int
4. )
5. as
6. set nocount on
7. declare @indextable table(id int identity(1,1),nid int)
8. declare @PageUpperBound int
9. set @PageUpperBound=@startIndex+@pagesize-1
10. set rowcount @PageUpperBound
11. insert into @indextable(nid) select orderid from orders order by orderid desc
12. select O.orderid,O.orderdate,O.customerid,C.CompanyName,E.FirstName+' '+E.LastName as EmployeeName
13. from orders O
14. left outer join Customers C
15. on O.CustomerID=C.CustomerID
16. left outer join Employees E
17. on O.EmployeeID=E.EmployeeID
18. inner join @indextable t on
19. O.orderid=t.nid
20. where t.id between @startIndex and @PageUpperBound order by t.id
21. set nocount off
22. RETURN
  1. ALTER PROCEDURE P_GetPagedOrders2005
  2. (@startIndex INT,
  3. @pageSize INT
  4. )
  5. AS
  6. begin
  7. WITH orderList AS (
  8. SELECT ROW_NUMBER() OVER (ORDER BY O.orderid DESC)AS Row, O.orderid,O.orderdate,O.customerid,C.CompanyName,E.FirstName+' '+E.LastName as EmployeeName
  9. from orders O
  10. left outer join Customers C
  11. on O.CustomerID=C.CustomerID
  12. left outer join Employees E
  13. on O.EmployeeID=E.EmployeeID)
  15. SELECT orderid,orderdate,customerid,companyName,employeeName
  16. FROM orderlist
  17. WHERE Row between @startIndex and @startIndex+@pageSize-1
  20. end
  以下示例说明如何用AspNetPager对Repeater进行分页。
  2. <%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>
  3. <%@Import NameSpace="System.Data"%>
  4. <%@Import NameSpace="System.Data.SqlClient"%>
  5. <script runat="server" Language="C#">
  6. void Page_Load(object sender, EventArgs e)
  7. {
  8. if (!IsPostBack)
  9. {
  10. int totalOrders = (int)SqlHelper.ExecuteScalar(CommandType.StoredProcedure, "P_GetOrderNumber");
  11. AspNetPager1.RecordCount = totalOrders;
  12. bindData();
  13. }
  14. }
  16. void bindData()
  17. {
  18. Repeater1.DataSource = SqlHelper.ExecuteReader(CommandType.StoredProcedure,"P_GetPagedOrders2005",
  19. new SqlParameter("@startIndex", AspNetPager1.StartRecordIndex),
  20. new SqlParameter("@pageSize", AspNetPager1.PageSize));
  21. Repeater1.DataBind();
  22. }
  24. void AspNetPager1_PageChanged(object src, EventArgs e)
  25. {
  26. bindData();
  27. }
  28. </script>
  29. <HTML><HEAD>
  30. <TITLE> AspNetPager Samples </TITLE>
  31. <META NAME="Author" CONTENT="Webdiyer (www.webdiyer.com)">
  32. </HEAD>
  34. <BODY>
  35. <form runat="server">
  36. <asp:Repeater ID="Repeater1" runat="server">
  37. <HeaderTemplate>
  38. <table width="100%" border="1" cellspacing="0" cellpadding="4" style="border-collapse:collapse">
  39. <tr style="backGround-color:#CCCCFF"><th style="width:15%"><asp:Literal ID="Literal1" runat="server" Text="<%$Resources:LocalizedText,orderid%>" /></th><th style="width:15%"><asp:Literal ID="Literal3" runat="server" Text="<%$Resources:LocalizedText,orderdate%>" /></th><th style="width:30%"><asp:Literal ID="Literal5" runat="server" Text="<%$Resources:LocalizedText,companyname%>" /></th><th style="width:20%"><asp:Literal ID="Literal2" runat="server" Text="<%$Resources:LocalizedText,customerid%>" /></th><th style="width:20%"><asp:Literal ID="Literal4" runat="server" Text="<%$Resources:LocalizedText,employeename%>" /></th></tr>
  40. </HeaderTemplate>
  41. <ItemTemplate>
  42. <tr style="background-color:#FAF3DC">
  43. <td><%#DataBinder.Eval(Container.DataItem,"orderid")%></td>
  44. <td><%#DataBinder.Eval(Container.DataItem,"orderdate","{0:d}")%></td>
  45. <td><%#DataBinder.Eval(Container.DataItem, "companyname")%></td>
  46. <td><%#DataBinder.Eval(Container.DataItem,"customerid")%></td>
  47. <td><%#DataBinder.Eval(Container.DataItem,"employeename")%></td>
  48. </tr>
  49. </ItemTemplate>
  50. <FooterTemplate>
  51. </table>
  52. </FooterTemplate>
  53. </asp:Repeater>
  54. <webdiyer:AspNetPager ID="AspNetPager1" runat="server" Width="100%" HorizontalAlign="right" PageSize="8" OnPageChanged="AspNetPager1_PageChanged">
  55. </webdiyer:AspNetPager>
  56. </form>
  57. </BODY>
  58. </HTML>