Wednesday, December 31, 2008

Lookup column - get data from parent list

In my previous post I shared a solution to replace in the CAML schema of a lookup field the GUID of the list from which the field get his data.
Now the situation is that we want to get data from the parent list. Of cource that we can use the previous way but for this case there is a more easy way.
The only thing we have to do is to specify the value of the List attribute to Self.
In this case we have a list with categories and each category can have subcategories from the same list.

 
<Field        
ID="{D2C297A5-1D6C-480d-9203-F54169A3FB18}"
Name="Category"
DisplayName="Category"
StaticName="Category"
SourceID="http://schemas.microsoft.com/sharepoint"
Group="MyGroup"
Type="Lookup"
ShowField="Title"
List="Self"
/>

 

Lookup column - replace GUID of referenced list

When we create a lookup field using CAML we need to specify the name or GUID of the list from which this field gets data. Is possible that this list to be created using a feature so we will not know his GUID.
 
<Field        
ID="{DFFDD227-CC2C-4a6f-930C-44BD65CDA80F}"
Name="Location"
DisplayName="Location"
StaticName="Location"
SourceID="http://schemas.microsoft.com/sharepoint"
Group="MyGroup"
Type="Lookup"
ShowField="Title"
List="Locations"
/>





I try to specify the name of the list in field CAML definition but I do not know why after creation the field point to a strange object with a different GUID that I expected to.


The solution I found is to have a feature who can modify this GUID after the required list is created. I created a function that get the SchemaXml of the field and in this xml replace the GUID of the list. The required list and the field are send as parameters. After we have the proper SchemaXml we delete the old field and create a new one using this new schema. In the feature activation we can call this function.


Here is the code:


 



private void SetTheProperGUID(SPList catList, SPField catField)    
{
string fieldXml = catField.SchemaXml;
Regex regex = new Regex(@"List=\""(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}\""");
if (regex.IsMatch(fieldXml))
{
fieldXml = regex.Replace(fieldXml, "
List=\"" + catList.ID.ToString("B").ToLower() + "\"");
}
catField.Delete();
catList.Fields.AddFieldAsXml(fieldXml);
catList.Update();
}

Thursday, December 18, 2008

Delete items from large Lists

Situation:

We have a large list (more than 100.000 items). The request is to delete all the items.

Solution:

We can try to iterate through all items and delete. Of course this approach will generate performance problems. The best solution I found is to use a batch:

private void ClearList(SPList taskList)        
{
StringBuilder sbDelete = BuildBatchDeleteCommand(taskList);
taskList.ParentWeb.ProcessBatchData(sbDelete.ToString());
}

/// <summary>
/// Builds a batch string with a list of all the items that are to be deleted.
/// </summary>
/// <param name="spList"></param>
/// <returns></returns>
private static 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;
}



 
More details on how to use batch : http://msdn.microsoft.com/en-us/library/cc404818.aspx