BCS allows accessing external data by using a CRUDQ (create, read, update, delete, and query) approach. It is a service application that ships natively with any edition of SharePoint 2013, including SharePoint Foundation 2013. The edition of SharePoint you install, however, will determine the exact mix of features included. Figure 14-1 presents an architectural schema of BCS.

Figure 14-1. The architectural schema of the BCS application in SharePoint 2013.

The service is based on a core engine named Business Data Connectivity (BDC) that uses a runtime engine to connect with various data providers. The supported data providers are

Regardless of the type of data provider that you use, the BDC engine stores configurations and shapes of data sources in a dedicated repository, which is called External Content Type Repository and corresponds to a dedicated database file. SharePoint is a presentation layer for data managed using BCS, and every item that you read or write data from an external data source through BCS, that data corresponds to an external content type (ECT), which is consumed through an external list. SharePoint also provides several Web Parts out of the box for rendering, filtering, and searching data provided by BCS. In addition, an external list renders with an appearance and behavior that is almost the same as a standard SharePoint list of items. The capability to render external data in SharePoint as if it were internal data is a key feature of BCS; you can provide end users with a common experience for both internal and external data.

Also, if you have the SharePoint Server 2013 edition, you can consume BCS data (even offline) from client applications such as those of the Microsoft Office 2013 suite, using the Client-Side Object Model (CSOM) or the BDC Client Runtime, which is a client-side engine that SharePoint can automatically install on any client hosting Office 2013. The capability to work offline on the client side makes BCS very interesting for partially connected solutions such as smart clients and Office Business Applications. For example, you can connect a Microsoft Outlook 2013 client to an external list published through SharePoint and BCS, and take its data offline. This allows users to work with the data, even when disconnected from the network. The offline data will be saved in local storage on the client PC, within the current user profile folder. For security reasons, the data is also encrypted on the local folder of the end user. If the user changes any of the items while offline, when he or she goes back online, the BDC Client Runtime will be able to synchronize the client-side data cache with the server-side online data.

By default, the client accessibility and the offline capabilities are available only in Outlook 2013. However, the BDC Client Runtime is provided with an object model, which you can use from any .NET application. This means that you can write custom code in Microsoft Word 2013, Microsoft Excel 2013, and so on. You could also write some code in a custom .NET smart client of your own. An interesting aspect to note is that the offline data cache is unique on a per-user basis. Thus, offline data will be shared between multiple client applications, avoiding data duplication and concurrency conflicts within the same user’s session.

To consume an external data source using BCS, you need to model the ECTs that you will use, together with a formal definition of the LOB system you are going to consume. This information can be defined with an XML file, built according to a BCS-specific XML schema. You can build the XML file by using a tool like SharePoint Designer 2013, Microsoft Visual Studio 2012, or any other XML editor. Depending on the type of data provider you plan to use, any of these applications could be useful. For example, SharePoint Designer 2013 is the ideal solution for modeling SQL Server–based solutions and WCF/web service–based solutions. Conversely, Visual Studio 2012 works very well with custom .NET assemblies and custom connectors. A generic XML editor is suitable for all the other situations.

It’s time to begin consuming some data using BCS. As an example, consider a SQL Server database containing some hypothetical records of a customer relationship management (CRM) system. Figure 14-2 shows the schema of the target database that accompanies this chapter. Notice that the Customers table contains a list of orders that consists of OrdersRows, which is related to a table of products.

Figure 14-2. The schema of a sample CRM database that you will manage by using BCS.

As previously stated, the ideal tool for modeling a BCS connection to a DBMS is SharePoint Designer 2013. Start the application and open the target SharePoint site. Move to the External Content Types section in the Site Objects menu on the left side of the UI, as shown in Figure 14-3.

Figure 14-3. The Site Objects menu of SharePoint Designer 2013, shown with External Content Types highlighted.

To create a new ECT, on the ribbon, under the New group, click External Content Type. A window appears (see Figure 14-4), in which you will set up a CRMCustomer entity corresponding to the records in the Customers table of the target DBMS.

Figure 14-4. The window for creating a new ECT.

Specifically, you need to provide the following information:

To define the concrete data source configuration, click the Click Here To Discover External Data Sources And Define Operations link adjacent to External System, or click the Operations Design View ribbon command.

A second page appears. You can either click the Add Connection button to define a new data connection, or choose an existing data connection in the Data Source Explorer area. When you add a new connection, you must determine the type of data source to which you will connect. SharePoint Designer 2013 gives you three options:

