ASP. NET calls the Oracle paging stored procedure and uses the ASPnetpager paging control to implement the paging function.

Previously, we used the pagination function provided by the GridView. The speed is really slow. We decided that the custom paging Oracle database had more than pieces of data.

AspnetPager is doing well. Use it.

Oracle paging stored procedure:

Create or replace package JT_P_page is
Type type_cur is ref cursor; -- defines the cursor variable used to return the record set
Procedure Pagination (Pindex in number, -- index of the number of pages to be displayed, starting from 0
Psql in varchar2, -- query statement that generates paging data
Psize in number, -- number of records displayed per page
Pcount out number, -- number of returned pages
Prowcount out number, -- number of returned records
V_cur out type_cur -- returns the cursor of the paging data
);
End JT_P_page;
-- Define the package subject
Create or replace package body JT_P_page is
Procedure Pagination (Pindex in number, -- index of the number of pages to be displayed, starting from 0
Psql in varchar2, -- query statement that generates paging data
Psize in number, -- number of records displayed per page
Pcount out number, -- number of returned pages
Prowcount out number, -- number of returned records
V_cur out type_cur -- returns the cursor of the paging data
)
V_ SQL VARCHAR2 (1000 );
V_Pbegin number;
V_Pend number;
Begin
V_ SQL: = 'select count (*) from ('| Psql | ')';
Execute immediate v_ SQL into Prowcount; -- calculate the total number of records
Pcount: = ceil (Prowcount/Psize); -- calculate the total number of pages
-- Display any page content
V_Pend: = Pindex * Psize + Psize;
V_Pbegin: = v_Pend-Psize + 1;
V_ SQL: = 'select * from ('| Psql |') where rn between '| v_Pbegin | 'and' | v_Pend;
Open v_cur for v_ SQL;
End Pagination;
End JT_P_page;

This is found on the Internet, but it is not bad. It mainly depends on the pseudo column rownum as the where query condition for handsome selection. You can view the rownum pseudo column.

Http://www.cnblogs.com/chinhr/archive/2007/09/30/911685.html write very thorough, through this Oracle paging is to have a preliminary understanding of rownum, practice the truth, with the Stored Procedure cut down is called

The first three parameters of the stored procedure are input parameters, and the last three parameters are output parameters.

Help. cs:

Public static DataTable ReturnDataTable (int index, string SQL, int pageSize)
{
DataTable dt = new DataTable ();
Try
{
OracleParameter [] param = new OracleParameter [] {new OracleParameter ("Pindex", OracleType. number), new OracleParameter ("Psql", OracleType. varChar), new OracleParameter ("Psize", OracleType. number), new OracleParameter ("Pcount", OracleType. number), new OracleParameter ("Prowcount", OracleType. number), new OracleParameter ("v_cur", OracleType. cursor )};
Param [0]. Value = index;
Param [1]. Value = SQL;
Param [2]. Value = pageSize;

Param [0]. Direction = ParameterDirection. Input;
Param [1]. Direction = ParameterDirection. Input;
Param [2]. Direction = ParameterDirection. Input;
Param [3]. Direction = ParameterDirection. Output;
Param [4]. Direction = ParameterDirection. Output;
Param [5]. Direction = ParameterDirection. Output;

Dt = OracleHelper. ReturnDataTable (OracleHelper. dbCon, CommandType. StoredProcedure, "JT_P_page.Pagination", param );
Help. _ rowCount = int. Parse (param [4]. Value. ToString ());

}
Catch (OracleException on)
{
Throw on;
}
Return dt;
}

Note that the parameter name must be consistent with the parameter in the stored procedure. Otherwise, an error may be returned, for example, new OracleParameter ("Pindex", OracleType. in Number), like Pindex in the Stored procedure Pagination (Pindex in number, Aspnetpage, as a third-party control, is directly downloaded from the internet and then stored in the VS2005 toolbar. after importing the dll, you can use the Update_Agent.aspx.cs file on the front-end page:

Protected void Page_Load (object sender, EventArgs e)
{

If (! IsPostBack)
{
BindGridView (0, "union_view", _ pageSize );
AspNetPager1.RecordCount = (Help. _ rowCount> = 0 )? Help. _ rowCount: 0;
}
}

Private void BindGridView (int index, string SQL, int pageSize)
{
DataTable dt;
If (SQL! = "")
{
ViewState ["IsProcedure"] = "yes ";
Dt = Help. ReturnDataTable (index, SQL, pageSize );
}
Else
{
ViewState ["IsProcedure"] = "no ";
Dt = Help. ReturnDataTableByCondition (dateinput. Value, dateinput2.Value, orderID. Value. Trim (), dgWay. SelectedValue, AspNetPager1.CurrentPageIndex-1, AspNetPager1.PageSize );
}
IagentView. DataSource = dt;
DataBind ();
AspNetPager1.RecordCount = (SQL! = "")? Help. _ rowCount: Help. ReturnRows (dateinput. Value, dateinput2.Value, orderID. Value. Trim (), dgWay. SelectedValue );
AspNetPager1.PageSize = pageSize;
AspNetPager1.CustomInfoHTML = "total records: <font color = \" blue \ "> <B>" + AspNetPager1.RecordCount. ToString () + "</B> </font> ";
AspNetPager1.CustomInfoHTML + = "Total number of pages: <font color = \" blue \ "> <B>" + AspNetPager1.PageCount. ToString () + "</B> </font> ";
AspNetPager1.CustomInfoHTML + = "Current page: <font color = \" red \ "> <B>" + AspNetPager1.CurrentPageIndex. ToString () + "</B> </font> ";
}

/// <Summary>
/// AspNetPager1 PageChanged event
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "e"> </param>

Protected void AspNetPager1_PageChanged (object sender, EventArgs e)
{
If (ViewState ["IsProcedure"]! = Null & ViewState ["IsProcedure"]. ToString () = "yes ")
BindGridView (AspNetPager1.CurrentPageIndex-1, "union_view", _ pageSize );
Else
BindGridView (AspNetPager1.CurrentPageIndex-1, "", _ pageSize );
}

In Update_Agent.aspx, The AspNetPager statement and some attribute settings can be searched online.

<Webdiyer: aspNetPager ID = "AspNetPager1" CssClass = "pages" watermark = "cpb" runat = "server" FirstPageText = "Homepage" LastPageText = "last page" NextPageText = "next page" PrevPageText =" previous Page "AlwaysShow =" true "PageIndexBoxType =" DropDownList "OnPageChanged =" aspnetpager?pagechanged "ShowCustomInfoSection =" left "NumericButtonTextFormatString =" {0} ">
</Webdiyer: AspNetPager>

I did a test. The paging efficiency of stored procedures is more than doubled than that of the original GridView, with less than half of the original time.