Article on paging controls

Original article:
Http://www.cnblogs.com/jyk/archive/2008/06/26/1230660.html

In fact, several posts have been posted in csdn in terms of paging.

Http://topic.csdn.net/t/20050804/20/4189627.html
Http://topic.csdn.net/t/20050802/08/4182510.html

Now, sort it out.

The page control is named mypage.

Version: 2.0.0.1
Framework:. net2.0
Webform (B/S)
Supports multiple databases and pagesAlgorithmThe data extraction method can be replaced.

Paging: since the project of B/S, it has been handed over to the page. At the beginning, usercontrol was used, which is rough and has no performance. However, it is inevitable that all the data is extracted at one time by PAGE, and there are too many resources to use. As a result, we are constantly trying to find a solution. First, the operation is simple and the performance is better.
To make operations simple, You Need To encapsulate them into custom controls. To achieve better performance, you need to study the features of the database. The most important thing about the paging algorithm of SQL statements is to set up reasonable indexes! Indexes are often the most important, but they are ignored at the same time.

So on the one hand, we studied how to write custom server controls and How to Write SQL statements to make data extraction faster. Since SQL Server2000 has been used all the time, paging algorithms are also transferred around this database. At the end of 04, the initial implementation was finally achieved. At least it was quite easy to use. That is, at this time, we found the paging control of wuqiwa. So we made a comparison:

The paging control of wuqiwa is only responsible for UI rendering. It displays the total number of records, total number of pages, current page number, Previous Page, next page, and page number navigation (, 3, 4 ...) you can generate paging events. Other types of paging algorithms, databases (or XML), and how to extract data need to be encoded separately.

This method is flexible. This method is applicable to multiple data sources, even XML files, and the paging algorithm can be freely selected. This method is indeed flexible, but the disadvantage is that it is cumbersome.

For paging only for databases, each paging operation requires that the data be extracted by another write point.CodeIt's really troublesome. I am very lazy and naturally don't want to accept this situation. I want to do so, only a few lines of simple code can be written to implement all the paging functions! I can accept that the paging control can only paging SQL Server and only use datatable to transmit data, but it must be simple and simple to call.

Therefore, my page control (mypage) is not only responsible for drawing the UI, but also for generating SQL statements (combining SQL statements based on attributes ), we also need to extract data from the database (of course, this function should be implemented by the data access function library), and finally we need to be able to automatically process the events generated during page turning and automatically bind the control.

Previous versions are based on. written in net1.1, the main code structure is determined at the end of. Later, it will only be minor repairs and minor supplements, and no major changes have been made, because it can be used all the time and there is no major problem. Now vs2008 has been out for a long time and should be upgraded. Since the upgrade is completely upgraded, it is not only to copy the code to vs2008, but also to recompile the code (this can indeed be a successful compilation ), I would like to take this opportunity to thoroughly change the structure.

Compared with the paging control of wubanwa, the previous paging control does not have the flexibility and is not supported by multiple databases, and the paging algorithm cannot be flexibly changed or freely written, only datatable can be used to carry data. PostBack paging and URL paging are implemented using two controls. Everything looks messy (but it's strange, it's okay to run it ).

Since we know that there are so many shortcomings, we must correct them one by one during the upgrade. According to the division of responsibilities, set a class to draw the UI, set a class to use the paging algorithm, and set a class to extract data, finally, these classes are used as members of the paging control, so that they can be called and replaced freely. The structure is as follows.

It seems like a bit of suspicion of over-design. I'm also thinking, is it so troublesome? Do you really need to write this?

