ASP. NET query paging technology: Implement pagination of Web Data

Use ASP. when developing the Web database query function module, to reduce network traffic and increase the page access speed, the query results are generally not displayed on a single page, instead, it is divided into multiple pages for display according to certain rules. A certain number of records are displayed on each page. The data record paging technology can improve data query performance and page display speed, reducing the burden on database servers.

1 Overview of ASP. NET query paging technology
The Web database query results are displayed by page in two ways: browser-side paging and server-side paging. The browser saves all records in the database that meet the query conditions at one time, and then sets the number of records to be displayed on each page, then determine the start and end points of the record to display the records by page. The server page displays and reads the specified number of records from the records that meet the query conditions based on the user's requests.
To this end, we use the server side paging method. When paging is performed on the browser side, we use the SQL Server Stored Procedure for paging to reduce network traffic.

2 main technologies
ADO. NET class
In the Web application system, ASP. NET uses ADO. NET to connect the front-end Web browser of the system with the back-end database. ADO. NET is a series of classes provided by. NET Framework. It provides access to relational data, XML, and application data.

3 Implementation of ASP. NET query paging technology
Here, we use System. data. SqlClient to include the class for accessing the SQL Server database) to operate ms SQL Server2000 and describe it using the C # language in ASP. NET. Taking the "view student message module" in the "online examination system" developed by the author as an example to describe the development process of this technology.

3.1 connect to the database
To access data, you must first connect to the database. Here we use the System. data. SqlClient. SqlConnection class to connect to the Student database in SQL Server. The main attributes and methods used are as follows:

ConnectionString
Gets or sets the string used to open the SQL Server database.

Open
Use the attribute settings specified by ConnectionString to open a database connection

Close
Close the connection to the database

The connection string is as follows:

 
 

  
  
SqlConnection SCN = new SqlConnection("server=;database=BSNetExam;uid=sa;pwd=");   
 
 

3.2 creation of Stored Procedures

 
 

  
  
Create procedure PageCut
  
  

  
  
@ PageSizeInt=10, -- Page size
  
  

  
  
@ PageIndexInt=1-- Current page number
  
  

  
  
AS
  
  

  
  
Declare @ strSQL varchar (500)
  
  

  
  
If @PageIndex=1
  
  

  
  
Set @StrSQL='Select top'+ Str (@ PageSize) + '* from tdbLevMess order by rqsj'
  
  

  
  
Else
  
  

  
  
Set @StrSQL='Select top'+ Str (@ PageSize) + '* from tdbLevMess where rqsj not in
  
  

  
  
(Select top '+ str (@ PageIndex-1) * @ PageSize) + 'rqsj from tdbpres) order by rqsj'
  
  

  
  
Exec (@ strSQL)
  
  

  
  
GO
 
 

3.3 database operations

Data Query operations can be performed in multiple ways. Commonly used objects include SqlCommand objects that represent an SQL query or a stored procedure) and SqlDataAdpater objects that submit a SqlCommand to the SQL Server database ). Here, we use the SqlCommand object to operate the database.

The SqlCommand class is located in the space of System. Data. SqlClient, indicating a Transact-SQL statement or stored procedure to be executed on the SQL Server database. The attributes and methods used in this example are as follows:

CommandText
Obtain or set the Transact-SQL statement or stored procedure to be executed on the data source.

CommandType
Gets or sets a value indicating how the CommandText attribute is interpreted.

Connection
Obtain or set the SqlConnection used by this instance of SqlCommand.

Parameters
Obtain SqlParameterCollection.

ExecuteReader
Send CommandText to Connection and generate a SqlDataReader.

The SqlParameter class is located in the space of System. Data. SqlClient, which indicates the SqlCommand parameter or its datasing to the DataSet column. In this example, the Value attribute is used to obtain or set the Value of this parameter ).

The implementation procedure is as follows:

 
 

  
  
SqlCommandSCM=NewSqlCommand (); // defines the SqlCommand class
  
  

  
  
SqlDataReader SDR; // defines the SqlDataReader class
  
  

  
  
Private void GridView_Init (int PageSize, int PageIndex)
  
  

  
  
{
  
  

  
  
SCN. Open (); // Open the database connection
  
  

  
  
SCM. Connection=SCN;
  
  

  
  
SCM. CommandText="Pagecut"; // Execute the Stored Procedure
  
  

  
  
SCM. CommandType= CommandType. StoredProcedure;
  
  

  
  
SqlParameter []Pares= {New SqlParameter ("@ PageSize", SqlDbType. Int ),
  
  

  
  
New SqlParameter ("@ PageIndex", SqlDbType. Int )};
  
  

  
  
Pares [0]. Value=PageSize;
  
  

  
  
Pares [1]. Value=PageIndex;
  
  

  
  
SCM. Parameters. Clear (); // Clear Parameters
  
  

  
  
Foreach (SqlParameter parameter in pares)
  
  

  
  
{
  
  

  
  
SCM. Parameters. Add (parameter );
  
  

  
  
}
  
  

  
  
SDR=SCM. ExecuteReader (); // executes the Stored Procedure
  
  

  
  
GridView1.DataSource=SDR; // Define the data source of the GridView
  
  

  
  
GridView1.DataBind (); // binds the GridView data source.
  
  

  
  
SCN. Close (); // Close the connection
  
  

  
  
}
 
 

3.4 Program Implementation

On the Web form, add a GridView Control for data display), four linkbuttons for hyperlink, home page, Previous Page, next page, and last page), and a Label control. Set the AllowPaging attribute of the GridView control to False. We use the Session object to save parameters in the paging program. The specific procedure is as follows:

 
 

  
  
Protected void Page_Load (object sender, EventArgs e)
  
  

  
  
{// Page Initialization
  
  

  
  
If (! IsPostBack) // If the page is not loaded for the first time
  
  

  
  
{
  
  

  
  
Int PageCount, PageIndex, PageSize; // defines the total number of pages, the current page number, and the number of records per page.
  
  

  
  
SCN. Open ();
  
  

  
  
SCM. CommandText="Select count (*) from tdbLevMess";
  
  

  
  
SCM. Connection=SCN;
  
  

  
  
IntReCount=Convert. ToInt32 (SCM. ExecuteScalar (). ToString (); // total number of dataset records
  
  

  
  
SCN. Close ();
  
  

  
  
Session. Add ("PageSize", 20); // assign a value to the number of records on each page of the shared variable PageSize
  
  

  
  
PageSize=Convert. ToInt16 (Session ["PageSize"]); // 20 records per page
  
  

  
  
PageCount=ReCount%PageSize= 0? ReCount/PageSize: ReCount/PageSize + 1; // calculates the total number of pages on the page.
  
  

  
  
Session. Add ("PageCount", PageCount); // assign a value to the total number of pages of the shared variable PageCount
  
  

  
  
PageIndex=1;
  
  

  
  
Session. Add ("PageIndex", PageIndex); // assign a value to the total number of pages of the shared variable PageIndex
  
  

  
  
GridView_Init (PageSize, PageIndex); // The call process fills data in the GridView
  
  

  
  
LinkButton1.Enabled=False,LinkButton2.Enabled=False;
  
  

  
  
LinkButton3.Enabled=True,LinkButton4.Enabled=True;
  
  

  
  
Label1.Text="Th"+ PageIndex. ToString () + "Page/total" + PageCount. ToString () + "page ";
  
  

  
  
}
  
  

  
  
}
  
  

  
  
Protected void LinkButton2_Click (object sender, EventArgs e)
  
  

  
  
{// Previous page button
  
  

  
  
IntPageIndex=Convert. ToInt16 (Session ["PageIndex"])-1;
  
  

  
  
IntPageCount=Convert. ToInt16 (Session ["PageCount"]);
  
  

  
  
Session. Add ("PageIndex", PageIndex );
  
  

  
  
IntPageSize=Convert. ToInt16 (Session ["PageSize"]);
  
  

  
  
If (PageIndex<= 1)
  
  

  
  
{
  
  

  
  
LinkButton1.Enabled=False;
  
  

  
  
LinkButton2.Enabled=False;
  
  

  
  
}
  
  

  
  
LinkButton3.Enabled=True;
  
  

  
  
LinkButton4.Enabled=True;
  
  

  
  
GridView_Init (PageSize, PageIndex );
  
  

  
  
Label1.Text="Th"+ PageIndex. ToString () + "Page/total" + PageCount. ToString () + "page ";
  
  

  
  
}
 
 

We only provide code implementation for LinkButton2 to flip pages). Other hyperlink codes are similar to this one.

4 Conclusion

ASP. the NET query paging technology is an important task frequently encountered in Web application system development. Selecting an efficient query paging technology not only greatly improves the response speed, but also improves the overall system performance, saves valuable server resources.

1. Introduction to ASP. NET page Lifecycle
2. Detailed research on ASP. NET data verification technology
3. Analysis of validation groups in ASP. NET data verification
4. Analysis on the use of ASP. NET data verification controls
5. Analysis on five common controls for ASP. NET data verification