Using the BDC Object Model

Scenarios for access to external data vary widely in complexity, from simply displaying a list of information from an external source to providing heavily customized interactivity. For many basic scenarios, using the built-in Business Data Web Parts or using the SPList API to query external lists provide straightforward and effective approaches to meeting your application requirements. However, for more complex scenarios, you will need to use the BDC object model for full control of how you interact with your external data entities.

The BDC object model is not available in sandboxed solutions. The following table summarizes the scenarios where external lists or Business Data Web Parts may meet your requirements and the scenarios where you must use the BDC object model.

External data scenario	External list	Business Data Web Parts	BCS API
Access two-dimensional (flat) data from a sandboxed solution
Access two-dimensional (flat) data from a farm solution
Access data with non-integer identifiers
Navigate one-to-one associations between entities
Navigate one-to-many associations between entities
Navigate many-to-many associations between entities
Read entities with complex types that can be flattened using a format string
Read entities with complex types that cannot be flattened using a format string
Create, update, or delete entities with complex types
Perform paging or chunking of data
Stream binary objects
Access two-dimensional (flat) data from client logic*
Navigate associations between entities from client logic*

*The BDC provides a client API and a server API that offer the same functionality. However, the client API is only available in full .NET applications; it cannot be used from Silverlight or JavaScript.

The BDC runtime API allows you to programmatically navigate a BDC model, and to interact with an external system through the model, without using intermediary components such as external lists or Business Data Web Parts. The following diagram illustrates the key components of the BDC programming model.

Key components of the BDC programming model

Each of the components in the programming model relates to a specific part of the BDC model, which was described in Business Data Connectivity Models. The BDC service application instance (BdcService) represents the service instance that manages the metadata for the external systems you want to access. Remember that the BDC service application instance you use is determined by the service application proxy group associated with the current SharePoint Web application. Each BDC service application instance exposes a metadata catalog (IMetadataCatalog) that you can use to navigate through the metadata definitions stored by the service.

Within the metadata catalog, the two primary concepts are the entity (IEntity) and the LOB system instance (ILobSystemInstance). An entity represents an external content type and defines the stereotyped operations that are used to interact with an external data entity. It can also define associations that allow you to navigate to related entities and filters that enable you to constrain a result set. A LOB system instance represents a specific instance, or installation, of the external system that the BDC model represents, and defines the connection and authentication details required to connect to the system.

Note

"LOB system instance" is a legacy term from Office SharePoint Server 2007. A LOB system is a line-of-business application, such as customer relationship management (CRM) or enterprise resource planning (ERP) software. Although the term "LOB system instance" is still used within the BDC object model, the broader term "external system" is preferred in other cases.

Entities, or external content types, are common to all instances of a system. To access data from a specific instance of an external system, you need to use an entity object in conjunction with a LOB system instance object to retrieve an entity instance (IEntityInstance). The following code example illustrates this.

public IEntityInstance GetMachineInstance(int machineId)
{
  const string entityName = "Machines";
  const string systemName = "PartsManagement";
  const string nameSpace = "DataModels.ExternalData.PartsManagement";
  
  BdcService bdcService = SPFarm.Local.Services.GetValue<BdcService>();
  IMetadataCatalog catalog = 
    bdcService.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
  ILobSystemInstance lobSystemInstance = 
    catalog.GetLobSystem(systemName).GetLobSystemInstances()[systemName];
  Identity identity = new Identity(machineId);
  IEntity entity = catalog.GetEntity(nameSpace, entityName);
  IEntityInstance instance = entity.FindSpecific(identity, lobSystemInstance);

  return instance;
}

When a method on an IEntity object takes an object of type ILobSystemInstance as a parameter—such as the FindSpecific method shown here—usually, it is querying the external system for information. The IEntity object defines the stereotyped operations that allow you to interact with a particular type of data entity on the external system, while the ILobSystemInstance object defines the details required to actually connect to a specific external system instance. Typically, you perform data operations on an IEntityInstance object or on a collection of IEntityInstance objects. Each IEntityInstance object contains a set of fields and values that correspond to the related data item in the external system. These fields can represent simple types or complex types. For more information, see IEntityInstance Interface on MSDN.

Using Filters

IEntity objects can include filter definitions that allow you to constrain result sets when retrieving more than one item. Filters can also provide contextual information to the external system, such as a trace identifier to use when logging. The following code example shows how you can use filters to retrieve entity instances that match a specified model number.

public  DataTable FindMachinesForMatchingModelNumber(string modelNumber)
{
  const string entityName = "Machines";
  const string systemName = "PartsManagement";
  const string nameSpace = "DataModels.ExternalData.PartsManagement";
  BdcService bdcService = SPFarm.Local.Services.GetValue<BdcService>();
  IMetadataCatalog catalog = 
    bdcService.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
  ILobSystemInstance lobSystemInstance = 
    catalog.GetLobSystem(systemName).GetLobSystemInstances()[systemName];
  IEntity entity = catalog.GetEntity(nameSpace, entityName);
  IFilterCollection filters = entity.GetDefaultFinderFilters();

  if (!string.IsNullOrEmpty(modelNumber))
  {
    WildcardFilter filter = (WildcardFilter)filters[0];
    filter.Value = modelNumber;
  }

  IEntityInstanceEnumerator enumerator = 
    entity.FindFiltered(filters, lobSystemInstance);
  
  return entity.Catalog.Helper.CreateDataTable(enumerator);
}

