The access|datagrid| paging DataGrid is a very powerful ASP.net Web server-side control that allows you to dynamically sort, edit, and page pages of data in a table in addition to the ability to format the data in a table in a variety of ways. Frees web developers from cumbersome code. Implementing the paging function of the DataGrid has been a thorny issue for many novice asp.net, especially the custom paging feature, which is a variety of ways to implement and very flexible. This article will introduce a Datagird control to the Access database in the fast pagination method, to help beginners master the DataGrid paging technology.
The current paging method
The paging method built into the DataGrid uses SQL statements such as "SELECT * from <TABLE>" To remove all records from the database table into the dataset, after the DataGrid control is bound to the dataset, Its automatic paging function will help you filter out the current paging data from the dataset and display it, and other useless data will be discarded.
Another approach is to use the custom paging feature to set the AllowCustomPaging property of the DataGrid to True, and then use the DataAdapter Fill method to advance the filtering of the data to the dataset. Instead of letting the DataGrid help you sift through:
public int Fill (
DataSet DataSet,//dataset to be populated.
int Startrecord,//zero-based record number from which to start.
int maxrecords,//maximum number of records to retrieve.
String srctable//The name of the source table for the table map.
);
The method first populates the dataset with the results from the query, and discards the data that does not need to be displayed. Of course, the custom paging feature needs to be done more than that, which is described later in this article.
Both of these methods work by taking all the records out of the database and then filtering out useful data for display. As you can see, the efficiency of both approaches is basically consistent because they do not take effective steps to reduce access to the disk in the data access phase. For a small number of records, this overhead may be relatively small, and if the paging for large amounts of data is expensive, the cost will be very large, resulting in a very slow paging rate. In other words, even if the data on each DataGrid page is just 10 of a database table with tens of thousands of records, every time the DataGrid is paginated, all the records are taken out of the table.
Many people are aware of this problem and have come up with a workaround: Use custom paging to fetch only the data to be displayed from the database at a time. In this way, we need to work on the SQL statement. Because Access does not support true stored procedures, it is not as free to write a paging algorithm as SQL Server. SQL Server can use temporary tables in stored procedures to achieve efficient paging algorithm, which is widely used. For access, however, we have to find a way to implement the most efficient algorithm within an SQL statement.
There are several ways to get a piece of data in a single SQL statement. The algorithm is different and the efficiency is different. After a rough test, I found that the worst-performing SQL statement took about 3 times times as much time as the most efficient SQL statement! And that number will increase as the number of records increases. The following two common SQL statements are described below.
In order to facilitate the next discussion, we will first agree as follows:
Variable
Description
Variable
Description
@PageSize
Total number of records displayed per page
@MiddleIndex
Index of intermediate pages
@PageCount
Total Paging
@LastIndex
Index of last page
@RecordCount
Total number of records in datasheet
@TableName
database table name
@PageIndex
Index of current page
@PrimaryKey
Primary key field Name
@FirstIndex
Index of the first page
@QueryFields
Set of fields to query
Variable
Defined
@PageCount
(int) Math.ceiling (double) @RecordCount/@PageSize)
@FirstIndex
0
@LastIndex
@PageCount –1
@MiddleIndex
(int) Math.ceiling ((double) @PageCount/2) –1
Let's take a look at the least efficient SQL statement:
SELECT Top @PageSize * from @TableName
WHERE @PrimaryKey Not IN (
SELECT Top @PageSize * @PageIndex @PrimaryKey from @TableName
ORDER BY @PrimaryKey ASC
) Order BY @PrimaryKey ASC
This SQL statement is slow slow in this case, 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 child SELECT statement, so the time complexity is very high. We have to remind you that you should try to avoid using the not in statement when writing SQL statements, because it often increases the time complexity of the entire SQL statement.
The other is an SQL statement that uses two top and three order by, 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 has a larger space complexity. If the page you want to display is just the last page, it's less efficient than simply select all the records. Therefore, for the paging algorithm, we should also concrete analysis of specific circumstances, can not generalize. Here's a brief introduction to the concepts that you can skip directly if you are familiar with primary keys and indexes.
About the concepts of primary keys and indexes
In Access, a table's primary key (PRIMARY key, also called the primary index) is necessarily a unique index (unique index), and its value is not duplicated. In addition, the index is sorted according to the value of the indexed column, and each index record contains a pointer to the data row it refers to, which is helpful for the execution of the order by. We can take advantage of the two features of a primary key to locate a record, and quickly remove the record to be displayed on a page.
For example, suppose a primary key field is an integer, in a database table, the index of the record has been sorted by the value of the primary key field (by default), and the index of the record with the primary key field value of "11" must precede the index of the record with the value "12" (assuming that the value of the primary key exists in the database table is "12 "record). If a primary key field does not have a unique constraint, it is possible for a database table to have records with a value of "11" for two or more primary key fields, so that the position between the records is not determined.
Let's take a look at how to use the primary key for data segmentation queries.
The principle of fast paging method
In fact, the pagination method is derived from other methods. I have carefully analyzed the original method, and found that through optimization and improvement can be very effective to improve its efficiency. The original algorithm itself is very efficient, but lacks the concrete analysis of specific problems. The same paging algorithm can be very efficient when fetching data from the first page, but may be less efficient when fetching the last page of data.
After analysis, we can divide the paging algorithm efficiency state into four kinds of cases:
(1) @PageIndex <= @FirstIndex
(2) @FirstIndex < @PageIndex <= @MiddleIndex
(3) @MiddleIndex < @PageIndex < @LastIndex
(4) @PageIndex >= @LastIndex
The status (1) and (4) represent the first and last pages respectively. They belong to special circumstances, we do not have to use a special algorithm, directly with top can be solved, otherwise it will complicate the problem, but reduce efficiency. For the remaining two states, if the total number of pages is even, we can consider deleting the first and last page records from the database table, then put the rest in the front and back position flat into two parts, that is, the previous part, that is, the state (2), followed by another part, that is, State (3); If the total number of paging is odd, The records belonging to the middle page are attributed to the previous section. These four states correspond to four groups of SQL statements, each composed of ascending and descending two SQL statements.
The following 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. The table will be used for example in the next SQL statement:
PageIndex
ItemId
ProductId
Price
0
001
0011
$
002
0011
$
1
003
0012
$
004
0012
$11
2
005
0013
$14
006
0013
$
3
007
0011
$
008
0012
$
4
009
0013
$
010
0013
$11
Available by table: @PageSize = 2, @RecordCount = ten, @PageCount = 5
An ascending SQL statement
(1) @PageIndex <= @FirstIndex
The first page of the data is simple enough, we just use the top @PageSize can be taken out of the first page to display the record.
SELECT Top @PageSize @QueryFields
From @TableName
WHERE @Condition
ORDER BY @PrimaryKey ASC
(2) @FirstIndex < @PageIndex <= @MiddleIndex
The performance can be improved effectively by separating the SQL statements that take the first half of the data table and the second half of the record. I'll explain the reason in detail later. Now look at the SQL statement that takes the first half of the record. First remove the primary key value of all records before the current page, select the maximum value, and then remove the previous @pagesize record with the primary key value greater than the maximum value. Here the @primarykey data type can be not an integer type, so can other types, CHAR, varchar, and so on.
SELECT Top @PageSize @QueryFields
From @TableName
WHERE @PrimaryKey > (
SELECT MAX (@PrimaryKey) 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, take a look at the SQL statements that are recorded in the second half of the database table. This statement is the same as the principle of the previous statement algorithm, except that the method is slightly different. First remove the primary key value of all records after the current page, select the minimum value, and then remove the previous @pagesize record with the primary key value less than 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 SQL statement that takes the first half of the data table and the second half of the record is written separately, because when you use the SQL statement that takes the first half of the record, the number of records in front of the current page increments with the page size, and we want to remove the value of their primary key field from those records and then select the maximum value from the record. As a result, paging speeds will slow as the number of pages increases. So I didn't do this, but I chose an algorithm that was faster when the current page index was larger than the Middle page index (@MiddleIndex < @PageIndex) with the increased number of pages. Thus, assuming that all the pagination is divided into the front, the middle and the next three parts, the first and the most behind the paging speed is the fastest, the most intermediate paging speed slowest.
For example: @PageIndex = 3, red--> yellow--> Blue
(4) @PageIndex >= @LastIndex
The last-page record can be simply done using a similar state (1):
SELECT * FROM (
SELECT Top @PageSize @QueryFields
From @TableName
WHERE @Condition
ORDER BY @PrimaryKey DESC
) TableA ORDER by @PrimaryKey ASC
However, the resulting last page is not necessarily the last page in the actual sense. Because the number of records on the last page may not be exactly equal to the @pagesize, the above SQL statement is the @pagesize record that gets the reciprocal directly. If you want to get an accurate record of the last page, you should first calculate the number of records for the page, as a condition for 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
The descending SQL statement is the same as ascending, and here's not a wordy. 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 (
SELECT Top (@RecordCount-@PageSize * (@PageIndex + 1)) @PrimaryKey
From @TableName
WHERE @Condition
ORDER BY @PrimaryKey ASC
) TableA
) WHERE @Condition
ORDER 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 do I generate the above SQL statement dynamically?
After looking at the above SQL statement, I believe that we have a basic understanding of the principle of the pagination method. Next, we're going to design a class fastpaging that dynamically generates SQL statements. The class has a public static method that dynamically generates an SQL statement based on the conditions you give it as the return value of the method.
Produces a SELECT statement that sorts and pages a query based on a specified field.
public static String Paging (
int pageSize,//number of records to display per page.
int PageIndex,//Index of the page to display.
int RecordCount,//The total number of records in the datasheet.
String tablename,//data table to query.
String queryfields,//field to query.
String PrimaryKey,//primary key field.
BOOL Ascending,//is in ascending order.
String condition//query filter criteria.
) {
StringBuilder sb = new StringBuilder ();
int pagecount = Getpagecount (recordcount,pagesize); Total number of pages paged
int middleindex = Getmidpageindex (PageCount); Index of intermediate pages
int firstindex = 0; Index of the first page
int lastindex = pageCount-1; Index of last page
if (PageIndex <= firstindex) {
Code slightly
else if (PageIndex > FirstIndex && pageIndex <= middleindex) {
Sb. Append ("select Top"). Append (pageSize). Append ("")
. Append (queryfields). Append ("from"). Append (tablename)
. Append ("WHERE"). Append (PrimaryKey);
if (ascending)
Sb. Append ("> ("). Append ("Select MAX (");
Else
Sb. Append ("< ("). Append ("select MIN");
Sb. Append (PrimaryKey). Append (") from (select top)
. Append (Pagesize*pageindex). Append (""). Append (PrimaryKey)
. Append ("from"). Append (tablename);
if (condition!= String.Empty)
Sb. Append ("WHERE"). Append (condition);
Sb. Append ("ORDER by"). Append (PrimaryKey). Append ("")
. Append (Getsorttype (Ascending)). Append (") TableA)");
if (condition!= String.Empty)
Sb. Append ("and"). Append (condition);
Sb. Append ("ORDER by"). Append (PrimaryKey). Append ("")
. Append (Getsorttype (ascending));
}
else if (PageIndex > Middleindex && pageIndex < lastindex) {
Code slightly
else if (PageIndex >= lastindex) {
Code slightly
}
Return SB. ToString ();
}
In addition to the paging method there are several other ways:
Calculates the number of pages per page based on Total records and paging size.
public static int Getpagecount (int recordCount, int pageSize) {
return (int) math.ceiling (double) recordcount/pagesize);
}
Calculates the page index of an intermediate page.
public static int Getmidpageindex (int pagecount) {
return (int) math.ceiling ((double) PAGECOUNT/2)-1;
}
Gets the way the sort is sorted ("ASC" means ascending, "DESC" means descending)
public static String Getsorttype (bool ascending) {
Return (ascending?) "ASC": "DESC");
}
Gets a Boolean value that indicates whether the sort is in ascending order.
public static bool Isascending (String ordertype) {
Return ((ordertype.toupper () = = "DESC") (false:true);
}
Let the DataGrid work.
With the above class, the task of implementing pagination is much simpler. First, we want to true the AllowPaging and AllowCustomPaging properties of the DataGrid, and in addition to the features of ascending and descending order, we also need to set the AllowSorting property to True. Then, on each page, we need to produce a OleDbDataReader object or DataView object bound to the DataGrid as the data source for the DataGrid. Here you need to use the paging method of the Fastpaging class to generate an SQL statement based on the criteria and assign it to the CommandText property of the OleDbCommand object:
Cmd.commandtext = fastpaging.paging (
Datagrid1.pagesize,
(int) viewstate["CurrentPageIndex"],
Datagrid1.virtualitemcount,
"Items",
"ItemId, ProductId, Price",
"ItemId",
Fastpaging.isascending (OrderType),
""
);
In the above program segment, the value of viewstate["CurrentPageIndex" is updated to E.newpageindex in the page event handler of the DataGrid. In order to facilitate the processing of null values of viewstate, it is best to encapsulate the access operation and null value of viewstate["CurrentPageIndex" in an attribute. DataGrid1. VirtualItemCount should be set to the total number of records in the database table. The DataGrid can virtualize the number of pages of a DataGrid through its and PageSize properties. The value of the VirtualItemCount is set in the page's Load event handler, and the size of the value requires a database access. To improve performance, you can set this value only the first time the page is loaded.
Summarize
The DataGrid is based on Access's quick page-splitting method here. Of course, this approach does not "cure all ills," and there may be a better way for you to implement the functionality. This requires everyone in peacetime work and learning constantly summing up experience, in solving practical problems as far as possible to find the most effective way. This is also the thought that permeates through the method of this article.