If you select SQL Server, a dialog box appears, in which you must provide the connection string information. You also need to configure an authentication method (this will be covered in depth in the next section). For the sake of simplicity, in the current example, use the default value Connect With User’s Identity, which corresponds to a pass-through connection that will use the identity of the user at run time. If the web application is not configured to authenticate with Windows credentials, the NT Authority/Anonymous Logon account will be passed to the external system.

After defining the connection string, you are presented with a list of tables, views, and stored procedures that are available in the external database. Right-click an item (table, view, or routine) in the Data Source Explorer window, and a contextual menu will appear, as shown in Figure 14-5. From this menu, you can add operations for managing data. Each operation corresponds to a method that will allow interaction with the data source. Using the SharePoint Designer 2013 interface, you can define the following operations:

Figure 14-5. The Operations Designer window for an ECT.

In addition, the context menu contains a New Association command with which you can create a relationship between two ECTs in a master/detail fashion. This last topic is covered in the “Associating entities” section later in the chapter.

When you click the Create All Operations command at the top of the context menu, a wizard will guide you through three simple steps, and then create all the desired operations. The pages of the wizard are

Figure 14-6 depicts the main window for managing the ECT with all operations created and fields defined. Now you are almost ready for consuming the list of ECTs, but pay attention to two key points:

Figure 14-6. The window for creating a new ECT, completely configured.

Save the newly defined ECT by clicking the Save button in the upper-left corner. Then, open SharePoint Central Administration (SPCA) and browse to the management page of the BDC Services service application, as shown in Figure 14-7.

From this page, you can do the following:

Figure 14-7. The SPCA page for managing BDC Services.

Select the CRMCustomer check box, and then on the ribbon, click Set Object Permission or select Set Metadata Store Permissions. In the window that appears, you can define permissions for a specific user or group. The available permission mask allows you to define four permissions:

You can propagate permissions on descendant items to work with a permission inheritance model.

The minimum requirement for viewing and managing ECT data is to have both the Selectable In Clients and the Execute permissions applied.

Now you are ready to create an external list for managing the list of customers of the SampleCRM database. You can create the external list from SharePoint Designer 2013 or from the web browser. For this exercise, use the web browser. Browse to the target site where you want to make the list available and choose the menu item to create a new list instance. Choose an External List template and create it. You will be asked to provide the standard properties of a new list (name and quick launch behavior) and the name for the ECT. Select the target ECT, and you are done. Figure 14-8 illustrates the result. Notice that the user experience is exactly the same as browsing a native list of SharePoint.

Figure 14-8. Browsing the Customers table through BCS in SharePoint 2013.

The Business Data Connectivity engine provides several authentication modes through which you can access a target data source:

Figure 14-9 shows these modes presented by SharePoint Designer 2013 while a data source is being configured.

Figure 14-9. The connection properties of a BCS data source with the available authentication modes.

For the sake of clarity, consider an example of configuring a WindowsCredentials authentication mode. First, you will need to configure an application in the Secure Store Service administration page. Open SPCA, navigate to the Application Management section, and choose the Manage Service Application page. There, assuming you have already configured the BCS service and the Secure Store service, you have the opportunity to access the administration page of the Secure Store service. In case this is the first time you are using the Secure Store service, you will need to generate a new key for securely storing credentials. You can accomplish this task by clicking the Generate New Key ribbon button under the Key Management ribbon group. Providing a secure passphrase, you will be able to generate a new key. Then you can start creating a new application by clicking the New ribbon button in the Manage Target Application ribbon group. A wizard will ask you for some information about the target application. Figure 14-10 shows the first step of the wizard.

Figure 14-10. The first step of the Create New Secure Store Target Application wizard.

Through this first step, you can configure the target application ID, which is fundamental information that you will need to reference while configuring the BCS data source. Moreover, aside from some descriptive information, you will need to provide the target application type. The available values are:

Suppose you want all the authenticated users to use a shared set of credentials for accessing the SampleCRM database. Thus, you should choose Group as the value for the target application type. Notice that you cannot change the target application type value after you have completed the configuration of a target application.

After you configure these properties, click Next, and the wizard will prompt you with a screen like the one in Figure 14-11.

Figure 14-11. The second step of the Create New Secure Store Target Application wizard.

As you can see, the page allows for providing the fields describing the credentials that will be associated with each group of credentials. In the case of a set of Windows credentials, the fields will be Windows Username and Windows Password. The last step of the wizard, illustrated in Figure 14-12, asks for the administrators of the new target application, as well as for the users and groups that will be in target for the application.

Figure 14-12. The last step of the Create New Secure Store Target Application wizard.

After you have created the target application, you need to configure a set of credentials for the target group. Click the Set ribbon button in the Credentials ribbon group after having selected the target application. Figure 14-13 shows the pop-up window for configuring the credentials.

Figure 14-13. The Set Credentials For Secure Store Target Application (Group) pop-up window.

Now, in order to use the new Secure Store target application, you need to configure the data source in SharePoint Designer 2013. For example, select Impersonate Windows Identity and provide the target application ID you defined at the very beginning of the Secure Store service application.

You can export the ECT model created in the previous section by using SharePoint Designer 2013 or via the management page of the BCS service application. If you try to export the Customer ECT definition, an XML file with extension .bdcm (meaning BDC model) will be generated; it will look similar to The BDCM file that defines the Customer ECT retrieved from the SampleCRM database.

<?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="CRMCustomer" xmlns="http://schemas.microsoft.com/windows/2007/
BusinessDataCatalog">
  <AccessControlList>
    <AccessControlEntry Principal="sharepointadministrator">
      <Right BdcRight="Execute" />
      <Right BdcRight="SetPermissions" />
      <Right BdcRight="SelectableInClients" />
    </AccessControlEntry>
  </AccessControlList>
  <LobSystems>
    <LobSystem Type="Database" Name="SampleCRM">
      <Properties>
        <Property Name="WildcardCharacter" Type="System.String">%</Property>
      </Properties>
      <AccessControlList>
        <!-- Code omitted for the sake of brevity -->
      </AccessControlList>
      <Proxy />
      <LobSystemInstances>
        <LobSystemInstance Name="SampleCRM">
          <Properties>
            <!-- Here is the database connection information -->
            <!-- Code omitted for the sake of brevity -->
          </Properties>
        </LobSystemInstance>
      </LobSystemInstances>
      <Entities>
        <Entity Namespace="http://schemas.devleap.com/SampleCRM" 
        Version="1.0.0.0"
        EstimatedInstanceCount="10000" Name="CRMCustomer" 
        DefaultDisplayName="CRMCustomer">
          <Properties>
            <Property Name="OutlookItemType" 
            Type="System.String">Contact</Property>
          </Properties>
          <AccessControlList>
            <!-- Code omitted for the sake of brevity -->
          </AccessControlList>
          <Identifiers>
            <Identifier TypeName="System.String" Name="CustomerID" />
          </Identifiers>
          <Methods>
            <Method Name="Create" DefaultDisplayName="CRMCustomer Create">
              <Properties>
                <Property Name="RdbCommandType" Type="System.Data.CommandType,
                System.Data, Version=4.0.0.0, Culture=neutral,
                PublicKeyToken=b77a5c561934e089">Text</Property>
                <Property Name="RdbCommandText" Type="System.String">INSERT INTO[dbo].[Customers]([CustomerID] , [ContactName] , [CompanyName] , [Email] , [Enabled])VALUES(@CustomerID , @ContactName , @CompanyName , @Email , @Enabled) SELECT[CustomerID] FROM [dbo].[Customers] WHERE [CustomerID] = @CustomerID</Property>
                <Property Name="BackEndObjectType" 
                Type="System.String">SqlServerTable</Property>
                <Property Name="BackEndObject"
                  Type="System.String">Customers</Property>
                <Property Name="Schema" Type="System.String">dbo</Property>
              </Properties>
              <AccessControlList>
                <!-- Code omitted for the sake of brevity -->
              </AccessControlList>
              <Parameters>
                <Parameter Direction="In" Name="@CustomerID">
                  <TypeDescriptor TypeName="System.String" CreatorField="true"
                  IdentifierName="CustomerID" Name="CustomerID">
                    <Properties>
                      <Property Name="Size" Type="System.Int32">10</Property>
                    </Properties>
                    <Interpretation>
                      <NormalizeString FromLOB="NormalizeToNull"
                      ToLOB="NormalizeToEmptyString" />
                    </Interpretation>
                  </TypeDescriptor>
                </Parameter>
                <!-- Code omitted for the sake of brevity -->
              </Parameters>
              <MethodInstances>
                <MethodInstance Type="Creator" ReturnParameterName="Create"
                  ReturnTypeDescriptorPath="Create[0]" Default="true"
                  Name="Create" DefaultDisplayName="CRMCustomer Create">
                    <AccessControlList>
                      <!-- Code omitted for the sake of brevity -->
                    </AccessControlList>
                </MethodInstance>
              </MethodInstances>
            </Method>
            <!-- Code omitted for the sake of brevity -->
          </Methods>
        </Entity>
      </Entities>
    </LobSystem>
  </LobSystems>
