Beyond web logs

To get back specific results within a list, you can use the SPQuery object. When you use an SPQuery object, you will create CAML statements to select specific data within the target list. To select announcements that have expired, you may want to use a query built with CAML statements, as shown in the following example:

SPQuery query = new SPQuery();

query.ViewFields = @"<FieldRef Name='Title'/><FieldRef Name='Expires'/>";

query.Query =

@"<Where>

   <Lt>

     <FieldRef Name='Expires' />

     <Value Type='DateTime'>

     <Today /></Value>

   </Lt>

</Where>";

SPList list = site.Lists["Litware News"];

SPListItemCollection items = list.GetItems(query);

foreach (SPListItem expiredItem in items) {

  Console.WriteLine(expiredItem["Title"]);

}

You must specify the fields you want returned in the query by using the ViewFields property. Also note that you must specify the fields in terms of the field Name, and not DisplayName. If you attempt to access fields without specifying them in ViewFields, you will experience an exception of type ArgumentException.

The basic syntax for the query is “<Where><operator><operand /><operand /></operator> </Where>”.  Following table lists the basic CAML you will use with queries; for a more complete listing see the SDK.

SPQuery is a great way to get back items from a single list. Furthermore, using SPQuery can be significantly faster than enumerating through all the items within a particular list when you are looking only for items that match certain criteria. However, WSS 3.0 introduces a new query mechanism via the SPSiteDataQuery class. A query run with the SPSiteDataQuery class can return items from many different lists through an entire site collection. For this reason, queries run with the SPSiteDataQuery class are sometimes referred to as cross-site queries.

As you saw in the last example, queries run against an SPQuery object return an SPListItemCollection. Queries run with an SPSiteDataQuery object are different, because they return an ADO.NET DataTable object. Just as with SPQuery, columns that are returned in the DataTable are specified as fields. For example, imagine a scenario in which you want to run a single query against every list in the current site collection that has been created from the Announcements list type and return all list items that were created today. The following code sample demonstrates how to do this by creating an SPSiteDataQuery object, initializing it with the necessary CAML statements, and then passing it to the current SPWeb object’s GetSiteData method.

SPSiteDataQuery query = new SPSiteDataQuery();

query.Lists = @"<Lists ServerTemplate='104' />";

query.ViewFields = @"<FieldRef Name='Title'/><FieldRef Name='Created'/>";

query.Webs = "<Webs Scope='SiteCollection' />";

string queryText =

@"<Where>

   <Eq>

     <FieldRef Name='Created' />

     <Value Type=""DateTime"">

       <Today />

     </Value>

   </Eq>

 </Where>";

query.Query = queryText;

DataTable table = site.GetSiteData(query);

foreach (DataRow row in table.Rows) {

  Console.WriteLine(row["Title"].ToString());

}

This example assigns a CAML statement to the Lists property that specifies the ServerTemplate of 104, which is the list type identifier for the Announcements list. Even though GetSiteData is a method of the SPWeb reference, the query is performed against all sites in the current site collection. The scope of the query is controlled through a CAML statement in the SPSiteDataQuery’s Webs property, which assigns a value of “SiteCollection” to the Scope attribute. You can limit the query to a scope of “Site” to just query the current site or to a scope of “Recursive” to query the current site and all the child sites beneath it.

If you need to get a reference to the actual list item, you can get it by using the columns WebId, ListId, and ID.

SPWeb parentWeb = web.Site.OpenWeb(new Guid(row["WebId"].ToString()));

SPList list = parentWeb.Lists[ new Guid(row["ListId"].ToString()) ];

SPListItem item = list.GetItemById((int.Parse(row["ID"].ToString())));