Programatically adding SPListItems with a BCS External Column.


In this blog I’m going to show you how to add an item to a list that contains a BCS External column. This is something I have struggled with, read many other blogs on the subject but for some reason never got it working 100%. I’m not going to show you how to set up the Business Connectivity Service, as I assume that you are reading this post because you have already done that and are ready to add your first item programmatically. Hopefully you have already tested that you can add an item via the UI GUI.

Basic Set up

Using SharePoint designer and the Adventure Works database, I created an External Content Type on the HumanResource.Department table. Included a Comparision filter on the Name column. On my SharePoint test team site, I have created a Generic List, called this list Employee, renamed the title column ‘Employee’, and added the external column called Department to my list to also include the GroupName.

The Code

To do demo this, I’m using a simple console application. First I’ve created an EmployeeData class, add a couple of employees, and passed the list to my InsertingBCSData class.

using System;
using System.Collections.Generic;
using Microsoft.SharePoint;
namespace InsertingBCSData
{
    public class EmployeeData
    {
        public String Employee { get; set; }
        public String Department { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var employees = new List<EmployeeData>
                                {
                                    new EmployeeData {Employee = "Cann0nF0dder", Department = "Sales"},
                                    new EmployeeData {Employee = "DiePiggyDie", Department = "Finance"}
                                };

            InsertingBcsData(employees);

            Console.WriteLine("****Complete****");
            Console.ReadLine();

        }
    }
}

The method InsertingBcsData isn’t where the magic happens, it is just a standard AddItem() to SPListItem.

private static void InsertingBcsData(List<EmployeeData> employeeItems)
       {
            using (SPSite site = new SPSite("http://cannonfodder.local/sites/TestTeamSite"))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    SPList list = web.Lists.TryGetList("Employee");
                    if (list == null)
                    {
                        Console.WriteLine("Please check your list name and try running this program again.");
                        return;
                    }
                    int itemNumber = 0;
                    web.AllowUnsafeUpdates = true;
                    foreach (var empItem in employeeItems)
                    {
                        itemNumber++;
                        try
                        {
                            SPListItem item = list.AddItem();
                            item["Title"] = empItem.Employee;
                            BCSData.SetGivenBcsColumn(item, "Department", empItem.Department, site);
                            item.Update();
                            Console.WriteLine(String.Format("Updated item {0} of {1}. ({2:0.00}%) ", itemNumber, employeeItems.Count, ((double)itemNumber / (double)employeeItems.Count) * 100));
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(String.Format("Unable to read in item {0} : {1}", itemNumber,
                                                            empItem.Employee));
                            Console.WriteLine("");
                        }
                    }
                    web.AllowUnsafeUpdates = false;
                }
            }
        }

In the static class BCSData I’m performing the insert to the BCS external column. The SetGivenBcsColumn accepts 4 parameters. The current SPListItem, the internal column name of the item to update, in our case the External column, the value to display in the display column, and the current SPSite. The following code obtains the RelatedFieldWssStaticName, which is the key to getting the BCS working. This field is hidden on your list and contains the BdcIdentity value for the item.

The call to GetIDByName is slightly overkill for what we are doing here. The method performs a comparision filter in the BCS and returns the entire row data for the given item, here we only need the BdcIdentity value. However, if you wish to take this code further, because it brings back all columns for the given item, you could update the extra columns too that are on your list. In my example that would be the Department: GroupName column. The reason why I don’t update this column in code, is because after I have done the import, by going to the list, I can click the little refresh icon above the Department column, and assuming I have hooked up the BdcIdentity correctly, these extra fields will be pulled back by the UI.

using System;
using System.Data;
using System.Xml;
using Microsoft.BusinessData.MetadataModel;
using Microsoft.BusinessData.Runtime;
using Microsoft.SharePoint.BusinessData.Runtime;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.BusinessData.SharedService;

