6 Lessons for Developing a Business Connectivity Services Solution

Have you ever attended a developer conference where a presenter said something like, "Watch, I'll create a sophisticated, full-featured application in the time it takes you to get a latte"? Instead of getting a latte, you stick around to see the presenter paste onto a design surface hundreds of lines of code that some poor developer needed a month to write. After a few button clicks, up pops the application, which the presenter walks through in a carefully scripted way. You look around at the smiling, clapping people. Few of them fully appreciate what those of us in the trenches know: the development effort that was involved in pulling together that "simple" feat. The next thing you know, you're being asked to create an advanced Business Connectivity Services (BCS) solution in a few days. Hey, that's just the time it takes to get about 20 lattes, right?

Developing and deploying a working, production-level solution takes longer than a couple days. If you're embarking on your first advanced BCS solution, you're liable to hit some roadblocks that aren't covered on any presentations about building a create, read, update, and delete (CRUD)-enabled, database-connected BCS solution in SharePoint Designer (SPD) on a local machine.

This article explores some of the lessons that we had to learn when building advanced BCS code solutions. We hope that these lessons -- along with the references to some fantastic developer documentation that you'll find within the article -- smooth your development effort. In some cases, we provide solutions for our lessons learned. In other cases, we point out limitations so that you're aware of them as you design your BCS solutions. Most of our tips are based on our experience writing .NET Connectivity solutions, but many are applicable to other types of BCS solutions.

Lesson #1: Get a Grip on Your Authentication Approach

There are several areas in which you might struggle with selecting and configuring authentication in BCS to access an external system. Fortunately, the broad architectural considerations for choosing an authentication approach are the same whether you're connecting to an external system via a custom web application or from a SharePoint BCS solution. In both cases, start by considering the type of external system, the forms of authentication that it supports, the form that your connection code will take (i.e., a no-code Business Data Connectivity -- BDC -- Model solution or one backed by managed code) and the authentication model that you'll use (i.e., trusted subsystem or impersonation/delegation).

You've a tremendous amount to consider when selecting your authentication approach for a particular BCS solution. Thankfully, a lot has been written about the topic. Start with these resources:

Other great references can help you to understand the various authentication types in BCS:

After you've done your research, consider these salient points (all terminology is covered in detail in one or more of the previous references):

Lesson #2: Keep BCS Performance Characteristics in Mind

Typically, the external systems that you access via a BCS solution host large amounts of data. Although BCS isn't responsible for ensuring that the LOB system handles the data in a high-performance and scalable manner, you can introduce several techniques into your solution to improve the overall responsiveness of the application and to mitigate potential performance bottlenecks.

Numerous type descriptors and performance. The number of type descriptors that are defined within your model and the need for the BDC runtime to periodically hydrate all the information about type descriptors (by executing numerous web service calls for each type descriptor) can affect performance. Although type descriptors are cached, our experience indicates that the cache invalidates frequently. To see these type descriptor calls logged in the Unified Logging Service (ULS), recycle the application pool for the target web application and render an external list. Note that even after the initial request is made, External Lists and forms in the solution take some time to render.

We approached Microsoft support about this issue. We were told to enhance our Windows PowerShell warm-up script not only to warm up the web application, but also to request each External List and associated form. Although this approach mitigates the problem, it isn't the solution we were hoping for. Rather, it's a way of hiding how chatty the BCS web service is as it calls each type descriptor individually before the cache is populated.

Wildcard filter vs. Comparison filter. Another potential performance bottleneck hides in the SharePoint External Item Picker control, which provides the facility for resolving user-entered data on forms. To instruct the control to resolve an entry, you can create a Wildcard filter descriptor for an identifying field and add the UsedForDisambiguation attribute to a property of that field. You specify this filter descriptor when developing an entity's Finder (ReadList) method. You must also create the SpecificFinder (ReadItem) method, for the entity to be available in the UI. For example, suppose you have a project entity that contains a ProjectNumber filter descriptor with UsedForDisambiguation set to True on the Finder method. As a result, the user can enter a project number on a form that shows this field, and the External Item Picker will try to resolve a unique project number value through calls to the Finder and SpecificFinder methods. Unfortunately, the significant quantity of these calls can lead to many calls to the LOB system.

The following sequence of events occurs when you click the Resolve button and have a Wildcard filter that is decorated with the UsedForDisambiguation attribute:

1. The External Item Picker calls ReadItem with the parameter that you passed in, if the parameter types match. For instance, if ReadItem takes an int parameter and you typed F0, then ReadItem won't be called. However, if ReadItem takes a string, then it is called as ReadItem('F0'). If a match (i.e., exactly one) is returned, then no other methods are called.

2. If the first call returns null, then the External Item Picker calls ReadList with the exact value ReadList('F0'), followed by a call to ReadList with the value surrounded by the wildcard characters ('%F0%'). For each item that is returned from either call, the control then calls ReadItem with the item's identifier value. If the control returns two projects from the first call and returns the same two projects from the second call, then the control calls ReadItem four times, repeating the same calls twice. This behavior might also cause duplicate values to appear in the External Item Picker, which treats the returned items from the first call as different from those from the second call.

If you're developing a .NET Connectivity or custom connector assembly, one way to get rid of duplicate entries and eliminate half the calls is to use a Comparison filter descriptor instead of a Wildcard filter descriptor. In this case, only one call, using the exact value, is made to the ReadList method, followed by a ReadItem call for each returned item from the ReadList call. Because the control doesn't repeat the call to the ReadList method by using the value surrounded by wildcard characters, it doesn't display duplicate items. However, you also must add the wildcard to the string value in your entity's service class:

if (projectNumber.StartsWith ('%') || projectNumber.EndsWith('%'))projectNumber = string.Concat('%', projectNumber, '%'); 

Server-side paging workaround. A common way to handle large data sets for responsive UI access is to introduce server-side paging. Unfortunately, it seems that SharePoint External Lists (a potential artifact of a BCS solution) don't support server-side paging in a code-based BCS solution. Therefore, the stereotypical operation for querying the back-end data (i.e., the Finder method, or ReadList) has no paging-specific parameters that can be leveraged inside the method to page the dataset. Without that information, the only way to achieve server-side paging when using external lists for the UI is to introduce filter descriptors for methods that require paging, resulting in method parameters that can be used to pass paging-specific information into the method.

The Lightning Tools Blog describes this approach in detail. As far as we know, this is the only meaningful back-end paging mechanism (although BCS does support batching to retrieve records, through the Batching and Batching Termination filters, as described in the MSDN article "FilterDescriptor Element in FilterDescriptors (BDCMetadata Schema").  However, you should be aware of some important caveats before you implement this solution.

First, the paging mechanism takes a top n approach, which doesn't always return a single page of data. Rather, the mechanism returns a single page when the first page is requested, increasing the size of the dataset by one page as you move further into the paged results. That is dictated by the default list pager implementation in the Extensible Stylesheet Language Transformations (XSLT) behind the External List, which requires that the result set contains data from the first to the current page.

Second, the same Finder method that SharePoint calls to render a page of data in an External List is also called when you attempt to filter or sort list column data. You aren't likely to want to page the results in a filter or sort operation, but if you do, you won't be filtering or sorting on the full dataset. To remove this pseudo-paging capability on certain requests (i.e., column filtering and sorting), you can inspect the page parameters that will determine the origin of the request. You can find this information in form variables such as __EVENTARGUMENT and _CALLBACKPARAM. For example, the __EVENTARGUMENT form variable might contain the value NotUTF8;__filter={Name=Smith}, which indicates that the user requested the list to be filtered on the Name column with the value of Smith. You might find different information in these form variables depending on the asynchronous load configuration of your External List Web Part. You can further enhance your code logic by differentiating between GET and POST requests, because the former is never a result of a postback. You can also inspect query string parameters, in which additional information might be present.

Lesson #3: Understand the BDC Model

Microsoft Visual Studio (VS) 2010 comes with built-in support for creating and editing BDC Models. This capability is a significant improvement over previous versions, in which developers were left either defining the entire model in XML or using third-party tools. The VS 2010 support comes in the form of toolbox items, such as Entities and Associations, or editors, such as BDC Explorer and BDC Method Details. Third-party tools such as Lightning Tools' BDC Meta Man can further simplify the creation of more advanced application definitions.

In most situations, the generated markup is suitable for deployment. In a few spots, however, dropping to XML lets you add details to your model that might not be intuitive or supported by the designer. For example, the designer offers no way to sort filter descriptors for a given method in any particular order, even though the order in which they were entered determines which option will be selected by default in the External Item Picker. In this case, the last filter descriptor is selected as the default option when the External Item Picker is displayed.

Another common problem is that some properties have dependencies on other properties; these dependencies are set on the siblings' type descriptors. This approach can be particularly confusing and isn't well documented. A good example is the ShowInPicker property, which controls the visibility of a type descriptor within the External Item Picker control. Whenever you have an association and use the External Item Picker control to select an associated record, the ShowInPicker property lets you define which type descriptors are shown in the entity dialog. By default, all the type descriptors (fields and columns) appear in the External Item Picker dialog. Logic suggests that entering a property with a value of False on a type descriptor of type System.Boolean would hide the associated column.

However, this is the case only after you specify at least one other type descriptor that is part of the same parent type descriptor with the ShowInPicker property set to True. Only then do the type descriptors revert to hidden by default in the dialog, and only then can you select which descriptors to show by setting the ShowInPicker property to True. Keep in mind that for this particular example, the properties are assigned on the Finder method's type descriptors. Unfortunately it isn't always clear where a given assignment belongs. Many type descriptors must be repeated across the stereotypical method definitions, and the parser doesn't react to an attribute being misplaced on a type descriptor.

Lesson #4: An External List Isn't a Typical SharePoint List

The SharePoint 2010 External List type, although quite useful, comes with limitations -- primarily associated with the data source being external to SharePoint. Many members of the SharePoint community and the SharePoint Patterns & Practices team have outlined these limitations. (See the Microsoft article "SharePoint Lists vs. External Databases." )

Some of these limitations are inherent in the back-end data being external to SharePoint. For example, it doesn't make sense to set SharePoint access control entries (ACEs) on items that are external to SharePoint. Instead, item-level security should be handled directly in the LOB system, if possible.

Some of the UI limitations can be worked around without losing the use of the SharePoint External List interface. In some situations, XSL transforms can improve the External List interface experience. For example, a Boolean field in an External List simply appears as a Yes/No option that you can edit by opening a New-Item or Edit-Item form. Being able to modify the value of Boolean fields via check boxes that appear directly on the External List, as shown in Figure 1, would be less cumbersome.

Figure 1: Boolean fields as checkboxes on an External List 

Use VS and XSLT. One way to achieve this type of list modification is by creating a VS 2010 SharePoint List Instance project for the target list or lists, and then using a reference to a custom XSLT file to render the check box and using JavaScript within the XSLT to control check box behavior. These are the summary steps for achieving this behavior:

1. Set up the target External List and then save a SharePoint Site Template (without content).

2. Import the template into VS 2010 by using the Import SharePoint Solution Package project type.

3. Import the External List and the list dependencies that VS reports.

4. From Solution Explorer, add to your project a SharePoint Mapped folder that points to the TemplateLayoutsXSL folder under the SharePoint root folder.

5. Create an Extensible Stylesheet Language (XSL) file.

6. Within a list instance schema.xml, add a reference to an XSL that contains your customizations.

Listing 2 shows an XSL transform that renders a field on the IsAdministrator list as a checkbox (see Listing 1). The JavaScript function toggleIsAdministrator notifies the user that the field value is being changed, and then calls the updateIsAdministrator function to update the underlying entity via the ECMA Script Client Object Model. The updates are handled asynchronously with a callback that is invoked when the operation completes. The custom code is placed in xsl:template, which matches the specific field that we're modifying.

The XslLink element is a child of a View element within schema.xml, as Listing 3 shows.

With XSLT, you can make numerous updates to the views that are deployed with your list instance. Because the UI improvements that you implement are view-specific, new views that you create don't inherit the view UI updates that you deploy via the list instance.

You can update new views through your VS list instance solution or via PowerShell, by setting the View's XslLink property to the name of your XSL transform file, as Listing 4 shows.

Some would say this is an unsupported approach and that Microsoft doesn't advocate modifying the External List schema or the XSLT List Form Web Part (XLF -- the default Web Part that's used on External List View pages). These people would point out that the two supported approaches for changing the look and feel of an External List are by using InfoPath Forms and removing the out-of-the-box Web Part and replacing it with the Data Form Web Part.

However, we think that customizing an InfoPath form is cumbersome and precisely what we're working around with this solution. Using the Data Form Web Part is a possible solution, but that Web Part doesn't replace the XSLT List View Web Part (XLV). The closest equivalent to the XLV is the Data View Web Part (DVW). The DVW is available for backward compatibility with SharePoint 2007 and is not intended for use with External Lists. The XLV, which replaced the List View Web Part (LVWP) is the default technology for displaying list data. The XslLink property is an exposed property on the XSLTListViewWebPart class and can be set via SPD or the object model, or from schema.xml (in a list instance solution). As Greg Chan, the SharePoint team product manager, points out, one benefit of the XLV is that it's extensible precisely because it supports custom XSLT. Take a look at the Microsoft article "XsltListViewWebPart and Custom List Views." In the second paragraph, the authors describe how to customize the XLV by using the XslLink element -- a supported approach.

Use a drop-down list box control. Another UI limitation that is associated with External List solutions is that the SharePoint External Item Picker is the only control that is available for rendering BCS Associations on forms. For shorter lists of values, a drop-down list box control is more useful than the External Item Picker. This is true whether you're using an InfoPath Browser Form Web Part or the default XLF. For further information, see the discussion "XSLT List View Web Part." In addition, secondary data sources are not allowed on forms that are backed by an External List.

Check your forms. An important UI element for a list is the form or forms that are used to perform create, read, and update operations against a SharePoint list. When you click an item in an External List that is backed by the XLF, the default behavior is to show the ribbon with the Edit Item button enabled if editing is allowed on the External List and the user is permitted to edit an item, as shown in Figure 2. However, in an InfoPath browser-enabled display form, the Edit Item button is disabled even if updating the item is permitted and an update method has been implemented for the underlying entity, as shown in Figure 3.

Figure 2: Display form Web Part hosting the XLV 

Figure 3: Display form Web Part hosting the InfoPath browser form 

One way to deal with this issue is to turn off the ribbon on the Display-Item form and show the ribbon in the Edit-Item form. You'll need to wire up at least two forms to your list -- possibly three, if you decide to use a unique New-Item form as well. (We outline how to do this and provide other examples of why you might want to have multiple forms for an External List in the article "Speeding InfoPath 2010 Custom Form Deployment to External Lists.")

Find another way. If the External List interface doesn't meet your needs, there are numerous other ways of accessing the data. You can use the Business Data Web Parts, write your own custom interface components (since BCS is backed by a rich API), use the Microsoft Office client applications (i.e., SharePoint Workspace and Outlook), or surface external data as external columns in a SharePoint list. In the latter scenario, you'll need to update the SharePoint list with the external data because a SharePoint list item must be added for each LOB record that should appear in the list. You can do so manually or automatically, through a synchronization process. You can write code to perform this synchronization or purchase a third-party tool to synchronize the data. SharePoint list synchronization with external data is not ideal for most scenarios, but might fill the gaps when the External List interface falls short.

Lesson #5: Deploying a BCS Solution Is Complicated

Because the nature of a BCS solution is to interact with one or more external systems, the solution is inherently full trust. Therefore, the rapid deployment experience you get with sandboxed solutions is unavailable for a BCS solution. (By "rapid," we mean that you have no requirement to recycle the app pool and nothing to deploy to the Global Assembly Cache -- GAC.) This information is useful as you consider where you will host a BCS solution. For example, as of this writing, Microsoft Office 365 can't support this type of solution. However, a hosting provider such as Amazon's Elastic Compute Cloud (EC2) or CloudShare supports full trust solutions because you are in full control of the SharePoint farm environment that you configure with these hosting providers.

Beyond the necessity of full trust for this type of solution, we have experienced interesting behavior around propagating BDC Model updates to a target farm. Depending on the type of update, you might need to first delete the BDC Model, the external content type (ECT), or the External Lists that use the ECT. For example, if you update or add the ShowInPicker property of a type descriptor to an entity, you need to delete the associated ECT from SharePoint for the property setting to work. You can delete an entity from SharePoint Central Administration, Application Management, Manage Service Applications, Business Data Connectivity Models, View External Content Types. Or, you can delete an ECT more quickly from PowerShell, as shown in Listing 5. Then, deploy the updated model.

Regardless of the SharePoint solution type, deployment via SPD or VS 2010 is fine until you need to deploy frequently (a common requirement for BCS solutions) or need to promote the solution into a production environment in which tools such as VS might not be readily available. We use Microsoft Team Foundation Server (TFS) and MSBuild to create updated release-mode SharePoint solution packages. When we have the solution package available, we can either automatically deploy it via PowerShell or manually run a PowerShell script via the SharePoint 2010 Management Shell to retract, remove, add, and deploy our models, as shown in Listing 6. This script uses hardcoded values for brevity, and the code for waiting for a timer job to finish and the solution to retract has been removed. You can find numerous examples of this wait code, such as in the Sohel's Blog post.  This post also provides an interesting approach for using PowerShell to automate builds.

If you're building a .NET Connectivity solution in VS and you make certain updates to your model through the BDC Method Details, then VS automatically adds a stereotyped method even if one already exists in your service class. For example, if you add and later delete a parameter to the ReadList method of an entity via the BDC Method Details pane, a non-implemented ReadList method appears in the entity's service class:

public static IEnumerable ReadList(string parameter01, Nullable parameter02){throw new System.NotImplementedException();} 

When you attempt to deploy your solution after VS 2010 has assisted you, the deployment fails with the error "Type 'your fully qualified service name' already defines a member called 'ReadList' with the same parameter types." Be sure to watch for this error and check your service classes after you update your model and before redeployment.

Still, as we mentioned earlier, a VS list instance project can be quite helpful in some scenarios. If you decide to implement your own list instance project for one or more External Lists in your solution, be sure to include the DataSource element in the ElementManifest file (elements.xml) associated with each External List in your project. The DataSource element is a child of the Elements element. Listing 7 shows one example of the properties within a DataSource element for an External List. You can also use PowerShell to get the DataSource property of the list and then get or set any properties within the SPListDataSource object. (See the Microsoft article "SPListDataSource Class.")

Finally, if you decide to back your External Lists with InfoPath forms, you can automate the deployment of those forms to any environment. For more information on how to do so, see the article "Speeding InfoPath 2010 Custom Form Deployment to External Lists."

Lesson #6: Choices for BCS Associations Can Have Unexpected Impacts

When you create a SharePoint External List from an entity containing a BCS association, New-Item and Edit-Item forms render the association by using the External Item Picker control. In some cases, selecting a value for the association is optional. For example, imagine a Project entity in which you use an association with a User entity to set a value for the Project Manager (PM) field. The PM might not be set when a project is created. To accommodate this scenario, you can create an optional association by defining the type descriptors on both sides of the association as nullable types, such as strings.

If you implement this approach in a scenario when your External List is backed by InfoPath forms, you're likely to run into a bug in the External Item Picker control. If a user enters an invalid value into an External Item Picker control while editing or adding an entry and then clicks the Resolve button, the External Item Picker informs the user that the entered value is invalid. When the user removes the invalid entry or replaces it with a valid entry and then attempts to save the form, the user sees an error message stating "The form cannot be submitted because of an error". In addition, the ULS reports the exception message "Schema validation found non-datatype errors".

This issue is caused by a bug in the way the External Item Picker control validates its data during a postback from an InfoPath form. We filed a hotfix request with Microsoft but, although the bug was acknowledged, the hotfix request was declined because of the amount of regression testing it would take to resolve this issue. Microsoft claims that a change to this area would be non-trivial and would require significant re-engineering.

When "The form cannot be submitted because of an error" message appears, the user can click OK and then click Save again to save the item successfully. This is hardly an acceptable user experience. In our opinion, the only options are not to use InfoPath to host your forms, or to make your associations required fields. We also hope that Microsoft addresses the problem in a future service pack.

The Final Lesson

BCS is a valuable technology, and its improvements over the BDC in SharePoint 2007 are numerous. The available services and tooling are significantly better. But with this more robust platform, you have more to consider as you build solutions that leverage the BCS platform. We've covered some of the lessons that we've learned for achieving acceptable performance, building BDC Models, authenticating with your LOB system, understanding various aspects of the UI experience, deploying BCS solutions, and dealing with the behavior of BCS associations. The final lesson is to do your research by reading the excellent literature that's been written about this platform and then applying the technology -- with or without a latte in hand.

Listing 1: LobSystemInstance Element Showing Secure Store-Specific Settings

# comment                      Name of the Secure Store AppId            Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c             

Listing 2: XSL File Showing How to Create a Checkbox that Toggles a Boolean Field Value

                                                                                                                                yes                             

Listing 3: Referencing the Custom checkbox.xsl in a List Instance schema.xml File

...

              checkbox.xsl        ...      

Listing 4: Setting the XslLink Property of a View by Using the SharePoint 2010 Management Shell (PowerShell)

# Get the web containing the list$web=get-spweb "http://www.contoso.com/sites/web01"# Get a list List01$list=$web.Lists["List01"]# Get a view View01$view = $list.Views["View01"]# Set the XslLink property to the name of your xsl file, in this case, Customizations.xsl$view.XslLink = "Customizations.xsl"# Update the view$view.Update() 

Listing 5: Deleting an ECT from the SharePoint 2010 Management Shell (PowerShell)

# Get an entity named User in namespace Bdc.BdcModel1 from the Central Administration web application# at http://server01:30000$entity=Get-SPBusinessDataCatalogMetadataObject `  -BdcObjectType Entity `  -ServiceContext "http://server01:30000" `  -Name "User" `  -Namespace "Bdc.BdcModel1"# Delete the entity$entity.Delete() 

Listing 6: Redeploying an Existing BDC Model via the SharePoint 2010 Management Shell (PowerShell)

# Get the existing BDC Model$model = Get-SPBusinessDataCatalogMetadataObject `  -Name "Model01" `  -BDCObjectType Model `  -ServiceContext http://server01:30000 # Remove the BDC Modelremove-spbusinessdatacatalogmodel `            -identity "Model01" `            -Confirm:$false # Deploy the updated model (solution file model01.wsp)uninstall-spsolution -identity "model01.wsp" -Confirm:$false# Wait for the timer job to finish and for the solution to retractremove-spsolution -identity "model01.wsp" -Confirm:$false

Listing 7: Properties in a DataSource Element of elements.xml for an External List in a VS List Instance