(Wait a moment. In this case, it looks a bit like a three-layer structure. pageui is a bit like a UI Layer. pagesql is a logic layer (paging, how do I write SQL statements, this can be regarded as a logic. If it doesn't, it doesn't matter.) pagegetdata is a data access function. Of course, a data access function library is required, but this is outside the paging control. I don't want to bother writing like three layers. Haha .)

However, I did not want to write all possible paging algorithms of all databases to the database. Instead, I can inherit the pagesql class from the page control to write a subclass, implement the required paging algorithm in this subclass and assign this instance to the paging control.

Mypage. managerpagesql = yoursql;

Of course, if you do not copy this attribute, the paging control will generate a default pagesql instance to automatically assign values. You can use the same method to replace pageui and pagegetdata with the desired instance (implementation method ).

Several misunderstandings:

1. stored procedure. It doesn't mean that the stored procedure is efficient. It depends on what the Stored Procedure looks like internally. If the Stored Procedure adopts the combination of SQL statements, the biggest advantage of stored procedures is lost: cache execution plan! Imagine how to save the execution plan for SQL Server by combining SQL statements in the stored procedure? If it cannot be savedProgramWhat is the difference between combining SQL statements and submitting them to the database?

I have seen a lot of pages on the Internet.ArticleIn most cases, a stored procedure is provided directly. In this stored procedure, combine SQL statements and you must know how depressing the code is.

Of course, you can use a stored procedure for a table to cache the execution plan, just like the stored procedure provided by the template that generates the stored procedure provided by Wu banwa's website, but there are a lot of problems like this: it will increase a lot of stored procedures, it is difficult to set query conditions flexibly (the more query fields, the less difficult to write), and upgrade the database (including upgrading the paging algorithm ).

It is difficult to manage many things. Imagine a database has 500 stored procedures, of which 100 are used for paging, 100 are used for adding data, and 100 are used for paging, is it messy?

Query: when developing a program for enterprise customization, you can set all fields in the list to queryable, and query multiple fields together. Haha, it is not easy to write the query method for the second and thirty fields in the stored procedure, and the requirements are frequently changed.

The previous project used SQL Server2000, And now SQL server2005 has come out. Is it necessary to upgrade it? previously, we used the paging method of table variables (or other methods). I heard that row_number () is used in 05 () it is more efficient and easier to write. Do you want to change it? This operation is not very easy.

It is much more convenient to use the paging control, because the paging algorithm is combined within the paging control, as long as the attributes (such as tabletname) remain unchanged, using these attributes almost combines them into any paging algorithm, without fear of database upgrades and algorithm replacement.

(This is why my paging controls do not use stored procedures .)

2. index. To improve the paging efficiency, you must set indexes, including non-clustered indexes.

The index mentioned in the article about paging algorithms has a low probability. It does not mean that indexes are required only when massive data volumes exist. In the case of a small amount of data, there are also needs.

We met one in the last two days. Six or seven tables are associated, and the master table has more than 10 thousand records (the table with the most records ), association is more complex (two associated fields must be used in two tables; otherwise, duplicate records may occur). There are also many query conditions and three fields must be used for sorting. These three fields are not in the same table.

At the beginning, there were not many records, and the speed was very fast. After more than 10 thousand records were recorded, the first few pages were also relatively fast. This was the customer who wanted to see the efficiency and took a look at the last few pages, this is terrible. It will only be reflected in five or six seconds later. The reverse review is really slow. This data volume is not so slow. It is not easy to persuade the customer (after all, there are only five or six fields in a record and no more than 20 words in total). I 'd like to find a solution.

Open the query analyzer, copy the SQL statements in the view, and view the execution plan. There are a lot of errors. One by one, many of them can use indexes to find data, A full table scan is required, occupying 46% of the total table scan. Let's start with him. In fact, I still cannot fully understand what the diagrams of the Execution Plan mean, and I can only guess what it means. Finally, a non-clustered index is added to the last sorting field (it seems that a non-clustered index is added to the Field). The problem is solved. Check the last few pages, data (LAN) can be displayed within two seconds ). This is acceptable. In fact, data is first filtered through the query conditions when it is officially used. So as long as the first 100 pages can be quickly displayed.

Therefore, to improve the paging efficiency, you must first set up a reasonable index.

I checked the dictionary. The page has the meaning of turning pages, and the pager is the meaning of a bpserver and a network pager. I don't know why a paging control is called mypager. No matter what other people think, my name is not the same.

How to Use mypage
Protected void page_load (Object sender, eventargs E)
{
Mypage. controlgridid = GV. ID; // you can specify the ID of the control that displays data.

If (! Page. ispostback)
{
// Attributes will be saved in viewstate, so you can assign values during the first access.
Setpageinfo ();
}
}

Private void setpageinfo ()
{
Mypage. tablename = "v_list_table"; // table name or view name
Mypage. tableshowcolumns = "*"; // displayed Field
Mypage. tableidcolumns = "ID"; // primary key
Mypage. tableordercolumns = "col1, col2, col3"; // sorting Field
Mypage. pagesize = 10; // number of records on one page
Mypage. navicount = 5; // number of page number navigation items
Mypage. tablequery = ""; // query Condition

Mypage. bindfirstpage (); // display the data on the first page

}

Don't be afraid, don't assign values to all attributes, at least
Mypage. controlgridid
Mypage. tablename
Mypage. tableordercolumns

This is the attribute value, and then execute mypage. bindfirstpage.

Write this first. I feel dizzy.

Page ControlSource codeDownload URL: http://www.cnblogs.com/jyk/archive/2008/04/25/1170979.html
(Under the webpage)

The downloaded file contains a test webpage: http: // localhost: 5561/test/testlist. aspx? FID = 1
Pay attention to the following parameters.
Modify the connection string and Database Type in webconfig. Datatype ---- 1: ms SQL; 2: oledb; 3: ODBC.

View the source code directly:

Http://www.cnblogs.com/jyk/archive/2008/06/25/1229967.html

Http://www.cnblogs.com/jyk/archive/2008/06/25/1229973.html

The paging algorithm currently used by SQL server2005

Set nocount on;
With t_pager (
Select *, Rn = row_number () over (order by id desc) from test_indexorder
)
Select ID, name, content, co1, CO2, O3, co4, co5 from t_rn where rn between 19007 and 19057;