</Model>

The main element of a BDCM file is the Model tag. This is the root element of the document, and it wraps an entire BDC model definition. Model has a dedicated AccessControlList element, and it defines one or more LobSystem definitions. A LobSystem element defines from an abstract viewpoint an external data source. A concrete data source is represented by a LobSystemInstance element, instead. Each ECT in a LobSystem is described by an Entity element, which declares a new ECT, together with its Identifiers and Methods elements. A single model in general defines a set of entities. Meanwhile, the Method elements are defined using single Method elements and are instantiated using elements of type MethodInstance. Each MethodInstance features a Type attribute, which defines the typology of method instance. Table 14-1 lists the available values for the MethodInstance/@Type attribute.

When you define a BDC model, regardless of the data provider you use on the back end, you end up defining a file such as the one shown in The BDCM file that defines the Customer ECT retrieved from the SampleCRM database and using methods like the ones illustrated here. SharePoint Designer 2013 and Visual Studio 2012 support just the most frequently used method instance types, while the others should be defined manually in the BDCM file using an XML editor.

If you have SharePoint Server 2013, you can experience the offline capabilities offered by BCS. Browse to and select an external list, such as the one you created in the previous section. To connect your list to Outlook 2013 and make it available offline, on the ribbon, click Connect To Outlook (see Figure 14-14).

The Outlook offline capability is available because you defined the ECT with an Office Contact behavior. A temporary window appears, displaying the message “Preparing External List For Synchronization With Outlook.”

Figure 14-14. The ribbon of an external list with the Connect To Outlook command highlighted.

Next, an installer dialog window appears (Figure 14-15), asking the end user for permission to install the BDC Client Runtime on the client side (if it is not already installed) and to install the model schema for the entity that you are connecting with Outlook.

Figure 14-15. The pop-up dialog box that appears when installing the client model and consuming the ECT.

Click the Install button if the displayed information is acceptable; click Don’t Install if it’s not.

Figure 14-16 shows the final output of the offline list in Outlook 2013.

Figure 14-16. The list of contacts available in Outlook 2013 and corresponding to the list of customers.

Now you can browse and edit data either from SharePoint 2013 within the web browser or using Outlook 2013, or you can manage data directly in the database storage. Regardless of the interface you use for managing data, all your modifications will be sooner (online) or later (offline) synchronized with the back-end database.

As soon as you connect the list to the Microsoft Office client platform, the BDC Client Runtime creates a folder under the local user’s profile path (which, for example, could be C:UsersYour UserNameAppDataLocalMicrosoftBCS), where the offline data is stored. Notice that the folder is green, meaning it is encrypted. There you will find an SDF file of SQL Server Compact Edition, as well as a PST offline cache file.

Using the standard SharePoint 2013 UI for accessing data stored in a DBMS with CRUDQ support is undoubtedly interesting and challenging. For security and privacy reasons, however, many companies prefer to lock and secure their databases, preventing direct data access from clients or even servers. In these situations, articulated business solutions built on top of the database provide access to data filtered by business rules and security policies. Quite often, the business rules are exposed or published through SOAP services, eventually implemented using WCF.

The BCS support for connecting with SOAP services over HTTP (web services), optionally implemented with WCF, can also make these kinds of applications available in SharePoint. BCS can consume any SOAP service that offers the minimum set of operations that is mandatory for the WCF/web service connector of BCS. For a minimal implementation that is capable of reading data with a read-only approach, you need one SOAP operation corresponding to a Finder method instance type and another supporting the SpecificFinder type. From a SOAP perspective, a Finder method is an operation that optionally accepts some filters and returns a collection of entities. A SpecificFinder operation accepts an identifier and returns the corresponding entity. Specifically, every returned entity should have an identifying property, and the result of a SpecificFinder operation has to return an entity with at least the same properties as the result of the Finder operation. There cannot be a Finder method that returns more information than a SpecificFinder method. A WCF service contract satisfying the read-only requirements for WCF shows a WCF service contract satisfying these requirements.

[ServiceContract(Namespace = "http://schemas.devleap.com/CustomerService")]
public interface ICustomerService {
    [OperationContract]
    Customer GetCustomerById(String customerID);
    [OperationContract]
    Customers ListAllCustomers();
}
[DataContract(Name = "Customer", Namespace = "http://schemas.devleap.com/Customers")]
public class Customer {
    [DataMember(Name = "CustomerID", Order = 1)]
    public String CustomerID { get; set; }
    [DataMember(Name = "ContactName", Order = 1)]
    public String ContactName { get; set; }
    [DataMember(Name = "CompanyName", Order = 1)]
    public String CompanyName { get; set; }
    [DataMember(Name = "Country", Order = 1)]
    public String Country { get; set; }
}
[CollectionDataContract(ItemName = "Customer", Name = "Customers", Namespace =
"http://schemas.devleap.com/Customers")]
public class Customers : List<Customer> {
    public Customers() : base() { }
    public Customers(IEnumerable<Customer> collection) : base(collection) { }
}

The sample contract uses a Customer entity and a Customers list of entities. These types are marked as serializable with the DataContract serialization engine used by WCF. The service contract publishes only two operations: GetCustomerById and ListAllCustomers. The former accepts the customerID (the identifier parameter) and returns a single Customer entity. The latter, for the sake of simplicity, does not expect any argument and returns a list of Customer instances.

If you would like to support a full CRUDQ scenario, you need to publish three more operations for the corresponding method types (Creator, Updater, Deleter). The Creator operation should accept the entity to create as input, and it should return the identifier of the created entity or the whole created entity. The Updater operation should accept the entity and, above all, its identifier. It is not required to return anything back to the caller, but it is not forbidden. The Deleter operation should accept the identifier of the entity to delete. A response is not required. A WCF service contract satisfying the CRUDQ requirements for WCF demonstrates an extended WCF contract supporting the CRUDQ scenario.

[ServiceContract(Namespace = "http://schemas.devleap.com/CustomerService")]
public interface ICustomerService {
    [OperationContract]
    Customer GetCustomerById(String customerID);
    [OperationContract]
    Customers ListAllCustomers();
    [OperationContract]
    Customer AddCustomer(Customer item);
    [OperationContract]
    Customer UpdateCustomer(Customer item);
    [OperationContract]
    Boolean DeleteCustomer(Customer item);
}

The internal code of a service that implements such a contract is trivial, and it will not be covered in this chapter. You will find a full sample implementation, however, in the code samples.

After you define a service contract and service implementation that adhere to the communication requirements, as well as publish the service through a dedicated endpoint, you can register a new ECT corresponding to the entity published by the service. The best tool for accomplishing this task is still SharePoint Designer 2013. The first part of the registration task is exactly the same as registering an external database. In the Data Source Explorer window, however, while adding a new connection for the external system behind the ECT, you need to select a new WCF Service data source type for the external data source. Figure 14-17 shows the WCF Connection dialog box for configuring a data source of type WCF Service.

Figure 14-17. The WCF Connection dialog box for registering a WCF Service external data source.

The configuration information is as follows:

After you register the external data source, you must define all the operations that you would like to support.

As with the SQL Server data source, you can add operations by right-clicking a SOAP operation in the Data Source Explorer window, which in the case of a WCF service will show you all the available SOAP operations. Figure 14-18 displays the resulting window.

Notice in Figure 14-18 that the menu does not provide a command for configuring all the operations in one shot. This is because it cannot generate them autonomously by simply reading the service metadata, so you need to configure each individual operation step by step. You should start by creating a Finder method, which is an operation of type ReadList . Then define a SpecificFinder method, which corresponds to a ReadItem operation. Lastly, define the Create, Update, and Delete operations, in case they’re needed. Each operation allows you to configure the input and output arguments via a wizard interface.

Figure 14-18. The Data Source Explorer window for the sample WCF Service data source.

During definition of the operation of type ReadList, you need to define the entity identifier in the Return Parameter Configuration wizard step. You should also define a field to show in the entity picker. For the purposes of the current example, you should define the CustomerID property of each Customer entity as the identifier field, and the ContactName property as the field to show in the picker. Figure 14-19 shows this wizard step.

