This article is the second part in a three-part series about SharePoint 2010 Business Connectivity Services (BCS). In "Business Connectivity Services, Part 1" (November 2010), I discussed BCS models, external content types (ECTs), external lists, and Business Data Catalog (BDC) Web Parts and demonstrated how to use them to implement functionality common to line of business (LOB) applications. In this article, I'll walk you through some advanced data access techniques with BCS.
You can use BCS to connect to many types of external data sources. In addition to being able to connect to common data sources such as databases, web services, and Windows Communication Foundation (WCF) services, BCS can connect to any data source that can be accessed via the Microsoft .NET Framework. So, if you can write .NET code to access a data source, you can connect to that data source with BCS. When you think about the flexibility the .NET Framework provides, it's easy to understand that, one way or another, you can connect to virtually any data source.
BCS connectors come in two main flavors:
- Out-of-the-box connectors, which come with BCS. They include database connectors for connecting to databases and service connectors for connecting to WCF and web services.
- Connectors that you create using the Connector Framework. These connectors include .NET assembly connectors and custom connectors.
Both .NET assembly and custom connectors are made up of managed code, which is compiled into an assembly. In both cases, the assembly is deployed to the Global Assembly Cache on the SharePoint servers where the BCS service application runs. Because the .NET assembly and custom connectors require an assembly to be deployed to the Global Assembly Cache, they must be deployed with a farm solution. They can't be deployed with a sandbox solution. The managed code in the compiled assemblies performs the Create, Read, Update, Delete, and Query (CRUDQ) operations on the external data sources the connectors are designed to interact with. Just like the out-of-the-box connectors, .NET assembly and custom connectors require a BCS model that contains the metadata describing the connection information and stereotyped operations that are performed against external data sources. A BCS model maps the methods in the managed code assemblies to the stereotyped operations that the BCS runtime defines, as Figure 1 shows.
One important difference between .NET assembly and custom connectors is that a .NET assembly connector is specifically tied to a single ECT, whereas a custom connector can be tied to one or more ECTs. For example, a custom connector can be used to provide BCS connectivity to SAP, while a .NET assembly connector can be used to provide BCS connectivity to a specific SAP installation for a given organization. As this example illustrates, custom connectors are more flexible than .NET assembly connectors. Custom connectors don't need to be recompiled if the schema representing the data in the external system changes. Instead, SharePoint Designer can be used to reconfigure them to handle schema changes. Unlike custom connectors, .NET assembly connectors are tightly bound to the schema representing the data in an external system. If the schema changes, a .NET assembly connector has to be recompiled.
Obviously, .NET assembly and custom connectors are required to connect BCS to external data sources that aren't supported by the database and service connectors. There are also other situations in which using .NET assembly or custom connectors makes sense, such as:
- When you need to aggregate external data from multiple external data sources and represent the external data within a single ECT.
- When you need to flatten complex data structures into fields that are compatible with BCS UI components.
- When you need to convert proprietary data types returned by an external system into .NET Framework data types that the BDC runtime can understand.
Out of these three situations, creating a .NET assembly connector that aggregates external data is the most common, so I'll focus on it.
The Scenario
In this example, I'll discuss how to create a single ECT based on a .NET assembly connector that reads and updates customer data residing in a Microsoft SQL Server database and a text file. Because of space limitations, I can't provide detailed instructions. However, you can find step-by-step instructions on how to create a .NET assembly connector in my blog "HOW TO: Create a Searchable SharePoint 2010 BDC .NET Assembly Connector Which Reads From a Flat File."
You begin by a creating new Business Connectivity Service Model project in Visual Studio 2010. After Visual Studio 2010 creates the new project, you can look at the project files in Solution Explorer. Let's take a look at the Entity1.cs, Entity1Service.cs, and BdcModel1.bdcm files shown in Figure 2.
Entity1.cs
When you create a .NET assembly connector, the first thing you need to do is define the entity to return to BCS. The entity can be thought of as the ECT defined by the connector.
By default, the Entity1.cs class defines the entity. You need to customize this class so that each property represents an individual column in the ECT tied to your .NET assembly connector. Listing 1 shows the customized portion of the Entity1.cs class for this example. As you can see, it defines columns from both the database and text file.
Entity1Service.cs
After the entity is defined, you need to create the methods that support the stereotyped BCS operations you want the .NET assembly connector to provide. The Entity1Service.cs class holds these methods. For this example, I customized the Entity1Service.cs class so that it implements the Finder, SpecificFinder, and Updater methods.