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>" +
"</Where>";

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' />" +
"</OrderBy>";

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>" +
"</Method>";

//Set the RowLimit, to optimize the performance.
query.RowLimit = 100;
do
{
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

7 comments:

prabhash said...

Excellent Avinash!!!
Thanks for this quick and accurate info!
Prabhash
www.skysoft.net.in

Anonymous said...

Hi Avinash,

Thanks for providing such a useful info. Just one more suggestion required. I require to implement custom paging. In this if user is currently on 1 page and he clicks directly on 10 th Page, then how would i fetch record. I have explored the related propertires of SPList i.e. RowLimit and ListItemCollectionPosition.. but these suffice only when user clicks pages in continuation like 1,2 3, 4.. How should i jump these pages.

Anonymous said...

Hi,

Assume you are displaying records based on ID (as an example).
Now you want to directly fetch records between 100 and 110 (10th paging).

You can write a SPQuery that will use "Greater than or Equal to" (Geq) and "Less than and Equal to" (Leq) operations to fetch in-between records.



string strQuery = "<Where>" +
"<And>" +
"<Geq>" +
"<FieldRef Name='ID' />" +
"<Value Type='Number'>100</Value>" +
"</Geq>" +
"<Leq>" +
"<FieldRef Name='ID' />" +
"<Value Type='Number'>110</Value>" +
"</Leq>" +
"</And>" +
"</Where>";

~ Avinash

Anonymous said...

thanks for your help. But this will not suffice if some of the IDs are deleted from the List. I need to find for eg 21st to 30th records irrespective of their IDs then what should be the approach.

Anita said...

Hi,

You can get the column name and its value by:

string startID = splist.Items[100].ID;
string endID = splist.Items[110].ID

~ Avinash

deepakporwal said...

hi sir,how to code about setvar mathod format and batch format. I am not getting the way..
how do we write setvar script..?
plz revert me..

Sharepoint Guy said...

Great Blog : I have Shared your Blog on my Blog.

http://expert-sharepoint.blogspot.com/2015/06/caml-query-tips-and-caml-query-best.html

If possible please stay in Touch.

Google