Repeater自定义分页排序

 分页加排序的存储过程:

view plain copy to clipboard print ?

1. --得到总记录数   
2. if exists(select 1 from sys.objects where name = 'GetProductsCount' and type = 'P')   
3.     drop proc GetProductsCount   
4. go   
5. CREATE PROCEDURE GetProductsCount   
6. as  
7.     select count(*) from products   
8. go   
9.   
10. --分页加排序   
11. if exists(select 1 from sys.objects where name = 'GetProductsByPage' and type = 'P')   
12.     drop proc GetProductsByPage   
13. go   
14. CREATE PROCEDURE GetProductsByPage   
15.     @sortExpression nvarchar(100),   
16.     @pageNumber int,   
17.     @pageSize   int  
18. AS  
19. -- 确保指定了 @sortExpression   
20. IF LEN(@sortExpression) = 0   
21.     SET @sortExpression = 'ProductID'   
22. DECLARE @sql nvarchar(4000)   
23. set @sql = 'select ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued   
24.     from (select row_number() Over (order by ' + @sortExpression  + ') as row,ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued   
25.     from products) as ProductsWithRowNumber   
26.     where row between ' + convert(varchar, (@pageNumber - 1) * @pageSize + 1) + ' and ' + convert(varchar, @pageNumber * @pageSize)   
27. exec sp_executesql @sql   
28. go   
29.   
30. --exec GetProductsByPage 'productid desc', 1, 10   
31. --exec GetProductsByPage 'ProductName desc',5, 10   
32. --exec GetProductsByPage '', 1, 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

--分页加排序
if exists(select 1 from sys.objects where name = 'GetProductsByPage' and type = 'P')
	drop proc GetProductsByPage
go
CREATE PROCEDURE GetProductsByPage
	@sortExpression nvarchar(100),
	@pageNumber	int,
	@pageSize	int
AS
-- 确保指定了 @sortExpression
IF LEN(@sortExpression) = 0
	SET @sortExpression = 'ProductID'
DECLARE @sql nvarchar(4000)
set @sql = 'select ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
	from (select row_number() Over (order by ' + @sortExpression  + ') as row,ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
	from products) as ProductsWithRowNumber
	where row between ' + convert(varchar, (@pageNumber - 1) * @pageSize + 1) + ' and ' + convert(varchar, @pageNumber * @pageSize)
exec sp_executesql @sql
go

--exec GetProductsByPage 'productid desc', 1, 10
--exec GetProductsByPage 'ProductName desc',5, 10
--exec GetProductsByPage '', 1, 10

页面代码
无法忍受如此垃圾的编辑器，页面代码转换的一塌糊涂，所以Repeater标记部分用图片代替

view plain copy to clipboard print ?

1. <ASP:LINKBUTTON id=lbtnFirst OnCommand="lbtnPage_Command" CommandName="First" runat="server">|<</ASP:LINKBUTTON>     
2. <ASP:LINKBUTTON id=lbtnPrevious OnCommand="lbtnPage_Command" CommandName="Previous" runat="server"><<</ASP:LINKBUTTON>  
3. <ASP:LABEL id=lblMessage runat="server" />    
4. <ASP:LINKBUTTON id=lbtnNext OnCommand="lbtnPage_Command" CommandName="Next" runat="server">>></ASP:LINKBUTTON>     
5. <ASP:LINKBUTTON id=lbtnLast OnCommand="lbtnPage_Command" CommandName="Last" runat="server">>|</ASP:LINKBUTTON>     
6. 转到第<ASP:DROPDOWNLIST id=dropPage runat="server" OnSelectedIndexChanged="dropPage_SelectedIndexChanged" AutoPostBack="True"></ASP:DROPDOWNLIST>页    

 
 
 
 
  
  |<
 
   

 
 
  
  <<
 
 

 
  

 
 
  
  >>
 
   

 
 
  
  >|
 
   
转到第
 
 页  

后台代码如下:

view plain copy to clipboard print ?

