In one scenario this past week, I needed to display the total number of files and folders within any folder.  These numbers needed to be recursive – so if a document is three folders deep within the current folder, that still counts.   Same with folders.  I also needed to show a date representing the modified date of the MOST RECENT item or folder, again recursive to the last level.

I found a way to achieve this using a single CAML query and some fancy XSL parsing!

The folder and document counts would be displayed like this:

I’m already using other mechanisms to get lists of folders for display.  The XSL-driven styling shown is probably the topic of a future post.  The challenge was, how to get those counts and dates…

I started with an SPQuery.  The following recursively query pulls the basic information required, within the folder specified.

SPQuery query = new SPQuery();
query.Folder = folder;
query.Query = string.Empty;
query.RowLimit = uint.MaxValue;
query.ViewFields = "<FieldRef Name='Modified' Nullable='True'/><FieldRef Name='ContentTypeId' Nullable='True'/>";
query.ViewFieldsOnly = true;
query.ViewAttributes = "Scope='RecursiveAll'";
SPListItemCollection items = parentList.GetItems(query); 

string itemsXml = items.Xml; 

The trick here is the ViewAttributes property.  There are a few different ways to run an SPQuery, and this was the Scope that gave me the results I needed.  Note that an SPListItemCollection may be accessed as API objects OR XSL, and I chose to pull out my results using XSL.

Our query result is something like this (if you look closely, you’ll see that we have two folders and two documents here):

<rs:data ItemCount="4">
  <z:row ows_Modified="2012-05-31 12:34:01" ows_ContentTypeId="0x01200082B0C5829FE047A1BF58F68DA1DAB12500C7BCD99F82ACA340A0131D59CE62371B" ows__ModerationStatus="0" ows__Level="1" ows_ID="1" ows_Created_x0020_Date="1;#2012-05-31 12:34:01" ows_PermMask="0x7fffffffffffffff" ows_FileRef="1;#depts/it/Lists/CTADocuments/mygroup" />
  <z:row ows_Modified="2012-05-31 12:36:51" ows_ContentTypeId="0x01200082B0C5829FE047A1BF58F68DA1DAB12500C7BCD99F82ACA340A0131D59CE62371B" ows__ModerationStatus="0" ows__Level="1" ows_ID="2" ows_Created_x0020_Date="2;#2012-05-31 12:36:51" ows_PermMask="0x7fffffffffffffff" ows_FileRef="2;#depts/it/Lists/CTADocuments/mygroup/another one" />
  <z:row ows_Modified="2012-05-31 17:49:45" ows_ContentTypeId="0x010100EB5D8789C471B04E80E2A7481607C23B" ows__ModerationStatus="0" ows__Level="1" ows_ID="9" ows_Created_x0020_Date="9;#2012-05-31 17:49:45" ows_PermMask="0x7fffffffffffffff" ows_FileRef="9;#depts/it/Lists/CTADocuments/Just_the_Essentials_Publishing.master" />
  <z:row ows_Modified="2012-06-07 17:05:11" ows_ContentTypeId="0x010100EB5D8789C471B04E80E2A7481607C23B" ows__ModerationStatus="0" ows__Level="1" ows_ID="10" ows_Created_x0020_Date="10;#2012-06-07 17:05:11" ows_PermMask="0x7fffffffffffffff" ows_FileRef="10;#depts/it/Lists/CTADocuments/mygroup/another one/junk.txt" />
</rs:data> 

In this case it was more straight-forward to take the raw XSL and get what I needed using lambda expressions.  For example, to get document counts I applied an understanding of how content type id’s work.  All document content type id’s which inherit from Document (with id 0x0101), start with that number:

const string docCtID = "0x0101"; 
const string ctypeIDElement = "ows_ContentTypeId"; 
documentCount = rowElements
    .Where(dt => dt.Attribute(ctypeIDElement).Value.StartsWith(docCtID))
    .Count(); 

Putting it all together

Finally, we can wrap all of this logic into a single, fairly dense method.   And achieve our goal using only one query!

    const string docCtID = "0x0101";
    const string folderCtID = "0x0120";
    const string ctypeIDElement = "ows_ContentTypeId";
    const string modIDElement = "ows_Modified";
    internal static void GetFolderStats(this SPFolder folder, SPList parentList, out string modDate, out int documentCount, out int folderCount)
    {
        SPQuery query = new SPQuery();
        query.Folder = folder;
        query.Query = string.Empty;
        query.RowLimit = uint.MaxValue;
        query.ViewFields = "<FieldRef Name='Modified' Nullable='True'/><FieldRef Name='ContentTypeId' Nullable='True'/>";
        query.ViewFieldsOnly = true;
        query.ViewAttributes = "Scope='RecursiveAll'";
        SPListItemCollection items = parentList.GetItems(query); 

        //get query results and read them as xml. 
        XDocument doc;
        using (TextReader tr = new StringReader(items.Xml))
        {
            doc = XDocument.Load(tr);
        } 

        var rowElements = doc.Root.Element(XName.Get("data", "urn:schemas-microsoft-com:rowset")).Elements();
        documentCount = rowElements
            .Where(dt => dt.Attribute(ctypeIDElement).Value.StartsWith(docCtID))
            .Count();
        folderCount = rowElements
            .Where(dt => dt.Attribute(ctypeIDElement).Value.StartsWith(folderCtID))
            .Count(); 

        if (items.Count == 0)
        {
            modDate = folder.Item.Value(MODIFIED);
        }
        else
        {
            var dtAttList = rowElements
                .Attributes()
                .Where(dt => dt.Name == modIDElement)
                .ToList();
            dtAttList.Sort((a, b) => string.Compare(b.Value, a.Value));  //sort on values, most recent at top
            modDate = dtAttList[0].Value;
        }
    }