A CAML query must be a well-formed XML document that is composed of the following elements:
<Query>
<Where><!--Comparison Operators here-->
<Eq>
<FieldRef Name=”insertFieldNameHere” />
<Value Type=”insertDataTypeHere”>insertValueHere</Value>
</Eq>
</Where>
<OrderBy>
<FieldRef Name=”insertFieldNameHere” />
<FieldRef Name=”insertFieldNameHere” />
</OrderBy>
</Query>
This simple CAML query definition defines a filter where a field equals a specified value using the Eq element. In addition, one or many FieldRef elements can be specified inside
the OrderBy element to sort by one or many columns.
You must supply all of these elements with a FieldRef child element. The FieldRef element specifies the SharePoint-specific name of the column that is being evaluated. In addition, almost all of the query elements (with the exception of IsNotNull and IsNull) require that you also specify a Value child element. This is where you will specify what value to evaluate the specified FieldRef element against.
Unfortunately, SharePoint doesn’t always intuitively name each of the FieldRefs that you need to reference. The following code is an example that you can use to extract the FieldRef name from a list by using a console application and the Microsoft Office SharePoint Server 2007 application programming interface (API):
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint;
namespace SharePointUtils
{class Program
{static void Main(string[] args)
{string siteUrl = args[0];
string listName = args[1];
string viewName = args[2];
SPSite site = new SPSite(siteUrl);
SPWeb web = site.OpenWeb();
SPList employeesList = web.Lists[listName];
SPQuery query = new SPQuery(employeesList.Views[viewName]);
System.Diagnostics.Debug.WriteLine(query.ViewXml);
Console.WriteLine(query.ViewXml);
Console.ReadLine();
}
}
}
The code is very simple and the output is very usable when you start writing CAML queries. Three arguments are required for the site uniform resource locator (URL), list name, and view name, respectively. Objects are created for the site, the web, and the list. Notice that an instance of SPQuery is also created. The SPQuery object is used to get the SharePoint-specific field names (or FieldRefs) from the view.
This code will help you to identify FieldRefs of the specified list. After you have identified the FieldRefs that you want to filter your query by, actually querying a list is quite simple. Taking into account the Employees list, you might want to create a query that displays all employees with a start date before January 1, 2003. The following is an example of a CAML query that will filter records by those with a start date beginning before January 1, 2003:
<Query>
<OrderBy>
<FieldRef Name=”Title” />
</OrderBy><Where>
<Lt>
<FieldRef Name=”Start_x0020_Date” />
<Value Type=”DateTime”>2003-01-01T00:00:00Z</Value>
</Lt></Where>
</Query>
This query does two things. First, it specifies how the data is sorted when values are returned by using the OrderBy element. Notice that the results will be ordered by Title, which is actually the Employee Name field. Next, a Where element is defined that will specify the filter, which is similar in functionality to a SQL WHERE clause. The Where element defines an Lt (less than element), which contains a FieldRef element and a Value element. The FieldRef element is the column in the list and the Value element represents the data type and value that is being compared.
The following is a code excerpt that will execute the CAML query defined previously:
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint;
namespace SharePointUtils
{class CAMLQuery
{static void Main(string[] args)
{string siteName = args[0];
string listName = args[1];
string viewName = args[1];
SPSite site = new SPSite(siteName);
SPWeb web = site.OpenWeb();
SPList employeesList = web.Lists[listName];
SPQuery query = new SPQuery(employeesList.Views[viewName]);
query.Query = “<Query><OrderBy><FieldRef Name=\”Title\”/></OrderBy><Where><Lt><FieldRef name=\”Start_x0020_Date\” /><Value Type=\”DateTime\”>2003-01-01T00:00:00Z</Value></Lt></Where></Query>”;
SPListItemCollection filteredEmployees = employeesList.GetItems(query);
foreach (SPListItem i in filteredEmployees)
{System.Diagnostics.Debug.WriteLine(i[“Title”].ToString() + “ “ + i[“Salary”].ToString() + “ “ + i[“Start_x0020_Date”].ToString());
}
Console.ReadLine();
}
}
}
The code to perform the query is identical to the first code example in the chapter with the exception of setting the Query property of the SPQuery object named query. This is
where you set the value of the CAML query that will be used to filter the data. The results are returned by using the GetItems method of the SPList object and an instance of the
SPListItemCollection class. This collection is then iterated and the values are output to the debug window. The following is an example of the results that are returned by the CAML query:
Brandon Bobb 62000 12/1/2001 12:00:00 AMRob
Foster 110000 1/1/1999 12:00:00 AM
Stephen Baron 79000 1/25/2002 12:00:00 AM
Though the number of records in the list is limited, the results are filtered to three records. Now what if you want to filter the list further? CAML has And and Or elements that can be used in conjunction with the Where element. The following is an example of a CAML query that filters by start dates before January 1, 2003 AND salaries lower than $80,000:
<Query>
<OrderBy>
<FieldRef Name=”Title” />
</OrderBy><Where><And><Lt>
<FieldRef Name=”Start_x0020_Date” />
<Value Type=”DateTime”>2003-01-01T00:00:00Z</Value>
</Lt><Lt><FieldRef Name=”Salary” />
<Value Type=”Currency”>80000</Value></Lt>
</And></Where></Query>
Naturally, this filters the sample list data to the following two records:
Brandon Bobb 62000 12/1/2001 12:00:00 AM
Stephen Baron 79000 1/25/2002 12:00:00 AM
As you can see in this tutorial, CAML queries are very easy to construct and execute.
No comments:
Post a Comment