1. using System;   
2. using System.Collections;   
3. using System.Configuration;   
4. using System.Data;   
5. using System.Web;   
6. using System.Web.Security;   
7. using System.Web.UI;   
8. using System.Web.UI.HtmlControls;   
9. using System.Web.UI.WebControls;   
10. using System.Web.UI.WebControls.WebParts;   
11. using System.Data.SqlClient;   
12.   
13. public partial class RepeaterPagingSorting : System.Web.UI.Page   
14. {   
15.     //每页显示的最多记录的条数   
16.     private int pageSize = 10;   
17.     //当前页号   
18.     private int currentPageNumber;   
19.     //排序表达式   
20.     private string sortExpression = string.Empty;   
21.     //排序方向   
22.     private string sortDirection = string.Empty;   
23.     //显示数据的总条数   
24.     private static int rowCount;   
25.     //总页数   
26.     private static int pageCount;   
27.   
28.     protected void Page_Load(object sender, EventArgs e)   
29.     {   
30.         if (!IsPostBack)   
31.         {   
32.             SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);   
33.             SqlCommand cmd = new SqlCommand("GetProductsCount", cn);   
34.             cmd.CommandType = CommandType.StoredProcedure;   
35.             cn.Open();   
36.             rowCount = (int)cmd.ExecuteScalar();   
37.             cn.Close();   
38.             pageCount = (rowCount - 1) / pageSize + 1;   
39.             currentPageNumber = 1;   
40.             for (int i = 1; i <= pageCount; i++)   
41.             {   
42.                 dropPage.Items.Add(new ListItem(i.ToString(), i.ToString()));   
43.             }   
44.             dropPage.SelectedValue = dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;   
45.             Query();   
46.         }   
47.         sortExpression = ViewState["sortExpression"].ToString();   
48.         sortDirection = ViewState["sortDirection"].ToString();   
49.         currentPageNumber = Convert.ToInt32(ViewState["currentPageNumber"]);   
50.     }   
51.   
52.     private void Query()   
53.     {   
54.         SetButton(currentPageNumber);   
55.         SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);   
56.         SqlCommand cmd = new SqlCommand("GetProductsByPage", cn);   
57.         cmd.CommandType = CommandType.StoredProcedure;   
58.         cmd.Parameters.AddWithValue("@sortExpression", sortExpression + " " + sortDirection);   
59.         cmd.Parameters.AddWithValue("@pageNumber", currentPageNumber);   
60.         cmd.Parameters.AddWithValue("@pageSize", pageSize);   
61.         cn.Open();   
62.         rptProducts.DataSource = cmd.ExecuteReader();   
63.         rptProducts.DataBind();   
64.         cn.Close();   
65.         lblMessage.Text = "共找到" + rowCount + "条记录, 当前第" + currentPageNumber + "/" + pageCount + "页";   
66.         Save();   
67.     }   
68.   
69.     protected void lbtnPage_Command(object sender, CommandEventArgs e)   
70.     {   
71.         switch (e.CommandName)   
72.         {   
73.             case "First":   
74.                 currentPageNumber = 1;   
75.                 break;   
76.             case "Previous":   
77.                 currentPageNumber = (int)ViewState["currentPageNumber"] - 1 > 1 ? (int)ViewState["currentPageNumber"] - 1 : 1;   
78.                 break;   
79.             case "Next":   
80.                 currentPageNumber = (int)ViewState["currentPageNumber"] + 1 < pageCount ? (int)ViewState["currentPageNumber"] + 1 : pageCount;   
81.                 break;   
82.             case "Last":   
83.                 currentPageNumber = pageCount;   
84.                 break;   
85.         }   
86.         dropPage.SelectedValue = dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;   
87.         Query();   
88.     }   
89.   
90.     protected void lbtnSort_Command(object sender, CommandEventArgs e)   
91.     {   
92.         if (e.CommandName != ViewState["sortExpression"].ToString())   
93.         {   
94.             sortDirection = "ASC";   
95.         }   
96.         else  
97.         {   
98.             if (sortDirection == "ASC")   
99.             {   
100.                 sortDirection = "DESC";   
101.             }   
102.             else if (sortDirection == "DESC" || sortDirection == string.Empty)   
103.             {   
104.                 sortDirection = "ASC";   
105.             }   
106.         }   
107.         sortExpression = e.CommandName ;   
108.         Query();   
109.     }   
110.   
111.     protected void dropPage_SelectedIndexChanged(object sender, EventArgs e)   
112.     {   
113.         currentPageNumber = int.Parse(dropPage.SelectedValue);   
114.         Query();   
115.     }   
116.   
117.     private void SetButton(int currentPageNumber)   
118.     {   
119.         lbtnFirst.Enabled = currentPageNumber != 1;   
120.         lbtnPrevious.Enabled = currentPageNumber != 1;   
121.         lbtnNext.Enabled = currentPageNumber != pageCount;   
122.         lbtnLast.Enabled = currentPageNumber != pageCount;   
123.     }   
124.   
125.     private void Save()   
126.     {   
127.         ViewState["currentPageNumber"] = currentPageNumber;   
128.         ViewState["sortExpression"] = sortExpression;   
129.         ViewState["sortDirection"] = sortDirection;   
130.     }   
131.   
132.     protected void rptProducts_ItemCreated(object sender, RepeaterItemEventArgs e)   
133.     {   
134.         if (e.Item.ItemType == ListItemType.Header)   
135.         {   
136.             if (!string.IsNullOrEmpty(sortDirection))   
137.             {   
138.                 Label lblSort = new Label();   
139.                 lblSort.EnableTheming = false;   
140.                 lblSort.Font.Name = "webdings";   
141.                 lblSort.Font.Size = FontUnit.Small;   
142.                 lblSort.Text = sortDirection == "ASC" ? "5" : "6";   
143.                 (e.Item.FindControl("td" + sortExpression) as HtmlTableCell).Controls.Add(lblSort);   
144.             }   
145.         }   
146.     }   
147. }  

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 RepeaterPagingSorting : System.Web.UI.Page
{
    //每页显示的最多记录的条数
    private int pageSize = 10;
    //当前页号
    private int currentPageNumber;
    //排序表达式
    private string sortExpression = string.Empty;
    //排序方向
    private string sortDirection = string.Empty;
    //显示数据的总条数
    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();
        }
        sortExpression = ViewState["sortExpression"].ToString();
        sortDirection = ViewState["sortDirection"].ToString();
        currentPageNumber = Convert.ToInt32(ViewState["currentPageNumber"]);
    }

    private void Query()
    {
        SetButton(currentPageNumber);
        SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
        SqlCommand cmd = new SqlCommand("GetProductsByPage", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@sortExpression", sortExpression + " " + sortDirection);
        cmd.Parameters.AddWithValue("@pageNumber", currentPageNumber);
        cmd.Parameters.AddWithValue("@pageSize", pageSize);
        cn.Open();
        rptProducts.DataSource = cmd.ExecuteReader();
        rptProducts.DataBind();
        cn.Close();
        lblMessage.Text = "共找到" + rowCount + "条记录, 当前第" + currentPageNumber + "/" + pageCount + "页";
        Save();
    }

    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 lbtnSort_Command(object sender, CommandEventArgs e)
    {
        if (e.CommandName != ViewState["sortExpression"].ToString())
        {
            sortDirection = "ASC";
        }
        else
        {
            if (sortDirection == "ASC")
            {
                sortDirection = "DESC";
            }
            else if (sortDirection == "DESC" || sortDirection == string.Empty)
            {
                sortDirection = "ASC";
            }
        }
        sortExpression = e.CommandName ;
        Query();
    }

    protected void dropPage_SelectedIndexChanged(object sender, EventArgs e)
    {
        currentPageNumber = int.Parse(dropPage.SelectedValue);
        Query();
    }

    private void SetButton(int currentPageNumber)
    {
        lbtnFirst.Enabled = currentPageNumber != 1;
        lbtnPrevious.Enabled = currentPageNumber != 1;
        lbtnNext.Enabled = currentPageNumber != pageCount;
        lbtnLast.Enabled = currentPageNumber != pageCount;
    }

    private void Save()
    {
        ViewState["currentPageNumber"] = currentPageNumber;
        ViewState["sortExpression"] = sortExpression;
        ViewState["sortDirection"] = sortDirection;
    }

    protected void rptProducts_ItemCreated(object sender, RepeaterItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Header)
        {
            if (!string.IsNullOrEmpty(sortDirection))
            {
                Label lblSort = new Label();
                lblSort.EnableTheming = false;
                lblSort.Font.Name = "webdings";
                lblSort.Font.Size = FontUnit.Small;
                lblSort.Text = sortDirection == "ASC" ? "5" : "6";
                (e.Item.FindControl("td" + sortExpression) as HtmlTableCell).Controls.Add(lblSort);
            }
        }
    }
}

分页排序效果图: