SPSiteDataQuery for Cross-Site and Cross List Searches

Practically everyone who deployed and developed in WSS 2.0 wished for a cross-site or cross-list search. SPS 2003 using the search service already allowed for aggregate search, though to limit it to lists only one had to do some fancy pruning of results. Effectively many were looking for “rollup” searches – Show me all my issues, show me all my tasks…

SPSiteDataQuery is a new type in WSS and MOSS that allows for cross-site/cross-list searching. Similar to SPQuery found in WSS 2.0 and still available in WSS 3.0 SPSiteDataQuery is CAML-based. CAML being that oh so fun Xml syntax for defining SharePoint views and queries.

Where SPSiteDataQuery and SPQuery differ SPSiteDataQueries ability to search more than a single site or single list. SPSiteDataQuery can be configured to search in all lists of a particular list type or list base type located in either
1) The complete site collection
2) a particular site and sub-sites

Note that you cannot pick individual lists only lists types nor can you select only certain sites. Your choices are all sites within a site collection or a particular site and all sub-sites. Via the Where clause if you are clever you can achieve a more limiting search.

Creating a search using SPSiteDataQuery is simple. The SPSiteDataQuery is documented in the WSS 3.0 SDK. Unfortunately there are no extremely useful examples to explain the limits of the query object. In this post I want to demonstrate a simple SPSiteDataQuery. In the next posts I will cover more details and point out a few gotchas.

To create a simple SPSiteDataQuery in Visual Studio.NET 2005 follow these steps:
(These steps assume you are working on the WSS or MOSS server!)

1. Create a new Console project. For this demonstration we will create a console application. Feel free to create a web control or Web Part if you wish buut the code below assumes Console app.
2. Add the WSS 3.0 reference to the new project
3. Replace the existing content in Program.cs with the following

using System;
using System.Data;
using Microsoft.SharePoint;

namespace SPSiteDataQueryDemo
{
class Program
{
static void Main(string[] args)
{
Program app = new Program();
app.RunQuery();
}

private void RunQuery()
{
SPSiteDataQuery q = new SPSiteDataQuery();
q.ViewFields = “<FieldRef Name=”Title”/>”;
q.Lists = “<Lists ServerTemplate=”1100″/>”;
q.Webs = “<Webs Scope=”SiteCollection”/>”;
q.Query = “<Where>” +
“<Contains>” +
“<FieldRef Name=”Title”/>” +
“<Value Type=”Text”>Share</Value>” +
“</Contains>” +
“</Where>”;

DataTable dt=null;
using (SPSite s = new SPSite(“http://[site]”))
{
SPWeb w = s.OpenWeb();
dt = w.GetSiteData(q);
w = null;
}

foreach (DataRow dr in dt.Rows)
{
Console.WriteLine(dr[“Title”]);
}
Console.Read();
}
}
}

Once compiled this code will search all issues lists contained within the site collection as defined by the SPSite object s. The ViewFields property contains the fields to be returned such as Title. The List property defines the type of lists to query for example 1100 which represents the id for Issues lists. Each lists template has a unique id. Built in list template ids can be found in the WSS SDK.

The Webs property defines the scope of the search. The Scope attribute of the Webs node can either be Recursive or SiteCollection. Recursive will search the web and any sub webs. SiteCollection will search all webs contained in the site collection regardless of which site the query is performed on.

The Query property contains the optional Where or Orderby clause. In this example the where clause is searching for “Share” within the Title field by using a Contains node. The WSS SDK details the available supported syntax. Multiple clauses can be nested to create more complex queries.

Once the query is defined the code binds to a site and then opens web as defined in the passed in URL to the SPSite constructor. The GetSiteData method of the SPWeb class accepts the query as a parameter and returns a dataset.

The rest of the code simply outputs the title field to the console and waits for a character input at the console before dismissing the console.

There is more to the SPSiteDataQuery then what this simple application demonstrates. But for today that is enough to get you started.