Recently I had a requirement to get data from a project management product and push it into a sharepoint list on a set interval.
The steps involved were:
1. Delete all items from current sharepoint list
- Get data from project management software
- Do some mapping and push into sharepoint
SSIS was going to be the vehicle to run the code I just had to figure out how to talk to sharepoint.
This was the first time I had attempted to do this so I relied mostly on Google to show me the way but it wasnt so straight forward. There are a few niggles to be aware of.
My approach was:
- Get All current sharepoint list items (for the IDs) - GetListItems
- Delete each one from sharepoint (Using the IDs) - UpdateListItems
- Get new data rows
- Push each one to sharepoint - UpdateListItems
Before we get started there's a few things we need to know:
- Url to sharepoint lists web service
eg. http://someLocationSomewhere/SCP2010/_vti_bin/lists.asmx - add this as a web reference to your project. - List GUID - From here Go to the list settings page. Right click on "Title, description and navigation" and copy the URL. Paste that into notepad and copy everything after "List="in the string. That's your URL Encoded GUID for the list
- View GUID- You can google this yourself. You can get it either by url hack or web service calls
Ok, first thing, how to get list items?
Lists list = new Lists()
{
Url = ServiceUrl,
Credentials = System.Net.CredentialCache.DefaultCredentials
};
XmlNode resultsNode = list.GetListItems(ListGUID, ViewGUID, null, null, null, null, null);
ServiceUrl, ListGUID and ViewGUID are class properties.
The above code will give you an XmlNode containing your list items. View the InnerXml in VS. You can iterate them using standard Xml node iteration techniques.
foreach (XmlNode outerNode in resultsNode.ChildNodes)
{
if (outerNode.NodeType.Equals(System.Xml.XmlNodeType.Element))
{
foreach (XmlNode node in outerNode.ChildNodes)
{
if (node.NodeType.Equals(System.Xml.XmlNodeType.Element))
{
XmlNode idNode = node.Attributes.GetNamedItem(IDFieldName);
try
{
DeleteListItem(idNode.InnerText);
Dts.Log("Deleted ID: " + idNode.InnerText, 997, null);
}
catch (Exception ex)
{
Dts.Log("An error occurred during DeleteListItem: " + ex.Message, 998, null);
if (!ContinueOnError)
throw ex;
}
}
}
}
}
The important bit in this code is IDFieldName. This attribute has the ID of the item we want to delete.
public string IDFieldName
{
get
{
return "ows_ID";
}
}
How to Delete a list item?
Now we have the ID of the list item, we can delete it.
I created a method that takes the ID and does the delete using UpdateListItems()
private void DeleteListItem(string itemId)
{
XmlDocument xmlDoc = new System.Xml.XmlDocument();
XmlElement elBatch = xmlDoc.CreateElement("Batch");
elBatch.SetAttribute("ViewName", ViewGUID);
elBatch.InnerXml = string.Format("
Lists list = new Lists()
{
Credentials = System.Net.CredentialCache.DefaultCredentials,
Url = ServiceUrl
};
XmlNode ndReturn = list.UpdateListItems(ListGUID, elBatch);
if (!ndReturn.InnerText.Equals(SuccessResult))
throw new ApplicationException(ndReturn.InnerText);
}
I used the returned XmlNode.InnerText to test for Success. The sharepoint web service returns
"0x00000000" when it succeeds.
public string SuccessResult
{
get
{
return "0x00000000";
}
}
Now the final step, How to Add List Items?
I created a method called AddListItem(). Pretty much the same code as DeleteListItems() except we build the xml stub with our new data and pass it to AddListItem() to be submitted to sharepoint via UpdateListItems().
private void AddListItem(string spAddItemXML)
{
XmlDocument xmlDoc = new System.Xml.XmlDocument();
XmlElement elBatch = xmlDoc.CreateElement("Batch");
elBatch.SetAttribute("OnError", "Continue");
elBatch.SetAttribute("ViewName", ViewGUID);
elBatch.InnerXml = spAddItemXML;
Lists list = new Lists()
{
Url = ServiceUrl,
Credentials = System.Net.CredentialCache.DefaultCredentials
};
XmlNode ndReturn = list.UpdateListItems(ListGUID, elBatch);
if (!ndReturn.InnerText.Equals(SuccessResult))
throw new ApplicationException(ndReturn.InnerText);
}
The xml stub you pass in to this method will depend on the structure of your list but the basic xml structure is:
Container: <Method ID="MyId" Cmd="New">
Row: <Field Name="MyFieldName">myFieldValueField>
Thats pretty much it, remember to always specify the Url even though the web service reference already knows it.