Furthermore, when defining the operation of type ReadItem, you have to map the identifier property to the corresponding argument of the SpecificFinder method in the Input Parameters Configuration wizard step. Then, in the Output Parameters Configuration step, you must define the entity identifier in the output message, and any property mapping to the corresponding Office property, if you defined the ECT as an Office item type.

The same considerations about the entity identifier are valid for the operations of type Create, Update, and Delete.

Figure 14-19. The Return Parameter Configuration wizard step that defines the ReadList operation.

When you finish configuring the ECT, save it, and then you can use it in external lists and Office clients, too.

One new capability of the BCS services introduced with SharePoint 2013 is support for OData services. OData stands for Open Data Protocol (see http://www.odata.org) and is an emerging technology for providing interoperable data-publishing services. A key feature of OData is that it is a web protocol for querying and updating data. Thus, you can use it to consume a complete CRUDQ experience. The data provided by an OData service can be published as an ATOM (XML) feed or using a JSON serialization format, both using the HTTP transport protocol.

To consume an OData service via BCS, you need to create a SharePoint 2013 app using Visual Studio 2012. In Chapter 8 you learned how to create an app. For the sake of brevity, imagine here creating a SharePoint-hosted app for consuming a publicly available OData service like the one offered by Netflix (see http://developer.netflix.com/docs/OData_Catalog).

To consume an OData service, you simply need to add to the app a specific project item. In Figure 14-20, you can see that you need to right-click the SharePoint 2013 app project, select Add from the menu that appears, and then choose Content Types For An External Data Source.

Figure 14-20. The Add | Content Types For An External Data Source menu item.

You will be prompted with a very brief wizard for providing the URL of the external OData service, which in the case of Netflix is http://odata.netflix.com/Catalog/, and the name to give to the data source. Figure 14-21 illustrates how this wizard step is made.

Figure 14-21. The Specify OData Source wizard page for adding a new OData source.

The next step is to choose the data lists to consume. Figure 14-22 shows how this last wizard step behaves. Notice that you can explicitly select the data lists you want to consume, and you can choose to automatically create external lists instances for every consumed data list in the target SharePoint app site.

Figure 14-22. The Select The Data Entities wizard page for adding a new OData source.

Click Finish on the page, and you will have a set of list instance elements—one for each selected data entity—as well as an ECT file for every single data entity. The ECT files will define a model for each entity. Figure 14-23 displays the project outline, indicating the OData entities that have been created.

Figure 14-23. The outline of the SharePoint 2013 app after adding the ECTs from an OData service.

If you deploy the app or start debugging it, you will see that the target app’s web will contain the external lists you defined. To check this result, you can simply navigate with your favorite browser to the corresponding URL. For example, to check the existence of the external list of titles consumed from Netflix, navigate to the URL {app web URL}/Lists/Titles. Figure 14-24 shows the results.

Figure 14-24. The list of titles from Netflix using the OData service connector for BCS.

Another opportunity you have while defining BCS solutions is to develop a custom model in Visual Studio 2012. This capability is useful whenever you need to consume, and in particular index and search with the search engine, a third-party data source that is not directly accessible through a database connection or by using a web service. It is also useful when you need to use an intermediary proxy to aggregate data that will eventually be provided by non-homogeneous sources. A custom model is a .NET assembly compiled in Visual Studio 2012 and built starting from a Visual Studio template project of type SharePoint 2013 - Empty Project. This project type is by necessity a full-trust, farm-level solution that deploys its assembly into the Global Assembly Cache (GAC). Thus, you cannot use it in Office 365. In fact, the custom model is accessible from any web application and can be shared across the farm. Inside the .NET assembly, you can write any code you like, and you can use any kind of library, service, or data provider in order to read the target data source. From a BCS viewpoint, you define a BDCM file within Visual Studio 2012, and you model a set of entities that will correspond to the ECT that you want to design. Visual Studio 2012 provides the BCS Model Designer and a BDC Explorer window to support model definition. Figure 14-25 illustrates the model designer, together with the BDC Explorer toolbox.

Figure 14-25. The BCS Model Designer available in Microsoft Visual Studio 2012.

The main goal of the model designer is to allow you to design entities and relationships (called associations in BCS) between entities. Each individual entity is made up of one or more identifier properties and some methods. The methods are defined and configured in terms of parameters, method instances, and filter descriptors using the BDC Method Details window. With the BDC Explorer, you can inspect the model by using the classic tree-view approach. The result of modeling is a BDCM file that you can manually import into SharePoint 2013. To do so, first deploy the corresponding assembly DLL into the GAC, and then use the BCS service application page in SPCA. Alternatively, you can take advantage of the automatic deployment provided by Visual Studio 2012, which uses a feature receiver (defined in class ImportModelReceiver of namespace Microsoft.Office.SharePoint.ClientExtensions.Deployment) for importing the file into the metadata catalog of BCS.

When you design a model in the graphical designer, Visual Studio 2012 automatically creates a code file called {Entity}Service.cs for each entity, where {Entity} corresponds to the name of the entity handled by that class file. Within that file, Visual Studio 2012 will place static methods corresponding to the method instances declared in the designer. In addition, you can use the designer to define the method instances and parameters for each designed method, as well as the data types of input, output, and return parameters.

To master the model design process, you should first define classes corresponding to all of the entities that you want to make available through the model. Then you should design the entities in the model designer and configure the methods that you want to make available. Remember that at the very least, you should define both a Finder and a SpecificFinder method. If you want to provide Creator, Updater, and Deleter methods, you can design them, too. In the accompanying code sample for this chapter, you will find a complete solution, which will be discussed in the next section.

Developing a custom model from scratch

In this section, you will use a step-by-step approach to learn how to design a simple model that publishes a list of customers read from the SampleCRM database (introduced previously in the “Accessing a database” section). In this example, however, you will read the customers using LINQ to Entities.

More Info

LINQ to Entities is a topic that will not be covered in this book. If you would like to understand how it works, read the book Programming LINQ in .NET Framework 4, by Paolo Pialorsi and Marco Russo (Microsoft Press, 2010).

First, create a new SharePoint 2013 project of type SharePoint 2013 - Empty Project. Then add an item of type BCS Model (for example, name it SampleCRMModel). A window will appear with a preconfigured model designer, describing a SampleCRMModel model with a hypothetical Entity1 item, together with both Entity1.cs and Entity1Service.cs classes. Remove the Entity1 item from the model, as well as the related CS files.

Add an Entity Framework 5.0 model to the project (click Add | New Item | ADO.NET Entity Data Model) and define a link to the table of customers defined in the target SampleCRM SQL Server database. To make the Entity Framework 5.0 model work inside SharePoint, you need to change the web.config file of the target web application, deploy the EntityFramework.dll assembly into the BIN folder of the web application, and create the connection string for the external data source by code. This chapter will not cover details about how to manage these tasks, which are related to Entity Framework 5.0. Nevertheless, in the companion code samples, you will find all the code for consuming an external SQL Server database via Entity Framework 5.0 within a SharePoint 2013 site.

Next, add a new entity to the BCS model, giving it a name of Customer. The designer will generate a CustomerService.cs file for you. Add a new identifier for the Customer entity and name it CustomerID.

Now you need to configure at least two methods (see Figure 14-26). The ReadList method will correspond to a Finder method, while the ReadItem method will correspond to a SpecificFinder method. To add these methods, simply go to the BDC Method Details panel and select the option Add A Method, as well as the appropriate method type.

Figure 14-26. The BCS Model Designer showing the Customer ECT with its identifier and methods.

Start with the ReadList method. Select the method in the designer and show the BDC Method Details window, which by default appears in the bottom area of Visual Studio. If the window is not displayed, go to View | Other Windows to show it. As you can see, the ReadList method has a return parameter called CustomersList; click it. In the property grid, specify that the type name behind the CustomersList parameter will be the List<Customer> type, where the Customer type is the one generated by Entity Framework. Do the same thing for the ReadItem method, this time selecting a return value of type Customer. You can add other parameters, or you can stop modeling the entities here.

Now you are ready to implement the model code. Open the source code of the CustomerService.cs file, and notice that the designer defined the service code for you. The autogenerated CustomerService.cs file shows this autogenerated code.

The autogenerated CustomerService.cs file

public partial class CustomerService {
    public static Customer Read(string customerID) {
        throw new System.NotImplementedException();
    }
    public static IEnumerable<Customer> List() {
        throw new System.NotImplementedException();
    }
}

Replace the methods implementation with concrete code, and you will be ready to provide read-only data to BCS. The autogenerated CustomerService.cs file with a concrete code implementation shows you a concrete code implementation.

The autogenerated CustomerService.cs file with a concrete code implementation

public partial class CustomerService {
    public static IEnumerable<Customer> ReadList() {
        SampleCRMEntities ctx = SampleCRMEntities.CreateContext();
        return (ctx.Customers);
    }
    public static Customer ReadItem(string customerID) {
        SampleCRMEntities ctx = SampleCRMEntities.CreateContext();
        return (ctx.Customers.FirstOrDefault(c => c.CustomerID == customerID));
    }
}

Notice that the static method SampleCRMEntities.CreateContext is part of the customization made to support Entity Framework 5.0 within SharePoint 2013. You can add also the Creator, Updater, and Deleter methods. To do so, click the Add A Method command menu item in the BDC Method Details window (illustrated previously in Figure 14-26). The Creator method accepts a parameter of type Customer with a direction value of In, and it returns a result of type Customer with a direction value of Return. The Updater method accepts at least a parameter of type Customer with a direction value of In, but it will not return anything. The Deleter method accepts a parameter of type customerID with a direction value of In, but it too does not return anything.

The final CustomerService.cs implementation presents the final implementation of the CustomerService.cs file.

The final CustomerService.cs implementation

public partial class CustomerService {
    public static IEnumerable<Customer> ReadList() {
        SampleCRMEntities ctx = SampleCRMEntities.CreateContext();
        return (ctx.Customers);
    }
    public static Customer ReadItem(string customerID) {
        SampleCRMEntities ctx = SampleCRMEntities.CreateContext();
        return (ctx.Customers.FirstOrDefault(c => c.CustomerID == customerID));
    }
    public static Customer Create(Customer newCustomer) {
        SampleCRMEntities ctx = SampleCRMEntities.CreateContext();
        ctx.Customers.Add(newCustomer);
        ctx.SaveChanges();
        return (newCustomer);
    }
    public static void Update(Customer customer) {
        SampleCRMEntities ctx = SampleCRMEntities.CreateContext();
        ctx.Customers.Attach(customer);
        ctx.SaveChanges();
    }
    public static void Delete(string customerID) {
        SampleCRMEntities ctx = SampleCRMEntities.CreateContext();
        ctx.Customers.Remove(ctx.Customers.FirstOrDefault
                 (c => c.CustomerID == customerID));
        ctx.SaveChanges();
    }
}

After you finish designing your model, you can validate it by right-clicking the designer surface and selecting the Validate command. If your model is correctly defined, Visual Studio 2012 displays the message “Model validation completed with no errors” in the Output window.

Now you can deploy the model and consume it from your SharePoint 2013 sites.

Regardless of the type of data source provider you choose for designing your ECTs, it is important to know that you can define associations between entities of the same namespace or model. In fact, whenever you have entities with a relationship, you can design an association with which you can navigate through your data, moving across associations.

Depending on the tool that you use for designing your BCS models, you can define the following kind of associations:

As an example, consider the SampleCRM database and the CRMCustomer ECT defined previously in the “Accessing a database” section. Add another ECT corresponding to the Orders table and call it CRMOrder. Each Order row is related to a specific Customer row, and the relationship is one-to-many, where CRMCustomer is the source and the related CRMOrder instances are the destinations. From within SharePoint Designer 2013, you can select the Operations Design View ribbon command of the destination ECT, and then click the Add Association menu item to create a new association.

When you undertake adding a new association, a wizard appears that asks you to select the source ECT and the related identifier (see Figure 14-27).

Figure 14-27. The first step of the wizard for creating an association between two ECT entities.

Then you need to select the parameters to provide to the association. An association is a particular kind of MethodInstance definition, as are, for example, Associator, AssociationNavigator, and BulkAssociationNavigator, which were described in Table 14-1. Thus, you have the ability to provide input parameters and filter parameters. The return type of the method is the related list of destinations.

You can use associations, for example, by using native Business Data Web Parts of SharePoint, creating pages that use the Business Data List and the Business Data Related List Web Parts. These Web Parts are available only in the Enterprise edition of SharePoint Server 2013. Figure 14-28 demonstrates the output of these Web Parts when configured to render CRMCustomer and related CRMOrder instances.

Figure 14-28. A Web Part page showing the output of Business Data List and Business Data Related List when connected.

If you need to define an association different from a one-to-many, based on a foreign key, you can use a text editor for the BDCM file or the Visual Studio 2012 BDC Model Designer.

In this chapter, you learned how BCS works on both the server and client side. You saw how to configure the most popular kinds of data source providers: DBMS, WCF/Web Service, OData, and the custom .NET model. You also read a quick overview of associations. You now have the basic elements to start using BCS in your real business solutions.