asp.net| Pagination | Display Summary: In this paper, we discuss the display of Web database records by using the DataGrid control in the ASP.net framework, and a custom paging display for database records.
Keywords: web database; asp.net;datagrid; paging
Introduction
This is usually the case when a user makes a data query, and a database query returns too many rows, and consistency cannot be displayed on a single page. If a user is using a slow link, sending a particularly large data result can take a long time. Once the data has been obtained, the user may find that it does not contain the correct content, or the scope of the query is too large, there is no easy way to check all the results to find important information. Therefore, the paging display of query results will provide great convenience for the user to manage the data query. Paging display is a very common way to browse and display large amounts of data. is one of the most frequently handled events in Web programming, and now the site basically provides paging display information, but most of it is based on ASP, and the. NET Platform framework is designed for a wide variety of Web applications, Therefore, this paper discusses a method of implementing the custom paging display of query records under the ASP.net framework.
asp.net
. NET simplifies the process of accessing and storing information through platform-independent data description methods (through XML) and the common Access media Internet.
Asp. NET is the core element of the Microsoft.NET framework, a powerful server-based technology that enables you to create dynamically interactive HTML pages for the WWW site or intranet. ASP.net is completely based on modules and components, and is well scalable and customizable. It mainly includes two programming modes of WebForm and WebService. The former provides users with powerful, rich-looking form-based Web pages based on forms (form); the latter provides a programming interface for accessing remote services, linking remote devices, and interacting remote applications in heterogeneous network environments through support for Internet standards such as HTTP, XML, SOAP, and WSDL.
Linking through ado.net, manipulating data sources
Since Microsoft launched the Open Database Interconnection (ODBC) Application Programming Interface (API), a variety of database access technologies have emerged, and the API can be processed in real time until ado.net occurs. Ado. NET is a technology that asp.net applications use to communicate with databases, allowing you to interact with relational databases and other data sources. Disconnected access is the most important feature of Ado.net and is the biggest change to ADO. Ado. NET creates a link to the database that populates the dataset with a copy of the information extracted from the database. If you change the information in the dataset, the information in the corresponding table in the database will not change. When needed, you can connect the dataset back to the original data source and apply all the changes.
Ado. NET relies primarily on the functionality of the following core objects. They are grouped into two groups: a set of objects for storing and managing data (for example: Datatable,datarow and DataRelation), and another set of objects to link to a particular data source (for example: connections, Commands and DataReader classes)
In most cases, the data you need is in a data source (for example, a relational database). For this data to be accessed, extracted, and inserted into the appropriate data object, the data source object must be used. The purpose of using a data source object is to create a link and move the relevant information into a dataset or DataReader. One of the easiest ways to access the database is to use the command object to go directly to the data source and retrieve the read-only rows through DataReader. Another option: Put the data into a disconnected dataset for longer periods of time.
To display records with the DataGrid control paging
After you create the links and command objects, you display the returned records. The DataGrid control is the most powerful and complex data control in ASP.net, and can be used to display and format data in a datasheet, which allows users to define their own representations in addition to the built-in data representations and methods. Paging technology facilitates user-managed data lookups.
1, the DataGrid built-in paging technology principle
The DataGrid built-in paging technology is easy to implement, but when the volume of data is large, its convenience is at the expense of performance. If a user requests only the result set on page 8th of 25 records per page in 100 pages, the server only needs to send the data for the 第175-200 row instead of the complete data for 1-1000 rows. The default delivery mode is shown in Figure 1.
Figure 1 DataGrid Default transfer Mode
As you can see from Figure 1, the built-in paging method of the DataGrid is inefficient, each request must send the entire query results to the Web server, and the Web server divides the data into the corresponding pages. Using the built-in paging method of the DataGrid, though it's simple, however, because of the unordered nature of Web applications, when a user shifts from one page to another, the DataGrid object is destroyed and recreated, which means that the database server must send all the result sets each time.
2, Custom Paging technology
So how do you get a result set that handles a lot of data quickly by customizing the paging method?
It is faster and more efficient than the default paging method of the DataGrid, because each request does not require all data results to be sent to the Web server. Instead, it only needs to send those datasets needed for each page. The custom paging method returns only those result sets that you want to retrieve, as shown in Figure 2.
Figure 2 How to customize the paging delivery
As you can see in Figure 2, the database only needs to return the data records that you want to display at a time.
The way to execute an SQL command on a asp.net page can be either direct execution or encapsulating the SQL command in a stored procedure before executing the stored procedure in the page. Executing stored procedures is slightly more complex than executing SQL commands directly, but can significantly improve the performance of database-driven Web sites. Every time you execute SQL commands directly from the asp.net page, SQL Server needs to parse, compile, and optimize it, and the stored procedures need to be parsed, compiled, and optimized.
The custom paging method here is to work with a stored procedure for paging, rather than being done by a Web server.
First, a stored procedure is established in the database that accepts two input parameters, the number of the first record and the last record to return the data. To create a stored procedure that returns the results of a specified record, you must first specify the number of records that return the result set, where the table variable (SQL Server), which is stored in memory, is automatically released after the stored procedure has ended. The stored procedures created are as follows:
Create proc Insertstudents
@Student_Last_Name as varchar = NULL,
@StartRow as int = null,
@StopRow as int = null
As
----to establish a table variable with an identifier column
DECLARE @t_table table
(
[RowNum] [INT] IDENTITY (1, 1) Primary key not NULL,
[Student_last_name] [varchar] (40),
[Student_first_name] [varchar] (20),
)
----Stop processing the query after returning the specified number of @stoprow rows
Set RowCount @StopRow
----inserted into the table variable
Insert @t_table
(
[Student_last_name],[student_first_name]
)
SELECT [Student_last_name],[student_first_name]
From Students
WHERE student_last_name like '% ' + @Student_Last_Name like '% '
ORDER BY Student_last_name
----Return to the correct result
SELECT * from @t_table WHERE rownum >= @StartRow
ORDER BY RowNum
Go
Parameters @startrow and @stoprow receive integer values, representing the start and end records to return, and if you want to return 8th page in a 25-record page, we can set @startrow to 176, @StopRow to 200.
The table variable @t_table defines a column of the integer type called RowNum and is specified as an identifier column, which automatically increases as it inserts the data, playing a sort function. The SET ROWCOUNT statement is the key to optimizing performance, it tells SQL Server to limit the data to be inserted, and if we want data between 176-200 records, then you don't have to insert data larger than 200 records. The final SQL statement selects those datasets rownum greater than or equal to @startrow through the @t_table table variable, and then returns them to the Web server, which is bound to the DataGrid object by the Web server. However, if the number of pages requested by the viewer becomes larger, the more records that need to be filled into the table variable, resulting in a decrease in page performance. Therefore, the performance will depend on your computer's hardware and the number of records you want to return, but in order to reduce the pressure of the database and network transmission, the design of reasonable query Results page is very effective.
You will then write code for the DataGrid object to use the paging technique. The AllowPaging, AllowCustomPaging, and Pagestyle properties of the DataGrid Help to record the user's access status. First set AllowCustomPaging to True. We use SqlDataReader to load the DataGrid object. Performance tests show that using SqlDataReader is twice times faster than using a dataset when the build list displays data.
In pursuit of performance optimization, the EnableViewState property of the DataGrid is false because it eliminates the need to store content in ViewState each time you interact with the Web server.
When the DataGrid is not saved in the ViewState, you need to add a navigation button to help the user navigate.
Then add two buttons on the page: Back page and Next page. To go to the next page, add the Click event on the next page button and request the corresponding record by customizing the paging stored procedure. For example, if the first page consists of 1th to 25th records, then to navigate to the second page, we pass parameter 26 to the @startrow of the stored procedure, pass parameter 50 to @stoprow, return to the first page, @StartRow and @stoprow are 1 and 25 respectively.
Use vb.net here to write a "next page" event:
Private Sub Buttonnext_click (ByVal sender as Object, _
ByVal E
