True/false pagination in ASP. NET

When there is a large amount of data in the database, the previous DataGrid often seems powerless. In this case, it is the turn of paging to show its skills. Paging refers to dividing the data in the database into several pages and displaying them independently. We usually divide pages into two types: real pages and false pages.
False paging indicates that all data is retrieved from the database and displayed to the user by page.
1. Fake paging front-end interface.

2. Set the AllowPaging attribute to True to allow pagination. Set the PageSize to the number of data entries displayed on each page.
3. Running interface:


The process of pagination is as follows:
1. Obtain AspNetPager. dll from the website.
2. Add this component to the toolbox. For details, see add web Control in my blog.
3. Add controls to the foreground page.


4. Background code:
[Csharp]
Protected void Page_Load (object sender, EventArgs e)
{
AspPage. RecordCount = 100;
}
/// <Summary>
/// Events executed when the page number is changed
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "e"> </param>
Protected void asp_PageChanged (object sender, EventArgs e)
{
Response. Write ("start and end" + aspPage. StartRecordIndex + "<br>" + "end at" + aspPage. EndRecordIndex); // start and end data rows
BindData (); // bind the DataGrid data
}
/// <Summary>
/// Bind data
/// </Summary>
Private void BindData ()
{// Bind the DataGrid data
DgPage. DataSource = dt ();
DgPage. DataBind ();
}
/// <Summary>
/// Generate a dataset based on the current page number
/// </Summary>
/// <Returns> </returns>
Private DataTable dt ()
{
Int pageStart = aspPage. StartRecordIndex; // obtain the current starting data row.
Int pageEnd = aspPage. EndRecordIndex; // obtain the data row at the end of the current table.
SqlConnection conn = new SqlConnection ("server =.; database = beidaqingniao; uid = sa; pwd = 123456;"); // create a database connection
String sqltxt = "select * from page where id between @ start and @ end"; // define an SQL statement
SqlCommand sqlcmd = new SqlCommand (sqltxt, conn); // create a database command
SqlParameter [] paras = new SqlParameter [] {new SqlParameter ("@ start", pageStart), new SqlParameter ("@ end", pageEnd)}; // defines the parameter Array
Sqlcmd. Parameters. AddRange (paras); // Add Parameters to the command
DataSet ds = new DataSet (); // defines dataset
SqlDataAdapter da = new SqlDataAdapter (sqlcmd); // create a data adapter
Try
{
Conn. Open (); // Open the connection
Da. Fill (ds); // Fill ds data
Return ds. Tables [0]; // return the dataset
}
Catch (Exception)
{
Throw new Exception ("retrieving data from the database failed! "); // The error message" "failed to retrieve data from the database! ""
}
Finally
{
Conn. Close (); // Close the connection
Sqlcmd. Dispose (); // sales command
}
}

5. Running interface:


For convenient calling, you can write the stored procedure as follows:
[SQL]
-- ===================================================== ======
-- Description: pagination. ROW_NUMBER () is used ()
-- ===================================================== ======
Create PROCEDURE [dbo]. [proc_ShowPage]
@ TblName varchar (255), -- table name
@ StrGetFields varchar (1000) = '*', -- the column to be returned, default *
@ StrOrder varchar (255) = '', -- Name of the sorted field, required
@ StrOrderType varchar (10) = 'asc ', -- sort method, ASC by default
@ PageSize int = 10, -- page size, default value: 10
@ PageIndex int = 1, -- page number; default value: 1
@ StrWhere varchar (1500) = ''-- Query condition (Note: Do not add where)
AS
Declare @ strSQL varchar (5000)
If @ strWhere! =''
Set @ strWhere = 'where' + @ strWhere
Set @ strSQL =
'Select * FROM ('+
'Select ROW_NUMBER () OVER (order by '+ @ strOrder + ''+ @ strOrderType +') AS pos, '+ @ strGetFields +'' +
'From' + @ tblName + ''+ @ strWhere +
') AS sp WHERE pos BETWEEN' + str (@ PageIndex-1) * @ PageSize + 1) + 'and' + str (@ PageIndex * @ PageSize)
Exec (@ strSQL)
True/false paging comparison. The advantage of false paging is that the code is simple. If attributes are set, they can be implemented without writing a method. This method is suitable for a small number of data rows (several hundred rows). The advantage of real paging is that no matter how much data, the time for retrieving a page is basically the same, which is applicable to situations with massive data (such as Baidu search results ).

Author lidaasky