This is a continuation of a previous blog post where I discussed some issues to be aware of around using business data columns in a list where you have deleted or changed the values in the underlying data source.  This post goes a little deeper into how to use code to update your lists when the "update" icon (or "hurricane" icon) throws an error upon attempting to refresh a list.

Background: Keeping Lists Up-To-Date with BDC Data

Let’s say you have a database (one type of a “Line of Business” or LOB system in BDC-speak) that contains a table called “tblAffiliates” that has all of the names of all of your organization’s affiliated organizations. Since this is commonly used information throughout your organization and your site, you decide to create a BDC Entity called “Affiliates” that will query this table to retrieve the names (and any other information) about your affiliates. You then go ahead and create a business data column in a list on your site called “Affiliate” that will present you with a lovely “people picker” style interface for list items. Awesome! This is what the BDC is perfect for!

Scenario 1: Underlying Data Changed – Not the End of the World, but Beware

Now let’s say you’ve got an affiliate called “Affiliate A” and you’ve got a list with (tens, hundreds, thousands of items? Mine has ~50k and growing) that are tagged as “Affiliate A” in your Affiliate column. Now, in the backend database, the affiliate has been renamed to “Affiliate A, Inc.” What happens to your list? The answer is nothing. Your list is not automatically updated to reflect the change. Now, moving forward, two things are going to happen:

1. Any new items you create will say “Affiliate A, Inc.” but they’ll exist concurrently with list items tagged as “Affiliate A” and this may not be good, especially if you’ve set up any filtering in web parts or views where the name may be hardcoded, or even if you just want to filter the list on the fly – you’ll have two names for the same affiliate (or more, depending).
   
2. If you attempt to edit a list item that says “Affiliate A” you’ll get a friendly validation error of “No exact match was found” (see Figure 1 below) and you’ll be forced to pick the affiliate again but with the proper name. Not the worst problem in the world, but it is work you have to do. And imagine if there are hundreds or thousands of list items like this.

Figure 1: No Exact Match Found Validation Error

Fortunately, you can “refresh” your list by clicking on the little green arrows in the column header of your business data column.

Figure 2: Refreshing a BDC Business Data Column

No problems so far, right? Sure, some potential inconvenience, but nothing you can’t handle. Microsoft has at least given us an OOTB solution – not ideal, but you can train your users to manually update their lists. Just remember to click on that little green arrow ON EVERY LIST USING A BUSINESS DATA COLUMN IN YOUR SITE COLLECTION everytime something in the LOB system changes - you can remember to do that, can't you?

Scenario 2: Underlying Data No Longer Available in BDC – OOTB Refreshing Breaks!

Now, here’s the real killer: what happens if you either a) delete “Affiliate A, Inc.” from your backend database completely, or b) there’s something about your BDC Entity’s underlying query that ceases to retrieve this data from the database based on some other condition in your application logic (in my example, affiliates can get “archived” and they cease to show up in the BDC)? Two things happen:

1. YOU CAN NO LONGER USE THE REFRESH OPTION ABOVE! Attempting to do so will cause an error when MOSS encounters the first list item referring to a BDC value that no longer exists when it queries the BDC Entity.
2. Your list items will continue to exist with the values of the deleted affiliate, but if you try to edit any of those list items, you’ll get the validation error from Figure 1. You are now forced to either change the affiliate to choose one that exists (not an option, most likely), or you are stuck and can’t edit that item.

Understanding, Diagnosing and Fixing the Problem

It’s helpful to understand what’s happening when you click on that magic little refresh icon in Figure 2 above. When you click on the refresh icon, you are taken to an application page in the _layouts directory that gives a confirmation dialogue that you’re about to embark upon a Long Running Operation:

If you look at the URL, you can see you’re calling up BusinessDataSynchronizer.aspx from the _layouts directory with a few query parameters that tell the application page about your list and the column you’re refreshing:

http://yoursite.com/website/_layouts/BusinessDataSynchronizer.aspx?ListId={GUID OF THE LIST HERE}&ColumnName={NAME OF YOUR BDC DATA COLUMN HERE}&Source={URL TO THE VIEW OF YOUR LIST}

We’ll come back to the significance of this in a second, but let’s proceed. If you click OK, you’ll see a progress meter that refreshes every few seconds:

OK, it looks like it’s doing something – the page keeps refreshing, but it still says “Updating item 1 of x.” Eventually, you get an error that looks like this:

It’s my favorite MOSS error, “Administrators, see the server log for more information.” Thanks, will do! So, let’s take a look at the logs like the good page says. Open the folder that contains the error logs on the web server (located in the “hive” at C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS) and open the file that appears to have been dated around the time you ran into this error. If you do a search for “Business Data” around the time you received your error, you should be able to locate and follow along with what the job was doing to gain some insight into what caused this error (my step numbers, comments and emphasis added; messages extracted from the log’s text):

So, one thing you can do is take the SQL statement underlying your BDC entity (omitted from step 3 above in the log), throw it into SQL Server Management Studio and run the T-SQL and pass the parameter in from steps 6 and 7 and see what happens to simulate the BDC’s invocation of the particular SpecificFinder Method Instance defined for the entity in question (if you configured the application definition yourself, you’ll know what this means. If not, basically every Entity needs to have this method defined to retrieve a single record from a data source in a “SELECT * FROM WHERE” type manner). When I did this, no records were returned. So, it’s starting to look like the empty result set is causing the error in step 8, which is killing the entire job.

Solution: Code Your Way Out of this Wet Paper Bag

Fortunately, there is a way out via code. Based on this blog post’s walkthrough about examining the BusinessDataSynchronizer.aspx application page and using Reflector for .NET to determine what the code behind is doing when you click on that page’s “OK” button. Basically, what you will have to do is model your code after the BusinessDataSynchronizerJob class inside the Microsoft.SharePoint.Portal.WebControls namespace located inside the Microsoft.SharePoint.Portal dll – you can create your own implementation and add your own error handling to work around the problem.

What this code is doing is connecting to the LOB system, getting the list items in the list, looping through the list items, and for each list item it finds the business data column that you indicated needs refreshing and uses the SpecificFinder method from the BDC entity to get most recent value. However, as noted above, if it hits a list item whose parameter when passed to the “WHERE” clause of the SpecificFinder method returns null, it causes the application page/job to terminate (raises an Exception of type “ObjectNotFoundException”). By writing your own class to handle the ObjectNotFoundException in the appropriate place as I've done below, you can gain a little more control and keep that code chugging along making its updates.  I owe a huge debt to Nick at Lightning Tools (the BDC Meta Man people) for posting the original code sample - I simply refactored it a little and made the mods that I explain below. 

Sample Usage

Sample usage to utilize the class that handles the refresh is very straightforward – instantiate a UpdateBDCFields object (the code for this class is below), pass it an SPList object, the name of your business data column, and the name of your Shared Services Provider, and then call the UpdateColumn method and you’re all set.  For example:

Console.WriteLine("Refresh business data columns.");
string rootURL = ConfigurationManager.AppSettings["SiteURL"]; 
string listName = ConfigurationManager.AppSettings["ListName"]; 
string columnName = ConfigurationManager.AppSettings["BDCColumnName"]; 
string sspName = ConfigurationManager.AppSettings["SSP"]; 
Console.WriteLine("Attempting to refresh list items with latest BDC values...");
 
try
{
   using (SPSite site = new SPSite(rootURL))
   {
      using (SPWeb web = site.OpenWeb())
      {
         SPList list = web.Lists[listName];
         UpdateBDCFields refresher =
            new UpdateBDCFields(list, columnName, sspName);
         refresher.UpdateColumn();
      }
   }
   Console.WriteLine("Successfully updated the list!");
}
catch (Exception ex)
{
   Console.WriteLine(
      string.Format("Error: {0}.\nStack Trace:{1}",
      ex.Message, ex.StackTrace));
}

I chose to implement this as console application, but it could easily be called from a SharePoint Timer Job or your own application page, etc. To try this code out, create a new console application in C#, download the zip file, and add the three files inside the zip to your solution (overwrite the default Program.cs file with this one).

The main modifications that I have made include the following:

• At line 125 in the UpdateBDCFields class' public UpdateColumn method, I modified it to make sure that the list item has data in the column in two places before attempting to call the private helper method UpdateListItem:
  
  if (!(item[_columnName] == null) && !(item[bizDataField.RelatedField] == null))
  
  This is necessary because the private helper method UpdateListItem needs to retrieve an encoded ID value at line 163 from the BDC that is stored in the list to check for related fields, without which the update code cannot execute properly. I may be wrong about this, but before I added it I was having problems with my list – you may want to play with that.

