Tuesday, September 25, 2012

Paging Data Using Query Expression

Problem:
               In previous post related to the query expression I have used simple query expression to get the records but now need to implement the paging by using the query expression.
Solution:
               Paging play very important role when you have large number of records. Say you have 1000 plus records or even 5000 plus records then it is better to page the data instead of showing all the records at once as it will be very difficult for the user to search in the 1000 plus record and find the record which he/she is looking in the record set.You can see the main screen as shown in Image 1, here you can see that I am shown the record information mean record start and end index like from 1 to 5 and then total number of record and the number of selected record at the bottom left of the grid( I have also attached note in the image as shown below) and then on the bottom right side I have display the current page number and then buttons for moving forward, backward and then to the first page( as there is no move to last page button in the MS CRM data grid so I have also not included that button in my grid.
Image 1
Let us now discuss the code needed for the paging in query expression. I have pasted only the related code for the paging as showing in List 1, here you can see that instead of declaring the separate object of the PageInfo I have use the query expression PageInfo object and set the properties which I will used for paging.Here you can see that first property which I set for the PageInfo object is the PageNumber property , mean which page which want to retrieved for the very first time we will send 1 as the page number ( I have used local variable to hold the current page number as page number will be changed from the backward, forward and first page buttons ). Second property which I set is the count property of the PageInfo object which is used to set the number of records per page which will be returned(I have set page size of 5 for my application you can set it to 25 or 50 whatever your requirement is).
List 1

Next property which I set is the PagingCookie, which is used for better performance when you use page and retrieve subsequent page. For the very first time query is send to retrieve the records the PagingCookie is set to null as no record is yet retrieved, when first call return it will contain the PagingCookie (the return PagingCookie is property of the EntityCollection object which you can save and then pass to the query expression if you want to move to next or previous page).The last property which I set for the PageInfo object is the ReturnTotalRecordCount property which is of type Boolean if you set it to true then it will return total number of records found for the criteria and if you set it to false then it will return -1 for total number of records for that criteria
In the callback function I have also only pasted code which is needed for the paging, rest of the code is same, code is listed in List 2 as shown below. Here you can see that first I have get the MoreRecords property of the EntityCollection object, which is very important property. which indicate whether you have more records or not for the criteria you passed.Next property of the EntityCollection object is the TotalRecordCount, which I have stored in my local variable so that to show the total number of records found for the given criteria.

Note: if you have more than 5000 plus records for the entity from where you want to display data, then TotalRecordCount property will return 5000 count not more then 5000, that is why the property MoreRecords play important role mean it will indicate that you can still query from MS CRM for more records if your paging reach the 5000.As in your MS Crm data grid you can see the total count display 5000+ and you can still query for more records.
 
List 2

Last property is the PagingCookie property of the EntityCollection object which is passed to the next query when send to retrieved the next or previous page records. 

Downloads
You can download the source code of the sample from here.
and working CRM 2011 solution from here.

Go to Home

All and any comments / bugs / suggestions are welcomed!

4 comments:

Anonymous said...

Can you show an example on how to merge 2 or more entities into one result set?

Asim Sajjad said...

Anonymous: You can make 2 calls if you have 2 entities to merge. do you want to do merging in query expression ?

Anonymous said...

Hi,
I can make 2 calls but I facing a problem --> when merging 2 result set and bind to the grid, the paging not working..

Asim Sajjad said...

Anonymous: from my point of you as you page size will be fix either 50 or 100, you should divide page size to both entities mean if 50 then for first page you should get 25 and 25 from both entities, mean you have to do in your coding.