One of things featured in the Alpha (Preview Release) is Pagination. How pagination works is the use of the ExecutePageReader method from the DbCommand class. In the Beta One release, the ExecutePageReader method is now obsolute. With the pagination, you can query what section of the data can be read from database. For example, you can say that you want records 20 to 40 from any given table.

However, it used SQL Cursors. This method is SLOW when used on huge tables. So Microsoft decided to leave it and move on. However, there is a technique that has been in my “books” for a long time is the following SQL Query:

Select * From
   (Select * Top RowCount From (
         Select Top {StartRow + RowCount}
             ColumnsToSelect FROM TableNameOrFurtherSelect
                    [NormalStuff]
             Order By NormalOrder
   ) Order By OppositeOrder)
Order By NormalOrder

There are a view variables that I italiced and bolded. I will discuss the normal stuff below:

• RowCount: Is the amount of rows to be returned from the database table.
• StartRow: The row index to start the selection from. This is used in the sum with RowCount in place of the {StartRow + RowCount} -- treat it as one constant; not an equation.
• ColumnsToSelect: Your normal columns to select from the table.
• TableNameOrFurtherSelect: This is basically one of the two:
  • The table to select from.
  • The query to run on other tables. This query must be encollapsed by brackets.
• NormalStuff: Things like WHERE clauses and such all goes here.
• NormalOrder: Well, this the trick to magic. Basically, you would know more or less the order of the data that is going to be played to you. For example, if you don't really do any sorting, you know that the order of the records/rows will be recieved in Primary Key-Identity Order.
• OppositeOrder: The complete reverse order of the NormalOrder. For example, if you NormalOrder was “Column1 ASC, Column2 Desc” then your OppositeOrder will be “Column1 Desc, Column2 Asc” and so forth.

Hope you guys and girls learnt something. If any of you have other ideas or tricks that you want to share for pagination, fire them my way. The only thing that I will be “over the moon” is for faster ways to do the selection process on the SQL Server.