Subsonic + Paging question

Question

Hi, I am trying to do server-side paging with Subsonic. Using the .Paged function works well (the link to the webcast seems to be broken btw). However, how do I go the total number of records from my query (without the .Paged function) i.e. do I need to run a recordcount on the query which returns the full result first? If yes, then it kinda defeits the purpose of server-side paging?

e.g.

SQLQuery q = Select.Allfromcolumns .Paged(1,10), will return 10 records only. However, I need to know the total number of records from Products first to determine my Page links?

For a simple table, there is no need for server-side paging, but my query is very complicated one and I need to use the Paging functionality for performance mainly.

thanks

Answer 1

The following will return a count of the total number of records that would be returned so you can build your query, then call GetRecordCount() on it to find how many records will be returned and then call Paged(1, 10) to return the first page:

SqlQuery query = Select.AllColumnsFrom<Product>();
int numberOfRecords = query.GetRecordCount();
List<Product> products = query.Paged(1, 10).ExecuteTypedList<Product>();

Answer 2

I would like to point out that typically when someone does "serverside" paging they run a query that returns all rows. They take a count of the rows (record count) and then they grab the rows they need for displaying to the user and display the subset (Good amount of overhead and processing). Using the query posted by Adam you will save yourself a lot of that overhead if on the first request you cache the "numberofRecords" and then on each of the subsequent requests you only perform a Paged query which only brings back the wanted rows from the database.

Answer 3

SqlQuery query = Select.AllColumnsFrom<Product>(); 
if(IsfirstCall){//Set firstCall = false on all subsequent calls to the method
int numberOfRecords = query.GetRecordCount(); }
List<Product> products = query.Paged(1, 10).ExecuteTypedList<Product>();