Implementing BCS Solutions using Stored Procedures

Recently I had a client request a BCS solution to provide custom forms to enable customers to manage their profiles and allow employees and other customers to be able to view those profiles. They wanted a BCS solution instead of a traditional web application with a database backend in order to take advantage of SharePoint Enterprise Search. This approach would allow users to be able to search against any customer information that is stored in SQL Server. In addition, they wanted a SharePoint solution package so that they can deploy/redeploy to multiple environments (Integration, QA, and Production).

Customer’s profile information was in tables but not in a form that was directly meaningful to end users (for example they have different types of contact information as different records in the same table and used as lookups). In addition, some of this data was imported from other systems by timer jobs and triggers. The solution was to abstract the data for the BCS by using stored procedures. So our challenge was to develop a BCS solution package(s) based SQL Server stored procedures.

Sounds reasonable…

If you have read any of the marketing materials on the BCS, you would think that the BCS is most ideal way to achieve the client’s goals. SharePoint articles and books would then state that you can create External Content Types (ECTs) and External lists in SharePoint Designer (SPD) and you can be done in an hour or two. To access this data, you can create web parts that use the SharePoint API to access the External lists just like any other SharePoint list.

Easy, right? Well, let’s explore some of the issues that are common to all BCS Solutions and then take a further look at the issues that this particular approach brings up.  For each issue, I will explain our design decisions and the final solution.

Issue 1: Authenticating a BCS Solution to an External System

Issue 2: Defining and developing your BCS entities into ECTs

Issue 3: BDC Models, Resource files, and making Content Types Searchable

Issue 4: How to develop and deploy the ECTs/BDC Models to multiple environments

Issue 5: Implementing ECTs in SPD using Stored Procedures

Leave a Reply

Fill in your details below or click an icon to log in:

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