Introduction
Anyone familiar with the SharePoint world is sure to have heard about the term BDC or Business Data Catalog. The concept was introduced in SharePoint 2007 and was touted as one of most powerful new additions. Marketing personnel would use the keyword BDC as one of their main selling points of SharePoint 2007. Some other terms used along with BDC included keywords like “No code solutions”, “out of the box solutions…” etc. Sometimes it made me wonder, what “out of the box” really meant; was it included in the “box” or was it NOT included in the “box”, both ways the statement “out of the box” made sense!
Once the mud settled on what BDC meant, how it worked, its limitations, the cost etc, there was some amount of skepticism about its potentials. Although the “No code solutions…” marketing pitch was true, it came with its own limitations. Technically the functionality provided by BDC was always possible through custom solutions and hence there was always a debate between .NET architects and SharePoint architects on the pros and cons of using the BDC.
While BDC in SharePoint 2007 itself provided good benefits, there were some major limitations, technical as well as cost perspective. The toolset that was available from MS to define and configure BDC was not comprehensive. Third party tools were required to utilize the full capabilities of the feature. Another main drawback for BDC was its cost; it was only available with SharePoint enterprise edition and it required some investment in terms of resources to get it working. A pure technical limitation of the BDC was that it did not provide any CRUD (read / write) operations; it only provided a read only view of the data.
What is BCS and why should we consider it?
BCS or Business Connectivity Services is the next generation evolution of Business Data Catalog. You may still here the term BDC in SharePoint 2010, but in the new world BDC stands for Business Data Connectivity, which is essentially the connection portion of the BCS suite of components. BDC in SharePoint 2010 is actually the BDC from SharePoint 2007
The main purpose of this feature is still very much the same as its predecessor; which is to connect with external data sources from within SharePoint, but BCS extends that capability beyond just SharePoint and makes it available to other clients such Microsoft Office. In other words, BCS is a set of components that includes connectors to different data sources and a runtime engine that uses connectors to can connect to the data sources.
BCS attempts to overcome its predecessor’s limitations with a new set of integrated tools (the new SharePoint 2010 designer and Visual Study 2010) and a powerful and extensible platform that provides full CRUD operations. Another major advantage of BCS is that it is built into SharePoint 2010 Foundation, which is the next generation of Windows SharePoint Services. I am expecting the next round of new keywords from solution marketers – “it’s free!!”, which is actually true and the free no code solution can accomplish quite a lot in the new SharePoint world. I will explore this in a more detail in the upcoming sections.
BCS Building Blocks
Now that I have provided my own marketing spiel on BCS lets discuss more about why the new BCS is worth consider. To start let’s take a high level view of the main building blocks.
Figure 1 shows a high level block diagram of BCS and is components
The above diagram presents three different layers of the BCS architecture:
a) Presentation Layer: This layer includes tools that allow users to work with the data presented by the BCS components. Within SharePoint, presentation components include a new type of list called “External lists”, out of the box web parts (yes, it is included with SharePoint, but enterprise edition only) and from within search (Standard and enterprise editions only).
b) Runtime Layer: This layer is the meat of the entire BCS architecture. This layer comprises of three main components:
a. External Content Types (ECT): Contains the metadata definitions of the external content, connection details and details about the actions such as read / write operations, performed on the external content. For example, if we consider a custom database with say sales and customer information, an ECT for the customer information will include the data column definition such as Customer Name, address, ID, etc, along with the connection details to the database and details of how perform the read / write / update / delete operations.
b. Runtime: This is the main engine that performs operations between the client tools and the content source. The BDC runtime is built into office clients such as Word, SharePoint workspace, Access and InfoPath, and within SharePoint 2010 itself. Since the runtime is built into the office clients, these tools can work with the data sources directly and do not need SharePoint as the intermediary layer.
c. Connectors: The connectors provide, well, the connectivity, to the specific content source. SharePoint 2010 has three connectors out of the box, a SQL connector, a WCF connector and a .NET assembly connector. Besides the default set of connectors, the framework is extensible, which means custom connectors could be built for specific content sources.
c) Content Sources: This layer is kind of self explanatory! Contains the data sources.
High level walkthrough
Now that we have a high level understanding of the main building blocks of BCS, let’s walk through the main steps involved in building a no code BCS solution within SharePoint. This is NOT a step by step how to, but an overview of the main steps involved.
In this walkthrough we will use the SharePoint Designer 2010 to connect to the a hypothetical customer table within a custom SQL database
a) Create the external content type (ECT)
a. From within SharePoint Designer, create a connection to the SQL database.
b. Once the connection is made, we choose the Customers table within the database and choose the different operations (read, create, update, delete) we would require on the customer ECT. SharePoint Designer then creates the operations for the customer table.
c. Next step is to choose the columns to be presented within the ECT. Other configuration items such as filters (such as row limits) can also be defined within ECT. When the external content type is saved, it is published back
Here is a screenshot of an ECT within SharePoint Designer (Figure 2)
b) Create the External list in SharePoint
a) Navigate to a SharePoint Site and in the options to create a new content, a new type of list called External list should appear.
b) In the external list creation settings, there is an option to chose an external content type, where in we choose the content type created in step 1. And that’s it! SharePoint automatically uses the settings of the ECT, connects to the external source and surfaces the content within the list in SharePoint. The content supports full read and write operations, if selected during the content type creation process.
Here is a screenshot of an external list within SharePoint 2010
Further this content type can be taken offline in SharePoint workspace. The SharePoint designer has a “sync to SharePoint workspace” feature, that packages the external content type, deploys it into the SharePoint workspace client. This allows the user to work with the external data offline and SharePoint workspace synchronizes the data back with the source when connected.
So that’s all it takes to create a read write interface with an external content source. One of the important concepts to remember here is that the data is NOT stored within the list in SharePoint. The data always resides in the original content source. Knowing this, let’s discuss the pros and cons of this no code solution and other options, with respect to SharePoint solutions.
In comparison with regular external lists, external lists do not support:
a. Event notifications (such as Items added, changed etc…)
b. Custom event handlers
c. Item level permissions
d. Export to Excel, editing in datasheet view etc
e. REST interface
f. And finally RSS feeds
All of the above is related to fact that the data is actually NOT stored in SharePoint but is maintained within the external sources. This raises many questions, of which first and foremost on my mind is what about data integrity and what happens if we want to enforce rules for data validations.
Certain limitations of a no code solution include:
a) No security on search results: All though SharePoint can search the external data, it does not apply security on the search results as the search results are maintained in the external source.
b) No support for attachments: By default there is no built in support to display attachments in list rows.
This is where we start considering our other options of developing solutions with BCS.
BCS Solution Types
At a high level BCS solutions can categorized into three:
a) Simple, no code solutions: This is what we discussed in our walk through.
b) Medium: If we wanted to build a more UI rich or build additional functionality such as workflows, we would have to use other tools such as InfoPath forms or Visual Studio. InfoPath provides a powerful, yet easy to build, means to develop a rich UI for external lists. Visual Studio provides a built in template for developing BCS models. The visual studio approach provides a very flexible way to control data logic; eg: if we wanted to create custom data validations, or perform security checks and so on.
c) Complex solutions: The BCS platform is quite extensible and complex solutions, such as custom connectors can be built using Visual Studio.
Summary
In this paper we explored the new BCS in SharePoint 2010. In comparison to its predecessor, the BDC in SharePoint 2007, this framework is highly flexible, and Microsoft has provided the right set of tools to make BCS work right out of the box!
