Chapter 13

Introducing Business Connectivity Services

WHAT’S IN THIS CHAPTER?

• Learning the basics of Business Connectivity Services
• Creating no-code BCS solutions
• Creating Office 365 solutions
• Using BCS solutions with SharePoint

WROX.COM CODE DOWNLOADS FOR THIS CHAPTER

The wrox.com code downloads for this chapter are found at www.wrox.com/remtitle.cgi?isbn=1118495829 on the Download Code tab. The code is in the chapter 13 download and individually named according to the names throughout the chapter.

Although Microsoft SharePoint Server 2013 is an excellent platform upon which to build information solutions, it will never be the only system in an organization. Organizations will always have additional systems, such as customer relationship management (CRM) and enterprise resource planning (ERP) to target specific datasets and business processes. Organizations may also have other custom applications, databases, and web services that are not part of the SharePoint infrastructure. These external systems (external to SharePoint that is) contain significant amounts of data and represent significant financial investments. As a consequence, these systems will not soon be replaced by any solution created solely in SharePoint.

The challenge is that SharePoint solutions are often closely related to the data and processes contained in external systems. For example, a document library containing invoices may contain meta data also found in the ERP system or be addressed to a customer whose information is also in the CRM system. Without some way to use data from the external systems, the SharePoint solution is forced to duplicate the same information. This duplication can then lead to data maintenance issues between the external system and the SharePoint solution.

In addition to the data challenges presented within SharePoint, there are challenges when integrating external data with Office 2013 documents. When salespeople create a quote, for example, they often look up customer contact information in a CRM system, copy it to the clipboard, and then paste it into the document. This duplication of effort obviously increases the time necessary to create documents. Furthermore, salespeople must be connected to the network to access the CRM system; they cannot easily create a quote while offline.

Failure to sufficiently integrate external systems with SharePoint solutions can slow the adoption of SharePoint within an organization. After all, the most important data used by information workers is often in external systems. Therefore, your solutions must consider how to integrate external data and that is where Business Connectivity Services (BCS) comes into play. This chapter is intended as an introduction to BCS technologies and is immediately followed by an advanced chapter on BCS development.

INTRODUCING BUSINESS CONNECTIVITY SERVICES

BCS is an umbrella term for a set of technologies that brings data from external systems into SharePoint Server 2013 and Office 2013. If you have worked previously with Business Connectivity Services in Microsoft SharePoint Server 2010, you will find only a few new things in the 2013 version, which are summarized in Table 13-1. If you have not previously worked with the BCS, don’t worry; no prior experience is required to understand or use BCS in SharePoint solutions. Figure 13-1 shows a diagram of the major functional blocks that make up BCS.

FIGURE 13-1

TABLE 13-1: New Capabilities and Improvements

In the context of BCS, the term external system refers to any data source that is outside of the SharePoint infrastructure. As previously noted, this can include third-party software, custom applications, databases, web services, and even cloud computing solutions. BCS communicates with external systems through the Business Data Connectivity (BDC) layer. The BDC layer contains the plumbing, run-time API, and connectivity functionality necessary to communicate with external systems.

Although the BDC layer provides connectivity to the external system, it does not dictate what data is returned from the system. The operations and schema for the returned data is instead defined by an External Content Type (ECT). An ECT contains an entity definition that specifies the exact fields that should be returned from an external source. For example, a “Customer” ECT might specify that the CustomerID, FirstName, and LastName fields be returned from the CRM system. In addition, an ECT defines the operations that can be performed. The available operations include create, read, update, delete, and query (CRUD). Defining ECTs is one of the primary activities involved in creating a BCS solution and may be performed in either Microsoft SharePoint Designer 2013 (SPD) or Microsoft Visual Studio 2012 (VS2012). When completed, ECTs are stored in the External Content Type catalog.

Although you can create many different custom solutions using BCS, the simplest way to expose external data in SharePoint is to use an external list. An external list is a SharePoint list based on an ECT. Just as standard lists (tasks, announcements, calendars, libraries, and so on) are based on content types, external lists are based on External Content Types. External lists behave similarly to standard lists, support views, and item editing. You can use external lists in on-premises SharePoint farms and Office 365 (O365). External lists can be part of classic SharePoint solutions and the new SharePoint app model.

In Office 2013, the BCS Client layer can use External Content Types to display external data in Office clients. This data may be displayed in Outlook using standard forms such as contact lists or used in Word to support meta data and document creation. In all cases, you can make use of InfoPath to enhance the presentation of external data.

Creating Simple BCS Solutions

Although BCS solutions can be complex, they can also be created without code. Using the tools found in SPD and SharePoint, you can easily create an External Content Type and an external list. This data can then be edited in SharePoint or Office clients. This section walks you through a simple BCS solution for an on-premises farm based on a SQL Server database. The database contains a single table of marketing campaign information, as shown in Figure 13-2. The goal of the walkthrough is to create a list in SharePoint and a calendar in Outlook based on this data.

FIGURE 13-2

Creating External Content Types

The solution begins with the definition of External Content Types to define the schema and operations to perform on the data. Regardless of whether your BCS solution ultimately uses code, you almost always define the ECTs using the SharePoint Designer. The tooling in SPD for creating ECTs was designed to be sophisticated enough to be used by professional developers across all types of BCS solutions. To begin, you simply open a development site in SPD and click the External Content Types object under the list of Site Objects, as shown in Figure 13-3.

FIGURE 13-3

Clicking on New External Content Types in the Ribbon enables you to start defining basic ECT information. The basic ECT information consists of a Name, Display Name, Namespace, and Version. You can also select from a list of various Office Item Types, which determines what form will be used to render the information when it is displayed in Outlook. Figure 13-4 shows the basic ECT information for the walkthrough with the Appointment Office Item Type selected.

FIGURE 13-4

Clicking Operations Design View presents a form for defining connection information to an external system. Clicking the Add Connection button allows you to select from three types of connections: WCF, SQL, and .NET Type. Selecting WCF enables you to connect to a web service, SQL enables you to connect to a database, and .NET Type enables you to use a custom .NET Assembly Connector, which is covered in Chapter 14.

When connecting to various data sources, you must always be concerned with the principal used to authenticate against the external source. This principal may be the user’s account or a service account. In addition, the account may use Windows authentications, claims authentication, or token authentication.

For this walkthrough, the service account running the associated SharePoint web application (aka the SharePoint system account) is used. This approach is known as revert-to-self authentication. Because the SharePoint system account is a powerful account, revert-to-self must be explicitly enabled using the following PowerShell script. The final SQL connection type information is shown in Figure 13-5.

FIGURE 13-5

$bdc = Get-SPServiceApplication | 
       where {$_ -match "Business Data Connectivity Service"}
$bdc.RevertToSelfAllowed = $true
$bdc.Update()

After the data source connection is made, SPD can create operations for the ECT. When using a SQL connection, SPD can infer a significant amount of information about the data source and the operations, so it is easier to create the entire set of CRUD operations. All you need to do is right-click the table in the connection and select Create All Operations from the context menu, which launches a wizard to collect the small amount of information required to complete the operation definitions. Figure 13-6 shows the context menu in SPD.

FIGURE 13-6

To complete the operation definitions, you must at least map fields from the ECT to fields in Outlook. This mapping determines how the ECT is displayed in Outlook form. In this example, the Subject, Start, and End fields in Outlook must be mapped to the ECT. This is because Appointment was selected as the Office Type. For this walkthrough, CampaignName is mapped to Subject, StartDate to Start, and EndDate to End. When the wizard finishes, the ECT definition is complete; you can save it in SPD by clicking the Save button. The ECT is then visible in the list of ECTs for the site.

Creating External Lists

After the ECT is created, you can use it as the basis for an external list. External lists can be created directly in SPD or in the browser. For this walkthrough, a new external list was created by selecting the Lists and Libraries object from the list of Site Objects and then clicking the New External List button. When you create a new external list, the set of available ECTs is presented. Figure 13-7 shows the list of available ECTs with the new Campaign type visible.

FIGURE 13-7

After the new external list is created, you can view it immediately in the browser. Because all the CRUD operations were created, the list supports editing items, adding items, and deleting items. Figure 13-8 shows the new list in SharePoint Server 2013.

FIGURE 13-8

SharePoint Server 2013 supports taking lists offline through Microsoft Outlook if the site feature “Offline Synchronization for External Lists” is activated. For this walkthrough, the external list was defined as an Appointment Office Type, and it can be synchronized with Outlook by clicking Connect to Outlook in the List tab of the Ribbon within SharePoint. When this button is clicked, a Visual Studio Tools for Office (VSTO) package is accessed and an installation screen is presented. This VSTO package must be installed for synchronization to continue. Figure 13-9 shows the installation screen presented to the user.

