SPSTCDC: Business Connectivity Services Explained by Example
This morning I attended Kirk Evans‘ demo-intensive session: “Business Connectivity Services (BCS) Explained by Example.” I had used BCS in a limited capacity for my job, but knew I was barely scratching the surface of what I could do with it. After 75 minutes with Kirk today, I can say with certainty that I wasn’t even touching the surface, let alone scratching it.
One participant asked how much things have changed between the BDC options in MOSS and BCS in SharePoint 2010. The answer: LOTS and LOTS has changed, and it’s all for the better.
With SharePoint 2007, it took a third-party tool (BDC Meta Man from Lightning Tools) to simplify working with the large, unwieldy (Kirk used the word “gnarly”) XML file that was required to achieve any line of business (LOB) system integration with MOSS enough for people to be willing to give it a shot. Today, SharePoint Designer 2010 provides a GUI so that dealing with “gnarly” XML files is now a thing of the past.
In the first demo of the morning, Kirk created a simple external content type in SharePoint 2010 that connected directly to the Northwind SQL Server database. Many people mistakenly assume that this is the best (or even the only) way to use BCS to connect to data in SQL Server. Although this is not the case, Kirk walked us through this option for the purposes of the demonstration. He created a Secure Store Service (SSS) application to securely maintain the credentials of the Windows identity that would be used to connect to the database. When creating new external content types, it is necessary to define operations that tell BCS how to retrieve one item from the list, how to retrieve the entire list, and how to update an item (if you wish to allow updates and deletes of items in the database). With SQL Server, a simple right-click and select of “Create All Operations” will take care of this automatically. Primary key values in database tables also automatically map to identifiers for the ReadItem and Update methods. It is also possible to define filter parameters that can limit how many values are retrieved from very large tables, and how to page through this data. By clicking a single checkbox, an InfoPath form is automatically generated for viewing and editing data in SQL Server as well! (Lots of things happening automatically here! Very cool!)
The question was asked if data surfaced in a SharePoint list via BCS is actually a “real” SharePoint list. The answer: no. Without significant custom coding, it is not possible to define workflows or set up alerts on external lists (this makes sense if you give it some thought, as SharePoint would not readily have any way to know when data was updated in a completely external system). There is no native caching of data in the BCS layer, either. Every time the list (or an item in the list) is displayed in SharePoint, a new query is issued to retrieve data from the external data source. That said, you are able to set permissions on the list and to query these lists through SharePoint’s Lists.asmx web service just as you can with “regular” SharePoint lists.
The next question was what mechanism BCS uses for updates. The answer: optimistic concurrency for updates. The last one in wins.
The second demonstration involved the use of a Windows Communication Foundation (WCF) web service. WCF was introduced by Microsoft in 2003 (along with WPF and WF) as the successor technology to ASMX web services (which lacked the security, translation, and workflow capabilities introduced in WCF). Many LOB systems (SAP was used as an example, but there are numerous others) already come with web services that can be consumed via WCF. This is the preferred mechanism for connecting to external data sources–even SQL Server–because we can introduce throttling, logging, performance management, load balancing, caching, routing, etc. (there were more; Kirk was rattling the advantages of using WCF to connect to SQL Server data so fast I couldn’t write them all down!)
Although it seems like there would be security concerns with the power and flexibility of external data surfaced through BCS, permissions are very granular and can be set at the object level all the way down to the individual operation level.
The third demonstration involved the use of a .NET type. From Visual Studio 2010, developers can choose File > New > Business Data Connectivity Model (from the SharePoint 2010 category) and go to town! This option allows developers to generate entity classes with methods to pull external data from any source. Kirk demonstrated a data model that pulled data from an external social media source (i.e., Twitter) and combined it with information stored in a Customer Relationship Management (CRM) system.
The list forms generated for the external content type can be manipulated in SharePoint Designer. At its core, the external data is represented in a Data View Web Part with a Collaborative Application Markup Language (CAML) definition of the fields to display.
List data surfaced through BCS can even be used in a Silverlight application! The SharePoint Client Object Model knows how to talk to lists of all kinds.
Some additional notes Kirk passed along:
- It is possible to crawl data stored in BCS lists by setting up a “Line of Business” content source. The crawler is able to crawl through profile pages via a QueryString URL pattern.
- The data displayed in search results involving BCS lists is from the crawler’s cache, but when a search result is clicked, the data is “freshly” retrieved via BCS at run time.
- User Profiles: BCS can be used to augment data stored in SharePoint user profiles with additional properties.
- With respect to SharePoint SP1 and the June Cumulative Update (CU), both should be applied (SP1 first, then the CU immediately after) in environments that make extensive use of user profiles. SP1 and the June CU introduce a lot of fixes to user profiles.
The session concluded by briefly touching on the offline capabilities of BCS. Data can be brought offline into SharePoint Workspace and can be accessed programmatically through the Silverlight Client API.
This session was jam packed with demos and a LOT of great information. The most important take-home point of the morning: use WCF or .NET types to talk to external data wherever possible…even in SQL Server!
Top Authors
