Implementing custom folder statistics in SharePoint 2010 using c#
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; } }
Hallo Michael, could You please explain, where You present results of this query (that is these black shapes with folder picture and description) – on custom form or on allitems.aspx page. If on allitems.aspx then how do You do it? With custom field or somehow other?
Thank You
Hi Alex, thanks for your question. Yes, the black boxes with information are the listing.
This was done on a custom ASP page. The query results are delivered through a custom control (though it could easily be a web part). The ASP page accepts a parameter containing the partial url of a list or one of its subfolders. That url is passed to the control in the codebehind.