FIGURE 13-9

Creating Simple O365 Solutions

In addition to creating simple database solutions for on-premises farms, you can also create them for Office 365 (O365). Creating such a solution requires that you have both a Windows Azure account and an O365 account. You must then create your database in SQL Azure before creating your External Content Type in O365. This section walks you through the basic approach. If you do not have a SQL Azure account, you may sign up for one at http://www.windowsazure.com/en-us/pricing/free-trial/. If you do not have an O365 account, you can sign up for a developer account at http://msdn.microsoft.com/en-us/library/office/apps/fp179924(v=office.15).

Creating SQL Azure Databases

If you have a Windows Azure account, you may easily create a SQL Azure database using some simple scripts. You can manage SQL Azure databases just like any on-premises SQL database by using the Microsoft SQL Server management tools. All you need is the name of the database server and administrator credentials, which you provide during the setup of a new SQL Azure database server. After the database is created, you can use scripts to create tables and populate those tables with data. If you have a lot of data to move, you can even create a SQL Server Integration Services package to move data from your on-premises development environment into the SQL Azure database.

Managing the Secure Store

The key to successfully creating a BCS solution in O365 is to set up the proper credentials for accessing the SQL Azure database. In the simple on-premises example, you use the SharePoint system account as the principal. This approach will not work for O365, however, because the database expects the solution to use a SQL Azure login and not a Windows account. Therefore, you must use the Secure Store to map the current user’s credentials to the expected service account principal. The Secure Store Service is explained in detail in the section “Understanding the Secure Store Service.” For this walkthrough, it’s enough to say that all the accounts in the O365 installation have been mapped to the SQL Azure credentials that were set up when the SQL Azure database was created. Figure 13-10 shows the connection information for the ECT in O365. Note the name of the database server refers to the SQL Azure database, and the Secure Store Application ID refers to the Secure Store entry that maps user credentials to SQL Azure credentials.

FIGURE 13-10

Creating External Lists

After the ECT is created and the credentials properly mapped, you can create an external list in O365 the same way you do in an on-premises farm. This means that you can use either the SharePoint Designer or the browser. The external list behaves identically in O365 as it does in an on-premises farm.

UNDERSTANDING BCS ARCHITECTURE

BCS is made up of several components and interacts with several services within SharePoint. To create effective BCS solutions, you must understand the architecture, components, and service interfaces available for development. A detailed diagram of this architecture is presented in Figure 13-11.

FIGURE 13-11

Understanding Connectors

As mentioned previously, BCS communicates with external systems using several connectors. The simple solution presented earlier utilized the SQL connector to access a SQL Server database, but BCS also supports a WCF connector for accessing general web services and an OData connector specifically designed for OData services. Together, these connectors cover a significant number of data sources, but they can’t cover all possible scenarios.

In scenarios in which you need more flexibility than is provided by the out of the box connectors, you can build a .NET Assembly Connector instead. A .NET Assembly Connector is a project that you create in Visual Studio 2012 that contains the ECT definition and associated business logic for accessing a specific external system. This exposes the full power of the .NET Framework for accessing and manipulating data. The .NET Assembly Connector is covered in Chapter 14, “Advanced Business Connectivity Services.”

Understanding Business Data Connectivity

As stated previously, BDC is the term that encompasses the plumbing and run-time components of BCS. Both the server and the client have BDC components. These components are complementary so that you can use a similar approach to creating BCS solutions whether you are focused on the server, client, or both. On the server, the BDC components consist of the ECT catalog and the server-side BDC run time. On the client, the BDC components consist of a meta data cache and the client-side BDC run time.

Managing the Business Data Connectivity Service

When you create ECTs in SPD and save them, they are stored in the ECT catalog (also referred to as the meta data catalog). This catalog is a database accessed through the Business Data Connectivity service application. Figure 13-12 shows the basic architecture of the BDC Service Application.

FIGURE 13-12

External connectors and ECT meta data are used to access the external systems and retrieve data through the run-time object model. The BDC Service Application then provides that data for use inside of SharePoint. A meta data cache is maintained in the service so that the ECT model is readily available. This meta data cache is updated every minute to ensure that the latest ECT model is available to the farm. Although BCS does cache the ECT model, external system data is never cached on the server. Each ECT operation is always performed directly against the external system.

Along with caching meta data to improve performance, BCS also can limit the connections and data size associated with operations. The BDC Service Application implements five throttle settings to limit the connections made and data returned from external systems. Table 13-2 lists the throttles and the default settings.

TABLE 13-2: BDC Service Application Throttles

Managing throttles is accomplished using PowerShell scripts. The following code displays the current throttle settings:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$bdc = Get-SPServiceApplicationProxy |
       Where {$_ -match "Business Data Connectivity"}
Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Connections -Scope Global 
                                        -ServiceApplicationProxy $bdc
Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Items -Scope Database 
                                        -ServiceApplicationProxy $bdc
Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Timeout -Scope Database 
                                        -ServiceApplicationProxy $bdc
Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Size -Scope Wcf 
                                        -ServiceApplicationProxy $bdc
Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Timeout -Scope Wcf 
                                        -ServiceApplicationProxy $bdc

You can modify each of the throttle settings using PowerShell. The following code shows how to change the number of items that can be returned from a database:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$bdc = Get-SPServiceApplicationProxy |
       Where {$_ -match "Business Data Connectivity"}
$throttle = Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Items
            -Scope Database  -ServiceApplicationProxy $bdc
Set-SPBusinessDataCatalogThrottleConfig -Maximum 3000 -Default 1000
                                        -Identity $throttle

Alternatively, you can simply disable any throttle. The following code shows how to disable the connections throttle:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$bdc = Get-SPServiceApplicationProxy | 
       Where {$_ -match "Business Data Connectivity"}
$throttle = Get-SPBusinessDataCatalogThrottleConfig -ThrottleType Connections 
            -Scope Global -ServiceApplicationProxy $bdc
Set-SPBusinessDataCatalogThrottleConfig -Enforced:$false -Identity $throttle

The BDC Service Application is part of the service application framework in SharePoint. The management interface for the BDC Service Application is accessible through Central Administration by selecting Application Management ⇒ Manage Service Applications. Figure 13-13 shows the BDC Service Application in Central Administration.

FIGURE 13-13

From the Service Applications page, you can click Properties in the Ribbon to see the basic service properties for the BDC Service Application. In the Properties dialog box, the name of the database where the ECTs are stored displays. This database is set up when the BDC Service Application is first created during farm installation and configuration. As with all services, from the Ribbon you can also set administrative and connection permissions for the service so that it can be used by other servers in the SharePoint farm.

Clicking the Manage button on the Service Applications page opens the View External Content Types page. This page lists all ECTs currently stored in the catalog. On the Edit tab, you can grant rights to manage the ECT catalog by clicking the Set Catalog Permissions button, and you can set rights for individual ECTs by clicking the Set Object Permissions button. This enables you to determine which users can use ECTs to access external systems.

On the Edit tab, there is also a drop-down list that determines how ECT information is presented on the page. Initially, the drop down is set to External Content Types, which shows the ECTs in a list. Selecting External Systems from the drop-down list shows all the available connections that are defined. Selecting BDC Models, on the other hand, lists the models that contain both connection and ECT information.

The list of BDC models is of special importance to the developer because the BDC model contains the reference to the ECT, the connection information for the external systems, security information, and more. Furthermore, the model can be exported using the drop-down menu on the list item and then subsequently imported into another catalog. When a model is exported, it is saved as an XML file known as a BDC Metadata Model. BDC Metadata Models can also be exported directly from SPD by right-clicking the ECT and selecting Export Application Model.

The following code shows part of a BDC Metadata Model based on the walkthrough earlier in the chapter. Take special note of the bolded code. In the LobSystemInstance properties, you can see the basic connection information for the external system. These values were all set when the external system connection was specified in SPD.

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<Model xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog
BDCMetadata.xsd"
 Name="SharePointDesigner-CampaignsData-Administrator-92aec138-31d3-
4155-980f-db4c681c2260" 
xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog">
  <Properties>
    <Property Name="Discovery" Type="System.String"></Property>
  </Properties>
  <LobSystems>
    <LobSystem Type="Database" Name="SharePointDesigner-CampaignsData">
      <Properties>
        <Property Name="DiscoveryVersion" Type="System.Int32">0</Property>
        <Property Name="WildcardCharacter" Type="System.String">%</Property>
        <Property Name="Discovery" Type="System.String"></Property>
      </Properties>
      <LobSystemInstances>
        <LobSystemInstance Name="SharePointDesigner-CampaignsData">
          <Properties>
            <Property Name="AuthenticationMode"
                      Type="System.String">RevertToSelf</Property>
            <Property Name="DatabaseAccessProvider"
                      Type="System.String">SqlServer</Property>
            <Property Name="RdbConnection Data Source"
                      Type="System.String">localhost</Property>
            <Property Name="RdbConnection Initial Catalog"
                      Type="System.String">ContosoAdvertising</Property>
            <Property Name="RdbConnection Integrated Security"
                      Type="System.String">SSPI</Property>
            <Property Name="RdbConnection Pooling"
                      Type="System.Boolean">true</Property>
            <Property Name="Discovery" Type="System.String"></Property>
            <Property Name="ConnectionName"
                      Type="System.String">CampaignsData</Property>
          </Properties>
        </LobSystemInstance>
      </LobSystemInstances>
    </LobSystem>
  </LobSystems>
</Model>

Introducing the BDC Server Runtime

The BDC Server Runtime consists of the run-time object model, the administration object model, and the security infrastructure. The run-time object model provides access to ECTs, whereas the administration object model provides objects for managing the ECT catalog. The security infrastructure facilitates authentication and authorization for ECT operations and external system access.

Understanding the Client Cache

When BCS solutions are taken to Office clients, a client cache is used to store both the BDC Metadata Model and the external system data. The client cache is a SQL Server Compact Edition (SQLCE) database that is installed as part of the Office 2013 installation. The database is encrypted to prevent tampering, as there is no reason for developers to access the database directly.

A synchronization process (BCSSync.exe) runs on the client to synchronize the cache with the associated external systems. When CRUD operations are performed on data within the Office clients, the operations are queued inside the client cache and synchronized with the external system when it is available. The synchronization process also attempts to update data in the cache at various intervals from the external system depending on the user settings and availability of the external system. Conflicts between the cache and the external system are flagged for the end user so that they can be resolved.

Introducing the Office Integration Runtime

The Office Integration Runtime (OIR) is the set of components and associated APIs that bind the ECTs to the Office clients and your own custom solutions. The OIR loads whenever a host Office client is started. The OIR is installed on the client as part of the Office 2013 installation process.

Understanding the Secure Store Service

The Secure Store Service (SSS) is a service application that provides for the storage, mapping, and retrieval of credential information. Typically, the credentials stored by SSS are used to access external systems that do not support Windows authentication. This is accomplished by mapping the stored credentials to an existing Windows user or group.

To store credential sets for an external system, a new target application must be created in SSS. The target application acts as a container for credential sets mapped to an external system. The target application settings page contains a name for the application and a setting to specify whether each individual user has a separate set of mapped credentials or whether every user maps to a single common set of credentials. Figure 13-14 shows application settings mapping a single set of credentials to an Active Directory group.

FIGURE 13-14

After the target application is defined, credential fields are defined. In most cases, the target application saves a username and password, but SSS can save any text-based credential information. For example, a domain field could be added so that the credential sets consisted of a username, password, and domain. Figure 13-15 shows typical username and password fields defined for an application.

FIGURE 13-15

After the application and credential fields are defined, you must enter the actual credential information by right-clicking the application and going to Set Credentials. For each user or group that will access the external system, a set of credentials must be created using the field definitions for the application. Figure 13-16 shows credentials being entered for an application. After the credentials are in place, the application can be used during the definition of an ECT to allow access to the external system using the credentials stored in the SSS. If end users attempt to access the system without proper credentials in SSS, they will be directed to a login page so the credentials can be entered and stored.

FIGURE 13-16

Although BCS and SSS work well together to provide authentication in many scenarios, there may be times when you want to utilize SSS in custom solutions. In these cases, you can access SSS programmatically to retrieve credentials. These credentials can then be used by your solution (such as a web part) to access external systems directly without using BCS. The following code shows how to access the credentials in the default instance of SSS:

ISecureStoreProvider p = SecureStoreProviderFactory.Create();
string username = string.Empty;
string password = string.Empty;
using (SecureStoreCredentialCollection creds = 
       p.GetCredentials("ContosoDatabases"))
{
    foreach (SecureStoreCredential c in creds)
    {
        switch (c.CredentialType)
        {
            case SecureStoreCredentialType.UserName:
                username = c.Credential.ToString();
                break;
            case SecureStoreCredentialType.Password:
                password = c.Credential.ToString();
                break;
            case SecureStoreCredentialType.WindowsUserName:
                username = c.Credential.ToString();
                break;
            case SecureStoreCredentialType.WindowsPassword:
                password = c.Credential.ToString();
                break;
            case SecureStoreCredentialType.Generic:
                //Generic credentials
                break;
            case SecureStoreCredentialType.Key:
                //Key
                break;
            case SecureStoreCredentialType.Pin:
                //Pin
                break;
        }
    }
    //Log in using the credentials
}

Understanding Package Deployment

When an end user elects to synchronize an external list with Outlook or the SharePoint Workspace, BCS creates a VSTO click-once deployment package that contains all the elements necessary to work with the list on the client. The package is created by BCS just in time and is stored under the list in a folder named ClientSolution. After the package is created, the deployment starts automatically.

The package contains the BCS model defining the external system, ECTs, operations, and security information necessary to access and modify data. The package also contains subscription information, which tells the client cache what data to manage and how it should be refreshed. Finally, the package contains pre- and post-deployment steps that you should take, such as creating custom forms in the client application to display the data.

When deployed, the add-in can make use of Office business parts on the client to help render data. Office business parts are Windows form controls that display a single item or list of items in a task pane. These parts simplify the rendering process so that custom task panes do not need to be created for the client.

Understanding App Architecture

SharePoint 2013 introduces the new app model, which provides complete isolation of the code associated with custom solutions. To support this model, Business Connectivity Services introduces the capability to BDCM models directly within the app. These app-level ECTs do not reside in the BDC Service Application. Instead, they are isolated into an in-memory instance of the BDC Runtime, which loads the BDC Metadata Model deployed as part of the app. Figure 13-17 shows the high-level architecture supporting app-level ECTs.

FIGURE 13-17

A SharePoint app that utilizes an app-level ECT can deploy the BDC Metadata Model as part of the app. This model is stored inside of a special document library as part of the solution architecture. When the app is accessed by a user, an instance of the BDC Runtime is spun up in memory and the model is loaded. The model can then be used to drive an external list within the app scope. When the app is closed, the BDC Runtime is torn down. This architecture means that the app-level ECT is completely isolated from the SharePoint server and the BDC Service Application. App-level ECTs are fairly easy to create, but the tooling is only present in Visual Studio 2012; they cannot be created in SPD. The exact process for creating these solutions is covered in Chapter 14.

WORKING WITH BDC METADATA MODELS

The BDC Metadata Model is an XML file that completely defines the ECT, its connection to the external system, and its operations. When creating BCS solutions using the SPD tooling, you are simply building up the XML contained in the BDC Metadata Model. This model is then stored in the Metadata Store.

Although you can create BCS solutions without looking at the XML contained in the BDC Metadata Model, professional solutions require a strong understanding of the elements in the model. When creating more advanced solutions in BCS, the BDC Metadata Model is often exported from SPD, modified, and imported when a wanted feature is not directly supported by the SPD tooling. Furthermore, it is educational to export the BDC Metadata Model and examine it as changes are made.

You export a BDC Metadata Model from the list of External Content Types. After selecting an ECT, you can click the Export BDC Model button, which brings up the Export BDC Model dialog box. In this dialog box, you can select to export the Default model or the Client model. The Default model is the one used on the server, and the Client model is the one stored in the client cache following list synchronization. The two models reflect any differences you made in the connection properties to be used on the server or client but have the same ECT definitions. The models are exported with a BDCM extension, which is the file extension for all BDC Metadata Models. Figure 13-18 shows the Export BDC Model dialog box.

FIGURE 13-18

When exported, the simplest way to work with the BDC Metadata Model is to change the file extension from .bdcm to .xml and open it in Visual Studio 2012. Although Visual Studio can open files with a .bdcm extension, doing so results in Visual Studio using its BCS designers instead of showing the XML directly. The BCS designers are covered in Chapter 14.

The schema file for the BDC Metadata Model is named BDCMetadata.xsd and can be found in the /Program Files/Common Files/Microsoft Shared/web server extensions/15/Template/XML directory. When working with exported models, you should copy the schema file into the directory where you exported the model. If you do this, you get IntelliSense support in Visual Studio when you open the file for editing.

You import a BDC Metadata Model through the BDC Service Application in Central Administration. When importing an edited model back into the BDC Service Application, you should be sure to update the ECT version, as a best practice. As an alternative, you can simply delete the existing model before importing the edited model.

Examining the BDC Metadata Model XML directly is an excellent way to learn the intricacies of the schema. Throughout this chapter, both the SPD tooling and resulting XML will be presented so that you can see exactly how the tools affect the model. As a starting point, the following code shows some of the basic elements used in the model:

<Model xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://schemas.microsoft.com/. . ./BDCMetadata.xsd"
       xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog"
       Name="My Model">
  <LobSystems>
    <LobSystem>
      <LobSystemInstances>
        <LobSystemInstance/>
      </LobSystemInstances>
      <Entities>
        <Entity Name="MyEntity" 
                DefaultDisplayName="My Entity" 
                Namespace="http://mynamespace"
                Version="1.0.0.0"
                EstimatedInstanceCount="10000" >
          <Methods>
          </Methods>
        </Entity>
      </Entities>
    </LobSystem>
  </LobSystems>
</Model>

The Model element is the root of the XML and contains the schema reference. This element also contains the Name attribute, which is displayed in the BDC Service Application. The LobSystem element is a container for the model associated with a particular external system. Notice that it is possible to have multiple external systems defined in the same model. The LobSystemInstance element provides connection information for a particular external system.

The Entity element begins the definition of an ECT for a particular external system. The Name attribute is the programmatic name of the ECT, and the DefaultDisplayName attribute is the display name that appears in the SharePoint UI. The Namespace attribute is used for disambiguation between ECTs that have the same programmatic name. The Version attribute is used to indicate the latest version of the ECT. All these attributes are entered in SPD when creating a new ECT.

The EstimatedInstanceCount attribute is used as a hint to BCS solutions as to how many entity instances can be expected from the external system. The EstimatedInstanceCount attribute cannot be edited through SPD, and its use is determined solely by the application consuming the model. You could use this attribute, for example, when creating a custom web part to determine whether to load all the data immediately or on demand. This is a good, albeit simple, example of why you might need to export a model, modify it by hand, and import it.

WORKING WITH EXTERNAL DATA SOURCES

External data sources contain the data from external systems and are the starting point for any BCS solution. An ECT must be associated with an external data source through one of five connector types: SQL Server, WCF Service, OData, .NET Assembly, or Custom. When creating BCS solutions in SPD, you primarily make use of the SQL Server or WCF Service connectors. The OData, .NET Assembly, and Custom connector types are typically part of a solution created in Visual Studio.

When creating a new ECT, you can associate an external data source by clicking the link titled Click Here to Discover External Data Sources. Clicking the link takes you to the Operation Designer, where you can see all the available external data sources. This view allows additional external data sources to be added or existing ones to be removed. The Operation Designer also allows the structure of external data sources to be searched using a keyword. This facility helps locate tables, views, and web methods by name, which is helpful if there are many external data sources available.

When working with an existing ECT, you can open the Operation Designer by clicking the Operations Design View button on the Ribbon. The Connection Properties dialog box can be opened by clicking the External System link in the ECT Information panel or by clicking the Edit Connection Properties button in the Ribbon. You can change the external data source associated with an ECT by clicking the Switch Connected System button in the Ribbon. Figure 13-19 shows the available buttons in the Ribbon.

FIGURE 13-19

Connecting with the SQL Server Connector

The SQL Server Connector provides connections to Microsoft SQL Server, Oracle, OLE DB, and ODBC databases. Because databases represent the bulk of the available external data, the SQL Server Connector is used frequently in BCS solutions. The SharePoint Designer provides tooling to support connections with Microsoft SQL Server, but connections to Oracle, OLE DB, and ODBC sources require hand editing of the BDC Metadata Model.

Connecting to Microsoft SQL Server Databases

When adding a new SQL Server Connection in the Operation Designer, you must fill out the SQL Server Connection properties dialog box with the required information to connect to the external system. The following code shows the LobSystemInstance element for a typical connection to Microsoft SQL Server using Microsoft’s AdventureWorks sample database available at http://msftdbprodsamples.codeplex.com.

<LobSystems>
 <LobSystem Type="Database" Name="MySystem">
  <Properties>
   <Property Name="WildcardCharacter" Type="System.String">%</Property>
  </Properties>
  <LobSystemInstances>
  <LobSystemInstance Name="MySystemInstance">
   <Properties>
    <Property Name="AuthenticationMode" Type="System.String">
      PassThrough
    </Property>
    <Property Name="DatabaseAccessProvider" Type="System.String">
     SqlServer
    </Property>
    <Property Name="RdbConnection Data Source" Type="System.String">
     AWSERVER
    </Property>
    <Property Name="RdbConnection Initial Catalog" Type="System.String">
     Adventureworks
    </Property>
    <Property Name="RdbConnection Integrated Security" Type="System.String">
     SSPI
    </Property>
    <Property Name="RdbConnection Pooling" Type="System.String">
     True
    </Property>
   </Properties>
  </LobSystemInstance>
 </LobSystem>
</LobSystems>

For the SQL Server Connector, a series of Property elements are used to specify the values that define the connection to the database. The AuthenticationMode property determines how authentication is performed to the external system. The possible values for AuthenticationMode are listed in Table 13-3.

TABLE 13-3: Authentication Modes

The DatabaseAccessProvider property specifies what type of database is targeted. This value may be set to SqlServer, Oracle, OleDb, or Odbc. Depending upon the value selected for this property, other properties may be required as children of the LobSystemInstance element. In the case of the SqlServer example, the RdbConnection Data Source, RdbConnection Initial Catalog, RdbConnection Integrated Security, and RdbConnection Pooling properties are required. You’ll recognize each of these properties as a component of a standard connection string.

Connecting to Oracle Databases

Because there is no tooling support in SPD for connecting to Oracle data sources, creating models for Oracle databases can be difficult. One approach is simply to start from scratch with a blank XML file and then import the model into the BDC Service Application. Another approach is to model the ECT against a SQL Server database, export the model, edit the model, and import the changed model. Neither approach is ideal. If you start from scratch, you are more likely to commit typographical errors. On the other hand, modifying a SQL Server model is error-prone because the query syntax differs between SQL Server and Oracle. In any case, you must eventually end up with something that looks like the following code:

<LobSystems>
 <LobSystem Type="Database" Name="MySystem">
  <Properties>
   <Property Name="WildcardCharacter" Type="System.String">%</Property>
  </Properties>
  <LobSystemInstances>
   <LobSystemInstance Name="MySystem Instance">
    <Properties>
     <Property Name="AuthenticationMode" Type="System.String">
      RdbCredentials
     </Property>
     <Property Name="DatabaseAccessProvider" Type="System.String">
      Oracle
     </Property>
     <Property Name="RdbConnection Data Source" Type="System.String">
      MY_NET_SERVICE_NAME
     </Property>
     <Property Name="SsoApplicationId" Type="System.String">
      MY_SECURE_STORE_APP_ID
     </Property>
     <Property Name="SsoProviderImplementation" Type="System.String">
      Microsoft.Office.SecureStoreService.Server.SecureStoreProvider,
      Microsoft.Office.SecureStoreService, Version=15.0.0.0,
      Culture=neutral, PublicKeyToken=71e9bce111e9429c
     </Property>
    </Properties>
   </LobSystemInstance>
  </LobSystemInstances>
 </LobSystem>
</LobSystems>

The AuthenticationMode property is set to RdbCredentials for connections to Oracle. This means that non-Windows credentials supplied by the Secure Store Service are used to access the Oracle database. The DatabaseAccessProvider property is set to Oracle to indicate that Oracle is the target system. The RdbConnection Data Source property is set to the value of the Net Service Name, which is the alias for the database found in the tnsnames.ora file. The SsoApplicationId property is set to the name of the application in the Secure Store Service that is providing the credentials. The SsoProviderImplementation property refers to the implementation of the Secure Store Service. In the sample code, the property references the server-side Secure Store Service. If the model were used on the client, the following code should be substituted:

<Property Name="SsoProviderImplementation" Type="System.String">
 Microsoft.Office.BusinessData.Infrastructure.
 SecureStore.LocalSecureStoreProvider, 
 Microsoft.Office.BusinessData, Version=15.0.0.0, Culture=neutral,
 PublicKeyToken=71e9bce111e9429c
</Property>

Connecting to ODBC Data Sources

Creating models for ODBC data sources is also not supported directly in SPD. Therefore, you must create the model from scratch and import it into the BDC Service Application. The following code shows what an ODBC connection looks like in the model XML:

<LobSystems>
 <LobSystem Name="ODBC" Type="Database">
  <LobSystemInstances>
   <LobSystemInstance Name="ODBCInstance">
    <Properties>
     <Property Name="AuthenticationMode" Type="System.String">
      PassThrough
    </Property>
     <Property Name="DatabaseAccessProvider" Type="System.String">
      Odbc
     </Property>
     <Property Name="RdbConnection Dsn" Type="System.String">
      MY_DSN_NAME
     </Property>
     <Property Name="RdbConnection uid" Type="System.String">
      MY_USERNAME
     </Property>
     <Property Name="RdbConnection pwd" Type="System.String">
       MY_PASSWORD
     </Property>
     <Property Name="RdbConnection Trusted_Connection" Type="System.String">
      yes
     </Property>
     <Property Name="RdbConnection integrated security" Type="System.String">
      true
     </Property>
    </Properties>
   </LobSystemInstance>
  </LobSystemInstances>
 </LobSystem>
</LobSystems>

The AuthenticationMode property is set to Passthrough, but the credentials used to access the data source are provided in the RdbConnection uid and RdbConnection pwd properties. You can see how these properties build an ODBC connection string similar to the way it is done for Microsoft SQL Server.

Connecting to OLEDB Data Sources

Just like Oracle and ODBC, creating models for OLEDB data sources is not supported by the SPD tooling. The BDC Metadata Model must be created from scratch and imported into the BDC Service Application. The following code shows what an OLEDB connection looks like for a Microsoft Access database:

<LobSystems>
 <LobSystem Type="Database" Name="MySystem">
  <LobSystemInstances>
  <LobSystemInstance Name="MySystemInstance">
   <Properties>
    <Property Name="AuthenticationMode" Type="System.String">
      PassThrough
    </Property>
    <Property Name="DatabaseAccessProvider" Type="System.String">
     OleDb
    </Property>
    <Property Name="RdbConnection Data Source" Type="System.String">
     C:Mydatabase.mdb
    </Property>
    <Property Name="RdbConnection Persist Security Info"
     Type="System.String">
     false
    </Property>
    <Property Name="RdbConnection Connection Provider" Type="System.String">
     Microsoft.ACE.OLEDB.12.0
    </Property>
   </Properties>
  </LobSystemInstance>
 </LobSystem>
</LobSystems>

The AuthenticationMode property is set to Passthrough. The RdbConnection Data Source refers to the location of the MS Access file. The RdbConnection Connection Provider specifies the OLEDB provider to use for the connection.

Connecting with the WCF Service Connector

The WCF Connector provides connections to web services, including Windows Communication Foundation (WCF) and ASP.NET web services. The SharePoint Designer provides tooling for connecting with web services and their associated meta data so that operations can be defined against the services. The key to using a web service as an external data source is for SPD to access the meta data of the web service that describes the available operations. SPD supports accessing service meta data through both Web Service Description Language (WSDL) and meta data exchange.

Connecting to ASP.NET Web Services

ASP.NET web services typically expose WSDL documents to describe the available operations. WSDL documents are accessed using the endpoint of the service appended with the query string ?WSDL. Figure 13-20 shows the Connection Properties dialog box with settings for an ASP.NET web service. Table 13-4 describes the settings in the dialog box.

FIGURE 13-20

TABLE 13-4: Web Service Connection Settings

The settings in the Connection Properties dialog box are used to generate the properties for the LobSystem and LobSystemInstance elements of the model. The exact properties presented in the model can vary according to the selections made in the dialog box. The following code shows how the settings in Figure 13-20 are translated into the BDC Metadata Model:

<LobSystems>
 <LobSystem Type="Wcf" Name="ASP.Net Web Service">
  <Properties>
   <Property Name="ReferenceKnownTypes" Type="System.Boolean">
    True
   </Property>
   <Property Name="WcfMexDiscoMode" Type="System.String">
    Disco
   </Property>
   <Property Name="WcfMexDocumentUrl" Type="System.String">
    http://webserver.aw.com:5000/aspnet/Service.asmx?WSDL
   </Property>
   <Property Name="WcfProxyNamespace" Type="System.String">
    BCSServiceProxy
   </Property>
   <Property Name="WildcardCharacter" Type="System.String">*</Property>
   <Property Name="WsdlFetchAuthenticationMode" Type="System.String">
    PassThrough
   </Property>
  </Properties>
  <Proxy>EABvmrlbJFsHTQdvYZp1cdN6TVqQAAMA. . .AAAAAA</Proxy>
  <LobSystemInstances>
   <LobSystemInstance Name="Item Service">
    <Properties>
     <Property Name="UseStsIdentityFederation" Type="System.Boolean">
      False
     </Property>
     <Property Name="WcfAuthenticationMode" Type="System.String">
      PassThrough
     </Property>
     <Property Name="WcfEndpointAddress" Type="System.String">
      http://webserver.aw.com:5000/aspnet/Service.asmx
     </Property>
     <Property Name="WcfImpersonationLevel" Type="System.String">
      Identification
     </Property>
    </Properties>
   </LobSystemInstance>
  </LobSystemInstances>
 </LobSystem>
</LobSystems>

Along with the properties set in the model, you can also notice a Proxy element. This element has been significantly truncated in the code listing but will normally contain a large text string. This large text string is the serialized proxy class generated by the SPD tooling when connecting to the web service. This proxy class is used by BCS to communicate with the web service when calling methods. Serializing the class in the BDC Metadata Model makes the class portable and simplifies deployment to client applications.

Connecting to WCF Web Services

WCF web services expose WSDL just like ASP.NET web services but can also expose a meta data-exchanged (MEX) endpoint to describe the available operations. MEX endpoints can be used by SPD to support generating a proxy class against the service. Figure 13-21 shows the Connection Properties dialog box with settings for a WCF web service exposing a MEX endpoint.

FIGURE 13-21

Just like ASP.NET web services, the values set in the Connection Properties dialog box are used to create the LobSystem and LobSystemInstance elements in the BDC Metadata Model. The properties in the model are the same as ASP.NET web services, but the values are set up to utilize a MEX endpoint instead of a WSDL endpoint.

CREATING METHODS

BCS method stereotypes define the operations that can be performed against an external system. The six method stereotypes supported by SPD are Finder, SpecificFinder, Creator, Updater, Deleter, and AssociationNavigator. These six method stereotypes are used to generate a view of many items, show details for a single item, create a new item, update an existing item, delete an item, and display data relationships, respectively.

Implementing Method Stereotypes

When implementing a method stereotype in the BDC Metadata Model, you use a Method and a MethodInstance element. The Method element defines the input parameters, output parameters, and filters that will be used with the method stereotype. The MethodInstance element defines the type of method stereotype to be implemented.

BDC Metadata Models typically consist of many Method elements defining operations against the external system. Each Method element can consist of one or more MethodInstance elements; however, it is typical to have a one-to-one relationship between Method and MethodInstance elements. This approach simplifies the model and makes it easier to develop the solution. The following code shows the basic XML schema to implement a method stereotype:

<Method Name=[Method Name]>
 <Properties>
  <Property>[Property Value]</Property>
 </Properties>
 <FilterDescriptors>
  <FilterDescriptor Type=["Limit", "PageNumber", "Wildcard", etc]
 </FilterDescriptor>
 <Parameters>
  <Parameter
   Direction=["In", "Out", "InOut", or "Return"]
   Name=[Parameter Name]
   AssociatedFilter=[Name of a FilterDescriptor]>
   <TypeDescriptor
    TypeName=[.NET Framework Type e.g, "System.Int32"] />
  </Parameter>
 </Parameters>
 <MethodInstances>
  <MethodInstance
   Type=["Finder", "SpecificFinder", "Creator", etc]
   Name="MyMethodInstance">
  </MethodInstance>
 </MethodInstances>
</Method>

Defining Properties

A Method element can contain one or more Property elements. These properties are specific to the method implementation and vary depending upon the connector type used to access the External System. Specific values are discussed in detail later in this chapter.

Defining Parameters

A Method element can contain one or more Parameter elements. Parameters are used as inputs and outputs to methods. Parameters are defined as In, Out, InOut, or Return types. The exact set of parameters required is based on the function signature of the method stereotype. For example, a Finder method might not have any In parameters and only a single Return parameter. A SpecificFinder method, on the other hand, might have a single In parameter representing the primary key of a record to return and a single Return parameter containing the record.

Parameter elements always contain one or more TypeDescriptor elements. TypeDescriptor elements are used to map data types in the external system to well-known .NET Framework types that can be used by BCS. The types may be single-value types such as a System.String or can be collections of types. Collections of types are required, for example, when the return value from an external system is a record that contains multiple fields.

Defining Filters

A Method element can contain zero or more FilterDescriptor elements. Filters are used by BCS to provide system or user input to methods. For example, you can set a filter in a view definition for an external list as a way to specify which entity instances to return from an external system. You can also set filters by system, such as when a filter is used to limit return data based on the identity of the current user. Filters are always associated with an input parameter. This association is how the filter value is transmitted to be received from the method implementation. Table 13-5 lists the filters supported by SPD.

TABLE 13-5: BCS Filters

Understanding Stereotype Requirements

The value of the SPD tooling is that it knows how to create the correct set of properties, parameters, and filters for the supported method stereotypes. In cases in which you implement method stereotypes by hand, however, you must be aware of the requirements implicit in each method stereotype. It is not enough to simply designate the Type attribute of a MethodInstance element; the parameters and filters must be defined so that an acceptable method signature results. Table 13-6 lists the parameters required for each method stereotype. Note that filters are generally not required but can be applied as an option to further refine an operation.

TABLE 13-6: Required Parameters

Creating Methods for Databases

Databases are the easiest external data sources to work with in SPD. Because databases have tables, views, stored procedures, primary keys, and foreign keys, it is easier for the SPD tooling to create valid BDC Metadata Models with less human input. After an ECT is created and associated with a database as an external system, the Operation Designer shows the available tables, views, and stored procedures with which to work. You can use any of these objects as the starting point for a method.

Creating Finder Methods

Finder methods return views of the external system. You can use these views to create views in external lists and the Business Data web parts, or to support search. As such, an ECT can support multiple Finder methods. To start creating a Finder method, you can right-click one of the available tables, views, or stored procedures and select New Read List Operation from the context menu. When you create a new Finder method, SPD starts the Read List Wizard.

The first step of the wizard asks for the Operation Name and Operation Display Name of the Finder method. When naming methods, you should adopt a standard and use it consistently. The names that you select appear in several places throughout SharePoint, so it’s a good idea to use a naming standard that end users can read and understand.

The next step in the wizard enables you to set up filters for the Finder method. When you create the first Finder method, SPD automatically marks it as the default Finder method. The default Finder method is the default view used for external lists and is the default method called by the crawler during search indexing. This is important because you do not want to filter the default Finder method in any way so this step in the wizard would be skipped.

When you create subsequent Finder methods, you want to apply filters. Filters are important because they limit the amount of data that can be returned from the external system, thus making the solution more efficient. In the same way that you create views in a standard SharePoint list, you want to create filtered Finder methods in SPD.

In the wizard, you can add a new filter to a Finder method by clicking the Add Filter Parameter button. Clicking this button, however, results only in the creation of an undefined filter, which generates a warning in the wizard. To configure the filter, you must click the Click to Add link, which opens the Filter Configuration dialog. Figure 13-22 shows the wizard with the Filter Configuration dialog open.

FIGURE 13-22

In the Filter Configuration dialog, you can select the type of filter that you want and set properties such as values to ignore. After you complete the settings in the Filter Configuration dialog box, you can close it, but the filter definition is still not complete. In the Properties section of the wizard, you must select the field to be associated with the filter and provide a default value for the filter.

The next step in the wizard asks you to configure the values returned from the external system. Here you will be asked to specify the Identifier for the ECT. The Identifier field is the field that has the value that uniquely identifies an entity instance in the external system. For databases, this is most often the primary key. Select this field and check the Map to Identifier check box.

In this step, you also are prompted to specify fields to be displayed in the External Item Picker dialog box. This dialog is the picker control that appears in Microsoft Word whenever a library uses a field based on an ECT. This is important because the user sees only the fields that you mark, so they should be the fields the end user needs to search. Select each of the fields, and click the Show in Picker check box.

In this step, it’s also a good idea to take a close look at the display name for each field. Make sure that these values are readable because they appear as column headers in external lists. You can also uncheck any fields that you do not want to display in the view. Finally, if you have a DateTime field in the data source that represents the last time the record was edited, mark that field as the Timestamp field. This allows Search to use the field in support of incremental crawls.

Modeling Finder Methods

Using tables, views, or stored procedures as sources result in the creation of different models. This is expected as the syntax to query these sources is different. The following code shows an implementation of the same Finder method using each of the three sources:

<!-- Table -->
<Method IsStatic="false" Name="AllNamesTable">
 <Properties>
  <Property Name="BackEndObject" Type="System.String">
   Names
  </Property>
  <Property Name="BackEndObjectType" Type="System.String">
   SqlServerTable
  </Property>
  <Property Name="RdbCommandText" Type="System.String">
   SELECT [ID] , [Title] , [FirstName] , [MiddleName] , [LastName] ,
   [EmailAddress] , [Phone] FROM [MiniCRM].[Names]
  </Property>
  <Property Name="RdbCommandType"
   Type="System.Data.CommandType, System.Data, Version=2.0.0.0,
   Culture=neutral, PublicKeyToken=b77a5c561934e089">
   Text
  </Property>
  <Property Name="Schema" Type="System.String">
   MiniCRM
  </Property>
 </Properties>
 <Parameters>
  <Parameter Direction="Return" Name="AllNamesTable">
   . . .
  </Parameter>
 </Parameters>
 <MethodInstances>
  <MethodInstance Type="Finder" ReturnParameterName="AllNamesTable"
   Name="AllNamesTable" DefaultDisplayName="All Names Table">
 </MethodInstance>
 </MethodInstances>
</Method>
 
 
<!-- View -->
<Method IsStatic="false" Name="AllNamesView">
 <Properties>
  <Property Name="BackEndObject" Type="System.String">
   vw_GetNames
  </Property>
  <Property Name="BackEndObjectType" Type="System.String">
   SqlServerView
  </Property>
  <Property Name="RdbCommandText" Type="System.String">
   SELECT [ID] , [Title] , [FirstName] , [MiddleName] , [LastName] ,
   [EmailAddress] , [Phone] FROM [dbo].[vw_GetNames]
  </Property>
  <Property Name="RdbCommandType"
   Type="System.Data.CommandType, System.Data, Version=2.0.0.0,
   Culture=neutral, PublicKeyToken=b77a5c561934e089">
   Text
  </Property>
  <Property Name="Schema" Type="System.String">
   dbo
  </Property>
 </Properties>
 <Parameters>
  <Parameter Direction="Return" Name="AllNamesView">
   . . .
  </Parameter>
 </Parameters>
 <MethodInstances>
  <MethodInstance Type="Finder" ReturnParameterName="AllNamesView"
   Name="AllNamesView" DefaultDisplayName="All Names View">
  </MethodInstance>
 </MethodInstances>
</Method>
 
 
<!-- Stored Procedure -->
<Method IsStatic="false" Name="AllNamesProcedure">
 <Properties>
  <Property Name="BackEndObject" Type="System.String">
   sp_GetNames
  </Property>
  <Property Name="BackEndObjectType" Type="System.String">
   SqlServerRoutine
  </Property>
  <Property Name="RdbCommandText" Type="System.String">
   [dbo].[sp_GetNames]
  </Property>
  <Property Name="RdbCommandType"
   Type="System.Data.CommandType, System.Data, Version=2.0.0.0,
   Culture=neutral, PublicKeyToken=b77a5c561934e089">
   StoredProcedure
  </Property>
  <Property Name="Schema" Type="System.String">
   dbo
  </Property>
 </Properties>
 <Parameters>
  <Parameter Direction="Return" Name="AllNamesProcedure">
   . . .
  </Parameter>
 </Parameters>
 <MethodInstances>
  <MethodInstance Type="Finder" ReturnParameterName="AllNamesProcedure"
   Name="AllNamesProcedure" DefaultDisplayName="All Names Procedure">
  </MethodInstance>
 </MethodInstances>
</Method>

The primary difference between each of the three implementations is the set of Property elements used in each. Tables and views, for example, use dynamic SQL statements, whereas the stored procedure uses a direct call to the procedure. Notice that none of the methods have any input parameters or filters defined. If parameters were defined, however, a new stored procedure would need to be defined because any input parameters must be reflected in the stored procedure definition, whereas the dynamic SQL statements written against tables and views can simply be changed in the model.

When a stored procedure with input parameters is used, the wizard presents a screen that enables you to assign filters to the parameters. These filter values can then be set in the view definition of the external list by an end user or as an input parameter in the Business Data web parts. The following code shows a stored procedure that supports a wildcard as an input parameter:

<Method IsStatic="false" Name="NamesByWildcardProcedure">
 <Properties>
  <Property Name="BackEndObject" Type="System.String">
   sp_GetNamesWildcard
  </Property>
  <Property Name="BackEndObjectType" Type="System.String">
   SqlServerRoutine
  </Property>
  <Property Name="RdbCommandText" Type="System.String">
   [dbo].[sp_GetNamesWildcard]
  </Property>
  <Property Name="RdbCommandType"
   Type="System.Data.CommandType, System.Data, Version=2.0.0.0,
   Culture=neutral, PublicKeyToken=b77a5c561934e089">
   StoredProcedure
  </Property>
  <Property Name="Schema" Type="System.String">
   dbo
  </Property>
 </Properties>
 <FilterDescriptors>
  <FilterDescriptor Type="Wildcard" FilterField="LastName" Name="Wildcard">
   <Properties>
    <Property Name="CaseSensitive" Type="System.Boolean">
     false
    </Property>
    <Property Name="IsDefault" Type="System.Boolean">
     false
    </Property>
    <Property Name="UsedForDisambiguation" Type="System.Boolean">
     false
    </Property>
   </Properties>
  </FilterDescriptor>
 </FilterDescriptors>
 <Parameters>
  <Parameter Direction="In" Name="@wildcard">
   <TypeDescriptor TypeName="System.String"
    AssociatedFilter="Wildcard" Name="@wildcard">
    <Properties>
     <Property Name="Order" Type="System.Int32">0</Property>
    </Properties>
    <DefaultValues>
     <DefaultValue
      MethodInstanceName="NamesByWildcardProcedure" Type="System.String">
      A
    </DefaultValue>
    </DefaultValues>
   </TypeDescriptor>
  </Parameter>
  <Parameter Direction="Return" Name="NamesByWildcardProcedure">
   . . .
  </Parameter>
 </Parameters>
 <MethodInstances>
  <MethodInstance Type="Finder"
   ReturnParameterName="NamesByWildcardProcedure"
   Name="NamesByWildcardProcedure"
   DefaultDisplayName="Names by Wildcard Procedure">
  </MethodInstance>
 </MethodInstances>
</Method>

In the code, notice that a FilterDescriptor of type Wildcard has been added to the model. This filter is associated with the LastName field and the @wildcard input parameter. What this does is pass the value of the filter into the @wildcard input parameter. This means that the stored procedure must have a parameter by that exact name available, as shown in the following code:

CREATE PROCEDURE [dbo].[sp_GetNamesWildcard]
@wildcard nvarchar(10)
AS
SELECT ID,Title,FirstName,MiddleName,LastName,Suffix,EMailAddress,Phone
FROM MiniCRM.Names
WHERE LastName LIKE @wildcard + '%'

Remember that the purpose of filters is to retrieve input from either the end user or the system. In the case of the wildcard filter, the idea is to let the end user set up a view based on a partial string search of the last name. In the SharePoint interface, this appears as a value that can be set in the view definition of the external list.

Understanding the Default Finder

The default Finder method deserves special consideration in the design of any BCS solution. As stated previously, the first method Finder method created in SPD will be the default. When you create subsequent Finder methods, the wizard presents a check box that you can use to change the default Finder. If you do nothing, however, it will always be the first one created.

The default Finder method serves two important purposes. First, this is the method that generates the default view for an external list. Second, this method is used by the search indexer to retrieve records during the crawl process. The method is identified as the default Finder through the Default attribute of the MethodInstance element and the target of the crawl through the RootFinder property, as shown in the following code:

<MethodInstance
 Type="Finder"
 ReturnParameterName="AllNames"
 Default="true"
 Name="AllNames"
 DefaultDisplayName="All Names">
 <Properties>
  <Property Name="RootFinder" Type="System.String"></Property>
 </Properties>
</MethodInstance>

When SPD defines a Finder as both the default Finder and the root Finder, it can cause significant problems in your solutions. This is because the default Finder should be filtered, but the root Finder should not. Finder methods should generally have filters on them to limit the number of rows returned. If they do not have filters, BCS throws an error if they return more than 2,000 rows to an external list. However, root Finders should never be filtered because the filtering excludes items from the search index so that they never appear in search results. Therefore, you must manually edit the BDC Metadata Model to assign the default Finder and root Finder to different Finder methods unless you know that your data source will never exceed 2,000 rows, which is the limit for external lists.

Creating Other Methods

Creating SpecificFinder, Creator, Updater, and Deleter methods generally follows the same concepts as Finder methods. The wizard walks you through the steps necessary to define appropriate parameters and filters. In the case of stored procedures, remember that any required input parameters or filters must be explicitly available as parameters in the stored procedure.

Creating Methods for Web Services

Web services are generally more difficult to work with than databases because the form of the exposed methods in a web service can vary widely. There is no explicit schema available for tables and views, and there are no primary/foreign keys available for inferring relationships. Therefore, the design of a BCS solution that utilizes web services must be carefully considered.

Although you can use an existing web service as an external data source, the requirements of the method stereotypes more often lead to the creation of a custom service for a BCS solution. Custom services should expose methods that correlate closely to BCS method stereotypes. The following code shows the programmatic interface for a simple WCF service:

[ServiceContract]
public interface IService
{
    [OperationContract]
    List<Customer> CrawlCustomers();
    [OperationContract]
    List<Customer> GetCustomers(int Limit);
    [OperationContract]
    List<Customer> GetCustomersByRegion(string Region);
    [OperationContract]
    Customer GetCustomer(string Id);
    [OperationContract]
    void CreateCustomer(string FirstName, string LastName);
    [OperationContract]
    void UpdateCustomer(string Id, string FirstName, string LastName);
    [OperationContract]
    void DeleteCustomer(string Id);
}
[DataContract]
public class Customer
{
    [DataMember]
    public string Id { get; set; }
    [DataMember]
    public string FirstName { get; set; }
    [DataMember]
    public string LastName { get; set; }
}

The exposed methods of the web service are designed in accordance with the requirements of Table 13-4. The Finder methods return collections of Customer entity instances, whereas the SpecificFinder returns only a single entity instance. The Creator, Updater, and Deleter methods return void.

Three Finder methods are exposed. The CrawlCustomers method has no filter and is intended for use by the indexer. The GetCustomers method accepts a limit filter to prevent causing errors in the external list if too many results are returned. The GetCustomerByRegion accepts a filter to limit the return results to customers in a given region. These parameters all appear in the SPD Wizard so that they can be mapped to the appropriate filter types.

Defining Associations

Associations are relationships between ECTs. SPD supports one-to-many, self-referential, and reverse associations. One-to-many associations return many related entity instances from a single parent entity instance. Self-referential associations return entity instances of the same type as the parent entity instance. Reverse associations return a parent entity instance from a single child entity instance. Associations created in SPD are of type AssociationNavigator.

Creating One-to-Many Associations

The most common type of association in BCS solutions is the one-to-many association, whereby a parent entity instance is related to many child entity instances. This type of association supports scenarios such as when a single Customer has many Orders or when a single Client has many Contacts. This design is identical to the way database tables are related.

To create a one-to-many association, you must first define both External Content Types. The child ECT should be defined so that it contains a foreign key related to the parent ECT. This means that the Order ECT would contain a CustomerID field or the Contact ECT would contain a ClientID field. If the relationship is based on a database table or view, nothing else needs to be done. If the relationship is based on a stored procedure or web service method, you must also create a stored procedure or method that accepts the parent entity instance identifier and returns the child entity instances.

After you have the ECTs defined, the new association can be created from the Operation Designer. If the relationship uses tables or views, select the child table or view. If the relationship uses stored procedures or web services, select the procedure or method that accepts the parent entity instance identifier and return the child entity instances. Right-click and select New Association in the context menu to start the Association Wizard.

In the Association Wizard, you map the child ECT to the parent ECT by clicking the Browse button and selecting the parent ECT from a list. After you select the parent ECT, you must map the identifier of the parent ECT to the foreign key in the child ECT.

In the next screen of the wizard, you map the input parameter of the child to the foreign key. Most of the time this field has the same name as the identifier that was selected in the first screen, but they can be different.

<Method IsStatic="false" Name="ContactsForClient">
 <Properties>
  <Property Name="BackEndObject" Type="System.String">
   ClientContacts
  </Property>
  <Property Name="BackEndObjectType" Type="System.String">
   SqlServerTable
  </Property>
  <Property Name="RdbCommandText" Type="System.String">
   sp_GetAllClientContacts
  </Property>
  <Property Name="RdbCommandType" Type="System.Data.CommandType,
   System.Data, Version=2.0.0.0, Culture=neutral,
   PublicKeyToken=b77a5c561934e089">
   StoredProcedure
  </Property>
  <Property Name="Schema" Type="System.String">dbo</Property>
 </Properties>
 <Parameters>
  <Parameter Direction="In" Name="@ClientID">
   <TypeDescriptor TypeName="System.Int32" IdentifierName="ClientID"
    IdentifierEntityName="Client"
    IdentifierEntityNamespace="http://clients_web"
    ForeignIdentifierAssociationName="ContactsForClient"
    Name="ClientID" />
  </Parameter>
  <Parameter Direction="Return" Name="ContactsForClient">
   . . .
  </Parameter>
 </Parameters>
 <MethodInstances>
  <Association Name="ContactsForClient" Type="AssociationNavigator"
   ReturnParameterName="ContactsForClient"
   DefaultDisplayName="Contacts For Client">
   <Properties>
    <Property Name="ForeignFieldMappings" Type="System.String">
     &lt;?xml version="1.0" encoding="utf-16"?&gt;
     &lt;ForeignFieldMappings
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
     &lt;ForeignFieldMappingsList&gt;
     &lt;ForeignFieldMapping ForeignIdentifierName="ClientID"
     ForeignIdentifierEntityName="Client"
     ForeignIdentifierEntityNamespace="http://clients_web"
     FieldName="ClientID" /&gt;
     &lt;/ForeignFieldMappingsList&gt;
     &lt;/ForeignFieldMappings&gt;
    </Property>
   </Properties>
   <SourceEntity Namespace="http://clients_web" Name="Client" />
   <DestinationEntity Namespace="http://clients_web" Name="Contact" />
  </Association>
 </MethodInstances>
