Walkthrough:SPSiteDataQuery example code in C#

SPSiteDataQuery:Describes a query performed across multiple lists, which may be located in multiple Web sites in the same Web site collection.
Namespace: Microsoft.SharePoint
Assembly: Microsoft.SharePoint (in microsoft.sharepoint.dll)

Here is a console application example that to get all tasks in all task lists of sitecollection:

using System;
using System.Collections.Generic;
using Microsoft.SharePoint;
using System.Data;

public class MyClass
{
    public static void Main()
    {
        try
        {
            using (SPSite oSite = new SPSite("http://<your site>"))
            {
                SPSiteDataQuery q = new SPSiteDataQuery();
                q.Lists = "<Lists ServerTemplate='107' BaseType='0' />"; 
                q.Query = "<Where><Gt><FieldRef Name='ID' /><Value Type='Number'>0</Value></Gt></Where>"; 
                q.Webs = "<Webs Scope='SiteCollection' />"; 
                q.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='ID' />"; 
                q.RowLimit = 10;

                DataTable t = oSite.RootWeb.GetSiteData(q); 
                t.TableName = "row";
                DataSet ds = new DataSet("rows");
                ds.Tables.Add(t);
                Console.WriteLine(ds.GetXml());
                Console.WriteLine("-- Press Any Key --");
                Console.ReadKey();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
            Console.ReadKey();
        }
    }
}

 

 

• The Webs Property
  There are basically three different values for this...
  "<Webs Scope='SiteCollection' />" This will search the entire site collection no matter which web you use to execute the query."
  "<Webs Scope='Recursive' />" This will search the web on which you execute the query and recurse through any child webs. " 
  If you leave it blank then it will only search the web on which you execute the query. No child webs will be queried. This is important as I have read on several other sites that this is not possible with SPSiteDataQuery, but it is!!
  
  I would also point out that that if you get anything wrong with this property SharePoint will not throw an error, it will just default to the blank behavior...It will only search the web on which you executed the query. This is an important point as "<Webs scope='Recursive' />" or "<Webs Scope='recursive' />" (small 's' in Scope and small 'r' in recursive) look OK but are actually invalid and the query will default to only the current web. 

 

• The Lists Property 
  Defines what type of document libraries and lists WSS will search for your items. You can specify the exact type of list, the base type or even specific lists. Examples of the Lists property are..."<Lists BaseType='1'/>" As above, this will search all lists which are based on a 'Document Library. This is useful if you only want to find documents. Other values for BaseType include...0 - Generic list - This will search all lists and not document libraries.
  1 - Document Library
  3 - Discussion Forum
  4 - Vote or Survey
  5 - Issues list
  PS:if u know what happened to number 2,please tell me. I should also point out that the default is to search BaseType = '0' , and so if you do not set or make a mistake in the XML only lists will be searched.
  You can find the description of the SPSiteDataQuery.Lists Property:http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.lists.aspx
  
  limits the query to lists with a specific ServerTemplate or BaseType. In the example above, the element limits the query to Task lists (ServerTemplate='107').
  See the description of the Type attribute for a list of ServerTemplate numbers: http://msdn2.microsoft.com/en-us/library/ms462947.aspx 
  
  

  Another options is Hidden, which determines if hidden lists or document libraries are searched. This an additional attribute and would be used like this...

  "<Lists ServerTemplate='850' Hidden='TRUE'/>"

  The MaxListLimit attribute specifies the total number of lists to search. You will receive an exception if the query exceeds the MaxListLimit. The default amount is 1000 and by setting this to 0 you can search everything. So the following would only search the first 50 lists...

  "<Lists BaseType='1' MaxListsLimit='50'/>"

  Another thing you can do with the Lists property is to query specific lists. This can be done by specifying the Guid of the list you want to search. An example would be...

  "<Lists><List ID="129AB4CAE-12EF-9871-DE45-F34A180D3EAB5"/></Lists>"

  You would obviously need to know the Guid of the lists you wish to query before creating this property.
   

• The ViewFields Property
  The ViewFields property specifies the fields (columns), that will be returned in the query. This is very similar to SQL and you should ensure that you specify any fields that you may wish to use in you Where or OrderBy part of the query.
  Things to point out here is that that you can specify the ID(Guid) of the property or the name of the property...this is the Internal Name, not the name you may see in the UI. For example the standard publishing field "Image Caption" would become "PublishingImageCaption" as that is it's internal name.
  So, to add the "Image Caption" filed to the results we would need...
  "<FieldRef Name='Title' /><FieldRef Name='ID' /><FieldRef Name='PublishingImageCaption' />"
  Another thing to remember is that not all lists or documents libraries contain the same fields. If you are not worried about a particular field and want the item returned whether the field (column) exists or not the you can set Nullable to true. So if we have some items which may not have an 'Image Caption' column then we could use...
  "<FieldRef Name='Title' /><FieldRef Name='ID' /><FieldRef Name='PublishingImageCaption' Nullable='TRUE'/>"
  and this would still find those items without an 'Image Caption' column (field).

 

• The Query Property 
  This property will allow you to bot limit and order you results. You can do both or just one, but it is similar to SQL in what you can do. There is a lot to this, but I will give a couple of samples...
  Querying by date...
  string sLastWeek = SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Today.AddDays(-1));
  q.Query = "<Where><Gt><FieldRef Name='Created'><Value Type='DateTime'>" + sLastWeek + "</Value></Gt></Where>";
  This will find items created within the last week. The <Gt> denotes 'Greater Than', you could also use <Gte>, <Lt> or <Eq>. These can be combined to create more complex queries.
  q.Query = "<OrderBy><FieldRef Name='Title' Ascending='FALSE'></OrderBy>";
  This will order the items descending by the title (Z-A).
  q.Query = "<Where><Gt><FieldRef Name='Created'><Value Type='DateTime'>" + sLastWeek + "</Value></Gt></Where><OrderBy><FieldRef Name='Title' Ascending='FALSE'></OrderBy>";
  This is a combination of the above...items created in the last week ordered Z-A.
  Finally, make sure you set the RowLimit property...you may not get any results otherwise!!