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.
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.