</Method>

One-to-many relationships are used in the SharePoint interface to display entity instances. In an external list of child entity instances, the foreign key for the parent entity instance can be set using the picker, as shown in Figure 13-23. If a profile page is defined for the parent ECT, it automatically is created to contain a list of related child ECTs.

FIGURE 13-23

Creating Self-Referential Associations

Self-referential associations are created using the same approach as one-to-many relationships. The difference is that a self-referential relationship uses the same ECT as the parent and the child. Therefore, the ECT must have a separate field defined that acts like the foreign key in a one-to-many relationship but instead refers to an entity instance of the same type.

For example, consider creating an organizational chart from a single table of employees. The table contains an ID field as the primary key and a ManagerID field to relate the current record to another record in the table. Using this information, an association can be created between the Employee ID and ManagerID field, as shown in the following code:

<Method IsStatic="false" Name="EmployeesForManager">
 <Properties>
  <Property Name="BackEndObject" Type="System.String">
   Employees
  </Property>
  <Property Name="BackEndObjectType" Type="System.String">
   SqlServerTable
  </Property>
  <Property Name="RdbCommandText" Type="System.String">
   SELECT [ID] , [ManagerID] , [Title] , [FirstName] , [MiddleName] ,
   [LastName] , [EmailAddress] , [Phone] FROM [dbo].[Employees]
   WHERE [ManagerID] = @ID
  </Property>
  <Property Name="RdbCommandType"
   Type="System.Data.CommandType, System.Data, Version=2.0.0.0,
   Culture=neutral, PublicKeyToken=b77a5c561934e089">
   Text
  </Property>
  <Property Name="Schema" Type="System.String">
   dbo
  </Property>
 </Properties>
 <Parameters>
  <Parameter Direction="In" Name="@ID">
   <TypeDescriptor TypeName="System.Int32" IdentifierName="ID"
    ForeignIdentifierAssociationName="EmployeesForManager"
    Name="ManagerID" />
  </Parameter>
  <Parameter Direction="Return" Name="EmployeesForManager">
   . . .
  </Parameter>
 </Parameters>
 <MethodInstances>
  <Association Name="EmployeesForManager" Type="AssociationNavigator"
   ReturnParameterName="EmployeesForManager"
   DefaultDisplayName="Employees for Manager">
   <Properties>
    <Property Name="ForeignFieldMappings" Type="System.String">
     &lt;?xml version="1.0" encoding="utf-16"?&gt;
     &lt;ForeignFieldMappings
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
     &lt;ForeignFieldMappingsList&gt;
     &lt;ForeignFieldMapping ForeignIdentifierName="ID"
     ForeignIdentifierEntityName="Employee"
     ForeignIdentifierEntityNamespace="http://bcs/orgchart"
     FieldName="ManagerID" /&gt;
     &lt;/ForeignFieldMappingsList&gt;
     &lt;/ForeignFieldMappings&gt;</Property>
   </Properties>
   <SourceEntity Namespace="http://bcs/orgchart" Name="Employee" />
   <DestinationEntity Namespace="http://bcs/orgchart" Name="Employee" />
  </Association>
 </MethodInstances>
</Method>

The key to creating the self-referential relationship is the SQL query that returns entity instances when the ManagerID=ID. Note that SPD does not always create this SQL query correctly when you are creating a new self-referential association in the tooling. Therefore, you should be sure to export and examine the query after the method is created. After it is created correctly, you can use the relationships like any other.

Creating Reverse Associations

Reverse associations return a single parent entity instance for a child entity instance. Reverse associations are not supported for tables and views but are supported for stored procedures and web services because the reverse association in not inherent in the database schema. It must be explicitly programmed through a stored procedure or web service. For example, you could create a stored procedure that takes the identifier for a Contact and returns the parent Client entity instance, as shown in the following code:

CREATE PROCEDURE sp_GetClientByContactID
@ClientContactID int
AS
Select Clients.ClientID, Clients.Name, Clients.Address1, Clients.Address2,
Clients.City, Clients.Province, Clients.PostalCode, Clients.Country,
Clients.Phone, Clients.Fax, Clients.Web
From Clients
Inner Join ClientContacts
On Clients.ClientID = ClientContacts.ClientID
Where ClientContactID = @ClientContactID

After the stored procedure is written, open the Operation Designer for the child ECT. Select the stored procedure, right-click, and select New Reverse Association from the context menu. As with the other associations, you can then browse and select the parent ECT.

WORKING WITH EXTERNAL LISTS

External lists support many of the same capabilities as standard SharePoint lists, such as custom list actions and custom forms. In SharePoint Server 2013, they have been enhanced to also support event handlers and notifications. This section takes a closer look at some of the capabilities of external lists.

Creating Custom List Actions

Custom list actions allow you to add a new button to the List Item menu, View Item Ribbon, New Form Ribbon, View Form Ribbon, or the Edit Form Ribbon. The target of the button can be an existing form or a URL. For standard lists, you can also initiate a workflow from the button, but external lists do not support this function.

New custom list actions are created from SPD by selecting the list and clicking the Custom Action button in the Ribbon. This button then opens the Create Custom Action dialog box.

Creating Custom Forms

For every standard and external list, a set of forms is created to display, edit, and add items. Using the SharePoint Designer, you can create and customize these forms as ASPX pages or as InfoPath forms. This capability helps you enhance data presentation and perform field-level validation on items.

Creating ASPX Forms

When you create an external list, new, edit, and display forms are automatically created as appropriate, based on the operations defined for the associated ECT. Using SPD, you can see these forms by clicking on the Lists and Libraries object followed by the list of interest. The existing forms are listed on the summary page.

The default forms created for the external list utilize the List Form web part (LFWP). The LFWP executes Collaborative Application Markup Language (CAML) queries against the external list to display items. Unfortunately, the LFWP does not support modifying its presentation; a new form must be created instead.

Clicking the New button above the form list in the summary page opens the Create New List Form dialog box. This dialog box is used to create new, edit, and display forms that are based on the Data Form web part (DFWP). The DFWP utilizes XSLT to transform list data into a display. Modifying this XSLT can easily change the presentation of list data.

For example, consider an external list that returns information about SharePoint images. The BCS solution has a column called Path that returns the path to the image. In a simple BCS solution, Path appears as a column and the user simply sees the text of the URL. A better experience, however, would be to show the image itself. This can be done by adding a new display form and modifying the XSLT, as shown in the following code.

<img>
 <xsl:attribute name="src">
  <xsl:value-of select="@Path"/>
 </xsl:attribute>
</img>

In addition to using SPD, you can also modify the list forms inside of the SharePoint 2013 interface. While viewing the external list, click the List tab on the Ribbon. The Modify Form Web Parts button enables you to select a form to modify. The difference is that you will be modifying the web part that is rendering the list using the Properties pane.

Creating InfoPath Forms

Instead of ASPX pages, you can choose to create custom InfoPath forms for the external list. InfoPath form creation can be initiated directly from SPD by clicking the Design Forms in InfoPath button from the List Summary page. This action opens InfoPath with a default form that you can edit.

InfoPath forms are easier to create and offer simpler styling and advanced controls. Using InfoPath, you can take advantage of lists and drop-downs as well as styles and themes. When the form is complete, you must save it and then execute a Quick Publish. Quick publishing is available by clicking the File tab in InfoPath. When published, the form is available on the new, edit, and display forms.

SUMMARY

Business Connectivity Services (BCS) provides the infrastructure and tooling necessary to bring external data into SharePoint. When creating BCS solutions for SharePoint 2013, the SharePoint Designer should be considered as your primary tool. The tooling support in SPD is easier to use than the equivalent tooling in Visual Studio. In addition, you can export the model and edit it by hand to include capabilities not supported in the SPD tooling. In short, you should use SPD for the majority of your solutions that are based on databases and web services. Complex solutions that use SharePoint apps or custom-coded connectors are covered in Chapter 14.