RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


SharePoint Applied: CAML, Your New Pet : Page 2

Discover several ways to search SharePoint efficiently, searching lists, sites, and even entire SharePoint installations.

The SPQuery Object
The SPQuery object lets you specify a query in CAML syntax, and find the matching items using the SPList.GetItems method. For instance, you could easily use SPQuery to filter out all documents for a given user. You can see this demonstrated by the code shown in Listing 2.

This lets you retrieve a subset of items, matching a given criterion in your .NET code. If you wanted to do further lightweight filtering or sorting etc., you could simply covert the SPListItemCollection to a DataTable, and offer client-side filtering capabilities. Even better, you could use LINQ to sift through your data as shown below:

   DataTable returnedItems = 
   IEnumerable<DataRow> selectedRows = 
      from r in returnedItems.rows
      where r["Title"] = 
   "My Announcement"
      select r;
An interesting thing to note about SPList.GetItems and SPQuery is that by default, SPList.GetItems will return results in only one folder of a given list. If you instead wanted to recurse the folders in a given list and identify all items matching the given criterion, you need to add the following line to your code:

   query.ViewAttributes =
The SPSiteDataQuery Object
Well of course, now that you've figured out how to scan subfolders, the business user coyly hits you with another requirement. Could you somehow retrieve all documents, modified or created by the given user, across the entire website? Well, you know that the CAML query for such a requirement would look exactly like you have seen in Listing 1 and Listing 2.

But how can you possibly filter out all documents or list items across the entire site? Use the SPSiteDataQuery object as demonstrated in the code below.

   using (
      SPSite site = 
      new SPSite("http://moss2007"))
       SPWeb web = site.OpenWeb();
       SPSiteDataQuery query = new 
       query.Query = "...";
       DataTable results = 
The code snippet above intentionally omits the CAML query and replaced it with an ellipsis (...). SPSiteDataQuery expects to see the CAML query without the <Query> element, and without any whitespace or line breaks. The query then looks a bit like this (without the line breaks).

  Name='Author'/><Value Type='User'>
</Eq><Eq><FieldRef Name='Editor' />
  <Value Type='User'>
The PortalSiteMapProvider Object
SPQuery and SPSiteDataQuery perform quite well. But sometimes, "quite well" is just not good enough. As illustration, the out-of-the-box views in SharePoint recommend that you place fewer than 2000 items in each container (such as a view or folder). Beyond the 2000 mark, you will see significant performance degradation. For example, a list with 100K items may require up to 152,000 milliseconds to fetch data using SPListItems and For Each. This shortens significantly—to about 2700 milliseconds—using a simple Page_Load in a browser for the out-of-the-box views. It takes even less time (450 milliseconds) when using CAML and the lists.asmx web service. In comparison, even the built-in-search takes about 350 milliseconds to return the results—possibly inaccurately. Amazingly, SPQuery and SPList.GetItems can reduce this time to just over 200 milliseconds, which is impressive for sifting through 100K records.

But what if you had a million items?

At some point, in certain very rare situations, you need extreme performance. Performance that can be met by advanced techniques such as caching. The PortalSiteMapProvider provides exactly that. The PortalSiteMapProvider reduces the time required for the preceding scenario to a mere 5-10 milliseconds for searching 100K records. Of course, due to caching, the downside of using this approach is much higher memory usage on the web server. In other words, at some point, you will actually hurt overall performance by using PortalSiteMapProvider.

The following code demonstrates how to use the PortalSiteMapProvider object.

   PortalSiteMapProvider ps = 
       WebSiteMapProvider ;
   SiteMapNodeCollection nodes = 
       web.ServerRelativeUrl) as 
   "Announcements", query, web);
Figuring Out the CAML Search Syntax
It would be incredibly remiss of me to end this article without mentioning an indispensible tool that makes writing CAML queries a whole lot easier. Check out the U2U CAMLBuilder, available as a free download.

Using U2U CAMLBuilder is incredibly simple and through a point-and-click interface, it lets you craft up a parameterized CAML query, which you can then copy and paste in your .NET code. It really doesn't get much easier than that.

Happy CAML riding!

Sahil Malik Sahil Malik is a Microsoft MVP, INETA speaker, a .NET author, consultant and trainer, and a well-rounded overweight geek. He has a passion for SharePoint 2007, data access, and application architecture. Sahil loves interacting with fellow geeks in real time. His talks are full of humor and practical nuggets. His talks tend to be highly charged, fast moving, and highly interactive. Be sure to check out his blog.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date