The idea is to maintain a data cache in your Web application so that you can retrieve the dataset directly from the memory between different page request and minimize the number of query to database. You can choose the follow technique for data caching in ASP.NET:
1. Session State - If you would like to maintain different dataset for each user session, you can choose session state since every user session is isolated from each other and the data cache cannot be shared among different user session.
2. Application State - If you would like to have one dataset shared among different user sessions, then you can choose application state. It is good to use if the backend data would update so often or even static and the memory resource used in application state is not a concern.
3. Object Caching - If you would like to have one dataset shared among different user sessions and the data needed to be refreshed, then you can use object caching. You can apply different object expiration policies when creating the object in the memory cache and you can also associate a dependency object to the cache so that you will receive a notification from database when data is being updated. Once you got the notification, you can execute your code to refresh the cached object.
You can still have the row_number in the query to populate your dataset so that you can apply manual paging in the presentation.