As you can see, the IEntity object includes a collection of filters that you can configure for use in queries. In this case, a wildcard filter is used to perform a partial match against a machine model number.

Note

The CreateDataTable method is a convenient new addition in SharePoint 2010 that automatically populates a DataTable object with the query results.

Using Associations

At a conceptual level, associations between entities in the BDC model are similar to foreign key constraints in a relational database or lookup columns in regular SharePoint lists. However, they work in a different way. Associations are defined as methods within an entity that allow you to navigate from instances of that entity to instances of a related entity. You cannot create joins across associations in a BDC model. Instead, you must retrieve an entity instance and then use the association method to navigate to the related entity instances. This is illustrated by the following code example, which retrieves the set of machine part entities that are associated with a machine entity.

public  DataTable GetPartsForMachine(int machineId)
{
  const string entityName = "Machines";
  const string systemName = "PartsManagement";
  const string nameSpace = "DataModels.ExternalData.PartsManagement";
  BdcService bdcService = SPFarm.Local.Services.GetValue<BdcService>();
  IMetadataCatalog catalog = 
    bdcService.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
  ILobSystemInstance lobSystemInstance = 
    catalog.GetLobSystem(systemName).GetLobSystemInstances()[systemName];
  IEntity entity = catalog.GetEntity(nameSpace, entityName);
  
  // Retrieve the association method.
  IAssociation association = (IAssociation)entity.GetMethodInstance(
    "GetPartsByMachineID", MethodInstanceType.AssociationNavigator);

  Identity identity = new Identity(machineId);
  
  // Retrieve an entity instance.
  IEntityInstance machineInstance = 
    entity.FindSpecific(identity, lobSystemInstance);
  EntityInstanceCollection collection = new EntityInstanceCollection();
  collection.Add(machineInstance);

  // Navigate the association to get parts.
  IEntityInstanceEnumerator associatedInstances = entity.FindAssociated(
    collection, association, lobSystemInstance, OperationMode.Online);

  return entity.Catalog.Helper.CreateDataTable(associatedInstances);
}

This approach is consistent with the way the BDC model works in general. You first retrieve definitions of entities and associations from the model, and then you use these entities and associations in conjunction with a LOB system instance to retrieve information from the external system. Although this may seem somewhat unnatural at first, it allows you to decouple your applications from the implementation details of the backend service or database.

Working with Database Views

In some scenarios, you may want to model external content types on composite views of entities in your external system. For example, you might create an external content type from a database view or a specialized service method instead of simply replicating database tables or other external entities. This approach is useful for read operations in which you need to aggregate fields from multiple external tables or entities, especially when you are working with large amounts of data. If you don't use a database view, you would need to traverse individual entity associations in order to generate the composite view you require. This increases the load on the BDC runtime and can lead to a poor user experience.

The drawback of binding external content types to database views is that create, update, and delete operations become more complicated, because updating a view inevitably involves performing operations on more than one table. There are two approaches you can use to enable updates to database views:

• You can create an INSTEAD OF trigger on the SQL Server database to drive update operations. An INSTEAD OF trigger defines the Transact-SQL commands that should be executed when a client attempts a particular update operation. For example, you might define an INSTEAD OF INSERT routine that is executed when a client attempts an INSERT operation.
• You can develop stored procedures on the SQL Server database to perform the update operations and map the relevant stereotyped operations for your external content type to these stored procedures.

Unless you are expert in creating Transact-SQL routines, using triggers can be somewhat cumbersome and complex. You may find that creating stored procedures offers a more palatable approach.

You might think that binding external content types to database views is more trouble than it's worth; especially if you must create stored procedures to support update operations. However, if you took the alternative approach of modeling database tables directly, one update to your user interface view would require updates to multiple external content types in the BDC model. To accomplish these updates in a single operation, you would need to write code using the BDC object model. By putting the multiple update logic in a stored procedure on the database, you reduce the load on the BDC runtime. From an efficiency perspective, the stored procedure approach requires only a single round trip to the database server, because the database view represents a single entity to the BDC runtime. In contrast, managing updates to multiple entities through the BDC object model requires multiple round trips to the database server; therefore, it is a more expensive operation.

Other Scenarios That Require the Use of the BDC Object Model

In addition to the operations described in this topic, there are several other scenarios for external data access in which you must write custom code using the BDC object model instead of using simpler mechanisms such as external lists or Business Data Web Parts. These include the following:

• You want to perform bulk write-back operations where you write multiple rows of data to the same entity in an external system. For example, you might need to add multiple line items to an order created by a user.
• You want to update multiple entities concurrently. For example, to submit an order, you might need to update order entities, order line item entities, and carrier entities simultaneously.
• You want to use a GenericInvoker stereotyped operation. A GenericInvoker method is used to invoke logic on the external system, and can call methods with arbitrary parameters and return types.