Friday, November 16, 2012

Retrieving List Items with CAML Query - Examples

 

If you need to retrieve items from a list when developing web parts, application pages or custom field types you can best use the SPQueryobject from the SharePoint object model. This object is located in the Microsoft.SharePoint namespace of the Microsoft.SharePoint.dll located in the Global Assembly Cache.

Instantiate the object as follows:
SPQuery query = new SPQuery();
 
The most important property is the Query property, which needs to be set to your CAML query:

string camlquery = "<OrderBy><FieldRef Name='Country' /></OrderBy><Where>"
    + "<Eq><FieldRef Name='LastName' /><Value Type='Text'>Smith</Value></Eq>"
    + </Where>";
query.Query = camlquery;
 
At this point you can execute the query on your list:
SPListItemCollection listItemsCollection = list.GetItems(qry); 

Get data in datatable

A small remark with the GetItems method of the SPList instance: this method returns a collection of type SPListItemCollection. It is possible that it is easier working with a DataTable. In that case you can execute the query as follows:


DataTable listItemsTable = list.GetItems(qry).GetDataTable(); 

View fields property

The query will not only return all list items that have their last name set to Smith, but also all columns of each list item. In cases you work with large lists it can be important to retrieve a subset of list items containing only the columns you need. In that case you will have to set the ViewFields property of the SPQuery object. 

You can do this by specifying all columns you want to see returned:


qry.ViewFields = "<FieldRef Name='FirstName' /><FieldRef Name='LastName' />";
query.ViewFieldsOnly = true;
 
This will return the first name and the last name of the retrieved employees, but also the system fields like the ID and the Created date.

The major disadvantage of the SPQuery object is that you can query only one list. If you want to query more than one list you will have to use the SPSiteDataQuery. More on this object in a later article because it earns special attention.
It is common knowledge by now but let me remind you that it’s always a good idea to use SPQuery to retrieve a subset of list items. You can loop through the list item collection to find the list items that match your needs but this will have a serious negative impact on the performance of your work.

IncludeMandatoryColumns

When this Boolean property is set to True, the result set will not only return the columns as defined in the ViewFields property, but also the columns that you defined in the list as required.
When working with the SPQuery object:
qry.IncludeMandatoryColumns = true;
When working with the GetListItems method of the Lists.asmx web service:

XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");
queryOptionsNode.InnerXml =
    "<IncludeMandatoryColumns>True</IncludeMandatoryColumns>";

RowLimit

Setting this property limits the number of rows returned in the result set.
When working with the SPQuery object:
qry.RowLimit = 10;
 

Example: 



 using (SPSite site = new SPSite("http://localhost"))
         {
            using (SPWeb web = site.OpenWeb())
            {
SPList List = oWebsiteRoot.Lists["Tasks"];
               // Build a query. 
 SPQuery query = new SPQuery();
 
Query.Query = "<Where><Eq><FieldRef Name='Status'/>" +
    "<Value Type='Text'>Completed</Value></Eq></Where>";

 query.ViewFields = string.Concat(
                                   "<FieldRef Name='AssignedTo' />",
                                   "<FieldRef Name='LinkTitle' />",
                                   "<FieldRef Name='DueDate' />",
                                   "<FieldRef Name='Priority' />"); 
 
query.ViewFieldsOnly = true;
query.RowLimit = 10; 
 
SPListItemCollection collListItems = List.GetItems(query);
DataTable listItemsTable = List.GetItems(query).GetDataTable(); 
}
} 

Another Example - Using Sharepoint Client Object Model: 

Four properties of ListItem are not available by default when you return list items: 
DisplayName, EffectiveBasePermissions, HasUniqueRoleAssignments, and RoleAssignments
Use Include method to get them. 
Only the specified properties are available after query execution.
Therefore, you receive a PropertyOrFieldNotInitializedException
if you attempt to access other properties beyond those that have been specified. 
In addition, you receive this error if you attempt to use properties such as 
ContentType or ParentList to access the properties of containing objects.

