Friday, November 28, 2008

CAML Tips n Tricks

CAML --- Collaborative Application Markup Language

1) CAML query to fetch a record :

SPWeb oWebsite = SPContext.Current.Web;
SPList oList = oWebsite.Lists["Announcements"];

string strQuery = "<Where>" +
"<Eq>" +
"<FieldRef Name='ID'/>" +
"<Value Type='Number'>1</Value>" +
"</Eq>" +

SPQuery oQuery = new SPQuery();
oQuery.Query = strQuery;
SPListItemCollection ItemCol = oList.GetItems(oQuery);
if (ItemCol.Count > 0)
string strTitle = ItemCol[0]["Title"].ToString();

2) CAML query to fetch highest/lowest record :

//To get highest value set Ascending to False else to
//get lowest value don't use it (by-default Ascending is set to True).
string strQuery = "<OrderBy>" +
"<FieldRef Name='ID' Ascending='False' />" +

SPQuery oQuery = new SPQuery();
oQuery.Query = strQuery;
//Set the RowLimit, so that it fetch only 1 record which is the required one.
oQuery.RowLimit = 1;

3) CAML query to fetch only the required columns instead of all columns (by-default) :

//Specify only the required columns name in ViewFields.
string strViewFields = "<FieldRef Name='ID'/>" +
"<FieldRef Name='Title'/>" +
"<FieldRef Name='Name'/>";

SPQuery oQuery = new SPQuery();
oQuery.ViewFields = strViewFields;

4) CAML query to update SPListItem(s) in batch/bulk :

//Current WebSite.
SPWeb oWebsite = SPContext.Current.Web;
SPQuery oQuery = new SPQuery();

// Set up the variables to be used.
StringBuilder methodBuilder = new StringBuilder();
string batch = string.Empty;

string batchFormat = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
"<ows:Batch OnError=\"Return\">{0}</ows:Batch>";

SPList oList = oWebsite.Lists["Announcements"];
//Get SPList's ID
string listGuid = oList.ID.ToString();

//Here we are going to change 'Title' Column/SPField of SPListItem.
string methodFormat = "<Method ID=\"{0}\">" +
"<SetList>" + listGuid + "</SetList>" +
"<SetVar Name=\"Cmd\">Save</SetVar>" +
"<SetVar Name=\"ID\">{1}</SetVar>" +
"<SetVar Name=\"urn:schemas-microsoft-com:office:office#Title\">{2}</SetVar>" +

//Set the RowLimit, to optimize the performance.
query.RowLimit = 100;
SPListItemCollection unprocessedItems = oList.GetItems(query);
//Process all the returned items in this page
// Build the CAML update commands.
for (int i = 0; i < unprocessedItems.Count; i++)
//SPListItem ID
int itemID = unprocessedItems[i].ID;
//Set the new value, based on the requirement/logic.
//Here you can use an Array or a Constant string.
string newValue = "Sample";
//string newValue = myArray[i];

methodBuilder.AppendFormat(methodFormat, itemID, itemID, newValue);

// Build the final string.
batch = string.Format(batchFormat, methodBuilder.ToString());

// Process the batch of commands.
string batchReturn = oWebsite.ProcessBatchData(batch);

//Gets an object used to obtain the next set of rows in a paged view of a list.
query.ListItemCollectionPosition = unprocessedItems.ListItemCollectionPosition;
} while (query.ListItemCollectionPosition != null);

Reference :
Batch Updating List Items in Windows SharePoint Services 3.0