Updating a SharePoint list with external web services data (Part 4)

In our last post, we connected to the ADP web service, retrieved a dataset and displayed the results in a console window. This data will be used to update a SharePoint list in the following manner:

  • Employees that exist in ADP but not in SharePoint will be added to SharePoint
  • Employees that exist in SharePoint but not in ADP will be deleted from SharePoint
  • Employees in SharePoint whose properties (such as “First Name”) do not match the properties of the same employee in ADP will have their properties updated.

Six functions must be written to perform the above tasks, one each for adding, deleting and updating the SharePoint list, and a function for each of the three comparisons, ADP employees to SharePoint employees, SharePoint employees to ADP employees, and ADP employee properties to SharePoint employee properties.

Create a SharePoint list

Before we begin, we must first build the SharePoint list that will contain the ADP data. In your SharePoint site, create an “Employees” list and add the following columns:

[table style=”1″]

[tr][th]Column Name[/th] [th]Type[/th][/tr]

[tr][td]FirstName[/td] [td]Single line of text[/td][/tr]

[tr][td]LastName[/td] [td]Single line of text[/td][/tr]

[tr][td]BirthDate[/td] [td]Date and Time[/td][/tr]

[tr][td]Gender[/td] [td]Choice[/td][/tr]

[tr][td]NationalID[/td] [td]Single line of text[/td][/tr]

[tr][td]FullName[/td] [td]Calculated (calculation based on other columns)[/td][/tr]

[tr][td]Active Status[/td] [td]Yes/No[/td][/tr]


Manually add two or three dummy employees to the list. These will be used later for the delete operation. For National ID, you can use any 9-digit number in a Social Security format except for the range of numbers from 000-00-0000 to 000-00-0028 which have already been assigned to ADP demo employees. The National ID property will be used as an index key when comparing lists. For the FullName column, construct a formula that concatenates the values from the First Name and Last Name columns:

Set up the project

Start Visual Studio, create a new .NET 3.5 console application named ADPapp and add the references to the WSE 3.0 package and web service as described in Part 2. Also add references to the Microsoft SharePoint and Microsoft SharePoint Client assemblies. Create two new clasess: GetUserInfo class will define the functions used to retrieve information from the ADP web service and to build the various lists that will be used by the comparison functions; the ListOperations class for the functions that perform the add, delete, and update actions on the SharePoint list. The comparison functions will be created and added later to the default Program.cs file.

In GetUserInfo, add the following references at the top of the file:

Retrieve data from ADP

The first method will be for retrieving the ADP employee dataset. It is identical to the code written for the same purpose in Part 3, except that it converts the EmployeePersonalInfoType[] arPersonalInfo object to a C# list and uses it as a return for the method.


Find new employees to add to SharePoint

Now that we have retrieved our employee dataset from ADP, we can use it to determine which new employees need to be added to SharePoint. This can be easily performed by comparing the National IDs of employees in both lists. Because we will eventually be adding employee objects from ADP to SharePoint, we will first build a list of these objects using the GetADPallUsers method.


Place this function in Program.cs below the Main method. Make sure to also add a reference to the web service:


Add new users to SharePoint

Now that we have the list of users that will be added to the SharePoint list, we can add the employees as SharePoint list items through the SharePoint Client Model. Add the following method and references to ListOperations.cs:


The last step is to call all the methods that retrieve the ADP employee information and create the list of new users that will be passed to AddNewItemToSPList. A final call to the method adds the employee items to the SharePoint “Employees” list. Make sure to add the reference to the ADP web service at the top of Program.cs.



January 21, 2013