Summary: This article is aimed at the display of web database records, discusses a custom paging display of database records using the DataGrid control under the ASP.NET framework with instances.
Key words: web database; ASP.NET; DATAGRID; Page
introduction
There is usually such a case when the user performs data queries, and a database query will return too much rows, which cannot be displayed in a page. If the user is using a slow link, send a particularly large data result may take a long time. Once the data is obtained, the user may find that it does not contain the correct content, or the query range is too large, and there is no easy way to check all the results to find important information. Therefore, the query result paging display will provide great convenience to the user-managed data query. Page Display is a very common way to browse and display a lot of data. It belongs to one of the frequently processed events in web program. Now the website basically provides the function of paging display information, but most of them are based on ASP, and The .NET platform framework is designed for a wide variety of web applications, so this article discusses a technology that implements a query record custom paging display under the ASP.NET framework.
ASP.NET
.NET is a process of simplifying user access and storage information by platform independent data description methods (via XML) and universal access media Internet.
ASP.NET is the core element of the Microsoft.NET framework. It is a server-based powerful technology that uses it, and can create dynamically can interact with the HTML page for WWW sites or intranet. ASP.NET is completely based on modules and components, with good scalability and customability. It mainly includes two programming modes of WebForm and WebService. The former provides users with powerful and expropsed form-based programmable web pages; the latter provides remote services in heterogeneous network environments by providing Internet standards such as HTTP, XML, SOAP, WSDL. The programming interface of the device, interacting remote application.
Connect the data source via ADO.NET
Since Microsoft introduces an Open Database Interconnect (ODBC) Application Programming Interface (API), a wide variety of database access technologies have emerged until ADO.NET appears, and the API can give the processing effect in real time. ADO.NET is the technology that communicates with the database to interact with the database and other data sources. Disconnect is the most important feature of ADO.NET, which is the biggest change in ADO. ADO.NET creates a link to the database and fill the data set with the information copies extracted from the database. If the information in the Dataset is changed, the information in the corresponding table in the database will not change. When you need, you can connect DataSet to the original data source and apply all changes.
ADO.NET relies primarily on the functionality of the following core objects. They are divided into two groups: a set of objects are used to store and manage data (for example: DataTable, DataRow, and DataRelation), another group of objects are used to link to a particular data source (for example: Connections, Commands, and DataReader class)
In most cases, the required data is in a certain data source (eg, a relational database). To access these data, extract and insert it into an appropriate data object, you must use a data source object. The purpose of using the data source object is to create a link and move the relevant information into a DataSet or DataReader. One of the simplest database access methods is: Use the Command object to enter the data source directly and retrieve read-only data lines through DataReader. Another option: put the data in a disconnected Dataset to operate over a longer period of time. Use the DataGrid control paging display Record to display the returned record after creating a link and a command object. The DataGrid control is the most powerful and most complex data control in ASP.NET. It can be used to display and format data of the data table, which allows users to define their performance form in addition to built-in data performance and methods. . Paging technology provides convenience for user-managed data lookups. 1. DataGrid built-in page technology principle DataGrid built-in paging technology is easy to implement, but the amount of convenience is based on sacrificial performance. If a user only requires a result set of 25 records on page 8 per page in 100 pages, the server only needs to send data on the 175-200 row, not the full data of 1-1000 rows. The default transmission is shown in Figure 1.
Figure 1 DataGrid default transmission method
As can be seen from Figure 1, the built-in method of DataGrid is not high, and each request must be sent to the web server, and the web server divides the data into the corresponding page. Despite DataGrid's built-in method, even if it is very simple, because of the unordered features of the web application, the DataGrid object is destroyed and recreated each time, the DataGrid object is destroyed and recreated. The database server must send all the result sets each time. 2, custom paging technology so how to quickly handle large amounts of data for the result of a large amount of data by custom paging technology? It is more efficient than the default paging method of DataGrid because each request does not need to be sent to the web server. Instead, it only needs to send those data sets required for each page. The custom paging method only returns those result sets to be retrieved, as shown in Figure 2.
Figure 2 Customize the transmission method of the paging
As can be seen from Figure 2, the database only needs to return the data record to be displayed each time. The method of executing the SQL command in the ASP.NET page can be directly executed, and the SQL command can be packaged in the stored procedure, and then the stored procedure is executed. Performing a stored procedure is slightly complex than directly executing the SQL command, but can significantly improve the performance of the Database-driven Web site. Each time you execute the SQL command directly from the ASP.NET page, SQL Server is required to parse, compile and optimize it, while the stored procedure only needs to be parsed, compiled, and optimized. The custom paging method here is to use the stored procedure to do paging work, not by the web server. First, create a stored procedure in the database, which accepts two input parameters, namely the first record number of data to return, respectively, and the last record number. To create a stored procedure that returns the specified strip record, you must first specify the number of records that return the result set. Here with the table variable (SQL Server 2000), the Table variable is stored in memory, but automatic after the stored procedure freed. The created stored procedures are as follows:
create proc InsertStudents @ Student_Last_Name as varchar (100) = null, @ StartRow as int = null, @ StopRow as int = nullAS ---- establishing an identifier column table variable declare @t_table table ([rownum] [int] IDENTITY (1, 1) Primary key not null, [student_last_name] [varchar] (40), [VARCHAR] (20),) ---- Stop processing query after returning the specified @stoprow line
SET ROWCOUNT @stoprow ---- Insert to Table variables
insert @t_table ([Student_Last_Name], [Student_First_Name]) SELECT [Student_Last_Name], [Student_First_Name] FROM StudentsWHERE Student_Last_Name like '%' @Student_Last_Name like '%' ORDER BY Student_Last_Name ---- return to the correct results
Select * from @t_table where rownum> = @startroworder by rownumgo parameter @startrow and @stoprow Receive integer values, representing the start recording and end record to return, if you want to return to page 8 in a 25 recorded page, we will You can set @startrow to 176, @ stoprow is 200. Table variable @t_table defines a column called ROWNUM's integer type, and specifies the identifier column, which will automatically increase when the data is inserted, and plays a sorting. The set rowcount statement is the key to optimizing performance. It tells SQL Server to limit the data to be inserted. If we want to include data between 176-200 records, you can not be inserted into data greater than 200 records. The last SQL statement selects ROWNUM greater than or equal to @startrow through @T_Table Table variable, and then returns them to the web server, binds them to the DataGrid object by the web server. However, if the number of pages requested by the viewer is getting bigger and bigger, the more records that need to be filled with Table variables, resulting in a decline in page performance. Therefore, 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 database and network transmission, the number of designed query results pages is very effective. Then write code to the DataGrid object to use paging skills. DataGrid's allowpaging, allowcustompaging, and pageStyle properties help to record the user's access status. First set allowcustompaging to true. We use SqlDataReader to load the DataGrid object. According to performance tests: When building a list shows data, using SqlDataReader is more than twice as fast as using DataSet. In order to pursue performance optimization, set DataGrid's EnableViewState property to false, because this is not necessary to store content in ViewState every time you deal with the web server. When the DataGrid is not saved in ViewState, you need to add a navigation button to help users navigate. So add two buttons on the page: "Previous" and "Next Page". To go to the next page, add the Click event on the "Next" button to request the corresponding record by custom paging stored procedure. For example, if the first page consists of Articles 1 to 25 records, then to navigate to the second page, we pass the @startrow of the stored procedure 26, pass the parameters 50 to @stoprow, to return to the first One page, @ Startrow and @stoprow are 1 and 25, respectively. Use VB.NET to write "Next" event here:
