Get and Set External Data / BCS column in SharePoint List

Following from my previous article to configure filtering in Business Connectivity Services, I would like to show you how to get and set External Data / BCS column in SharePoint List.

Get External Data column

private void GetItemByIndex(SPList mylist, int index)
{
    try
    {
        if(mylist.Items.Count < index)
           return;

        SPListItem existingItem = mylist.Items[index];
        string title = existingItem["Title"].ToString();
        string networkAffiliate = existingItem["NetworkAffiliate"].ToString();
        // If you select networkf affiliate Id column to display in external data column (NetworkAffiliate)
        string networkAffiliateId= existingItem["NetworkAffiliate_x003a__x0020_Id"].ToString();
    }
    catch (Exception ex)
    {
        // Error Exception handling
    }
}

Set External Data column
To set the actual external data column itself is straight forward, but it is more complicated to set the secondary external data columns if you included it in external data column settings.

private void InsertNewItem(SPList mylist, string title, string valueId)
{
    try
    {
        SPListItem newItem = mylist.Items.Add();
        newItem["Title"] = title;
        SPBusinessextdataField extdataField = newItem.Fields["NetworkAffiliate"] as SPBusinessextdataField;
        IEntityInstance entityinst = GetEntityInstance(extdataField, valueId, mylist.ParentWeb.Site, newItem);
        SetSecondaryFields(newItem, extdataField, entityinst);
        newItem.Update();
    }
    catch (Exception ex)
    {
        // Error Exception handling
    }
}

private IEntityInstance GetEntityInstance(SPBusinessDataField dataField, string entityId, SPSite site, SPListItem item)
{
    IEntityInstance entInstance = null;
    try
    {
        IEntity entity = GetEntity(site, dataField);
        ILobSystemInstance LobSysteminstance = entity.GetLobSystem().GetLobSystemInstances()[0].Value;

        // Get methods collection 
        foreach (KeyValuePair<string, IMethod> method in entity.GetMethods())
        {
            // Get current method’s instance 
            IMethodInstance methodInstance = method.Value.GetMethodInstances()[method.Key];
            // Execute specific finder method. 
            if (methodInstance.MethodInstanceType == MethodInstanceType.SpecificFinder)
            {
                Identity id = null;

                if (EntityInstanceIdEncoder.IsEncodedIdentifier(entityId))
                {
                    object[] oIDList = EntityInstanceIdEncoder.DecodeEntityInstanceId(entityId);
                    id = new Identity(oIDList[0]);

                    // Execute specific finder method and get the entity instance 
                    entInstance = entity.FindSpecific(id, entity.GetLobSystem().GetLobSystemInstances()[0].Value);
                    item[dataField.RelatedField] = entityId.ToString();
                }
                else
                {
                    object oID = GetTypedIDValue(entityId, entity);
                    id = new Identity(oID);
                    string encodedIdentifier = EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { oID });
                    // Execute specific finder method and get the entity instance 
                    entInstance = entity.FindSpecific(id, entity.GetLobSystem().GetLobSystemInstances()[0].Value);
                    item[dataField.RelatedField] = encodedIdentifier;
                }
            }
        }
    }
    catch (Exception ex)
    {
        // Error Exception handling
    }
    return entInstance;
}

private IEntity GetEntity(SPSite site, SPBusinessDataField dataField)
{
    IEntity result = null;
    try
    {
        SPServiceContext context = SPServiceContext.Current; //SPServiceContext.GetContext(site);
        IMetadataCatalog catalog = null;
        BdcService bdcService = SPFarm.Local.Services.GetValue<BdcService>(String.Empty);
        if (null != bdcService && dataField != null)
        {
            catalog = bdcService.GetDatabaseBackedMetadataCatalog(context);
            if (catalog != null)
                result = catalog.GetEntity(dataField.EntityNamespace, dataField.EntityName);
        }
    }
    catch (Exception ex)
    {
        // Error Exception handling
    }
            
    return result;
}

private object GetTypedIDValue(string sID, IEntity oEntity)
{
    object oID = null;
    try
    {
        IIdentifierCollection type = oEntity.GetIdentifiers();
        String sIdentifierType = type[0].IdentifierType.FullName.ToLower().Replace("system.", String.Empty);
                
        //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 "int64":
                oID = Int64.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;
        }
    }
    catch (Exception ex)
    {
        // Error Exception handling
    }
    return oID;
}

private void SetSecondaryFields(SPListItem listItem, SPBusinessDataField dataField, IEntityInstance entityInstance)
{
    try
    {
        // Convert the entity to a formatted datatable
        System.Data.DataTable dtBDCData = entityInstance.EntityAsFormattedDataTable;

        // Set the BCS field itself (Display Value)
        listItem[dataField.Id] = dtBDCData.Rows[0][dataField.BdcFieldName].ToString();

        // Get the specific finder method to get the columns that returns
        IMethodInstance method = entityInstance.Entity.GetMethodInstances(MethodInstanceType.SpecificFinder)[0].Value;
        ITypeDescriptorCollection oDescriptors = method.GetReturnTypeDescriptor().GetChildTypeDescriptors()[0].GetChildTypeDescriptors();

        // Set the column names to the correct values
        foreach (ITypeDescriptor oType in oDescriptors)
        {
            if (oType.ContainsLocalizedDisplayName())
            {
                if (dtBDCData.Columns.Contains(oType.Name))
                {
                    dtBDCData.Columns[oType.Name].ColumnName = oType.GetLocalizedDisplayName();
                }
            }
        }

        // get the secondary field display names; these should be set
        string[] sSecondaryFieldsDisplayNames = dataField.GetSecondaryFieldsNames();

        // loop through the fields and set each column to its value
        foreach (string columnNameint in sSecondaryFieldsDisplayNames)
        {
            Guid gFieldID = listItem.Fields[String.Format("{0}: {1}", dataField.Title, columnNameint)].Id;
            listItem[gFieldID] = dtBDCData.Rows[0][columnNameint].ToString();
        }
    }
    catch (Exception ex)
    {
        // Error Exception handling
    }
}
Advertisements

Tagged: , , , , , ,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: