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
}

Programmatically set a BDC column

********** UPDATE **********

I've created a new post as a follow up to this article. It contains C# code for a class I created to work with business data in SharePoint 2007. There is a public method in there called "SetFieldByID" which will set the value of a BDC column including all auxiliary fields.

Click here to view the post.

*****************************


Finally!

I spent way too much time playing around with this one... There is no documentation, and very little discussion about it in the forums. All I wanted to do was be able to set the value(s) of a Business Data Catalog field programatically through the SharePoint object model.

Sounds simple enough right?

Not quite....

The first thing to note is that you can't just set the value of the column like you would do with other fields like so:

mySPListItem["myBDCField"] = 123;

If you do this, you won't get an error, but the value will not be set...

This reference was a useful start: http://www.portalsolutions.net/Blog/Lists/Posts/Post.aspx?ID=18. However, I still encountered some challenges in setting a BDC column value.

First of all, the EntityInstanceIdEncoder class is found in the following namespace:
Microsoft.Office.Server.ApplicationRegistry.Infrastructure;
The referenced dll is found in the ISAPI folder on the SharePoint server:
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI

Now for some code...

To check if a field is a BDC field:

if(spItem.Fields["SomeField"].TypeAsString == "BusinessData")


To get the BDC field:
SPField bdcField = spItem.Fields[sBDCColumnName];

To get the BDC column entity internal name (this is key):
XmlDocument xmlData = new XmlDocument();
xmlData.LoadXml(bdcField.SchemaXml);
String sEntityName = xmlData.FirstChild.Attributes["RelatedFieldWssStaticName"].Value;

Set the primary key of the BDC field:
spItem[sEntityName] = EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { Value }); - Here you may want to add some logic to cast the the passed value to the proper type.

Set the display value of the column:
spItem[sBDCColumnName] = Value;

You also need to explicitly set the values of any of the additional related fields. These values can be set like any other column:

spItem[ BDCFieldName + ": " + BDCDisplayFieldName ] = "SomeValue";

// ex: spItem["Products: Name"] = "XYZ";

Hope this saves somebody the trouble that I went through to figure this out!

Prevent Microsoft Office from prompting users for credentials

If you're running Windows Vista and you try to open a Microsoft Office 2007 document from a SharePoint 2007 site, you may be prompted for your user credentials every time. This can be extremely annoying and frustrating for clients who use SharePoint as there primary document management system. You'd think you could get around this problem simply by adding the sharepoint url to your list of Trusted Sites, or Intranet Sites in IE, but for many, this is not the case. If your SharePoint url contains a period (ex: http://sharepoint.myorg/, https://sharepoint.myorg.com/, etc), Vista takes the liberty of assuming that it is an Internet site, and not part of the local intranet.

And you know what happens when you assume.... Well, normally you'd make an ass out of you and me, but in Vista's case, you just piss off a lot of people.

Luckily, Microsoft has acknowledged this as being a bug, and has released a fix. Unfortunately, this is a client side fix, so it requires updating all client machines. A pain yes, but as of now, it is the only solution I have found to actually work.


Read the full KB article here:
http://support.microsoft.com/?id=943280

If you have SP1 installed, all that is required is to add a key into the registry at the following location:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WebClient\Parameters

Add a new Multi-String Value key.
Add your site as the key's value.
Reset the Web Client service
Et Voila! (That's French for 'Tadaa' - See, your learning so much!)

If this does not work, make sure your site is added to the list of Intranet sites in IE.

If you do not have SP1 installed then - well you should probably install SP1.... But if that's just not an option, Microsoft has released a Hot Fix for just this problem. It can be found at the url above as part of the KB article.

Pretty straight forward. It's unfortunate that we're forced to implement such a workaround, but that's life. Life is all about repairing Microsoft's mistakes.

I kid...

Or do I?

Controlling the SharePoint Redirection URL

For anybody who has ever tried to customize SharePoint, you may have encountered the following problem:

You create a page which has a link to a list item's EditForm.aspx, NewForm.aspx, or DispForm.aspx. However, if a user clicks one of these links, then subsequently closes that page by pushing Cancel or OK, they are redirected back to the corresponding list or library, rather than to the page they were previously on (i.e. your custom page).

SharePoint uses a querystring argument to control the return url on any given page. By default, this parameter is set to the respective list or library for the current form.

You can control the return url by modifying or adding this parameter to any link.

Example:

<a href="/Lists/MyList/NewForm.aspx?Source=/Pages/MyCustomPage.aspx">Click Me!</a>

When the link is clicked the user will be directed to the new form for "MyList". After clicking OK or Cancel, they will be redirected back to "MyCustomPage.aspx".

Simple enough, right? Fine, let's dig a little deeper.

This works great if you are creating your own link through a Content Editor WebPart or SharePoint designer, but what about if you are using one of the List web parts, and want to control the redirection for the 'Add new item' link, or some other content you can't modify directly.

This is where we get to hack out a solution in JavaScript.

To put it simply, you can create a custom JavaScript function to append the "Source" querystring argument to the desired links.

For example, put the following into a text file, and save it as AddSourceUrlToLinks.js:

**************************************************************************

//Tell SharePoint to run our function when the page loads
_spBodyOnLoadFunctionNames.push("appendSourceUrlToLinks");

//The function that appends the Source url to links
function appendSourceUrlToLinks()
{
//Collect all links on the page
var links = document.getElementsByTagName("a");

//Loop through the links
for (var curLink=0; curLink <>0
links[curLink].href.indexOf("EditForm.aspx")>0
links[curLink].href.indexOf("DispForm.aspx")>0)
{

//optional, ignore links that already have a source url
if(links[curLink].href.indexOf("Source=") <=0) { if(links[curLink].href.indexOf("?")>0)
links[curLink].href = links[curLink].href + "&Source=" + escape(window.location);

else
links[curLink].href = links[curLink].href + "?Source=" + escape(window.location);

}
}
}
}

**************************************************************************

Save this file in the following location on the SharePoint server:

C:\Program Files\Common Files\Microsoft Shared\Web Server Extenstions\12\Template\Layouts\1033

The final step is to add a javascript reference to the custom page via SharePoint Designer (unfortunately, I don't think there is any other way...).

Find the following asp tag in the code of your custom page:

<asp:Content ContentPlaceHolderId="PlaceHolderMain" runat="server">

Add the following JavaScript reference directly below it:

<script type="text/javascript" src="/_layouts/1033/custom_webpart_script.js"></script>

Save your page, and giv'r a go.

That's it! Obviousely you can customize the code to your liking. Play around with it. I have also used a similar method to append other querystring arguments to pages that use a querystring filter.

My First Blog

Hello!

Finally my first blog!

Hopefully I will find the time to post useful articles here.

I am a software developper / technical analyst for a small software company in Ottawa, Ontario, Canada. We are a Gold Certified Microsoft partner, and I specialize in C# .net, and SharePoint development.