BizTalk 2004 -> Integrating with Sharepoint 2003 and when NOT to use the BizTalk WSS Adapter

The Sharepoint Send/Receive Adapter released last March of 2004 on GotDotNet can be downloaded HERE.
It provides functionality to add (Send) items from BizTalk into Sharepoint Libraries and to pull (Receive) items that have been added into a Sharepoint Library and to submit them into the BizTalk environment. The Sharepoint Adapter is a great example of how a custom BizTalk 2004 Adapter can be built to allow BizTalk to communicate with any type of application.

Last year in 2004, I was part of a team working on a Sharepoint 2003 Portal/BizTalk 2004 project. BizTalk 2004 was used to integrate an HR (Human Resources) system with Sharepoint WSS libraries and Sharepoint Portal User Profiles. In fact, BizTalk was used to aid in the replication of the data from the HR system (in real time) to the Sharepoint Portal Server (i.e. any changes to the HR Employee Profiles (updates/inserts/deletes) were to be replicated in a timely manner to the Sharepoint Portal Server(s) WSS Lists and/or User Profiles).

More Specifically:

1) Employee profiles from the HR system were inserted, updated and deleted in a Sharepoint Library Lists.
2) Employee Resumes (doc,pdf,rtf formats) from the HR system were inserted,updated and deleted in Sharepoint Document Library Lists.
3) Employee Photos (tiff,jpg formats) from the HR System were inserted updated and deleted in Sharepoint Picture Library Lists. 
4) Employee profiles from the HR system were used to create, update and delete Sharepoint User Profiles and to also generate MySites for each User Profile.
Note: User Profiles and MySites is a feature of Sharepoint Portal Server 2003 and not Windows Sharepoint Services (WSS). User Profiles can be used to store information about each portal user. Additionally each portal user can have their own MySite, where they can add/modify and delete their own specific content and have control of their own Web Site within the portal.


Some of the specific requirements/functionality/infrastructure of the integration project:

1) There were two Sharepoint 2003 Portal Servers:
a) English Portal Server (The English Portal Server was a load balanced web farm implemented with two physical servers hosting the English Portal.)
b) French Portal Server

2)
a) The English portal hosted the English User Profiles and English MySites.
On the English Portal there were two main WSS sub sites -> an English Sub Site and a French Sub Site.
Note: there was some discussion on moving the French Sub Site to the Physical French Portal server, but at the time of development the English and French Sub Sites were on the English Portal. Therefore the Employee profile, Resume and Picture libraries were duplicated on the English and French Sub sites of the English portal.
Note: We were trying to have one main list with both the English and French columns at the portal level and then create views of the this main list down on the English and French Sub Sites. This did not work. Therefore the duplicate libraries -> one on each sub site was used as below:

English Employee List:

French Employee List:


b) The French Portal Server hosted the French User Profiles and MySites


3) Any change made about an employee on the HR System (insert/update/delete), was to be replicated across to the Sharepoint Employee Profile List.

4) Any change to an Employee Picture or Employee Resume on the HR System (insert/update/delete), was to be replicated across to the Sharepoint Employee Picture Libraries and Resume Libraries. A Document or Picture Library can be thought of a regular Sharepoint list, except that a File is also associated with the list.

Additionally the HR system could indicate if only the actual Resume file or Picture file changed or only if text information about the the Employees changed (ie, their name or the city they worked in). This could be used to aid in performance -> i.e. if only the persons name changed, then do not bother updating the actual Resume Binary or Picture Binary and only update the Sharepoint List text columns (i.e. First Name, Last Name, City).

Below is the Detail View of the English Picture Library:

Below is the ThumbNail View of the English Picture Library:

5) When a File is added to a Document library, an actual URL (for example: http://spserver/sites/EnglishSite/EmployeePictures/Bob.jpg) 
is generated that can be retrieved from a Sharepoint API method call as below:

url = spListItem.File.ServerRelativeUrl;

This URL was sent back to the calling BizTalk Orchestration, so that it could ultimately update a Sharepoint Portal Server User Profile URL property. For example, one of the default properties for UserProfiles is the picture URL. When the MySite of the user is navigated to, the picture url is used to automatically display the picture on the users MySite as below:

Below is the edit page for Bob the Builder's User Profile:


Below is the MySite for Bob The Builder.
Note: That the User Profile for Bob the Builder is used to populate the MySite.


6) If the particular Sharepoint Library did not exist, then automatically create it.
For example if BizTalk submitted an Employee Profile(s) to Sharepoint for processing and the particular library list did not exist, then: 
a) Automatically create the list.
b) Populate the list from the submitted HR profile(s).

7) Any change made about an employee on the HR System (insert/update/delete), was to be replicated across to the Sharepoint User Profile Database.

8) A possible future enhancement was to have the information about Resumes also stored in Sharepoint Portal Areas. In this implementation Areas were used to represent the taxonomy of the company.

From the above functionality and requirements it became clear that it was going to be difficult or impossible to use the existing WSS adapter directly. Much finer grained control of the Sharepoint functionality was needed in order to implement the system.
Therefore there were two choices:

Choice 1) Extend the WSS BizTalk Adapter to handle the additional functionality.
Choice 2) Let Biztalk call custom web methods hosted by web services installed on Sharepoint Portal Servers. The custom web methods would receive as parameters information about employees in an XML format and additionally binary employee resumes and pictures. Custom code in the web methods would utilize the Sharepoint API methods in order to insert/update/delete items in the Sharepoint Lists and the Sharepoint User Profiles. The Web Methods could also return results, such as the URL's of the pictures or resumes to set into Sharepoint Portal User Profile properties.

In the end choice 2) was chosen to implement the solution.
The particular customer where I was doing the work had a number of strong .NET/Sharepoint/Web Services developers so it ultimately can down to the lead developers choice and he preferred choice number 2).

Choice 1), is also a viable option, but in retrospect, I am thankful that the custom web services method was chosen. Each Sharepoint List had it's own little quirks and the functionality to populate them varied from list to list. In the end it was much easier to have a separate set of web methods to control the population of each Sharepoint list and  the Portal User Profiles. To make the code more modular, a common set of Sharepoint helper methods was used, so that  code did not have to be duplicated, from web method to web method. If there were more Sharepoint Lists to be populated and the number of list to be populated grew over time, then using an adapter becomes more attractive. One of the things you lose by writing custom web methods, is that each List is tightly bound to a web method, so if the number of Sharepoint lists grow then the number of web methods also grows.

Below is a more detailed discussion of how the Custom Code was implemented and discusses in some detail using the Sharepoint API. Additionally at the end of this blog you can download the code that populates the Sharepoint Employee List.

A simplified flow of information from the HR System to Sharepoint went something like this:

1) From the HR System, information about the employees was delivered into Biztalk in an XML format.


2) A BizTalk Orchestration would then subscribe to the incoming Employee message.

A simplified Employee XML message is as below:

<ns0:EmployeeProfiles xmlns:ns0="http://BobTheBuilder">
  <EmployeeProfile>
    <EMPLOYEE_ID>1</EMPLOYEE_ID>
    <LAST_NAME>The Builder</LAST_NAME>
    <FIRST_NAME>Bob</FIRST_NAME>
    <PHONE>111-1111</PHONE>
    <CITY>Toronto</CITY>
    <FUNCTION>Owner</FUNCTION>
    <PROCESSLISTINDICATOR>-1</PROCESSLISTINDICATOR>
  </EmployeeProfile>
  <EmployeeProfile>
    <EMPLOYEE_ID>2</EMPLOYEE_ID>
    <LAST_NAME>The Contractor</LAST_NAME>
    <FIRST_NAME>Wendy</FIRST_NAME>
    <PHONE>222-2222</PHONE>
    <CITY>Mississauga</CITY>
    <FUNCTION>Contractor</FUNCTION>
    <PROCESSLISTINDICATOR>-1</PROCESSLISTINDICATOR>
  </EmployeeProfile>
</ns0:EmployeeProfiles>


The XML was converted to a string in the BizTalk Orchestration Expression shape. An example is as below.

varXMLDomEmployee = msgEmployee;
// Now the following can be used to extract the XML as a string
strEmployeeXML = varXMLDomEmployee.OuterXML.
More details about the above can be found HERE

If the Employee information was associated with a Resume, or a Picture, then in the Orchestration a .NET Helper Component using ADO.NET was called to retrieve the file from Sql Server. The code would return the Resume or Picture Binary as a string, from a Sql Server text column . An excerpt of the code is as below:

string result;
System.Byte[] resumeImageBytes;
resumeImageBytes =  (System.Byte[]) this.sqlCmdGetResumeImage.ExecuteScalar();
result = System.Convert.ToBase64String(resumeImageBytes);
return result; 

3) The string of Employee XML and optionally the Base64String of the Employee Resume or Picture was then sent to the Sharepoint Portal Servers from the BizTalk Orchestration via a Web Port to a Web Service installed on the Sharepoint Portal Servers. More detail about this can be found HERE

4) The particular web method on the portal Server would then accept as parameters the string of XML and optionally the binary Resume or Picture (as a string).

5) On the Web Server side, strongly typed datasets were used to hold configuration information for the Sharepoint Lists/Portal User Profiles and also included mapping information between the incoming Employee XML and the Sharepoint List Columns/ User Profile Properties. The dataset is as below.

XML installed on the Web Service side would then populate the dataset. A sample of the XML to populate the dataset is as below:

<?xml version="1.0" standalone="yes" ?>
<SharePointListData xmlns="http://tempuri.org/SharePointListData.xsd">
 <SharePointList>
    <SharePointListID>1</SharePointListID>
    <WebLocation>sites/EnglishSite</WebLocation>
    <SharePointListDescription>Employee Profiles List</SharePointListDescription>
    <SharePointListTitle>Employee Directory</SharePointListTitle>
    <SharePointListPKColumn>EmployeeID</SharePointListPKColumn>
    <XMLSourcePKColumn>EMPLOYEE_ID</XMLSourcePKColumn>
    <TemplateName>Custom List</TemplateName>
    <Language>en</Language>
 </SharePointList>
 <SharePointListColumns>
    <SharePointColumnName>EmployeeID</SharePointColumnName>
    <XMLSourceColumnToMap>EMPLOYEE_ID</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Last Name</SharePointColumnName>
    <XMLSourceColumnToMap>LAST_NAME</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>First Name</SharePointColumnName>
    <XMLSourceColumnToMap>FIRST_NAME</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Phone</SharePointColumnName>
    <XMLSourceColumnToMap>PHONE</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>City</SharePointColumnName>
    <XMLSourceColumnToMap>CITY</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Function</SharePointColumnName>
    <XMLSourceColumnToMap>FUNCTION</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
 </SharePointListColumns>
 <SharePointList>
    <SharePointListID>2</SharePointListID>
    <WebLocation>sites/FrenchSite</WebLocation>
    <SharePointListDescription>Les Profils d'employé Enumèrent</SharePointListDescription>
    <SharePointListTitle>Annuaire d'employé</SharePointListTitle>
    <SharePointListPKColumn>EmployeeID</SharePointListPKColumn>
    <XMLSourcePKColumn>EMPLOYEE_ID</XMLSourcePKColumn>
  <TemplateName>Custom List</TemplateName>
  <Language>fr</Language>
 </SharePointList>
 <SharePointListColumns>
    <SharePointColumnName>EmployeeID</SharePointColumnName>
    <XMLSourceColumnToMap>EMPLOYEE_ID</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Nom de famille</SharePointColumnName>
    <XMLSourceColumnToMap>LAST_NAME</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Prénom</SharePointColumnName>
    <XMLSourceColumnToMap>FIRST_NAME</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Téléphone</SharePointColumnName>
    <XMLSourceColumnToMap>PHONE</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Ville</SharePointColumnName>
    <XMLSourceColumnToMap>CITY</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
 </SharePointListColumns>
 <SharePointListColumns>
    <SharePointColumnName>Fonction</SharePointColumnName>
    <XMLSourceColumnToMap>FUNCTION</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
 </SharePointListColumns>
</SharePointListData> 

The above XML contains all the information necessary to create and/or get a handle to the necessary Sharepoint list(s) and contains the mappings between the incoming XML and the Sharepoint Columns. For example: The LAST_NAME node in the incoming XML would map to the Last Name column in the English Sharepoint list and would also map to the Nom de famille column in the French Sharepoint list. This configuration XML was then stored in the Web Services Cache. When the XML file changed, the File Dependency Cache would automatically load in the new XML file. In this way, the configuration information about the Sharepoint Lists and mappings could be changed on the fly.

Below is Sample Web Dependency Cache loading code. 

result.ReadXml(System.Web.HttpContext.Current.Server.MapPath(fileName));
// Save to the Web Cache. Put dependency on file, so when file is changed, old copy will get flushed from the Cache
System.Web.HttpContext.Current.Cache.Insert(cacheKey,
result,
new System.Web.Caching.CacheDependency(System.Web.HttpContext.Current.Server.MapPath(fileName)));


6) The Web Method would then call a helper class, that would actually do the processing
against the Sharepoint List(s)/User Profile Database with the Employee XML and the Configuration Dataset.

a) The Helper class would first determine if the required Sharepoint List(s) existed. The configuration dataset contained the necessary information to create the Sharepoint List(s) if they did not exist, otherwise just get a handle to the existing list. The code to create the list looks something like the below:

 try
 {
  // If the below WSS list exists, then get it.
  employeeList = this.siteCollection[0].AllWebs[(sharepointListRow.WebLocation)].Lists[sharepointListRow.SharePointListTitle];
 }
 catch (System.ArgumentException ex)
 {
  // Means the WSS list did not exist, therefore create it.
  employeeList = null;
  System.Diagnostics.Debug.WriteLine("The List does not exist. error is as follows -> " + ex.ToString()); 
 }
 if (employeeList == null)
 {
  // Create the WSS List
  string listTitle="" ,listDescription = "";
  listTitle = sharepointListRow.SharePointListTitle;
  if (!sharepointListRow.IsSharePointListDescriptionNull())
  {
   listDescription = sharepointListRow.SharePointListDescription;
  }
  else
  {
   listDescription = sharepointListRow.SharePointListTitle;
  }
  // This will add the new list to the List Collection of the Site.
  siteCollection[0].AllWebs[sharepointListRow.WebLocation].Lists.Add(listTitle,  // Title of New List
                  listDescription,       // Description of the List
                  Microsoft.SharePoint.SPListTemplateType.GenericList); // Type of list need to create

  // This will get a handle to the just added list
  employeeList = siteCollection[0].AllWebs[sharepointListRow.WebLocation].Lists[listTitle];
  // Now add the necessary columns to the newly created list.
  // Also add the columns to the Default View of the List.
  // Note: This code only handles text type columns.
  Microsoft.SharePoint.SPView defaultView = employeeList.DefaultView;
  Microsoft.SharePoint.SPViewFieldCollection spViewFieldsCollect = defaultView.ViewFields;
  foreach (SharePointListData.SharePointListColumnsRow spColumn in sharepointListRow.GetSharePointListColumnsRows())
  {
    employeeList.Fields.Add(spColumn.SharePointColumnName ,Microsoft.SharePoint.SPFieldType.Text,false);
    spViewFieldsCollect.Add(spColumn.SharePointColumnName);
  }
  defaultView.Update();
 }

b) The Helper class would then take the Configuration dataset and use it to map the Employee XML into the correct Sharepoint List columns. An indicator is also passed in with the employee XML. If it is set to -1, then the Employee in the Sharepoint list must be deleted, otherwise the Employee must be added to the list if it is not found or updated if it is found. A column in the Sharepoint list is used as a primary key for CAML Queries.

public void setDataInEmployeeSPList(string employeeXml,Microsoft.SharePoint.SPList employeeList, SharePointListData.SharePointListRow sharepointListRow )

{

 System.Xml.XmlElement employeeChildNode;
 System.Xml.XmlDocument employee = new System.Xml.XmlDocument();
 employee.LoadXml(employeeXml);
 System.Xml.XmlNode xmlNode;

 xmlNode = employee.DocumentElement["PROCESSLISTINDICATOR"];
 string deleteListIndicator = xmlNode.InnerText;

 xmlNode = employee.DocumentElement[sharepointListRow.XMLSourcePKColumn];
 string employeeID = xmlNode.InnerText;

 Microsoft.SharePoint.SPListItem spListItemEmployee;
 Microsoft.SharePoint.SPListItemCollection spListItemsEmployees;
 // Call method that does CAML query to try to find the Employee in the list.
 // If found, we will either update or delete it in the list.
 spListItemsEmployees = this.QueryListForItems(employeeList,
             sharepointListRow.SharePointListPKColumn,
             employeeID);
             
 if (deleteListIndicator == "-1")
 {
  if (spListItemsEmployees.Count == 1)
  {
   // Means must delete the Employee from the list
   spListItemEmployee = spListItemsEmployees[0];
   employeeList.Items.DeleteItemById(spListItemEmployee.ID);
   //employeeList.Update();
  }
  else
  {
   // Means could not find the item in the list to delete 
   System.Diagnostics.Debug.WriteLine("Could not find the Employee to delete in the library list.");    
     
  }
 }
 else // Either the Employee must be updated or deleted in the Employee list
 {
  spListItemEmployee = null;
  switch (spListItemsEmployees.Count)
  {
   case 1:  // Found an existing Employee -> Update
    System.Diagnostics.Debug.WriteLine("Found in library");
    spListItemEmployee = spListItemsEmployees[0];
    break;
   case 0: // Employee does not exist. Create a new employee item in the list
    System.Diagnostics.Debug.WriteLine("Did not find in library");
    spListItemEmployee = employeeList.Items.Add();
    break;
  }
  // Now set the information from the Employee XML to the Employee Sharepoint List Columns
  foreach (SharePointListData.SharePointListColumnsRow employeeColumnsRow in sharepointListRow.GetSharePointListColumnsRows())
  {
   employeeChildNode = employee.DocumentElement[employeeColumnsRow.XMLSourceColumnToMap];
   if (employeeChildNode != null)
   {
    spListItemEmployee[employeeColumnsRow.SharePointColumnName] = employeeChildNode.InnerText;
   }  
  }
  if (spListItemEmployee != null)
   spListItemEmployee.Update();
 }
}

/// <summary>
/// Will query a list in Sharepoint. Only one column in the list is queried. For example City = "Toronto"
/// </summary>
/// <param name="listToQuery">The SPList to query</param>
/// <param name="columnToQuery">The name of the column to query in the SPList</param>
/// <param name="queryValue">The value to query for in the column</param>
/// <returns>A collection of List items that were returned by the query</returns>
public Microsoft.SharePoint.SPListItemCollection QueryListForItems(Microsoft.SharePoint.SPList listToQuery,
 string columnToQuery,
 string queryValue)
{
 Microsoft.SharePoint.SPListItemCollection result;
 Microsoft.SharePoint.SPQuery spQuery = new Microsoft.SharePoint.SPQuery();
 spQuery.Query = "<Where><Eq><FieldRef Name='"+ columnToQuery +"'/><Value Type='Text'>" +  queryValue + "</Value></Eq></Where>";
 result = listToQuery.Items.List.GetItems(spQuery);
 return result;

}

c) In the end, there was not too much code to write, somewhere between 50 and 100 lines of code to get the job done.

Some Notes about the Sharepoint API's

1) The Sharepoint API's will give the maximum amount of control over a WSS or Portal Site.
2) In order to use the WSS API you must make a reference to Microsoft.SharePoint.dll. The Sharepoint dll contains methods to interact with Sharepoint lists, Document/Picture libraries and much more.
3) In order to use the Sharepoint Portal API you must make a reference to the Microsoft.SharePoint.Portal.dll. The Portal dll contains methods to interact with a portals User Profile database, areas and much more.
4) The Sharepoint API's are organized into a series of collections. For example:
a) A Portal contains a collection of WSS sites
b) A WSS site contains a collection of Sharepoint Lists
c) A Sharepoint List contains a collection of Sharepoint columns and Items that contains the data in the List
5) The API's are fairly intuitive, but like everything have their own set of nuances.
For example when adding an new item into a Generic list, it looks something like the below:

// Just add the new item to the Items collection of the list
spListItemEmployee = employeeList.Items.Add();

But when adding a new item into a Document Library or Picture list, it looks something like the below:

// Must add the new item to the lists Folder Files collection 
file = resumeList.SharePointFolder.Files.Add(xmlNode.InnerText /* Note.doc comes from PS+ ".doc"*/,Convert.FromBase64String(resumeByteStream)); 

BizTalk 2006

When BizTalk 2006 ships, it will include a built in out of the box WSS adapter. I am sure there will be improvements to the existing BizTalk 2004 WSS adapter that can be downloaded from GotDotNet. Read more about the new out of the box WSS adapter and other BizTalk 2006 Adapter additions and enhancements HERE

HERE download the sample code that populates a Sharepoint Employee List. Read the Readme.txt before installing and running.
Note: You must have Sharepoint WSS installed and Visual Studio 2003 on a Windows Server 2003 machine in order for the sample to work.