• I wrapped lines 165-248 of the private method UpdateListItem inside a Try…Catch block to check for exceptions, specifically the ObjectNotFoundException caused by a no-longer-existing value in the BDC that does exist in the list, which allows the code to keep going in case of error. I also changed this method from a void to return a bool that indicates whether or not there was a failure which is returned to the calling UpdateColumn method. This enables a simple mechanism to keep a running tally of success/failure information that can be output in the logging at the end of the batch.

Please note that as I've provided the sample for this console app, the app.config file holds the key/value pairs for all variables used in the sample above -- you'll want to update those values for your own environment, or create local variables and use those instead.

app.config Contents

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="SiteURL" value="http://yoursite" />
    <add key="ListName" value="Your List Name" />
    <add key="BDCColumnName" value="Your Business Data Column Name" />
    <add key="SSP" value="Your Shared Service Provider's Name" />
  </appSettings>
</configuration>

The console app is also configured to output an audit trail as it goes by numerous Console.WriteLine("blah blah") type statements, so you can always call this from a command prompt and pipe the output to a text file if you want to keep an eye on its behavior.  Or get rid of them, alter them as you need, etc.  I made this pretty verbose for troubleshooting and testing prior to pulling the trigger in our production environment...I hope this can help someone!

Download the Sample Project Files: UpdateBDCFields.zip (3.66 kb)

UpdateBDCFields.cs Class:

using System; 
using System.Collections.Generic; 
using System.ComponentModel; 
using System.Data; 
using System.Text; 
using Microsoft.SharePoint; 
using Microsoft.Office.Server.ApplicationRegistry.Infrastructure; 
using Microsoft.SharePoint.Portal.WebControls; 
using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;
 
namespace BDC.Utilities
{
   public class UpdateBDCFields
   {
      private SPList _list = null;
      private string _columnName = "";
      private string _sharedResourceProvider = "";
      private LobSystemInstance _lobSysInst = null;
      private Entity _entity = null;
      private Microsoft.Office.Server.ApplicationRegistry.MetadataModel.View _specificFinderView = null;
      private SPListItemCollection _items = null;
 
      public UpdateBDCFields(SPList list, string businessDataColumnName, string sharedResourceProvider) 
      { 
         _list = list;
         _columnName = businessDataColumnName; 
         _sharedResourceProvider = sharedResourceProvider;
      }
 
      #region Properties
 
      public string ColumnName
      {
         get { return _columnName; }
         set { _columnName = value; }
      }
 
      public SPList List
      {
         get { return _list; }
         set { _list = value; }
      }
 
      public string SharedResourceProvider
      {
         get { return _sharedResourceProvider; }
         set { _sharedResourceProvider = value; }
      }
 
      #endregion
 
      public void UpdateColumn()
      {
         //check to make sure the field is a valid column name
         if(!(_list.Fields.ContainsField(_columnName)))
         {
            throw new
               ArgumentException("The field '" + _columnName +
               "' is not a valid field name in this list - check for typos!");
         }
 
         SPField fieldByInternalName =
            _list.Fields.GetFieldByInternalName(_columnName);
 
         //check to make sure that the field is a business data column
         if (!(fieldByInternalName is BusinessDataField))
         {
            throw new
               BusinessDataListConfigurationException(
                 "The field " + _columnName + " is not a business data field");
         }
 
         //get the bdc data column in the list
         BusinessDataField bizDataField = (BusinessDataField)fieldByInternalName;
 
         //build a list of related fields in the list that derive their data from
         //the bdc data column
         string[] secondaryFieldsNames = bizDataField.GetSecondaryFieldsNames();
         string[] secondaryWssFieldNames = new string[0];
         string property = bizDataField.GetProperty("SecondaryFieldWssNames");
 
         //populate the array of secondary names
         secondaryWssFieldNames = property.Split(new char[] { ':' });
 
         SqlSessionProvider.Instance().SetSharedResourceProviderToUse(
            _sharedResourceProvider);
 
         _lobSysInst =
            ApplicationRegistry.GetLobSystemInstanceByName(
            bizDataField.SystemInstanceName);
         _entity = _lobSysInst.GetEntities()[bizDataField.EntityName];
         _specificFinderView = _entity.GetSpecificFinderView();
         _items = _list.Items;
 
         int successes = 0;
         int failures = 0;
         bool success = false;
 
         //loop through list items in list and try to update/refresh with latest BDC value
         foreach (SPListItem item in _items)
         {
            //make sure the item has data in the column before trying to update
            if (!(item[_columnName] == null) && !(item[bizDataField.RelatedField] == null))
            {
               success = UpdateListItem(item, bizDataField, _lobSysInst, _entity,
                  _specificFinderView, secondaryFieldsNames, secondaryWssFieldNames);
 
               if (success)
                  successes++;
               else
                  failures++;
            }
         }//end foreach
 
      }//end method
 
