Consuming Business Data Catalog Entities in Excel Services

One of the coolest (only?) SharePoint external data features is the Business Data Catalog (BDC).

The BDC is brand new with SharePoint 2007, and is not yet fully integrated into Excel and Excel Services... though, if you've read one or two other entries in this blog you've probably figured out where I'm going with this :o)

Here's a pretty simple UDF that will take a BDC instance name, entity name, and will return the data associated with it.

Without further delay... just call the UDF below in an array formula in your workbook as usual (e.g. "{=getBDCData("SharedServices1", "AdventureWorksDWInstance", "Product")}")

PreReq: I used the Adventure Works DW BDC sample from https://msdn2.microsoft.com/en-us/library/ms494876.aspx ... you'll need to set up the BDC yourself as well to use this UDF.

PS: Sorry about the horrible formatting... It's a pain refactoring to get the code to look half-way decent on this blog.

/// <summary>

/// Gets data from the entity of the specified BDC instance.

/// </summary>

/// <param name="SSPName">Shared Services Provider Name</param>

/// <param name="BDCInstanceName">BDC Instance Name</param>

/// <param name="entityName">Entity Name</param>

/// <returns>Results array</returns>

[UdfMethod(IsVolatile = true,

           ReturnsPersonalInformation = true)]

public object[,] getBDCData( string SSPName,

string BDCInstanceName,

string entityName)

{

    object[,] results = null;

SPSite site = null;

 

    try

    {

        // Impersonate the current user viewing EWR.

        // (See previous Blog post on user impersonation)

        using (WindowsImpersonationContext wiContext =

                                           impersonateUser())

        {

            site = new SPSite(serverName);

            try

            {

                // Need to set this so we can grab instance

                SqlSessionProvider.

                Instance().

                SetThreadLocalSharedResourceProviderToUse(

                Microsoft.Office.Server.ServerContext.

                GetContext(site));

            }

            catch (System.Exception ex)

            {

               // Ignore the exception if a provider

               // is already set.

            }

            // Get the sample Adventure Works instance

            // For XML of this instance see:

// https://msdn2.microsoft.com/en-us/

            // library/ms494876.aspx

LobSystemInstance advWorks =

                                ApplicationRegistry.

                                GetLobSystemInstanceByName(

                                             BDCInstanceName);

            // Get the entities

            NamedEntityDictionary entities =

                                advWorks.GetEntities();

            // Get requested entity

            Entity prodEntity = entities[entityName];

            // Get entity Filters

            FilterCollection fc =

                               prodEntity.GetFinderFilters();

           

            // Enumerate results

            IEntityInstanceEnumerator

                               prodEntityInstEnumerator =

                               prodEntity.

                               FindFiltered(fc, advWorks);

            // Convert it to a DataTable

            EntityDataTableAdapter dataAdapter =

  new EntityDataTableAdapter(

      prodEntityInstEnumerator);

            System.Data.DataTable resultTable =

                               dataAdapter.EntitiesAsDataTable;

            // Define dimensions of results array

            results = new object[ resultTable.Rows.Count,

resultTable.Columns.Count];

            // Get the data from the DataTable into object[,]

            int i = 0;

            foreach (System.Data.DataRow currRow in

                                            resultTable.Rows)

            {

                int j = 0;

                foreach (object currField in

                                            currRow.ItemArray)

   {

                    results[i, j] = currField.ToString();

                    j++;

                }

                i++;

            }

        }

    }

    catch (System.Exception ex)

    {

        results = new object[1, 1];

        results[0, 0] = ex.Message;

    }

finally

{

if (site != null)

site.Dispose();

SqlSessionProvider.

Instance().

ResetThreadLocalSharedResourceProviderToUse();

}

 

    return results;

}