How to get connection string from BDC and use ADO.NET in your queries ?

The SharePoint BDC is great in a sense that once you target your data to be displayed, the end user can create and format many reports and data grids at his own convenience, however there is a price to pay for that: performance.
The operations executed via BDC can be extremely slow. Just so you know I will talk about a project I had to implement and how an alternative solution can be put in place to maximize the overall performance.
The task was to is to hit a specific database and bring a whole set of records in order to build a report. For many architectural reasons particular to the project, the BDC approach had to be used for the task.
The SharePoint web site makes the request to the BDC, the BDC then drags the data out of the external database and then this data is manipulated and displayed back in the end user report page. The transaction is much like the picture below:

In this specific case, the whole task takes approximately 6 minutes. Yeah, I know. Painful.
Let's run a profiler against that page and see what it tells about it. (I will blur all text which might contain sensitive information about the client.)

Well, once we have analyzed that data in our hands outlining all the calls being executed during this specific SharePoint's page  life cycle and what's going on behind the scenes we can draw a few conclusions and the most important conclusion here is that 2 single calls are responsible for almost 99% of the total execution time. Now that an impressive bottleneck.
Let's dive a bit further on it into the internal calls and we can identify the very single calls responsible for these times.

 

 

When we go back to the source code to take a look at them, it turns out that they were the ones connecting and loading the data from the BDC.
The world would be perfect if we only could have done things our way, but unfortunately due to requirements restrictions this can not be changed.
As a good exercise I made myself a mirror copy of this environment to test a theory. What if we change the approach to loading data from the BDC?
The idea is to connect to the BDC and only extract the necessary connection string to the external data source and from there I would load the data via ADO.Net. That would be great because :

• we are not performing any breaking changes in the current structure
• the BDC still plays the game
• All the permissions and security levels are still managed by the BDC definition
• I have an opportunity to retrieve data from an external source much faster than via BDC

 

• 1 and 2 we would get the BDC connection string.
• 3 and 4 we will query the external database and get the data displayed on screen. 

Using the properties in the code below you can get the properties returned from the BDC catalog and amongst them you can see the returned connection string with all the permissions etc for your use in your SharePoint code. Neat!
Note that the code is also using Entity Framework.

   25         public static void SetSharedServiceProvider(SPSite site)

   26         {

   27             try

   28             {

   29                 if (site != null)

   30                 {

   31                    

   32                     SqlSessionProvider.Instance().SetThreadLocalSharedResourceProviderToUse(

   33                         ServerContext.GetContext(site));

   34                 }

   35             }

   36             

   37             catch (Exception)

   38             {

   39                 // Ignore the exception if a provider

   40                 // is already set.

   41             }

   42             

   43         }

   44 

   45         public static EntityConnection GetOnePortalConnectionString(SPSite site)

   46         {

   47             const string instanceName = "TEST_Instance";

   48             SetSharedServiceProvider(site);

   49             LobSystemInstance instance = ApplicationRegistry.GetLobSystemInstances()[instanceName];

   50             var properties = instance.GetProperties();

   51 

   52            

   53             return GetEntityConnection(GetProperty(properties, "CONN Data Source"),

   54                                        GetProperty(properties, "CON Initial Catalog"));

   55         }

Once implemented the results are impressive. The page that use to load in minutes now takes a couple of seconds to run.
Now moving from the current scenario to the improved version is easier said than done and the lesson learned here is that BDC can be as great as mush as it can be plain dangerous to kill an application.
See you later,

By Edge Pereira