Tuesday, March 4, 2014

SPSiteDataQuery to aggregate results from multiple lists

If we need to execute a query against multiple lists to aggregate results, the best approach is to use SPSiteDataQuery.

Following is a sample code of the usage. In this scenario I want to get all documents created by user “spadmin” those reside anywhere within the site collection. So the query should execute against all document libraries within all sites under the site collection

  1. using (var site = new SPSite("http://sp13:8080/sites/8674"))
  2. {
  3.   using (var web = site.RootWeb)
  4.   {
  5.     var query = new SPSiteDataQuery();
  6.     query.Webs = "<Webs Scope=\"Site Collection\">";
  7.     query.Lists = "<Lists ServerTemplate=\"101\" />";
  8.     query.ViewFields = "<FieldRef Name=\"Title\" />";
  9.     query.Query = "<Where><Eq><FieldRef Name=\"Author\"/><Value Type='User'>spadmin</Value></Eq></Where>";
  10.     DataTable table = web.GetSiteData(query);
  11.   }
  12. }

There are 3 configurations for the Webs Scope setting.

  • Not Set : This is the default setting. If this is the case, it will query inside the current web only
  • Recursive : Query inside current web and all its sub webs
  • Site Collection : Query inside all webs within the site collection

No comments: