Tuesday, May 12, 2009

CAML Tips n Tricks --- Part 2

CAML --- Collaborative Application Markup Language

In continuation with my previous post ---
CAML Tips n Tricks

In this post I am going to discuss about CAML query for batch Update and Delete.

1) BATCH DELETE


    
/// <summary>
/// Get the CAML query for BATCH DELETION
/// </summary>
/// <param name="spList">SPList instance</param>
/// <returns>SPQuery</returns>
StringBuilder buildBatchDeleteCommand(SPList spList)
{
StringBuilder sbDelete = new StringBuilder();
sbDelete.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");

string command = "<Method>" +
"<SetList Scope=\"Request\">" + spList.ID + "</SetList>" +
"<SetVar Name=\"ID\">{0}</SetVar>" +
"<SetVar Name=\"Cmd\">Delete</SetVar>" +
"</Method>";

foreach (SPListItem item in spList.Items)
{
sbDelete.Append(string.Format(command, item.ID.ToString()));
}
sbDelete.Append("</Batch>");
return sbDelete;
}

/// <summary>
/// Multiple records Deletion with SPQuery
/// </summary>
void BatchDelete(Object sender, EventArgs e)
{
//Get the SPQuery
StringBuilder finalDeleteAllQuery = buildBatchDeleteCommand(oList);

//Get the SPSite and Allow unsafe updates
SPSite oWebsite = SPContext.Current.Web;
oWebsite.AllowUnsafeUpdates = true;

//Run the Batch command
oWebsite.ProcessBatchData(finalDeleteAllQuery.ToString());

//Disable unsafe updates
oWebsite.AllowUnsafeUpdates = false;
}




2) BATCH UPDATE


/// <summary>
/// Get the CAML query for BATCH UPDATION
/// Here building SPQuery to change "Final Status" column's value
/// </summary>
/// <param name="spList">SPList instance</param>
/// <returns>SPQuery</returns>
StringBuilder buildBatchUpdateCommand(SPList spList)
{
StringBuilder sbDelete = new StringBuilder();
sbDelete.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");

string command = "<Method>" +
"<SetList Scope=\"Request\">" + spList.ID + "</SetList>" +
"<SetVar Name=\"ID\">{0}</SetVar>" +
"<SetVar Name=\"Cmd\">Save</SetVar>" +
"<SetVar
Name=\"urn:schemas-microsoft-com:office:office
#Final_x0020_Status\">{1}</SetVar>" +
"</Method>";

//SPView specific SPListItems, you can skip following 3 lines
//and directly run foreach on SPList.
//E.g, foreach (SPListItem item in spList.Items)
SPView spView = spList.Views["SPView_NAME"];
SPViewFieldCollection collViewFields = spView.ViewFields;
SPListItemCollection collItemsSrc = spList.GetItems(spView);

foreach (SPListItem item in collItemsSrc)
{
sbDelete.Append(string.Format(command, item.ID.ToString(), "Completed"));
}

sbDelete.Append("</Batch>");
return sbDelete;
}

/// <summary>
/// Multiple records Update with SPQuery
/// </summary>
void BatchUpdate()
{
//Get the SPQuery
StringBuilder finalUpdateAllQuery = buildBatchUpdateCommand(oList);

//Get the SPSite and Allow unsafe updates
SPSite oWebsite = SPContext.Current.Web;
oWebsite.AllowUnsafeUpdates = true;

//Run the Batch command
oWebsite.ProcessBatchData(finalUpdateAllQuery.ToString());

//Disable unsafe updates
oWebsite.AllowUnsafeUpdates = false;
}

4 comments:

Bill said...

That's beautiful. Why didn't I find it 3 days ago when I needed it ;-) The only things I would add is perhaps an explanation of AllowUnsafeUpdates related to performing the operations on a GET, not needed on a POST. And I think a distinct ID attribute should be included on the METHOD.

This would have been a huge time saver if I'd found it on Monday.

Anonymous said...

Hey Avinash,
I was looking for the Delete method to be more specific, by taking it to the next level of adding filters to it.
By any means, do you think we may modify the CAML to include filter like: [Delete ONLY those rows from the List where value of the Column ="REJECTED"].
Or something on these lines.

And yes, Thanks for th egreat post.
NCH

Avinash said...

Hi NCH,

You can check following links for CAML query for Batch operation with conditions:

Batch Delete Approach 1

Batch Delete Approach 2

~ Avi

Anonymous said...

This doesn't work.

"Value does not fall within the expected range"

Not sure which value it refers to (nice message from the WSS OM...)

Google