      private bool UpdateListItem(SPListItem item, BusinessDataField bizDataField, 
         LobSystemInstance _lobSysInst, Entity _entity, 
         Microsoft.Office.Server.ApplicationRegistry.MetadataModel.View view, 
         string[] secondaryBdcFieldNames, string[] secondaryWssFieldNames) 
      {
         bool success = false;
         string bdcFieldName = bizDataField.BdcFieldName; 
         string encodedId = null; 
         object[] objArray; 
         IList<object> identifierValues = null; 
         object[] objArray2 = null; 
         List<Field>.Enumerator enumerator; 
 
         encodedId = (string)item[bizDataField.RelatedField];
 
         try
         {
            if (encodedId != null)
            {
               objArray = EntityInstanceIdEncoder.DecodeEntityInstanceId(encodedId);
               identifierValues =
                  _entity.FindSpecific(objArray, _lobSysInst).GetIdentifierValues();
               objArray2 = new object[identifierValues.Count];
            }
 
            for (int i = 0; i < identifierValues.Count; i++)
            {
               objArray2[i] = identifierValues[i];
            }
 
            item[bizDataField.RelatedField] =
               EntityInstanceIdEncoder.EncodeEntityInstanceId(objArray2);
            enumerator = view.Fields.GetEnumerator();
 
            Microsoft.Office.Server.ApplicationRegistry.Runtime.IEntityInstance instance =
               _entity.FindSpecific(objArray2, _lobSysInst);
 
            Field field;
            string name;
 
            //loop through all of the fields for the current list item.  when the bdc 
            //business data column is found, obtain the most recent data from the data source
            //and update the list item with that value.  similarly, loop through any related
            //fields and refresh them as well
            while (enumerator.MoveNext())
            {
               field = enumerator.Current;
               name = field.Name;
               if (name == bdcFieldName)
               {
                  //this sets the current value for the field from the database via the bdc
                  item[bizDataField.InternalName] =
                     Convert.ToString(instance.GetFormatted(field));
                  item.Update();
               }
 
               //loop through the secondary fields to refresh those as well
               for (int i = 0; i < secondaryBdcFieldNames.Length; i++)
               {
                  //if a secondary field is found, update it
                  if (secondaryBdcFieldNames[i] == field.Name)
                  {
                     item[secondaryWssFieldNames[i]] =
                        Convert.ToString(instance.GetFormatted(field));
                     item.Update();
                  }//end if
               }//end for
 
            }//end while
 
            success = true;
            return success;
         }//end try
         catch (Microsoft.Office.Server.ApplicationRegistry.Runtime.ObjectNotFoundException onfex)
         {
            Console.WriteLine(string.Format("{0}.  Stack Trace: {1}",
               "Oops, tried updating a list item with a value from the bdc with data that no longer exists in the entity.  " +
               "This can be caused by creating a list item with a value from the BDC, but later that item no longer exists in the BDC -- " +
               "this app just tried to lookup the most recent display name for that BDC item, but it no longer exists so it threw an error.",
               onfex.StackTrace));
            success = false;
            return success;
         }
         catch (Exception genericErr)
         {
            Console.WriteLine(string.Format("{0}.  Error Message: {1}  Stack Trace: {2}",
               "Oops, an unexpected error occured.  The details follow.\n",
               genericErr.Message,
               genericErr.StackTrace));
            success = false;
            return success;
         }
 
      }//end method
 
   }//end class
}//end namespace