Repeater自定义分页排序(2)

分页加排序的存储过程:

--得到总记录数
ifexists(select1fromsys.objectswherename='GetProductsCount'andtype='P')
dropprocGetProductsCount
go
CREATEPROCEDUREGetProductsCount
as
selectcount(*)fromproducts
go

--分页加排序
ifexists(select1fromsys.objectswherename='GetProductsByPage'andtype='P')
dropprocGetProductsByPage
go
CREATEPROCEDUREGetProductsByPage
@sortExpressionnvarchar(100),
@pageNumberint,
@pageSizeint
AS
--确保指定了@sortExpression
IFLEN(@sortExpression)=0
SET@sortExpression='ProductID'
DECLARE@sqlnvarchar(4000)
set@sql='selectProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued
from(selectrow_number()Over(orderby'+@sortExpression+')asrow,ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued
fromproducts)asProductsWithRowNumber
whererowbetween'+convert(varchar,(@pageNumber-1)*@pageSize+1)+'and'+convert(varchar,@pageNumber*@pageSize)
execsp_executesql@sql
go

--execGetProductsByPage'productiddesc',1,10
--execGetProductsByPage'ProductNamedesc',5,10
--execGetProductsByPage'',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标记部分用图片代替

<asp:linkbuttonid="lbtnFirst"runat="server"commandname="First"oncommand="lbtnPage_Command">|<</asp:linkbutton>
<asp:linkbuttonid="lbtnPrevious"runat="server"commandname="Previous"oncommand="lbtnPage_Command"><<</asp:linkbutton>
<asp:labelid="lblMessage"runat="server">
<asp:linkbuttonid="lbtnNext"runat="server"commandname="Next"oncommand="lbtnPage_Command">>></asp:linkbutton>
<asp:linkbuttonid="lbtnLast"runat="server"commandname="Last"oncommand="lbtnPage_Command">>|</asp:linkbutton>
转到第<asp:dropdownlistid="dropPage"runat="server"autopostback="True"onselectedindexchanged="dropPage_SelectedIndexChanged"></asp:dropdownlist>页
</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>页  

后台代码如下:

usingSystem;
usingSystem.Collections;
usingSystem.Configuration;
usingSystem.Data;
usingSystem.Web;
usingSystem.Web.Security;
usingSystem.Web.UI;
usingSystem.Web.UI.HtmlControls;
usingSystem.Web.UI.WebControls;
usingSystem.Web.UI.WebControls.WebParts;
usingSystem.Data.SqlClient;

publicpartialclassRepeaterPagingSorting:System.Web.UI.Page
{
//每页显示的最多记录的条数
privateintpageSize=10;
//当前页号
privateintcurrentPageNumber;
//排序表达式
privatestringsortExpression=string.Empty;
//排序方向
privatestringsortDirection=string.Empty;
//显示数据的总条数
privatestaticintrowCount;
//总页数
privatestaticintpageCount;

protectedvoidPage_Load(objectsender,EventArgse)
{
if(!IsPostBack)
{
SqlConnectioncn=newSqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
SqlCommandcmd=newSqlCommand("GetProductsCount",cn);
cmd.CommandType=CommandType.StoredProcedure;
cn.Open();
rowCount=(int)cmd.ExecuteScalar();
cn.Close();
pageCount=(rowCount-1)/pageSize+1;
currentPageNumber=1;
for(inti=1;i<=pageCount;i++)
{
dropPage.Items.Add(newListItem(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"]);
}

privatevoidQuery()
{
SetButton(currentPageNumber);
SqlConnectioncn=newSqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
SqlCommandcmd=newSqlCommand("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();
}

protectedvoidlbtnPage_Command(objectsender,CommandEventArgse)
{
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();
}

protectedvoidlbtnSort_Command(objectsender,CommandEventArgse)
{
if(e.CommandName!=ViewState["sortExpression"].ToString())
{
sortDirection="ASC";
}
else
{
if(sortDirection=="ASC")
{
sortDirection="DESC";
}
elseif(sortDirection=="DESC"||sortDirection==string.Empty)
{
sortDirection="ASC";
}
}
sortExpression=e.CommandName;
Query();
}

protectedvoiddropPage_SelectedIndexChanged(objectsender,EventArgse)
{
currentPageNumber=int.Parse(dropPage.SelectedValue);
Query();
}

privatevoidSetButton(intcurrentPageNumber)
{
lbtnFirst.Enabled=currentPageNumber!=1;
lbtnPrevious.Enabled=currentPageNumber!=1;
lbtnNext.Enabled=currentPageNumber!=pageCount;
lbtnLast.Enabled=currentPageNumber!=pageCount;
}

privatevoidSave()
{
ViewState["currentPageNumber"]=currentPageNumber;
ViewState["sortExpression"]=sortExpression;
ViewState["sortDirection"]=sortDirection;
}

protectedvoidrptProducts_ItemCreated(objectsender,RepeaterItemEventArgse)
{
if(e.Item.ItemType==ListItemType.Header)
{
if(!string.IsNullOrEmpty(sortDirection))
{
LabellblSort=newLabel();
lblSort.EnableTheming=false;
lblSort.Font.Name="webdings";
lblSort.Font.Size=FontUnit.Small;
lblSort.Text=sortDirection=="ASC"?"5":"6";
(e.Item.FindControl("td"+sortExpression)asHtmlTableCell).Controls.Add(lblSort);
}
}
}
}

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);
            }
        }
    }
}

分页排序效果图: