Protiviti / SharePoint Blog

SharePoint Blog

December 09
​Working with Business Data Columns in Code

Accessing business data housed in multiple disparate database systems can be a challenging scenario for end users and IT alike. SharePoint has a scalable solution to successfully address these scenarios - Business Connectivity Services.​ 

Introduced with SharePoint 2007, the Business Connectivity Services (Business Data Catalog in 2007) allows you to easily connect backend data sources to your SharePoint solution. You can use this for displaying backend data, tagging your SharePoint listitems, or even writing data to your backend system. All of this can be done through SharePoint Designer and the SharePoint User Interface, but sometimes you’ll want to work with business data in code. Below are some examples of how to accomplish this.

Create a Business Data Column

In order to create a Business Data column in code, you’ll need to know a few things:

      1.       list – The list you where want to create the business data field.

      2.       fieldName – The name you want for your business data field.

      3.       systemInstanceName – Value from this property in your BDCM.

<LobSystemInstance Name="InstanceName">

      4.       entityName and entityNamespace – The name and namespace of the entity you want to point at. Found here in your BDCM:

<Entity Namespace="http://www.site.com" Version="1.0.0.0" Name="MyEntityName">

      5.       bdcFieldPrimary – The field that gets displayed in the business data picker. This should be an easily distinguishable display name for the item. This must be one of the type descriptors returned with your entity definition.

      6.       secondaryFields – Additional fields that get pulled from the type descriptors associated with your entity and stored in the SharePoint item.

Once you know these values, creating the field is very straightforward. We create the field definition, add it to the list, and update it to set the secondary fields.

 

SPBusinessDataField bdcField = new SPBusinessDataField(list.Fields, "BusinessData", fieldName);

bdcField.SystemInstanceName = systemInstanceName;

bdcField.EntityNamespace = entityNamespace;

bdcField.EntityName = entityName;

bdcField.BdcFieldName = bdcFieldPrimary;

bdcField.StaticName = fieldName;

 

list.Fields.AddFieldAsXml(bdcField.SchemaXml);

list.Update();

 

bdcField = (SPBusinessDataField)list.Fields.GetFieldByInternalName(fieldName);

if (secondaryFields != null && secondaryFields.Length > 0)

{

bdcField.SetSecondaryFieldsNames(secondaryFields);

}

bdcField.Update();

 Setting a Business Data Column value

In order to set a Business Data column’s value, you’ll need to know a few things:

      1.       li – The SPListItem that you want to modify.

      2.       fieldName – The name of your business data field.

      3.       displayName – The display name of the selected item.

      4.       keys – The value or values of the Identifiers for this entity instance. For example, if you have a “Product” entity with an identifier of “ProductKey,” this will be the value of the product key for the item you want selected. This value gets encoded by a method in the Microsoft.SharePoint.BusinessData.Infrastructure.EntityInstanceIdEncoder class.

 

SPBusinessDataField field = li.Fields.GetFieldByInternalName(fieldName) as SPBusinessDataField;

li[field.Id] = displayName;

li[field.RelatedField] = EntityInstanceIdEncoder.EncodeEntityInstanceId(keys);

li.Update();

 Getting a Business Data Column Value

This is nearly the reverse of setting the value. You’ll retrieve the encoded value from the SPListItem and pass it to a method for decoding back into your array of identifier values.

SPBusinessDataField field = li.Fields.GetFieldByInternalName(fieldName) as SPBusinessDataField;

string value = li[field.RelatedField].ToString();

object[] identifiers = EntityInstanceIdEncoder.DecodeEntityInstanceId(value);

You can also retrieve the values of the secondary fields. They are stored separately with a field name consisting of <parent Business Data field name>: <secondary field name>. For example, if you had a Business Data column named Product and a secondary field named weight, you could retrieve its value from an SPListItem like this:

string productWeight = li["Product: weight"].ToString();

 Querying on a Business Data Column

You can query a Business Data column like any other column. They will be available as crawled properties in search and you can easily reference them in a CAML SPQuery with their full encoded names. The encoded name will be in the format of <internal Business Data field name>_x003a__x0020_<encoded secondary field name>. For example, if you had a Business Data column with an internal name of productBDC and a secondary field named Product ID, the full encoded name of that secondary field would be productBDC_x003a__x0020_Product_x0020_ID.

 ​

Quick Launch


© Protiviti 2019. All rights reserved.   |   Privacy Policy