One of the many features of the Business Data Catalog (BDC) is the ability to pull data from external databases or Web Services and cache this data within columns in a SharePoint list.  This is accomplished using a Business Data column.  While this does offer many advantages, one of the drawbacks is how to populate the list with items that point to the corresponding row in your external system.

Let me walk through what I mean.  Let’s say I have a contact list in SharePoint that stores some basic information about customer points of contact.  In it I want to store the contact’s name, the company name, phone number and address.  It idea is to have it look something like this:

Let’s say that much of this information lives in a CRM system, so it would be a lot of duplicate work to maintain both sets of information.  Specifically, I want to pull the company name, address, city and state from CRM and align it with my contact and his/her phone number.  This is where a Business Data column comes in very handy as it allows you to merge in data from an external system and marry up with SharePoint content.

So, we proceed in adding a Business Data column to the list and removing our duplicate columns.  (For more details on how to do this, check out the second half of my article on SharePoint magazine linked here.)  After adjusting the All contacts view, here is the empty list that is ready to populate:

This is where the trouble lies.  For me to work with this, I need to create a new item for every contact, and this can be tedious.  And for each contact add, I have to then match it up to the company.

What would be great is if the empty list of customers could pre-populated with current customers from CRM.  All I would then need to do is quickly edit each one to add the contact name and phone number.  That’s what we’re talking about in this post: a simple console app to pull the current customers defined in BDC, create and link each one up in this contact list.

I have already written the code, so let’s see how it works.  First off, we need to get our project references set.  This solution will require three specific references: Microsoft.Office.Server.dll, Microsoft.SharePoint.dll, and Microsoft.SharePoint.Portal.dll.  I have also added these using clauses to the top of my C# application:

using Microsoft.SharePoint;
using Microsoft.Office.Server.ApplicationRegistry.Runtime;
using Microsoft.Office.Server.ApplicationRegistry.Infrastructure;
using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;

SPSite site = new SPSite("http://portal.synergy.com");
SPWeb web = site.OpenWeb("teamsite");
SPList customerList = web.Lists["Customers"];

Once I have my SPList handle, I then need to connect to BDC and execute the method instance which will return all customers.  Here is the code for that:

//Connect to named SSP
SqlSessionProvider.Instance().SetSharedResourceProviderToUse("DefaultSSP");

//Connect to our LOBSystemInstance
LobSystemInstance synergyData = ApplicationRegistry.GetLobSystemInstanceByName("SynergyData");

//Connect to our customers entity
Entity customers = synergyData.GetEntities()["Customers"];

//Connect to our finder method for customers
MethodInstance getCustomers = customers.GetFinderMethodInstance();

//Execute finder method
IEntityInstanceEnumerator results = (IEntityInstanceEnumerator ) customers.Execute(getCustomers, synergyData);

The results variable shown in the last line above is our returned collection of rows.  This IEntityInstanceEnumerator object works like an ADO.NET DataReader, and it is very easy to iterate through the rows.  What we now need to do for each row is create the item and link it to the matching row for this entity.  Here is the code to do that:

//iterate through each row returned and add a new item
while (results.MoveNext())
{
    SPListItem item = customerList.Items.Add();
    item["Customers_ID"] = EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { results.Current["CustomerID"].ToString() });
    item.Update();
}

<Identifiers>
    <Identifier TypeName="System.String" Name="CustomerID" />
</Identifiers>

For an item in SharePoint to match up to the corresponding row in the database, it need an specially encoded form of the identifier’s value.  We use the EncodeEntityInstanceId method to encode the CustomerID column value into the correct format.  If you’re curious, here is how the encoded form looks: __bk4100c4001400a5009500b400.  There is also a decode method called DecodeEntityInstanceId, but we don’t need to use it here.  If we had multiple columns for the identifier, we would just need to list each one for the object[] array that the encode method uses as its input parameter.  Overall, it’s pretty simple.

The last bit of code is just our usual housekeeping for the SPSite and SPWeb objects.

//release memory for site and web objects           
site.Dispose(); 
web.Dispose();

Now that the code is done, we run the program, which creates the new items.  Here is how it initially looks:

That doesn’t look good—it looks like we have no data loaded.  This is expected since we only populated the Customer_ID link field.  If we click the refresh icon next to the Company column header, it looks much better since this is what copies and stores the current data through the BDC into the list.

At this point, we can just edit the list in datasheet mode as shown here to quickly fill in the contact name and phone number:

With that, I hope you have a better understanding of how Business Data columns are added to a list and how you can automatically populate a list based on content from your Business Data Catalog.  For those running this application, keep in mind that it must run directly on a SharePoint Web Front End since it uses the object model to connect.

Happy Holidays!