Error when refreshing BCS external data

Recently I have been working with the Business Connectivity Service in SharePoint. Done a simple set up using SharePoint Designer, and used User’s Identity for the Authentication Mode. Once I have added my External column to the list and additional columns for it to pull through. I am able to add new items, I can update items. It appears my list is working correctly.

With the backend system changing, the columns in SharePoint do not automatically update, you have to click the refresh button next to the external column.

So you click the button, follow the steps and you get the following error message:

0 item(s) were refreshed.
An error occurred while retrieving data from Database. Administrators, see the server log for more information.

By checking the SharePoint logs the following messages are found:

The security configuration of the LobSystemInstance does not permit updating the external data column values. Use Secure Store credentials for this LobSystemInstance to enable this scenario.
Exception thrown while synchronizing business data System.NotSupportedException: Specified method is not supported. at Microsoft.SharePoint.WebControls.BusinessDataSynchronizerJob.DoWork()
System.NotSupportedException: Specified method is not supported. at Microsoft.SharePoint.WebControls.BusinessDataSynchronizerJob.DoWork()

After checking online, there are a few blogs out there saying that you should change your Authentication Mode from User’s Identity to BDC Identity also known as revert to self. This does work, however I would argue this isn’t the best recommended practice in a production environment. I recommend to use Impersonate Windows Identity, or Impersonate custom identity. For instructions how to set this up, please follow a blog by Sridhar on MSDN blogs. http://blogs.msdn.com/b/sridhara/archive/2010/01/27/setting-up-bcs-with-secure-store-application-impersonation.aspx

So why?

Question is why does it work for Revert To Self, and Impersonate Windows Identity? From reflecting on Microsoft code, at Microsoft.SharePoint.WebControls.BusinessDataSynchronizerJob.DoWork() found in the Microsoft.SharePoint.dll, there is a method call BdcClientUtil.CheckSupportedAuthenConfiguration()

   if (!BdcClientUtil.CheckSupportedAuthConfiguration(lobSystemInstance))
            {
                ULS.SendTraceTag(0, ULSCat.msoulscat_WSS_WebControls, ULSTraceLevel.Medium, "The security configuration of the LobSystemInstance does not permit updating the external data column values. Use Secure Store credentials for this LobSystemInstance to enable this scenario.");
                throw new NotSupportedException();
            }

As you can see there is our error message about the LobSystemInstance does not permit updating the external data column values. Reflecting through to CheckSupportedAuthConfiguration method you can see that if the lobsystem instance is Database, the only valid authentications are RevertToSelf, RdbCredentials, WindowsCredentials. Which is why User’s Idenitity (Passthrough) doesn’t work.

internal static bool CheckSupportedAuthConfiguration(ILobSystemInstance lobSystemInstance)
{
 bool flag = false;
 if (lobSystemInstance != null)
 {
 ILobSystem lobSystem = lobSystemInstance.GetLobSystem();
 if (lobSystem != null)
 {
 switch (lobSystem.SystemType)
 {
 case SystemType.Database:
 return CheckSupportedAuthConfiguration(lobSystemInstance, "AuthenticationMode", new string[] { DbAuthenticationMode.RevertToSelf.ToString(), DbAuthenticationMode.RdbCredentials.ToString(), DbAuthenticationMode.WindowsCredentials.ToString() });
 case SystemType.WebService:
 return CheckSupportedAuthConfiguration(lobSystemInstance, "WebServiceAuthenticationMode", new string[] { HttpAuthenticationMode.RevertToSelf.ToString(), HttpAuthenticationMode.Credentials.ToString(), HttpAuthenticationMode.WindowsCredentials.ToString() });
 case (SystemType.WebService | SystemType.Database):
 case ((SystemType) 4):
 case ((SystemType) 5):
 case (SystemType.Custom | SystemType.Database):
 return flag;
 case SystemType.Custom:
 return true;
 case SystemType.Wcf:
 return CheckSupportedAuthConfiguration(lobSystemInstance, "WcfAuthenticationMode", new string[] { WcfAuthenticationMode.RevertToSelf.ToString(), WcfAuthenticationMode.Credentials.ToString(), WcfAuthenticationMode.WindowsCredentials.ToString(), WcfAuthenticationMode.DigestCredentials.ToString() });
 case SystemType.DotNetAssembly:
 return true;
 }
 }
 }
 return flag;
}

Programatically adding SPListItems with a BCS External Column.

In this blog I’m going to show you how to add an item to a list that contains a BCS External column. This is something I have struggled with, read many other blogs on the subject but for some reason never got it working 100%. I’m not going to show you how to set up the Business Connectivity Service, as I assume that you are reading this post because you have already done that and are ready to add your first item programmatically. Hopefully you have already tested that you can add an item via the UI GUI.

Basic Set up

Using SharePoint designer and the Adventure Works database, I created an External Content Type on the HumanResource.Department table. Included a Comparision filter on the Name column. On my SharePoint test team site, I have created a Generic List, called this list Employee, renamed the title column ‘Employee’, and added the external column called Department to my list to also include the GroupName.

The Code

To do demo this, I’m using a simple console application. First I’ve created an EmployeeData class, add a couple of employees, and passed the list to my InsertingBCSData class.

using System;
using System.Collections.Generic;
using Microsoft.SharePoint;
namespace InsertingBCSData
{
    public class EmployeeData
    {
        public String Employee { get; set; }
        public String Department { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var employees = new List<EmployeeData>
                                {
                                    new EmployeeData {Employee = "Cann0nF0dder", Department = "Sales"},
                                    new EmployeeData {Employee = "DiePiggyDie", Department = "Finance"}
                                };

            InsertingBcsData(employees);

            Console.WriteLine("****Complete****");
            Console.ReadLine();

        }
    }
}

The method InsertingBcsData isn’t where the magic happens, it is just a standard AddItem() to SPListItem.

private static void InsertingBcsData(List<EmployeeData> employeeItems)
       {
            using (SPSite site = new SPSite("http://cannonfodder.local/sites/TestTeamSite"))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    SPList list = web.Lists.TryGetList("Employee");
                    if (list == null)
                    {
                        Console.WriteLine("Please check your list name and try running this program again.");
                        return;
                    }
                    int itemNumber = 0;
                    web.AllowUnsafeUpdates = true;
                    foreach (var empItem in employeeItems)
                    {
                        itemNumber++;
                        try
                        {
                            SPListItem item = list.AddItem();
                            item["Title"] = empItem.Employee;
                            BCSData.SetGivenBcsColumn(item, "Department", empItem.Department, site);
                            item.Update();
                            Console.WriteLine(String.Format("Updated item {0} of {1}. ({2:0.00}%) ", itemNumber, employeeItems.Count, ((double)itemNumber / (double)employeeItems.Count) * 100));
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(String.Format("Unable to read in item {0} : {1}", itemNumber,
                                                            empItem.Employee));
                            Console.WriteLine("");
                        }
                    }
                    web.AllowUnsafeUpdates = false;
                }
            }
        }

In the static class BCSData I’m performing the insert to the BCS external column. The SetGivenBcsColumn accepts 4 parameters. The current SPListItem, the internal column name of the item to update, in our case the External column, the value to display in the display column, and the current SPSite. The following code obtains the RelatedFieldWssStaticName, which is the key to getting the BCS working. This field is hidden on your list and contains the BdcIdentity value for the item.

The call to GetIDByName is slightly overkill for what we are doing here. The method performs a comparision filter in the BCS and returns the entire row data for the given item, here we only need the BdcIdentity value. However, if you wish to take this code further, because it brings back all columns for the given item, you could update the extra columns too that are on your list. In my example that would be the Department: GroupName column. The reason why I don’t update this column in code, is because after I have done the import, by going to the list, I can click the little refresh icon above the Department column, and assuming I have hooked up the BdcIdentity correctly, these extra fields will be pulled back by the UI.

