Hour 7, “Understanding SharePoint 2010 Server Side Development,” looked at how to query and manipulate data in lists using the object model—the SPList
and SPListItem
class. While this is good for a simple scenario, this is not the best way to query and edit data in lists. This section shows other better and recommended ways to do the same.
CAML (Collaborative Application Markup Language) is an XML-based query language used in conjunction with the SPQuery
and SPSiteDataQuery
classes to perform data operations on SharePoint lists. While the SPQuery
class is used to retrieve data from a specific list, the SPSiteDataQuery
class is used to retrieve data from multiple lists across different sites in a particular site collection. To query data from a list using the SPQuery
class, you need to specify the properties of an SPQuery
object as shown in Table 11.3.
Consider a scenario where you have the following two lists in your SharePoint site:
• Departments—Having fields like Department ID, Department Name, and Department Location
• Employees—Having fields like Employee Number, First Name, Last Name, Designation, and Department ID (a lookup column to the Departments list), Department Name (linked to Department Name in the Departments list via the Department ID lookup column)
Department related information for an employee is present in the Departments list, which is linked to the Employees list via the Department ID column. In SharePoint, columns (such as Department ID) defining relationships between lists are of type lookup, and SharePoint provides you the option to select an existing column from the foreign list while defining a column of this type. For example, Figure 11.8 shows how the Department ID column should be defined in the Employees list.
Now let’s use the SPQuery
class and retrieve all employees from the Employees list having the designation of Manager. The following code helps achieve this:
// Create SPQuery object
SPQuery allManagersQuery = new SPQuery
{
Query =
"<Where>
<Eq>
<FieldRef Name='Designation' />
<Value Type='Text'>Manager</Value>
</Eq>
</Where>",
ViewFields =
"<FieldRef Name='Last_x0020_Name' />
<FieldRef Name='LinkTitle' />
<FieldRef Name='Department_x0020_ID_x003a_Depart' />",
};
// Call GetItems method on Employees list passing SPQuery instance
SPListItemCollection results =
SPContext.Current.Web.Lists
["Employees"].GetItems(allManagersQuery);
if (results != null && results.Count != 0)
{
// Enumerate through the result set and
// display Last Name, First Name and
// Department fields
foreach (SPListItem listItem in results)
{
writer.Write("{0} {1} {2}<br/>",
listItem["Last_x0020_Name"],
listItem["LinkTitle"],
listItem["Department_x0020_ID_x003a_Depart"]);
}
}
The easiest way to build a CAML query for the preceding scenario is to create a view (on the Employees list) to get all managers with the filter criteria configured as shown in Figure 11.9.
Once the view is ready, use Visual Studio’s Server Explorer to connect to the SharePoint site and examine the view’s property. Copy the contents of the SchemaXml
property to Notepad and extract the value of the Query
and ViewFields
elements, as shown in Figure 11.10. The value of these elements is used directly in the preceding code while creating a new instance of the SPQuery
class.
If you examine the contents of the AllManagersWebPart, you will notice that the lookup fields containing an employee’s department name, displays a number followed by semicolon and hash characters and finally the department name, as shown in Figure 11.11.
This is because the lookup fields store the Lookup ID along with the actual Lookup value, separated by “;#” characters. The SPFieldLookupValue
class’s LookupValue
property helps to obtain the actual lookup value. The following code demonstrates how to use this class to get only the department name from the lookup:
SPFieldLookupValue DepartmentLookup =
new SPFieldLookupValue(
listItem["Department_x0020_ID_x003a_Depart"].ToString());
string departmentName = DepartmentLookup.LookupValue;
Next, try to get all employees located in the north wing. Since the Department Location column is not a linked lookup column (via the Department ID field), you need to include a join predicate here to join the Employees and Department lists and apply the filter on the Department Location column to get the employees located in the North Wing. Make the following modifications to your SPQuery
object:
SPQuery northWingEmployeesQuery = new SPQuery
{
Query = @"<Where>
<Eq>
<FieldRef Name='EmployeeDepartmentLocation' />
<Value Type='Text'>North Wing</Value>
</Eq>
</Where>",
ViewFields = @"<FieldRef Name='Last_x0020_Name' />
<FieldRef Name='LinkTitle' />
<FieldRef Name='EmployeeDepartmentLocation' />",
Joins = @"<Join Type='INNER' ListAlias='EmployeeDepartmentList'>
<Eq>
<FieldRef Name='Department_x0020_ID' RefType='Id' />
<FieldRef List='EmployeeDepartmentList' Name='ID' />
</Eq>
</Join>",
ProjectedFields = "<Field Name=' EmployeeDepartmentLocation' Type='Lookup' List='EmployeeDepartmentList' ShowField='Department_x0020_Location' />"
};
First, understand the Joins
property in the preceding code. We specify here that we want an Inner Join with the Department ID (internal name Department_x0020_ID
) being the join field, and we assign the EmployeeDepartmentList
alias to the join result. The ProjectedFields
property helps to specify (and assign an alias as well) the columns to retrieve from the foreign list. We retrieve DepartmentLocation
in this case and call it EmployeeDepartmentLocation
. Once the EmployeeDepartmentLocation
field is defined as a projected field, it can be used in the Query
to filter the list items having the department name of North Wing. Further, because you want to display the department’s location in the result, this field is included in the ViewFields
property as well.
The rest of the procedure of obtaining the results remains the same, as in the case of the all managers query. Refer to the NorthWingEmployeesWebPart in the DataAccessDemo project in the sample code for this hour for a complete listing of the code.
Now look at the usage of the SPSiteDataQuery
class, which is used to retrieve data from multiple lists across different sites in a particular site collection. Retrieve the value of title field for all the list items across all the generic lists in the current site collection. The following code represents the SPSiteDataQuery
object for this operation:
// Create SPSiteDataQuery object
SPSiteDataQuery siteDataQuery = new SPSiteDataQuery
{
ViewFields = @"<FieldRef Name='Title'/>",
Lists = @"<Lists BaseType='0' />",
Webs = "<Webs Scope='SiteCollection' />",
RowLimit = 10
};
// Call GetSiteData method on the current web
DataTable results = SPContext.Current.Web.GetSiteData(siteDataQuery);
In the Lists
property, a value of zero for the BaseType
attribute indicates that you are looking for all the Generic lists. Other values that you can specify for this property include 1 to look for a document library, 3 to look for a discussion forum, 4 to look for a vote or survey, and 5 to look for an Issues list.
Further, you can also restrict your results to lists of a particular server template by specifying a value for the ServerTemplate
attribute.
Using the Webs
property, you can restrict the scope of your query in the following manner:
• <Webs Scope='SiteCollection' />
searches the entire site collection.
• <Webs Scope='Recursive' />
searches the current web and its child webs recursively.
SPSiteDataQuery
does not support Joins and projected fields. Refer to the AllListTitlesWebPart in the code samples for this hour for complete source code of this example.
CAML takes some amount of time to gain expertise in. Debugging CAML is not easy, and often you get cryptic error messages that are difficult to interpret. Language-Integrated Query (LINQ) to SharePoint offers an alternative. However, you cannot completely do away with CAML as it is still the most appropriate choice in certain scenarios. Even internally CAML queries are generated corresponding to the LINQ statements, which ultimately perform the specified data operations.
LINQ provides a SQL-like syntax that can be used to query data. It operates against strongly typed entities that represent the items in your lists. These entities can be generated with the help of a command-line tool, shipped with SharePoint, called SPMetal.