Akal Tech RSS Feed

Topic: Getting total record count in paging scenario

Fri, 26 Dec 2008 14:30:38 GMT

Topic Description:

I was playing with the built-in paging capabilities:

Setting PageIndex to the 0-based index of the desired page
Setting PageSize to the number of records per page

However, I am unable to find a third property, that would supply total number of records in the database matching the search criteria. I need this third piece in order to calculate the total number of pages, for display in a paging control. AffectedRecords is equal to the number of records returned by the query (thus, is <= PageSize). Calling Count() makes a second trip to the database, and does a COUNT(*) with no WHERE clause. Is there currently a means to obtain this information from the business object?

Thanks!

Thomas

Replies: 3
Last Reply: Fri, 26 Dec 2008 14:30:38 GMT
Reply By: Ish Singh

Reply: Re: Getting total record count in paging scenario

Fri, 26 Dec 2008 14:30:38 GMT

Hi Thomas,

If you have the ObjectMode set to Search on the business object, as you load values into fields they get marked with UseInSearch = True. You can set the ObjectMode = None before loading the values this will ensure that only the fields originally marked for search will be included in where clause, however, the current loaded field values will be used.

It is best to call the Count() immediately before or after the Find call. I have added an item in requested feature list that will automatically call the Count (when paging is turned on) and retain the value in the business object for later use.

Happy Holidays!
Ish

Reply: Re: Getting total record count in paging scenario

Wed, 24 Dec 2008 17:00:30 GMT

Thanks Ish, this has been working well. I did notice one gotcha though. I'm not sure if this is only constrained to this scenario, but here is a brief description:

Create a search entity, and set a couple of WHERE clause conditions. A couple of fields are made not visible.
Left outer join this to another table entity. All fields set to not visible, then a couple of fields are made visible.
Paging set to return first page, page size is 15.
I run search. Expected results are returned (1-15 of 78 records)
Here is the interesting part:

If I immediately call Count(), I get the correct result (78).
If I instead, retrieve the value of any one of the returned fields from the search, calling count now returns 0. An inspection of the SQL statement sent by Count() reveals that the field that was examined is now being incorporated into the WHERE clause.

Not sure if this is by design or not, but since the fields are only being read (not changed), I would expect the WHERE clause used by Count() to also remain unchanged.

Thanks,

Thomas

Reply: Re: Getting total record count in paging scenario

Wed, 10 Dec 2008 22:51:49 GMT

Hi Thomas,

You have the correct idea of calling the Count() method, and it will make a second trip to database. However, the Count is fairly trivial for a database to do (assuming you have a primary key/index on your table).

Essentially, we could process both the Select and the Count statement within single call to the database using something called MARS, but that feature is not supported by all databases.

Here is a code sample on how to get the Count of all the records that match your search criteria. For this example, I hade 50000+ rows matching my criteria, but I only wanted 5 rows after skipping the first 5.

using (Customers customer = new Customers()) 
{ 
    customer.ObjectMode = Akal.QuickObjects.ObjectBase.ObjectModes.Search; 
    customer.FirstName.StartsWith("FN"); 
    customer.StartRowIndex = 5; 
    customer.PageSize = 5; 
    int count = customer.Count(); 
    this.GridView.DataSource = customer.Find(); 
    this.GridView.DataMember = customer.GetResultSetName(); 
} 
 

Hope that helps,
Ish