This post describes my first attempt, and the challenges I encountered, using BCS to expose and write back external data with SharePoint. Before BCS, I'd used regular SharePoint lists and associations, but over time my 20 lists outgrew SharePoint's limited relational model. Hence I switched to MS SQL Server, added a web service on top, and began looking into BCS as a way to expose CRUD logic and support for different views on the data. Something that SharePoint excels at for regular lists.
At this point, I should probably point out that some of the pain I encountered may well be due to my lack of experience with the framework and tooling. In this case, feel free to drop me a note. I would also like to point out that my aim here is to highlight the significant issues I encountered. There're already a lot of posts out there iterating the positive sides of BCS, but as a developer I'm equally, if not more, concerned with its limitations.
With the aid of SharePoint Designer 2010 (SPD), it's a breeze to define an external content type to match the response of a web service and create an external list based on this content type. The ugly truth, though, is that although the external list will visually appear much like a standard SharePoint list, in terms of functionality it's significantly impaired (see also Inside Microsoft SharePoint 2010, page 493). In short, while BCS and SPD enables you get off the ground quickly, if you don't understand their limitations, you may have incurred a significant technical debt from the get-go.
Behind the scenes, what SPD does is maintain an XML-based metadata model, mapping a set of method stereotypes, such as Finder, SpecificFinder, Insert, Update, and Delete to actual methods on the web service. For BCS to work, these methods must satisfy a rigid set of requirements concerning their signature and behavior. When SharePoint, through the BCS runtime, needs to interact with the external system, BCS will look up the specific method to call within the model and process the result accordingly.
Interestingly, while SPD may be a good starting point for creating the model, the model is actually richer than SPD is able to express. Oftentimes you need to go beyond SPD and export the model, edit the XML file by hand, and then import it back into SharePoint. Your modifications will mostly be left alone by SPD, though not always. Take field ordering within a content type for example: whenever you edit the content type, the order seems to get reset to the order in which .NET reflects over the DataMembers of the DataContract within the generated web service proxy. This in turn affects the order in which form fields appear on the generated display, new, and edit forms. The DataMember attribute actually has an Order property but since it has no effect on the generated WSDL, SPD can't possibly take it into account. The net effect is that you'll oftentimes have to go edit the XML file by hand or script the it.
When creating an external list based on a content types, the display, edit, and new forms are generated from the model. But how are future changes to the model propagated to existing forms? It turns out some are carried over automatically (see Professional Business Connectivity Services in SharePoint 2010, page 55), whereas others will require the list to be re-generated. Not the ideal choice if your end-users made any modifications that needs to be preserved. It seems difficult, bordering impossible, to modify the generated ASPX forms, except through hacks with JavaScript, in which case InfoPath is probably the better choice. I wouldn't want to use InfoPath for a 20 lists solution, though. Using InfoPath on this scale quickly becomes tedious, error-phone, and not maintainable.
In my opinion, the process of defining content types with SPD doesn't scale. Once you go beyond a few content types, working with SPD becomes tedious. When your data source is a web service, the list of methods on it required just to make CRUD operations work makes the contract quite large. Up to more than 100 methods for my 20 content types, and except for the stereotypical Finder method, none of these support bulk operations. As SPD has to retrieve the contract and generate a proxy from it, not to mention manipulate the model stored within the BCS Service Application, the UI isn't particularly responsive. The SPD UI itself seems to have been optimized for working with only a few read-only external content types within a solution. You can't specify field ordering or which field control to use for rendering or editing a field, and it's sometimes hard to locate your web method amongst the 100+ candidates.
External content types are made up of fields whose types are a subset of the primitive types of .NET. For each such .NET type, a rudimentary mapping to SharePoint's field controls exists, describing a field's visual appearance. Fields of type string for instance will, on the new and edit forms, render as a single-line text field. With regular lists, using the browser interface, you can easily turn a string field into a multi-line text area or a Rich Text field, but not so with external lists. At least not unless your forms are InfoPath-based. For ASPX forms, the .NET DateTime type, on the other hand, will render as the standard SharePoint date/time picker, allowing you to set date and time, but not easily only the date.
What I'm particularly missing is the People Picker. Within my database a number of fields store a domain\username provided by the user. These fields are of type string within the database and web service, so SPD will render these as single-line text field without the usual validator button. BCS does offer the ability to render custom SPFields by hand-tweaking the model (see Professional Business Connectivity Services in SharePoint 2010, page 174) and you may be able to use this approach to get the People Picker to render. Another alternative would be to alter the visual appearance and logic of the generated forms with JavaScript, although this seems like a brittle hack.
While on the subject of picker controls, the external item picker is used to setup one-to-many associations between external lists. To make the picker show up, you need to add of a lookup method to your web service and provide a stereotypical mapping to it. I mention this picker because it makes SharePoint display the associated item's friendlier title instead of its foreign key identifier on the new and edit forms. Back on the list view, however, SharePoint will keep displaying the foreign key identifier, forcing end-users to have to lookup the meaning of it themselves. Moving on to many-to-many associations, they're simply not supported by the user interface. You'd have to build a custom control for setting up the association or directly expose the junction table to end-users. But remember how list views can't display the title of foreign items? The list view will end up showing rows of foreign key identifiers.
In my opinion, SharePoint does a decent job at presenting external data in read-only form but is severely lacking when it comes to editing. Creating a decent data entry experience is too much work compared to using an established technology such as ASP.NET. Sure, you could go with InfoPath, but it comes with its own set of peculiarities.
My 20 external content types turn into a metadata model of roughly 8,000 lines of XML with a huge blob at the top, storing the MSIL of the service proxy generated by SPD. You can think of this model as an XML representation of 20 vtables, enriched with attributes to support form generation. All in all, a complex setup for what it actually brings to the table. As a technology, I believe BCS to be too immature for intermediate to complex web service-based, read/write solutions — and I don't want to create a code-based model using the BCS templates that ship with SharePoint for Visual Studio. In this case I'd rather go with an ASP.NET solution and embed it into SharePoint. I'd probably also skip the web service altogether and use a data access assembly instead.
But for the sake of the argument, let's say BCS is still your tool of choice and that your solution warrants the creation of a significant number of external content types/external lists/forms. Then you should probably roll your own tools to either create the XML of the model directly or use the BCS object model to create the model and then export it. This way, you'll have more control over field ordering and the like and be less dependent on SPD.