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:

=FirstName&" "&LastName

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:

using System.Linq;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Client;
using ADPapp.com.employease.GetUserInfo;
using Microsoft.Web.Services3.Security.Tokens;

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.

class GetUserInfo
         //WSE Token credentials
         private static string USERNAME = "[email protected]";
         private static string SHARED_SECRET = "VfmHcgz1QXMG2";
         //Value for filter
         private static string COMPANY_IDENTIFIER = "openapi";
         //ADP Personal Info Object. Note that object is static so that it can be called from Main.
         public static List<EmployeePersonalInfoType> liPersonalInfo = new List<EmployeePersonalInfoType>();

         // **METHOD GetFromADP** 
         /// <summary> Connects to ADP web service and retrieves an array of employee objects…>> ///…(EmployeePersonalInfoType[]) ///filtered by COMPANY_IDENTIFIER. This array is converted to a…>>  ///…stringarray list for use by various ///methods. </summary>
         /// <returns>List<EmployeePersonalInfoType></returns>
         public static List<EmployeePersonalInfoType> GetFromADP()
            //Initialize GetEmployeePersonalInfoService, pass security headers
            GetEmployeePersonalInfoService oService = new GetEmployeePersonalInfoService();

            UsernameToken oUsernameToken = new UsernameToken(USERNAME, SHARED_SECRET);

          System.Xml.XmlElement oSecurityHeaderXml = oUsernameToken.GetXml(new System.Xml.XmlDocument());
            SecurityHeaderType oSecurityHeader = new SecurityHeaderType();
            oSecurityHeader.Any = new System.Xml.XmlElement[] { oSecurityHeaderXml };

            oService.Security = oSecurityHeader;

            GetEmployeePersonalInfoRequestFilter oFilter = new GetEmployeePersonalInfoRequestFilter();
            oFilter.Companies = new string[] { COMPANY_IDENTIFIER };

            GetEmployeePersonalInfoRequest oRequest = new GetEmployeePersonalInfoRequest();
            oRequest.Filter = oFilter;

            //this variable has been declared outside this method to make it accessible to Program.cs
            EmployeePersonalInfoType[] arPersonalInfo = oService.GetEmployeePersonalInfo(oRequest);

            //Convert to C# list
            liPersonalInfo = arPersonalInfo.ToList();

            return liPersonalInfo;


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.

// **METHOD GetADPallUsers** 
         /// <summary>
         /// Get list from ADP web service of all users and and all properties.
         /// </summary>
         /// <param name="liPersonalInfo">List of ADP employee info objects</param>
         /// <param name="logger">NLog utility</param>
         /// <returns>list of employee object arrays (EmployeePersonalInfoType[])</returns>
         public static List<string[]> GetADPallUsers(List<EmployeePersonalInfoType> liPersonalInfo)
             //Declare return list
             List<string[]> ADPallUsersList = new List<string[]>();

             // Loop through each employee in the ADP response payload.
             foreach (EmployeePersonalInfoType item in liPersonalInfo)
                 //Define employee info
                 String stLastName = item.PersonalInfo.PersonName.LastName;
                 String stFirstName = item.PersonalInfo.PersonName.FirstName;
                 GenderType stGender = item.PersonalInfo.Gender;
                 String stBirthDate = item.PersonalInfo.BirthDate.ToShortDateString();
                 String stNationalID = item.EmployeeKey.Identifier.NationalId.Value;

                 //Construct an array (stUserInfoList) with the employee's info
                 string[] stUserInfoList = { stFirstName, stLastName, stBirthDate, Convert.ToString(stGender), stNationalID };

                 //Add employee info array to list of all employee info arrays (ADPallUsersList)
             return ADPallUsersList;

The GetSPallNatID method extracts the National IDs from the SharePoint list and adds them to a list that will be used for the comparison.
// **METHOD GetSPallNatID** 
         /// <summary>
         /// Gets list of National IDs for all items in SharePoint. Returns string list of National IDs.
         /// </summary>
         /// <returns>List<string> SPallNatID</returns>
         public static List<string> GetSPallNatID( )
             // choose your site
             string strUrl = ListOperations.SPsiteURL;

             //Create a sting list that will contain the SharePoint list's employee National IDs.  
             List<string> SPallNatID = new List<string>();

             //Connect to SharePoint list
             using (SPSite site = new SPSite(strUrl))
                 using (SPWeb web = site.OpenWeb())
                     // choose the SharePoint list
                     SPList list = web.Lists["Employees"];

                     //filter results with CamlQuery. Empty "" selects all items.
                     SPQuery myquery = new SPQuery();
                     myquery.Query = "";

                     //Instantiate collection of list items
                     SPListItemCollection SPListItems = list.GetItems(myquery);

                     //Loop through each employee item in the collection to extract National IDs
                     foreach (SPListItem item in SPListItems)
                         // check the item for null value
                         if (item != null)
                             //Add the value of the NationalID column to SPallNatID list
                             int itemID = item.ID;
                             SPListItem employee = list.GetItemByIdSelectedFields(itemID, "NationalID");
             return SPallNatID;
Add the GetADPallUsers and GetSPallNatID methods to GetUserInfo.cs. 
The next step is to write the function that compares the two lists and finds the ADP employees that are missing from SharePoint. The missing employees will be added to a NewUsersList that can be processed by the function that adds them to the SharePoint Employee list.
// **METHOD CompareADPtoSPnatID**
        /// <summary>
        ///  Compare ADP National ID list to SharePoint National ID list. Those from ADP that have no        ///matching NationalID ...>>
        ///  ...in SharePoint are added to the Add User list.
        /// </summary>
        /// <param name="ADPallUsersList"></param>
        /// <param name="SPnatIDlist"></param>
        /// <returns> List<string[]>  NewUsersList;</returns>
        private static List<string[]> CompareADPtoSPnatID(List<string[]> ADPallUsersList, List<string> SPnatIDlist)
            //Loop through the ADP employees and add employees that have no matching item
            foreach (string[] ADPuser in ADPallUsersList)
                string ADPnatID = ADPuser[4];
                if (!SPnatIDlist.Contains(ADPnatID))
            // return the list object for use as an argument in //EmployeeCardOperations.AddItemToList(AddUsersList)
            return NewUsersList;


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

using ADPapp.com.employease.developer;


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:

using Microsoft.SharePoint;
using Microsoft.SharePoint.Client;

//SharePoint site URL for SP Microsoft.SharePoint.Client.List.Update method
        public const string SPsiteURL = "http://aubiera7:80/";

        // **METHOD AddNewItemToSPList** 
        /// <summary>
        /// Add new employee items to SharePoint Employees list.
        /// </summary>
        /// <param name="AddUsersList">
        /// A list of new employee info string arrays that will be added to the SharePoint Employees list…>> 
///…using SPListItem.Update(). This list is created by the Program.CompareADPtoSPnatID method.
       /// </param>
        public static void AddNewItemToSPList(List<string[]> AddUsersList)
            //Connect to SharePoint list
            using (SPSite site = new SPSite(SPsiteURL))
                using (SPWeb web = site.OpenWeb())
                    web.AllowUnsafeUpdates = true;

                    //Create an SPList object from SharePoint "Employees" list
                    SPList list = web.Lists["Employees"];

                    //filter results with CamlQuery. Empty "" selects all items.
                    SPQuery myquery = new SPQuery();
                    myquery.Query = "";

                    //Instantiate collection of list items
                    SPListItemCollection SPListItems = list.GetItems(myquery);

                    foreach (string[] newADPUser in AddUsersList)
                        //Create list item
                        SPListItem newSPListItem = list.Items.Add();

                        //Define item fields to be added
                        newSPListItem["FirstName"] = newADPUser[0];
                        newSPListItem["LastName"] = newADPUser[1];
                        newSPListItem["BirthDate"] = Convert.ToDateTime(newADPUser[2]);
                        newSPListItem["Gender"] = newADPUser[3];
                        newSPListItem["NationalID"] = newADPUser[4];

                        //Update the new list item with the field values above 

                    web.AllowUnsafeUpdates = false;


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.

using ADPapp.com.employease.developer;

class Program
        //Declare lists to be used by ListOperations.cs to process employee information
        static List<string[]> NewUsersList = new List<string[]>();

static void Main(string[] args)
                //Retrieve list of employee objects from ADP
                List<EmployeePersonalInfoType> liPersonalInfo = GetUserInfo.GetFromADP();

                //Create string array list of all ADP employee objects
                List<string[]> ADPallUsersList = GetUserInfo.GetADPallUsers(liPersonalInfo);

                //Create string list of all SharePoint National IDs
                List<string> SPnatidList = new List<string>();
                SPnatidList = GetUserInfo.GetSPallNatID();

                //Create list of new employees to add by calling comparison function of National ...>> 
//…IDs (CompareADPtoSPnatID) to determine new employees to be added to SharePoint ...>> //…Employees list (ADPallUsersList; see CompareADPtoSPnatID method below).  
                //Execute add new employee operation
                CompareADPtoSPnatID(ADPallUsersList, SPnatidList);



January 21, 2013


Email [email protected] with any questions you have pertaining to this course.

New CPE Accredited Courses Now Available for Dynamics AX, GP, and NAVEARN CREDITS TODAY