Working with the SharePoint 2007 Business Data Catalog Programmatically

--EDIT ----------------------------------------------------------------------------------
The methods below are compatable with MOSS 2007. The code has been reworked by Jasper Siegmund to work with SharePoint 2010. His blog and code can be viewed here: http://jsiegmund.wordpress.com/2010/12/03/sp2010-setting-bcs-column-and-related-fields/
-------------------------------------------------------------------------------------------

A few years back I created a post on how to programmatically update the value of a Business Data Column in SharePoint 2007. I wanted to provide a follow up to that article as I have since developed more methods of working with business data. The following code is a class that I developed to work with business data and business data columns. It's not very well commented but hopefully it is clear enough for someone to take the bits and pieces they need to develop their own solutions.

using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Xml;
using Microsoft.Office.Server;
using Microsoft.Office.Server.ApplicationRegistry.Infrastructure;
using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;
using Microsoft.Office.Server.ApplicationRegistry.Runtime;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Portal.WebControls;



public class SPBusinessData
{
#region ********** Public Static Methods **********

///

/// Searches the specified BDC entity and returns the results in a DataTable
///

public static DataTable GetItems(SPSite oSite, string sInstanceName, string sEntityName, string sSearchString)
{
SetServerContext(oSite);

LobSystemInstance oLobInstance = GetLobSystemInstance(sInstanceName);
Entity oEntity = GetEntity(oLobInstance, sEntityName);
IEntityInstance oEntityInstance = null;

//Search the entity
FilterCollection fc = oEntity.GetFinderFilters();
if (fc[0] is WildcardFilter)
{
((WildcardFilter)fc[0]).Value = "%" + sSearchString + "%";
}
else if (fc[0] is ComparisonFilter)
{
((ComparisonFilter)fc[0]).Value = sSearchString;
}

IEntityInstanceEnumerator prodEntityInstanceEnumerator = oEntity.FindFiltered(fc, oLobInstance);
string[] sFields = GetFields(oSite, sInstanceName, sEntityName);

DataTable oData = new DataTable();
oData.TableName = oEntity.Name;

foreach (string sField in sFields)
{
oData.Columns.Add(sField);
}

while (prodEntityInstanceEnumerator.MoveNext())
{
oEntityInstance = prodEntityInstanceEnumerator.Current;
DataTable dtData = oEntityInstance.EntityAsFormattedDataTable;
oData.Rows.Add(dtData.Rows[0].ItemArray);
}

string sIDColumn = GetIdentifierName(oLobInstance, oEntity);
oData.Columns[sIDColumn].SetOrdinal(0);

return oData;
}

///

/// Retrieves the item(s) with the specified ID
///

public static DataTable GetItemsFromID(SPWeb oWeb, string sID, string sInstanceName, string sEntityName)
{
SetServerContext(oWeb.Site);
LobSystemInstance oLobInstance = GetLobSystemInstance(sInstanceName);
Entity oEntity = GetEntity(oLobInstance, sEntityName);

IEntityInstance oEntityInstance = null;
DataTable dtBDCData = null;

if (EntityInstanceIdEncoder.IsEncodedIdentifier(sID))
{
oEntityInstance = GetBDCItemFromEncodedID(sID, oEntity, oLobInstance);
}
else
{
object oID = GetTypedIDValue(sID, oEntity);
string sEncodedIdentifier = EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { oID });
if (EntityInstanceIdEncoder.IsEncodedIdentifier(sEncodedIdentifier))
{
oEntityInstance = oEntity.FindSpecific(new object[] { oID }, oLobInstance);
}
}

if (oEntityInstance != null)
{
dtBDCData = GetBDCData(oEntityInstance);
}

return dtBDCData;
}

///

/// Retrieves the list of fields that the specified BDC entity is configured to return
///

public static string[] GetFields(SPSite oSite, string sInstanceName, string sEntityName)
{
string[] sFields;

SetServerContext(oSite);

LobSystemInstance oLobInstance = GetLobSystemInstance(sInstanceName);
Entity oEntity = GetEntity(oLobInstance, sEntityName);
TypeDescriptorCollection oDescriptors = oEntity.GetSpecificFinderMethodInstance().GetReturnTypeDescriptor().GetChildTypeDescriptors()[0].GetChildTypeDescriptors();

List oFields = new List();
foreach (TypeDescriptor oType in oDescriptors)
{
if (oType.ContainsLocalizedDisplayName())
{
oFields.Add(oType.GetLocalizedDisplayName());
}
else
{
oFields.Add(oType.Name);
}
}

sFields = oFields.ToArray();

return sFields;
}

///

/// Sets a BDC field on a list item, given the item's encoded or unencoded ID
///

public static void SetFieldByID(SPListItem oListItem, String sColumnName, string sID)
{
BusinessDataField oBDCField = (BusinessDataField)oListItem.Fields.GetFieldByInternalName(sColumnName);
String sEntityName = oBDCField.EntityName;
String sInstanceName = oBDCField.SystemInstanceName;

SetServerContext(oListItem.ParentList.ParentWeb.Site);

LobSystemInstance oLobInstance = GetLobSystemInstance(sInstanceName);
Entity oEntity = GetEntity(oLobInstance, sEntityName);
IEntityInstance oEntityInstance = null;

if (EntityInstanceIdEncoder.IsEncodedIdentifier(sID))
{
//the key is already encoded
object[] oIDList = EntityInstanceIdEncoder.DecodeEntityInstanceId(sID);
oEntityInstance = oEntity.FindSpecific(oIDList[0], oLobInstance);
oListItem[oBDCField.RelatedField] = sID.ToString();
}
else
{
object oID = GetTypedIDValue(sID, oEntity);
string sEncodedIdentifier = EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { oID });
if (EntityInstanceIdEncoder.IsEncodedIdentifier(sEncodedIdentifier))
{
oEntityInstance = oEntity.FindSpecific(new object[] { oID }, oLobInstance);
oListItem[oBDCField.RelatedField] = sEncodedIdentifier;
}
}

if (oEntityInstance != null)
{
SetSecondaryFields(oListItem, oBDCField, oEntityInstance);
}
}

///

/// Returns the name of the identifier field for the given entity
///

public static string GetIdentifierName(SPSite oSite, string sInstanceName, string sEntityName)
{
SetServerContext(oSite);
LobSystemInstance oLobInstance = GetLobSystemInstance(sInstanceName);
Entity oEntity = GetEntity(oLobInstance, sEntityName);
return GetIdentifierName(oLobInstance, oEntity);
}

#endregion

#region ********** Private Static Methods **********

private static string GetIdentifierName(LobSystemInstance oLobInstance, Entity oEntity)
{
return oEntity.GetIdentifiers()[0].Name.Replace("[", string.Empty).Replace("]", string.Empty);
}

private static void SetServerContext(SPSite oSite)
{
try
{
ServerContext oContext = ServerContext.GetContext(oSite);
SqlSessionProvider.Instance().SetSharedResourceProviderToUse(oContext);
}
catch { }
}

private static Entity GetEntity(LobSystemInstance oLobInstance, string sEntityName)
{
Entity oEntity = oLobInstance.GetEntities()[sEntityName];
return oEntity;
}

private static LobSystemInstance GetLobSystemInstance(string sInstanceName)
{
NamedLobSystemInstanceDictionary oLobInstanceDic = ApplicationRegistry.GetLobSystemInstances();
LobSystemInstance oLobInstance = oLobInstanceDic[sInstanceName];;

return oLobInstance;
}

private static IEntityInstance GetBDCItemFromEncodedID(string sEncodedID, Entity oEntity, LobSystemInstance oLobInstance)
{
object[] oIDList = EntityInstanceIdEncoder.DecodeEntityInstanceId(sEncodedID);
return oEntity.FindSpecific(oIDList[0], oLobInstance);
}

private static DataTable GetBDCData(IEntityInstance oEntityInstance)
{
DataTable dtBDCData = oEntityInstance.EntityAsFormattedDataTable;
dtBDCData.TableName = oEntityInstance.Entity.Name;

TypeDescriptorCollection oDescriptors = oEntityInstance.Entity.GetSpecificFinderMethodInstance().GetReturnTypeDescriptor().GetChildTypeDescriptors()[0].GetChildTypeDescriptors();
foreach (TypeDescriptor oType in oDescriptors)
{
if (oType.ContainsLocalizedDisplayName())
{
if (dtBDCData.Columns.Contains(oType.Name))
{
dtBDCData.Columns[oType.Name].ColumnName = oType.GetLocalizedDisplayName();
}
}
}

//Set the ID column to ordinal 0
string sIDColumn = oEntityInstance.Entity.GetIdentifiers()[0].Name.Replace("[", string.Empty).Replace("]", string.Empty);
dtBDCData.Columns[sIDColumn].SetOrdinal(0);

return dtBDCData;
}

private static IEntityInstance GetEntityInstanceFromID(string sID, Entity oEntity, LobSystemInstance oLobInstance)
{
IEntityInstance oEntityInstance = null;
IdentifierCollection oIDCollection = oEntity.GetIdentifiers();
String sIdentifierType = oIDCollection[0].IdentifierType.Name.ToLower().Replace("system.", String.Empty);
object oID = GetTypedIDValue(sID, oEntity);

if (oID != null)
{
oEntityInstance = oEntity.FindSpecific(oID, oLobInstance);
}

return oEntityInstance;
}

private static object GetTypedIDValue(string sID, Entity oEntity)
{
IdentifierCollection oIDCollection = oEntity.GetIdentifiers();
String sIdentifierType = oIDCollection[0].IdentifierType.Name.ToLower().Replace("system.", String.Empty);
object oID = null;

//find the instance value based on the given identifier type
switch (sIdentifierType)
{
case "string":
oID = sID;
break;
case "datetime":
oID = DateTime.Parse(sID, CultureInfo.CurrentCulture);
break;
case "boolean":
oID = Boolean.Parse(sID);
break;
case "int32":
oID = Int32.Parse(sID);
break;
case "int16":
oID = Int16.Parse(sID);
break;
case "double":
oID = Double.Parse(sID);
break;
case "char":
oID = Char.Parse(sID);
break;
case "guid":
oID = new Guid(sID);
break;
default:
oID = sID;
break;
}

return oID;
}

private static void SetSecondaryFields(SPListItem oListItem, BusinessDataField oBDCField, IEntityInstance oEntityInstance)
{
DataTable dtBDCData = oEntityInstance.EntityAsFormattedDataTable;

//Set display field
oListItem[oBDCField.Id] = dtBDCData.Rows[0][oBDCField.BdcFieldName].ToString();

//Setup localized display names
TypeDescriptorCollection oDescriptors = oEntityInstance.Entity.GetSpecificFinderMethodInstance().GetReturnTypeDescriptor().GetChildTypeDescriptors()[0].GetChildTypeDescriptors();
foreach (TypeDescriptor oType in oDescriptors)
{
if (oType.ContainsLocalizedDisplayName())
{
if (dtBDCData.Columns.Contains(oType.Name))
{
dtBDCData.Columns[oType.Name].ColumnName = oType.GetLocalizedDisplayName();
}
}
}

string[] sSecondaryFieldsDisplayNames = oBDCField.GetSecondaryFieldsNames();
string[] sSecondaryFieldsInternalNames = GetSecondaryInternalFieldNames(oBDCField);

for (int i = 0; i < sSecondaryFieldsDisplayNames.Length; i++)
{
Guid gFieldID = oListItem.Fields.GetFieldByInternalName(sSecondaryFieldsInternalNames[i]).Id;
oListItem[gFieldID] = dtBDCData.Rows[0][sSecondaryFieldsDisplayNames[i]].ToString();
}
}

private static string[] GetSecondaryInternalFieldNames(BusinessDataField oBDCField)
{
XmlDocument xmlData = new XmlDocument();
xmlData.LoadXml(oBDCField.SchemaXml);
String sFieldsString = xmlData.FirstChild.Attributes["SecondaryFieldsWssStaticNames"].Value;

return sFieldsString.Split(':');
}

#endregion
}