Cross-List Queries in SharePoint 2007

How to use cross-list queries in SharePoint 2007.

A Cross-List Query is a way to query a list that could be in the same site or another site and get back the meta data related to that list. This can come in useful when you need to display information about a list that is located in another site such as an HR department displaying information about a list in accounting. To easily retrieve the list data across multiple lists, the SPSiteDataQuery object can be used to bring back a collection of items. To perform the same operation in previous versions of SharePoint required the creation of custom code to bring back all of the list items individually from each list. To perform the query, the SPWeb object is used with a method called GetSiteData that passes the SPSiteDataQuery object and returns a DataTable with the list items selected.

Follow the below steps to enable the user to use cross-queries:

1. Create an SPSiteDataQuery object and create an SPWeb to search through:

     SPWeb web = SPContext.Current.Web;
     SPSiteDataQuery query = new SPSiteDataQuery();
     DataTable dataTableResults = site.GetSiteData(query);

2. Using the CAML markup language set the list types that will be searched through. Every list has a numeric value that is used to define the list.

     SPWeb web = SPContext.Current.Web;
     SPSiteDataQuery query = new SPSiteDataQuery();
     query.Lists = "<Lists ServerTemplate=\"101\" />";
     DataTable dataTableResults = site.GetSiteData(query);

3. To define a query that will return the list items requested, first create a query in CAML.
            
            SPSite site = new SPSite("http://localhost:20918");
            SPWeb web = site.OpenWeb();
            SPSiteDataQuery query = new SPSiteDataQuery();
            query.Lists = "<Lists ServerTemplate=\"101\" />";            
            query.Query = "<Where><Contains><FieldRef Name=\"Title\" /><Value Type=\"Text\">edition</Value></Contains></Where>";
           DataTable dtResults = web.GetSiteData(query);

4. As we are searching the whole site for information, the list dta could be very large. The RowLimit property helps limit the size of data that is returned to you.
            
             SPSite site = new SPSite("http://localhost:20918");
            SPWeb web = site.OpenWeb();
            SPSiteDataQuery query = new SPSiteDataQuery();
            query.Lists = "<Lists ServerTemplate=\"101\" />";            
            query.Query = "<Where><Contains><FieldRef Name=\"Title\" /><Value Type=\"Text\">edition</Value></Contains></Where>";
            query.RowLimit = 10;
           DataTable dtResults = web.GetSiteData(query);

5. The ViewFields property allows you to specify the fields that will be part of the list of items returned. This way, only the fields that are important are returned, without all of the other columns that don't matter. This also helps limit the amount of data returned to the user.
           
            SPSite site = new SPSite("http://localhost:20918");
            SPWeb web = site.OpenWeb();
            SPSiteDataQuery query = new SPSiteDataQuery();
            query.Lists = "<Lists ServerTemplate=\"101\" />";            
            query.Query = "<Where><Contains><FieldRef Name=\"Title\" /><Value                  Type=\"Text\">edition</Value></Contains></Where>";
            query.RowLimit = 10;
            query.ViewFields = "<FieldRef Name=\"Title\" />";
           DataTable dtResults = web.GetSiteData(query);

6.  The Webs property allows you to manage the scope in which the query executes. The query is set to the current web site when created, but can be changed based on the Webs property. The current web site is determined from the method GetSiteData of the current SPWeb object. There are two possible values that can be used with this option: recursive and SiteCollection.
            
             Recursive - Instructs the query to search all web sites below the current web site object.
             SiteCollection - The query will search all locations that are in the same site collection as the web site object.

The following code demonstrates how you can limit the scope of your query just to the SiteCollection. This allows you to get data only from the locations that are required.
            
            SPSite site = new SPSite("http://localhost:20918");
            SPWeb web = site.OpenWeb();
            SPSiteDataQuery query = new SPSiteDataQuery();
            query.Lists = "<Lists ServerTemplate=\"101\" />";            
            query.Query = "<Where><Contains><FieldRef Name=\"Title\" />" +
                 "<Value Type=\"Text\">edition</Value></Contains></Where>";
            query.RowLimit = 10;
            query.ViewFields = "<FieldRef Name=\"Title\" />";
            query.Webs = "<Webs Scope='SiteCollection' />";
           DataTable dtResults = web.GetSiteData(query);