namespace InsertingBCSData
{
    public static class BCSData
    {
        public static void SetGivenBCSColumn(SPListItem listItem, String internalColumnName, String displayValue, SPSite site)
        {

           //Convert the field into a SPBusinessDataField, this is what the External BCS Column known as.
            SPBusinessDataField bcsField = listItem.Fields[internalColumnName] as SPBusinessDataField;
           //If field equal null, then cannot perform update
            if (bcsField != null)
            {
               //Read in the field schema so that we can obtain extra values to perform a query against the BCS.
                XmlDocument xmlData = new XmlDocument();
                xmlData.LoadXml(bcsField.SchemaXml);
                //This get the ID Field in the list for the BCS
                string entityStaticName = xmlData.FirstChild.Attributes["RelatedFieldWssStaticName"].Value;
                string entityNameSpace = xmlData.FirstChild.Attributes["EntityNamespace"].Value;
                string entityname = xmlData.FirstChild.Attributes["EntityName"].Value;

                //Makes a call to the BCS Service and returns a datatable of results.
                var datatable = GetIDByName(entityNameSpace, entityname, displayValue, site);

                //As we are doing a comparison filter, there should be only one row of data, therefore we can grab the BdcIdentity from the first row.
                if (datatable != null)
                    listItem[entityStaticName] = datatable.Rows[0]["BdcIdentity"];

                //Set the field Display Value, you can set this to say anything, because when list is refreshed from the Department column, it will get updated from the BCS,
                //An idea might be to set the display value to "Refresh me", to tell the user to do this first time they hit the list after you have inserted all the data.
                listItem[internalColumnName] = displayValue;
            }
        }

          public static DataTable GetIDByName(string nameSpace, string entityName, string entityValue, SPSite site)
        {
            BdcService service = SPFarm.Local.Services.GetValue<BdcService>();
            //Could use SPServiceContext.Current instead of using the next line in a non console application.
            SPServiceContext context = SPServiceContext.GetContext(site);

//Found this line was required otherwise would fail with SSO.
SPServiceContextScope contextScope = new SPServiceContextScope(context);

IMetadataCatalog catalog = service.GetDatabaseBackedMetadataCatalog(context);
 IEntity entity = catalog.GetEntity(nameSpace, entityName);

 ILobSystemInstance lobSystemInstance = entity.GetLobSystem().GetLobSystemInstances()[0].Value;
 IFilterCollection filters = entity.GetDefaultFinderFilters();

 //set the wildcard filter value
 if (!string.IsNullOrEmpty(entityValue) && filters.Count != 0)
 {
 if (filters[0].GetType().Name == "ComparisonFilter")
 {
 ComparisonFilter filter = (ComparisonFilter)filters[0];
 filter.Value = entityValue;
 }
 else
 {
 //Added this for reference, and filter value is against a SQL datasource.
 WildcardFilter filter = (WildcardFilter)filters[0];
 filter.Value = "%" + entityValue + "%";
 }

 IEntityInstanceEnumerator enumerator = entity.FindFiltered(filters, lobSystemInstance);
 DataTable table = null;
 int limit = 0;
 while (enumerator.MoveNext())
 {
 //first time setup the datatable, evertytime there after add a row.
 if (table == null)
 table = enumerator.Current.EntityAsDataTable;
 else
 table.ImportRow(enumerator.Current.EntityAsDataTable.Rows[0]);

 limit++;
 //If more than 2000 rows in the filter it will fall over when trying to enumerate the 2001 item, we only need the first item in this example
 // so limit set for 2, header and first row of data..
 if (limit >= 2)
 break;
 }
 return table;
 }
 return null;
 }
 }
}

So now the code is complete we can run the console application. After running and checking the list you will see the items added to the list.

Now to update the Department: GroupName, just click the refresh icon next to Department, follow the click through instructions.

Advertisements

One thought on “Programatically adding SPListItems with a BCS External Column.

  1. Pingback: Nova Tech Consulting Blog | Programatically adding SPListItems with a BCS External Column.

Comments are closed.