using System;
using System.Data;
using System.Xml;
using Microsoft.BusinessData.MetadataModel;
using Microsoft.BusinessData.Runtime;
using Microsoft.SharePoint.BusinessData.Runtime;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.BusinessData.SharedService;

namespace InsertingBCSData
{
    public static class BCSData
    {
        public static void SetGivenBCSColumn(SPListItem listItem, String internalColumnName, String displayValue, SPSite site)
        {

           //Convert the field into a SPBusinessDataField, this is what the External BCS Column known as.
            SPBusinessDataField bcsField = listItem.Fields[internalColumnName] as SPBusinessDataField;
           //If field equal null, then cannot perform update
            if (bcsField != null)
            {
               //Read in the field schema so that we can obtain extra values to perform a query against the BCS.
                XmlDocument xmlData = new XmlDocument();
                xmlData.LoadXml(bcsField.SchemaXml);
                //This get the ID Field in the list for the BCS
                string entityStaticName = xmlData.FirstChild.Attributes["RelatedFieldWssStaticName"].Value;
                string entityNameSpace = xmlData.FirstChild.Attributes["EntityNamespace"].Value;
                string entityname = xmlData.FirstChild.Attributes["EntityName"].Value;

                //Makes a call to the BCS Service and returns a datatable of results.
                var datatable = GetIDByName(entityNameSpace, entityname, displayValue, site);

                //As we are doing a comparison filter, there should be only one row of data, therefore we can grab the BdcIdentity from the first row.
                if (datatable != null)
                    listItem[entityStaticName] = datatable.Rows[0]["BdcIdentity"];

                //Set the field Display Value, you can set this to say anything, because when list is refreshed from the Department column, it will get updated from the BCS,
                //An idea might be to set the display value to "Refresh me", to tell the user to do this first time they hit the list after you have inserted all the data.
                listItem[internalColumnName] = displayValue;
            }
        }

          public static DataTable GetIDByName(string nameSpace, string entityName, string entityValue, SPSite site)
        {
            BdcService service = SPFarm.Local.Services.GetValue<BdcService>();
            //Could use SPServiceContext.Current instead of using the next line in a non console application.
            SPServiceContext context = SPServiceContext.GetContext(site);

//Found this line was required otherwise would fail with SSO.
SPServiceContextScope contextScope = new SPServiceContextScope(context);

IMetadataCatalog catalog = service.GetDatabaseBackedMetadataCatalog(context);
 IEntity entity = catalog.GetEntity(nameSpace, entityName);

 ILobSystemInstance lobSystemInstance = entity.GetLobSystem().GetLobSystemInstances()[0].Value;
 IFilterCollection filters = entity.GetDefaultFinderFilters();

 //set the wildcard filter value
 if (!string.IsNullOrEmpty(entityValue) && filters.Count != 0)
 {
 if (filters[0].GetType().Name == "ComparisonFilter")
 {
 ComparisonFilter filter = (ComparisonFilter)filters[0];
 filter.Value = entityValue;
 }
 else
 {
 //Added this for reference, and filter value is against a SQL datasource.
 WildcardFilter filter = (WildcardFilter)filters[0];
 filter.Value = "%" + entityValue + "%";
 }

 IEntityInstanceEnumerator enumerator = entity.FindFiltered(filters, lobSystemInstance);
 DataTable table = null;
 int limit = 0;
 while (enumerator.MoveNext())
 {
 //first time setup the datatable, evertytime there after add a row.
 if (table == null)
 table = enumerator.Current.EntityAsDataTable;
 else
 table.ImportRow(enumerator.Current.EntityAsDataTable.Rows[0]);

 limit++;
 //If more than 2000 rows in the filter it will fall over when trying to enumerate the 2001 item, we only need the first item in this example
 // so limit set for 2, header and first row of data..
 if (limit >= 2)
 break;
 }
 return table;
 }
 return null;
 }
 }
}

So now the code is complete we can run the console application. After running and checking the list you will see the items added to the list.

Now to update the Department: GroupName, just click the refresh icon next to Department, follow the click through instructions.