Paging has appeared in examples in several previous sections, and now we will explain it in detail.
There are three ways to achieve this:
1. Top(int topCount) method
In sqlserver and msaccess, it is actually top, and in Oracle, it is implemented through rownum.
2. From(int startIndex, int endIndex)
The number of records from the startIndex bar to the endIndex bar.
3. Page(int pageSize, int pageIndex)
pageSize per page, pageIndex
Below are examples of these three methods in turn.
top method:
DbSession.Default.From<Products>()
.Top(10)
.ToList(); This query queries the first 10 records in the products table. The generated sql statement is as follows: (How to output the sql generated by the viewing component)
Text: SELECT TOP 10 * FROM [Products]
from method:
DbSession.Default.From<Products>()
.From(3, 8)
.ToList(); searches for data from item 3 to item 8 (including items 3 and 8). The generated SQL statement is as follows:
Text: SELECT * FROM ( SELECT TOP 6 * FROM ( SELECT TOP 8 * FROM [Products] ORDER BY [Products].[ProductID] ASC) AS tempIntable ORDER BY [ProductID] DESC) AS tempOuttable ORDER BY [ProductID] ASC
page method:
DbSession.Default.From<Products>()
.Page(10, 2)
.ToList(); Query the data on page 2 of 10 items per page. The sql statement is as follows:
Text: SELECT * FROM ( SELECT TOP 10 * FROM ( SELECT TOP 20 * FROM [Products] ORDER BY [Products].[ProductID] ASC) AS tempIntable ORDER BY [ProductID] DESC) AS tempOuttable ORDER BY [ProductID] ASC
It can be seen that the SQL statements generated by the from method and the page method have the same format. In fact, the page method ultimately calls the from method.
If the startIndex parameter of from is equal to 1, the top method will be called to query.
By default, we do not set a sort. The component will automatically add a sort. If there is a primary key, choose the primary key sort, otherwise choose another column to sort.
Of course, in many cases we need to set which columns to sort.
For example:
DbSession.Default.From<Products>()
.Page(10, 2)
.OrderBy(Products._.UnitPrice.Desc)
.Where(Products._.CategoryID == 2)
.ToList(); The query condition is that categoryid is equal to 2, sorted by unitprice backwards, and there are 10 data on page 2 per page.
The generated sql is as follows:
Text: SELECT * FROM ( SELECT TOP 2 * FROM [Products] WHERE [Products].[CategoryID] = @bee7551993404c8592f07f9b01710bb5 ORDER BY [Products].[UnitPrice] ASC) AS temp_table ORDER BY [UnitPrice] DESC Parameters: @bee7551993404c859 2f07f9b01710bb5[Int32 ] = 2 Is this sql statement unexpected? It turns out that the second page of the query that meets the conditions only has 2 pieces of data, so when querying, just use unitprice in positive order top 2 and it's done.
Let’s remove the condition and take a look:
DbSession.Default.From<Products>()
.Page(10, 2)
.OrderBy(Products._.UnitPrice.Desc)
//.Where(Products._.CategoryID == 2)
.ToList(); The generated sql is as follows:
Text: SELECT * FROM ( SELECT TOP 10 * FROM ( SELECT TOP 20 * FROM [Products] ORDER BY [Products].[UnitPrice] DESC) AS tempIntable ORDER BY [UnitPrice] ASC) AS tempOuttable ORDER BY [UnitPrice] DESC This is The normal generated format is now available.
The above sql scripts are all generated under sql server2000.
In fact, during the query process, the establishment will also automatically query the count() number of records that meet the conditions, so in the case of big data, the efficiency will not be very good.
If the database you set up is sql server2005, count will not be queried, but will be queried directly through row_number() to obtain better efficiency. (Oracle is implemented by rownum)
Here is an example of sql server2005:
DbSession.Default.From<Products>()
.Page(10, 2)
.OrderBy(Products._.UnitPrice.Desc)
//.Where(Products._.CategoryID == 2)
.ToList(); The code is still the same as the above example, and the generated sql is:
Text: SELECT * FROM ( SELECT *,row_number() over( ORDER BY [Products].[UnitPrice] DESC) AS tmp__rowid FROM [Products] ) AS tmp_table WHERE (tmp__rowid BETWEEN 11 AND 20) The method call is still the same, so If the parameter startIndex of from is equal to 1, top is still used first, and row_numer() is not used.
Of course, if you think paging is not ideal enough, you can write SQL or stored procedures yourself.