Determine Total Count Of Items Returned By SPQuery

Question

I'm running a number of CAML queries against a large list in SharePoint 2010, and displaying the results in a gridview to the end user. I wish to page through the results returned by the query to improve performance. However, I am required to display a total count of the items returned by the query on the paging control. My question is, how can I determine the total number of items that will be returned by each query without actually returning them all in a single SPListItemCollection? To be precise, I wish to page through the results 10 items at a time; how can I do this and still have a total count of all items returned by the query?

Update

So far, none of the answers given have addressed my question - as such, I'm offering a bounty. I need to be able to get a total count of the number of items that a CAML query will return without having to run the query and return all of the items. This will enable me to display this total count value to the end user (a set requirement), while paging through the items collection to display a specific page of results in a gridview. This would avoid a massive performance hit for large lists on the page containing the gridview first loading.

If no one offers a valid answer to the above, I will accept an answer that gives a link to an MSDN article that explicitly says that the above functionality cannot be implemented.

Thanks, MagicAndi.

Solution 1

Unfortunately, I don't think you're going to get a definitive answer from MSDN saying what isn't possible... only what is. Here is the entire CAML Query Schema page on msdn. There is no mention of any type of "count" function there.

As for solving your problem, first you need to choose the correct method for finding items. For example, here are a couple cases:

1. Items from one list? CAML
2. Items from a couple lists in the same site? SPSiteDataQuery
3. Items from many lists in many sites? Search API

Second, you can implement some sort of caching in your web part to reduce the time it takes to sort/page/filter. I like using the System.Web.HttpRuntime.Cache to store a DataTable (makes it easy to change the method for retrieving items while your grid can keep using the datatable). You could also use Page.Session instead of caching.

Solution 2

It is a SharePoint limitation. This is why SharePoint doesn't show how many pages you have in view.

Solution 3

You can create a view for that list with items per page limited to say 10. Then use a listview control (instead of gridview) and bind the view.

Solution 4

Sorry it seams the CAML query didn't go through... so once again

    <Aggregations Value="On">
        <FieldRef Name="LinkTitle" Type="COUNT"/>
    </Aggregations>

Solution 5

Just spent couple of hours trying to figure out how to do that.... Below is a list of things that you need to do to get the full list count.

1) Modify the list view that you are accessing and within the totals add COUNT to one of the Columns

2) Add the following code to the CAML query within your webpart

3) Add the following code for displaying the count

            <xsl:value-of select="/dsQueryResponse/Rows/Row/@*[name()='YOUR_COLUMN_NAME_FROM_FIRST_STEP.COUNT']"/>

And enjoy... I have checked it with and without Filter.

Art

View more solutions

Comments

[Tangiest]

Cement, thanks, this is what I thought. Can you point me towards a MSDN reference for this?

[cement]

No feature - no MSDN reference :) Only forums social.msdn.microsoft.com/Forums/en-US/sharepointdevelopment‌​/…

[Tangiest]

sssreddy, thanks for your reply. Unfortunately, I have already looked at using the XSLTListViewWebPart to display the list, but I wasn't able to customize the control to display a total count. This is a set customer requirement.

[Tangiest]

sssreddy, no, unfortunately not. In addition to displaying a set number of items per page, the customer requires that the total number of items in the current view of the list (including any filtering criteria being applied) is displayed in the pager control. It is not possible to update the list view control to include this total count of items in the view.

[sssreddy]

If I understand right, you want a count of the number of items displayed per page. {SPList list = web.lists["TestList"]; SPViewCollection viewCollection = list.Views; stringCollection listofFieldstodisplay = new stringCollection; listofFieldtodisplay.Add("Field1"); viewCollection.Add("NamoftheNewView",listofFieldtodisplay,CA‌​MLQUERY, 10,true,false;} .. Now on UI... { Microsoft.SharePoint.ListView myListView = null; myListView.ID = list.ID; myListView.view = list.views["nameofthenewview"]; mylistview.Databind(); } ..

[sssreddy]

As we mentioned 10 items and set pagination as true, it will always display only 10 items per page. Litview will automatically display the which set of 10 items it is displaying. Hope this solves your problem.

[sssreddy]

Also filtering, sorting are automaticallly handled

[Tangiest]

Kit, as no other answers have appeared, I'm awarding you the bounty. Thanks for your help!

[Tangiest]

Robert, thanks for your answer. The problem is, this solution retrieves ALL the items in the list that match the criteria. In the case I discussed above, this was approx. 80,000 items, which obviously had a significant performance impact. My question was asking how to determine the total count of items returned by a specific query without actually running it.