using System;
using Microsoft.SharePoint.Client;
using SP = Microsoft.SharePoint.Client;

namespace Microsoft.SDK.SharePointServices.Samples
{
    class RetrieveListItems
    {
        static void Main()
        {
            string siteUrl = "http://MyServer/sites/MySiteCollection";

            ClientContext clientContext = new ClientContext(siteUrl);
            SP.List oList = clientContext.Web.Lists.GetByTitle("Announcements");

            CamlQuery camlQuery = new CamlQuery();
            camlQuery.ViewXml = "<View><Query><Where><Geq><FieldRef Name='ID'/>" +
                "<Value Type='Number'>10</Value></Geq></Where></Query><RowLimit>100</RowLimit></View>";
            ListItemCollection collListItem = oList.GetItems(camlQuery);

clientContext.Load(collListItem);
OR
clientContext.Load(collListItem,
                 items => items.Include(
                    item => item.Id,
                    item => item.DisplayName,
                    item => item.HasUniqueRoleAssignments));
 
 
 clientContext.ExecuteQuery();

            foreach (ListItem oListItem in collListItem)
            {
                Console.WriteLine("ID: {0} \nTitle: {1} \nBody: {2}", oListItem.Id, oListItem["Title"], oListItem["Body"]);
            }
        }
    }
}
 

Retrieving specific fields from a specified number of items

 

The following example shows how to retrieve specific fields from only the first five items in a list. Because only the Title and Body columns are specified, these are the only properties that are available.


using System;
using System.Linq;
using Microsoft.SharePoint.Client;
using SP = Microsoft.SharePoint.Client;

namespace Microsoft.SDK.SharePointServices.Samples
{
    class RetrieveSpecificItemsFields
    {
        static void Main()
        {
            string siteUrl = "http://MyServer/sites/MySiteCollection";

            ClientContext clientContext = new ClientContext(siteUrl);
            SP.List oList = clientContext.Web.Lists.GetByTitle("Announcements");

            CamlQuery camlQuery = new CamlQuery();
            ListItemCollection collListItem = oList.GetItems(camlQuery);

            clientContext.Load(
                collListItem,
                items => items.Take(5).Include(
                item => item["Title"],
                item => item["Body"]));

            clientContext.ExecuteQuery();

            foreach (ListItem oListItem in collListItem)
            {
                Console.WriteLine("Title: {0} \nBody: {1}\n", oListItem["Title"], oListItem["Body"]);
            }
        }
    }
}

Retrieving items from all the lists in a Web site 

 

using System;
using System.Linq;
using Microsoft.SharePoint.Client;
using SP = Microsoft.SharePoint.Client;

namespace Microsoft.SDK.SharePointServices.Samples
{
    class RetrieveFirstTenItemsAllLists
    {
        static void Main()
        {
            string siteUrl = "http://MyServer/sites/MySiteCollection";

            ClientContext clientContext = new ClientContext(siteUrl);
            ListCollection collList = clientContext.Web.Lists;

            clientContext.Load(
                collList,
                lists => lists.Where(
                    list => list.Hidden == false).Include(
                    list => list.Title,
                    list => list.Items.Take(10)));

            clientContext.ExecuteQuery();

            foreach (SP.List oList in clientContext.Web.Lists)
            {
                string listTitle = oList.Title;
                int itemCount = oList.Items.Count;

                Console.WriteLine("List {0} returned with {1} items", listTitle, itemCount);
            }
        }
    }
}

7 comments:

  1. very nice. it's very usefull for all sharepoint developers... thanks.

    ReplyDelete
  2. hi Dinoop
    i have 4 fields (i.e 2 text boxes and 2 dropdowns) . i want fiter the grid based on selection .is ther any example for u ..

    ReplyDelete
  3. what to use in place of GetDataTable() in client side object model inorder to get all items of list and binding them in gridview.

    ReplyDelete
  4. Great stuff. Is it possible to retreive computed columns? I'm specifically thinking of: EncodedAbsUrl (Encoded Absolute URL)

    ReplyDelete