by Chris Pragash

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 hypothetical customer table within a custom SQL database. From a high level, the main steps can be broken down into two: creation of the ECT and creation of the external list based on the content type created in the first step.

Let’s start with the first step, creating the ECT:

• From within SharePoint Designer, create a connection to the SQL database.
• Once the connection is made, choose the customers table within the database and select the different operations (read, create, update, delete) you would require on the customer ECT. SharePoint Designer then creates the operations for the customer table.
• The 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 ECT is saved, it is published back with the source.

Here is a screenshot of an ECT within SharePoint Designer (Figure 1).

Figure 1: An example ECT within SharePoint Designer (Source: http://msdn.microsoft.com/en-us/library/ee557243.aspx).

After the content type is created, you can proceed to create the list that uses the ECT you already created:

• Navigate to a SharePoint Site, and in the options to create new content, a new type of list called external list should appear.
• In the external list creation settings, there is an option to choose an ECT, wherein 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.

Figure 2 provides a screenshot of an external list within SharePoint 2010.

Figure 2: An external list within SharePoint 2010 (Source: http://msdn.microsoft.com/en-us/library/ee558778.aspx).

Further, this content type can be taken offline in SharePoint workspace. The SharePoint Designer has a “sync to SharePoint workspace” feature that packages the ECT and 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 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:

• Event notifications (such as items added and changed)
• Custom event handlers
• Item-level permissions
• Export to Excel, editing in datasheet view, and so on
• REST interface
• And finally RSS feeds

The lack of all of the listed features 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 you want to enforce rules for data validations?

Limitations of a no-code solution include:

• No security on search results: Although 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.
• No support for attachments: By default, there is no built-in support to display attachments in list rows.

This is where to start considering other options of developing solutions with BCS.

BCS Solution Types

At a high level, BCS solutions can be categorized into three areas:

• Simple, no-code solutions: This is what we discussed in our walkthrough.
• Medium: If you wanted to build a more UI-rich solution or include additional functionality such as workflows, you 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—for example, if you wanted to create custom data validations or perform security checks and so on.
• Complex solutions: The BCS platform is quite extensible, and complex solutions, such as custom connectors, can be built using Visual Studio.

Summary

Over the course of this tip and the tip entitled Introduction to SharePoint 2010 Business Connectivity Services, we explored the new BCS in SharePoint 2010. In comparison with its predecessor, the BDC in SharePoint 2007, the BCS in SharePoint 2010 framework is highly flexible. In addition, Microsoft has provided the right set of tools to make BCS work straight out of the box!

About the Author

Chris Pragash has more than 12 years of experience in the IT industry. His primary focus has been architecting and developing solutions based on Microsoft platforms. He started working on SharePoint in its 2003 version and has designed and developed various types of solutions, including corporate intranets, company Internet Web sites, BI dashboards, and publishing Web sites. His academic background includes PMP, MS Project Management, MCSD (.NET), and MCP (SharePoint 2007 administration). He is currently working on his MBA in strategic management.