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.

Answer 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.

Answer 2

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

Answer 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.

Answer 4

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

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

Answer 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

Answer 6

Is possible two solutions, both with limitations:

• perform lightweight query with no defined viewfields
• use 'unlimited pager'(ex. if you use DataGrid, set VirtualItemCount to 10K or above)

Answer 7

I have only found a "dirty" way that's totally outside anything supported :) I've created a view on the database and abused SQL server to count for me. Totally broken and totally non supported and absolutely going to bite myself but the only way I've found to get a count on a large set of records. Did not use it as it's not safe for production.

Edit: all list data is dumped into one huge table AllUserData and if you have the tp_listid you can create a count

SELECT        COUNT(nvarchar7) AS Expr1
FROM          dbo.AllUserData
WHERE        (tp_ListId = 'xxxxxxxx-9999-3333-7777-yyyyyyyyyyyy')

But then you have to start filtering for versions if you use them

WHERE        (tp_Version =
             (SELECT        MAX(tp_Version) AS ex1
             FROM            {{view above}} AS V1
             WHERE        (key = M.key)))

etc etc. You can get the count and you can get it very fast but foot meet gun.....

Answer 8

Don't know if we are on the same page... but you can count items received by SPQuery quite simple:

   SPQuery query = new SPQuery();
   query.Query = string.Concat(
                  "<Where><Eq>",
                     "<FieldRef Name='Status'/>",
                     "<Value Type='CHOICE'>Not Started</Value>",
                  "</Eq></Where>",
                  "<OrderBy>",
                     "<FieldRef Name='DueDate' Ascending='TRUE' />",
                     "<FieldRef Name=’Priority’ Ascending='TRUE' />", 
                  "</OrderBy>");                    


   SPListItemCollection items = list.GetItems(query);
   double totalCount = items.Count;

Maybe this is your solution.