Asp.Net中的三种分页方式总结

Question

解决分页这个问题搞了一天半，耗时巨大，现在总结一下。
通常分页有3种方法，分别是asp.net自带的数据显示空间如GridView等自带的分页，第三方分页控件如aspnetpager，存储过程分页等。这里分别做总结。第一种：使用GridView自带分页，这种是最简单的分页方法。
前台的方法：<asp:GridView ID="GridView1" AllowPaging="true" runat="server"
            onpageindexchanging="GridView1_PageIndexChanging" PageSize="3">
</asp:GridView>

后台方法：
代码using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using JXSoft.TicketManage.Model;
using JXSoft.TicketManage.BLL;
using System.Text.RegularExpressions;
using System.Data;

namespace JXSoft.TicketManage.Web
{
    public
partial
class Test : System.Web.UI.Page
    {
        protected
void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack)
            {
                BindData();
            }
        }

        protected
void BindData()
        {
            DataTable dt=new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("Name");
            for (int i =
0; i <
10;i++ )
            {
                dt.Rows.Add(i.ToString(), i.ToString());
            }
            this.GridView1.DataSource = dt;
            this.GridView1.DataBind();
        }
        protected
void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            this.GridView1.PageIndex = e.NewPageIndex;
            BindData();
        }
    }
}

第二种：使用个性化显示的AspNetPager.dll进行分页
此处需要添加aspnetpager.dll的引用
前台：代码 <form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1"   runat="server"
>
</asp:GridView>
<webdiyer:AspNetPager ID="AspNetPager1" runat="server"  
    CustomInfoHTML="第%CurrentPageIndex%页，共%PageCount%页，每页%PageSize%条"  
    FirstPageText="首页" LastPageText="尾页" LayoutType="Table" NextPageText="下一页"  
    onpagechanging="AspNetPager1_PageChanging" PageIndexBoxType="DropDownList"  
    PagingButtonLayoutType="Span" PrevPageText="上一页" ShowCustomInfoSection="Left"  
    ShowPageIndexBox="Always" SubmitButtonText="Go" PageSize="4" TextAfterPageIndexBox="页"  
    TextBeforePageIndexBox="转到">
</webdiyer:AspNetPager>
</div>
</form>

后台：
代码using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using JXSoft.TicketManage.Model;
using JXSoft.TicketManage.BLL;
using System.Text.RegularExpressions;
using System.Data;

namespace JXSoft.TicketManage.Web
{
    public
partial
class Test : System.Web.UI.Page
    {
        protected
void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack)
            {
                BindData();
            }
        }

        protected
void BindData()
        {
            DataTable dt=new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("Name");
            for (int i =
0; i <
10;i++ )
            {
                dt.Rows.Add(i.ToString(), i.ToString());
            }
            DataSet ds =
new DataSet();
            ds.Tables.Add(dt);

            Pager(this.GridView1, this.AspNetPager1, ds);
        }

        protected
void Pager(GridView dl, Wuqi.Webdiyer.AspNetPager anp, System.Data.DataSet dst)
        {
            PagedDataSource pds =
new PagedDataSource();
            pds.DataSource = dst.Tables[0].DefaultView;
            pds.AllowPaging =
true;

            anp.RecordCount = dst.Tables[0].DefaultView.Count;
            pds.CurrentPageIndex = anp.CurrentPageIndex -
1;
            pds.PageSize = anp.PageSize;

            dl.DataSource = pds;
            dl.DataBind();
        }

        protected
void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
        {
            AspNetPager1.CurrentPageIndex = e.NewPageIndex;
            BindData();
        }
    }
}

Answer 1

第三种：使用AspNetPager结合存储过程进行分页
这种方法分页稍微复杂一些，但是可以应付比较大的数据量。

前台:

代码
<asp:GridView ID="GridView1" runat="server" CssClass="GridTable" AutoGenerateColumns="false" onrowdatabound="GridView1_RowDataBound"  >
        </asp:GridView>
        <webdiyer:AspNetPager ID="AspNetPager1" runat="server"  
        CustomInfoHTML="第%CurrentPageIndex%页，共%PageCount%页，每页%PageSize%条"  
        FirstPageText="首页" LastPageText="尾页" LayoutType="Table" NextPageText="下一页"  
        onpagechanged="AspNetPager1_PageChanged" PageIndexBoxType="DropDownList"  
        PagingButtonLayoutType="Span" PrevPageText="上一页" ShowCustomInfoSection="Left"  
        ShowPageIndexBox="Always" SubmitButtonText="Go" PageSize="4" TextAfterPageIndexBox="页"  
        TextBeforePageIndexBox="转到">
    </webdiyer:AspNetPager>  

后台：

代码
//绑定方法中需要传递aspnetpager的两个属性

protected void DataBind(){

DataSet ds = reportQueryBLL.GetTcikDetailReport(this.txtStartDate.Text,this.txtEndDate.Text,int.Parse( this.DropDownListPartment1.SelectedValue),

this.txtPayPerson1.Text,this.txtTicketNum.Text,this.txtTicketNo.Text,

AspNetPager1.StartRecordIndex,AspNetPager1.EndRecordIndex);//注意最后两个参数是aspnetpager的属性。

this.GridView1.DataSource = ds;
this.GridView1.DataBind();

}

//分页控件的页索引变化事件

protected void AspNetPager1_PageChanged(object src, EventArgs e)
        {
            BindDetailReportToGv();
        }

//page_base中需要加载首次的数据条数

