DataGrid is a very powerful ASP.NET Web server-side control, which can easily format data in the table in addition to various ways to format data in the table, edit, and pagin. Let web developers liberate from a cumbersome code. The paging function of achieving DataGrid has always been a number of tricky problems in many beginners ASP.NET, especially custom paging functions, and implementing a variety of ways, very flexible. This article will introduce you to a fast paging method under the Access database to help beginners master the Paging technology of DataGrid.
Current paging method
DataGrid's built-in paging method is to use the SQL statement such as "Select * from
" to remove all records from the database table to DataSet, after the DataGrid control is binding to the DataSet, its automatic paging function will help you The data of the current paging is filtered out in the DataSet and displayed, and the data that is not used will be discarded.
Another way is to use custom paging functions, first set the DataGrid's baseowcustomPaging property to True, and use the DataAdapter's Fill method to make data filtering to fill DataSet, not let DataGrid help you filter:
Public Int Fill
DataSet DataSet, // The DataSet to be filled.
INT StartRecord, // From the record number starting from zero.
INT maxRecords, // to retrieve the maximum number of records.
String srctable // The name of the source table for the table map.
);
This method first fills the results from the query into the DataSet and discards the data that does not need to be displayed. Of course, things that customize paging features need to be completed, this article will be described in detail later.
The working principle of the above two methods is first removed from the database, and then filter out useful data is displayed. It can be seen that the efficiency of the two methods is basically consistent because they do not take effective measures in the data access phase to reduce Access's number of access to disk. For small amounts of records, this overhead may be relatively small, and the overhead will be very huge, resulting in a very slow speed of the patching. In other words, even if the data to be displayed each DataGrid page is just a database table with tens of thousands of recorded database tables, all records are taken from the table each time the DataGrid is paged.
Many people have realized this problem and proposed a solution: use custom paging, and take only data to display only from the database each time. In this way, we need to work hard in the SQL statement. Since Access does not support a real stored procedure, there is no SQL Server in writing a paged algorithm. SQL Server can utilize temporary tables in the stored procedure to achieve high efficiency paging algorithms, which are widely used. For Access, we must find a way to achieve the most efficient algorithm in a SQL statement.
There are several ways to obtain a certain piece of data in a SQL statement. The algorithm is different, the efficiency is different. After a rough test, I found that the time consuming time-consuming time-efficient SQL statement is about 3 times the highest efficiency of SQL statement! And this value increases with the increase of the total number of records. Next, two commonly used SQL statements will be described below.
In order to facilitate the next discussion, we agree as follows:
SELECT TOP @ PageSize * @PageIndex @PrimaryKey from @tablename
Order by @primaryKey ASC
) Order by @PrimaryKey ASC
This SQL statement is slow in NOT IN, and the value of each @PrimaryKey traversed by the main SELECT statement is compared to the value of each @PrimaryKey in the result set of the SELECT statement, which is very large. Here you have to remind you that you should try to avoid using the NOT in statement when you write SQL statements, because it tends to increase the time complexity of the entire SQL statement.
The other is the use of two tops and three ORDER BY SQL statements as follows:
SELECT * FROM
Select Top @Pagesize * from
SELECT TOP @Pagesize * (@PageIndex 1) * from @tablename
Order by @primaryKey ASC
) Tablea Order by @PrimaryKey DESC
) Tableb Order by @PrimaryKey ASC
This SQL statement is relatively large. If the pages you want to display are just the last page, its efficiency is lower than the records of direct SELECT. Therefore, for the paging algorithm, we should also specifically analyze and not generally. Here will briefly introduce the relevant concept, if you are very familiar with the primary key and index, you can skip directly.
Concept of primary keys and indexes
In Access, the primary key of a table (Primary Key, also known as the main index) is inevitably unique index, and its value is not repeated. In addition, the index is sorted based on the value of the index column, and each index record contains a pointer to the data row it referenced, which is very helpful for the execution of Order By. We can use the primary key to achieve the positioning of a record, so that the records you want to display on a paging are quickly removed.
For example, assume that the primary key field is an Integer type. In the database table, the recorded index has been ranked in the value of the primary key field (by default), then the index of the record of the primary key field value is "11", is certainly just right The index of the recorded value "12" (assuming that there is a record "12" of the primary key in the database table). If the primary key field does not have a unique constraint, there may be a record of the value of "11" in two or more primary key fields in the database table, so that the front and rear position between these records cannot be determined. Let's take a look at how to use the primary key to perform data segmentation query.
The principle of fast paging method
In fact, the patching method is derived from other methods. I am seriously analyzed by the original way, and it is found that it can improve its efficiency very effectively by optimization and improvement. The original algorithm is highly efficient, but the lack of specific analysis of specific issues. The same pagination algorithm may be very efficient when taking the data of the first page, but may be lower in the last page of data.
After analysis, we can divide the efficiency state of the paging algorithm into four situations:
(1) @PageIndex <= @FirstIndex
(2) @firstindex <@PageIndex <= @middleindex
(3) @MiddleIndex <@PageIndex <@LastIndex
(4) @PageIndex> = @LastIndex
Status (1) and (4) represent the first page and the last page, respectively. They belong to special circumstances, we don't have to use special algorithms, you can solve them directly, otherwise it will complicate problems, but reduce efficiency. For the remaining two states, if the total number of pagins is an even number, we can see that the first page and the last page record is deleted from the database table, and then divide the remaining positions into two parts, that is, the front Part of the status (2), the back is another part, that is, state (3); if the total number of paging is odd, the record belonging to the intermediate page is attributed to the front part. These four states correspond to four sets of SQL statements, each of which consists of two SQL statements from ascending and descending order.
Below is a database table, the first column on the left is virtual, not part of the database table structure, which represents the paging index where the corresponding record is located. This table will be used in the example of the next SQL statement:
PageIndex
ItemID
ProductID
PRICE
0
001
0011
$ 12
002
0011
$ 13
1
003
0012
$ 13
004
0012
$ 11
2
005
0013
$ 14
006
0013
$ 12
3
007
0011
$ 13
008
0012
$ 15
4
009
0013
$ 12
010
0013
$ 11
From the table available: @PageSize = 2, @ recordcount = 10, @ pagecount = 5
Supreme SQL statement
(1) @PageIndex <= @FirstIndex
Take the data on the first page is simple, we can remove the records to display by using top @pageSize to remove the first page.
Select Top @Pagesize @queryfieldsfrom @tablename
Where @condition
Order by @primaryKey ASC
(2) @firstindex <@PageIndex <= @middleindex
The SQL statement that records the first half of the data sheet and the SQL statement recorded in the subsequent part of the record can be effectively improved. Behind I explain this reason. Now look at the SQL statement that takes the first half of the record. First take the primary key value of all records before the current page, then select the maximum value, then remove the main key value greater than the front @PageSize strip record of the maximum value. Here @PrimaryKey's data type may not be an Integer type, CHAR, VARCHAR, and other types of samples.
Select Top @Pagesize @Queryfields
From @tablename
Where @primaryKey>
SELECT MAX (@PrimaryKey) from FROM
SELECT TOP @ PageSize * @PageIndex @PrimaryKey
From @tablename
Where @condition
Order by @primaryKey ASC
TABLEA
) Where @condition
Order by @primaryKey ASC
For example: @PageIndex = 1, red-> yellow-> blue
(3) @MiddleIndex <@PageIndex <@LastIndex
Next, look at the SQL statement recorded in the latter half of the database table. The statement is the same as the principle of the previous statement algorithm, but the method is slightly different. First take the primary key value of all records after the current page, then select the minimum, then take out the main key value less than the front @PageSize strip record of the minimum value.
SELECT * FROM
Select Top @Pagesize @Queryfields
From @tablename
WHERE @primarykey <(
Select min (@PrimaryKey) from
SELECT TOP (@ recordcount- @ PageSize * (@PageIndex 1)) @PrimaryKey
From @tablename
Where @condition
Order by @primaryKey DESC
TABLEA
) Where @condition
Order by @primaryKey DESC
Tableb
Order by @primaryKey ASC
The reason why the SQL statement recorded in the first half of the data sheet and the semi-part record is written separately because the number of records in front of the current page is incremented with the number of pages, and we have to increase from these Remove the value of their primary key fields in the record, select the maximum. In this way, the page speed will slow down with the increase of the number of pages. So I didn't do this, but an algorithm that accelerated the page speed with the increase of pages with the increase in the number of pages when the current page index greater than the intermediate page index (@middleindex <@pageIndex). It can be seen that it is assumed that all pages are divided into front, middle and back three parts, the first and last paging speeds are the fastest, the most intermediate page speed is slower.
For example: @PageIndex = 3, red-> yellow -> blue (4) @PageIndex> = @LastIndex
Take the last page record can simply use similar states (1):
SELECT * FROM
Select Top @Pagesize @Queryfields
From @tablename
Where @condition
Order by @primaryKey DESC
) Tablea Order by @primaryKey ASC
However, the last page that is not necessarily the last page in the actual sense. Because the number of records in the last page is not necessarily equal to @PageSize, the above SQL statement is a recorded @Pagesize strip record directly. If you want to get the record of the last page, you should first calculate the number of records, as the Top statement:
SELECT * FROM
SELECT TOP (@ recordcount- @ Pagesize * @ lastindex) @Queryfields
From @tablename where @condition
Order by @primaryKey DESC
) Tablea Order by @primaryKey ASC
Descending SQL statement
Descending SQL statement is similar to the Datong of ascending, this is not in Luo J
(1) @PageIndex <= @FirstIndex
Select Top @Pagesize @Queryfields
From @tablename
Where @condition
Order by @primaryKey DESC
(2) @firstindex <@PageIndex <= @middleindex
Select Top @Pagesize @Queryfields
From @tablename
WHERE @primarykey <(
Select min (@PrimaryKey) from
SELECT TOP @ PageSize * @PageIndex @PrimaryKey
From @tablename
Where @condition
Order by @primaryKey DESC
TABLEA
) Where @condition
Order by @primaryKey DESC
(3) @MiddleIndex <@PageIndex <@LastIndex
SELECT * FROM
Select Top @Pagesize @Queryfields
From @tablename
Where @primaryKey>
SELECT MAX (@PrimaryKey) from FROM
SELECT TOP (@ recordcount- @ PageSize * (@PageIndex 1)) @PrimaryKey
From @tablename
Where @condition
Order by @primaryKey ASC
TABLEA
) Where @conditionorder by @primaryKey ASC
Tableb Order By @primaryKey DESC
(4) @PageIndex> = @LastIndex
SELECT * FROM
SELECT TOP (@ recordcount- @ Pagesize * @ lastindex) @Queryfields
From @tablename where @condition order by @primaryKey ASC
) Tablea Order by @PrimaryKey DESC
How to dynamically generate the above SQL statement?
After reading the SQL statement above, I believe that everyone has basically understood the principles of the pages. Below, we will design a dynamically generated SQL statement class fastpaging. This class has a public static method that dynamically generates a SQL statement based on the condition you give, as a return value of the method.
// Generate the SELECT statement to query the query according to the specified field.
Public static string paning
INT pageSize, / / The number of records to display per page.
INT pageIndex, // The index of the page to display.
The total number of records in int Recordcount, // data table.
String TableName, // To query the data sheet.
String queryfields, // to query the field.
String primaryKey, // Primon field.
Bool ascending, // is arranged as an ascending order.
String Condition // Filtering Conditions for Query.
) {
Stringbuilder SB = New StringBuilder ();
INT PageCount = getPageCount (RecordCount, Pagesize); // Package total
INT MIDDLEINDEX = getMidpageIndex (pageCount); // Index of the intermediate page
INT firstIndex = 0; // The index of the first page
INT LastIndex = PageCount - 1; // The index of the last page
IF (PageIndex <= firstIndex) {
// code slightly
} else if (pageindex> firstIndex && pageindex <= middleindex) {
With the above class, it is simple to achieve paging. First, we have to put the DataGrid's baseowPaging property and the allowCustomPaging property to True, in addition to reflecting the functionality of ascending and descending, but also set the allowsorting property to TRUE. Then when you paginize, we need to generate an OLEDBDataReader object or the DataView object to bind to DataGrid as the data source of DataGrid. Here you need a SQL statement based on the Paging method of the FastPaging class, and assign the COMMANDTEXT attribute of the OLEDBCommand object:
In the above block, the value of ViewState [CurrentPageIndex "] is updated to E.NewpageIndex in the DataGrid's Page Event Handle. In order to facilitate the empty value of ViewState, it is best to encapsulate access and null values for ViewState ["CurrentPageIndex"] in an attribute. DataGrid1. VirtualItemcount should be set to the total number of records in the database table. DataGrid can virtualiate the number of pags for DataGrid through it and the PageSize property. The value of VirtualItemcount is set in the LOAD event handler in page, and the size of this value requires a database access to get. To improve performance, you can set this value only when you load the page.
to sum up
DataGrid is over here based on Access's fast paging method. Of course, this method does not "package the hundred diseases", and there may be other better methods for your functionality. This requires everyone to continuously summarize experience in the usual work and study, and find the most effective way to solve practical problems. This is also the idea of this method.
