asp.net universal Data Paging code [revised download address]_ practical Tips

1. Theme, asp.net environment, universal data paging (including ORACLE,SQLSERVER,DB2 ...)
Long ago, I wanted to make a general data paging component, but I didn't have enough time so the delay has not been achieved (of course, mainly because there is a certain degree of difficulty, to deal with the view state, return events, painting, etc.), "11" period, with his girlfriend to lie down "Xiangxi", the day sightseeing, night nothing, Eat local supper, boring, Google a Oracle data paging, unexpectedly did not have a complete on asp.net + Oracle data pagination example, or there is a version of the problem, simply decided to get one.
2. Environmental
ide;vs2008+asp.net2.0 or 3.0+oracle9i
Oracle test data; 410,000-odd
Bandwidth, Netcom said that there is 2M of bandwidth, in fact, Tnnd is only more than 100 kb
3. Principle
Use Oracle9i's rownum to get the data we want, as shown in Figure 3.1

Copy Code code as follows:

Public IEnumerable getpagesqlbyoracle (String sql, int startrowindex, int maximumrows, string by)
{
String Sqlformat = String. Format ("Select * from" (select RowNum rn, pagetable.* from {0} pagetable where rownum <={1}) where rn>{2} ", SQL, star Trowindex + maximumrows, startrowindex);

Return Ydjworacleconn.retrivedataset (Sqlformat). Tables[0]. DefaultView;
}

startRowIndex represents the start index,maximumrows represents the number of records per page to get.

Then bind the paging data to the ObjectDataSource component, as shown below

4. Effect

4.1 Support for multiple sorting, such as time, identification number, etc.

4.2 410,000 data

4.3 Single condition query, first time 1.547 seconds

4.4 Second time, spents 1.515 seconds

The third time is similar, will not stick.

4.5 Look at the query without conditions, one to 410,000 data all the time to use the query out

You can see that 410,000 data is queried without conditions, with 1.265 seconds for the first time.

4.6 can see 410,000 data without conditions, second time 1.156 seconds
4.7 can be replaced with SQL Server, or DB2 database, only need to modify figure 3.1 to obtain the data method, other places do not move.
5. Where attention is required
5.1 Remember the amount of data, must be indexed, otherwise you have to wait for "flowers bloom."
5.2 ObjectDataSource All parameter names must be in the same order as the method parameter names that are bound.
5.3 Can use the cache, as far as possible with the cache
5.4 SQL Server paging algorithm, do not say, replace the figure 3.1 method on the line
5.5 Because the database file is too large, inconvenient to download, interested friends to get a table on the OK, plus webconfig in the database connection configuration
6. Download
Test Demo Download Http://xiazai.jb51.net/200810/yuanma/TestDataBindControlls.rar "Modified"
7. Something
The said all said, I hope to help inspire you, there is this test demo has not been added cache, interested friends can add, welcome to discuss, criticize, guide ...