DataSet ds = reportQueryBLL.GetDetail(this.txtStartDate.Text, this.txtEndDate.Text, int.Parse(this.DropDownListPartment1.SelectedValue), this.txtPayPerson1.Text, this.txtTicketNum.Text, this.txtTicketNo.Text);
                this.AspNetPager1.RecordCount = ds.Tables[0].Rows.Count;
                BindDetailReportToGv();  

这里用的存储过程比较复杂，因为SQL语句没有能够放到视图中，也无法直接从表中查出结果，这个存储过程有点变态，如果有朋友看到了，希望能指点一下。

其实存储过程的核心在于：

代码
Create PROCEDURE [dbo].[P_GetPagedOrders2005]
(@startIndex INT,  
@endindex INT
)
AS
select * from (SELECT ROW_NUMBER() OVER(ORDER BY IPid DESC) AS rownum,
[IPid],[IPFrom],[IPTo],[IPLocation],[IPCity],[IPToNumber],[IPFromNumber] from IPInfo) as U
WHERE rownum between @startIndex and @endIndex
GO

代码

--下方可以忽略

--我用到的是存储过程：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create PROCEDURE [dbo].[pro_pager]
(@startIndex INT,
@endindex INT,
@strwhere varchar(200)
)
AS

SELECT  tb_On_Tick_Info.On_Tick_ID_Int,tb_On_Tick_Info.On_Tick_SellDatetime_Dtm,tb_On_Tick_Info.On_Tick_TicketsNum_Str,  tb_Department_Info.Dept_Name_Str,  tb_User_Info.User_Name_Str,  
  tb_On_Tick_Info.On_Tick_SellNumber_Str,  tb_On_Tick_Info.On_Tick_ShouldPay_Dec,  tb_On_Tick_Info.On_Tick_Count_Int,  
  tb_On_Tick_Info.On_Tick_Discount_Dec, tb_On_Tick_Details.On_Tick_Details_StartNo_Int, CHARINDEX(N'a',  
  tb_On_Tick_Info.On_Tick_Note_Text) AS Expr3, tb_User_Info_1.User_Name_Str AS Expr1,  tb_Ticket_Type.TicketType_Name_Dec,  
COUNT( tb_On_Tick_Details.On_Tick_Details_ID_Int) AS Expr2 ,tb_Department_Info.Dept_ID_Int
into #temp
FROM tb_User_Info INNER JOIN
  tb_On_Tick_Info ON  tb_User_Info.User_ID_Int =  tb_On_Tick_Info.On_Tick_SellPerson_Int INNER JOIN
  tb_Department_Info ON  tb_User_Info.User_DepartID_Int =  tb_Department_Info.Dept_ID_Int INNER JOIN
  tb_User_Info AS tb_User_Info_1 ON  tb_On_Tick_Info.On_Tick_PayPerson_Int = tb_User_Info_1.User_ID_Int INNER JOIN
  tb_On_Tick_Details ON  tb_On_Tick_Info.On_Tick_SellNumber_Str =  tb_On_Tick_Details.On_Tick_SellNumber_Str INNER JOIN
  tb_Ticket_Type ON  tb_On_Tick_Details.On_Tick_Details_TicketsType_Int =  tb_Ticket_Type.TicketType_ID_Int  
where 1=1 +@strwhere
GROUP BY  tb_On_Tick_Info.On_Tick_SellDatetime_Dtm,tb_On_Tick_Info.On_Tick_TicketsNum_Str,  tb_Department_Info.Dept_Name_Str,  tb_User_Info.User_Name_Str,  
   tb_On_Tick_Info.On_Tick_SellNumber_Str,  tb_On_Tick_Info.On_Tick_ShouldPay_Dec,  tb_On_Tick_Info.On_Tick_Count_Int,  
   tb_On_Tick_Info.On_Tick_Discount_Dec, CHARINDEX(N'a',  tb_On_Tick_Info.On_Tick_Note_Text), tb_User_Info_1.User_Name_Str,  
   tb_Ticket_Type.TicketType_Name_Dec,  tb_On_Tick_Details.On_Tick_Details_StartNo_Int ,tb_Department_Info.Dept_ID_Int,tb_On_Tick_Info.On_Tick_ID_Int

declare @sql varchar(8000)
set @sql = 'select  CONVERT(varchar(12) , On_Tick_SellDatetime_Dtm, 111 ) as On_Tick_SellDatetime_Dtm,Dept_Name_Str,User_Name_Str,On_Tick_SellNumber_Str,convert(varchar(15), On_Tick_ShouldPay_Dec) as On_Tick_ShouldPay_Dec,On_Tick_Count_Int,On_Tick_Discount_Dec'
select @sql=@sql+',sum(case tickettype_name_dec when '''+tickettype_name_dec+''' then expr2 else 0 end) ['+tickettype_name_dec+']'
from (select distinct tickettype_name_dec from tb_Ticket_Type ) as a
set @sql=@sql+' ,expr3,Expr1,On_Tick_TicketsNum_Str,Dept_ID_Int,On_Tick_ID_Int into ##t from #temp  
group by  On_Tick_SellDatetime_Dtm,Dept_Name_Str,On_Tick_TicketsNum_Str,User_Name_Str,On_Tick_SellNumber_Str,On_Tick_ShouldPay_Dec,On_Tick_Count_Int,
On_Tick_Discount_Dec ,expr3,Expr1,Dept_ID_Int,On_Tick_ID_Int order by On_Tick_SellDatetime_Dtm '
exec( @sql )

--select * from ##t

select * from (SELECT ROW_NUMBER() OVER(ORDER BY on_tick_id_int DESC) AS rownum,
* from ##t) as U
WHERE rownum between @startIndex and @endIndex

drop table ##t