Business Connectivity Services (BCS) lets Microsoft SharePoint Server 2010 and Microsoft Office 2010 applications read and write data from external systems such as databases, Web services, and custom applications. BCS provides out-of-the-box features, services, and tools that streamline development and deeply integrate external data and services. The Business Data Connectivity (BDC) service provides a set of operations for BCS to interact with the external system.
One handy feature in BCS is the ability to read BLOB data. However, building a BDC model that reads BLOB content isn’t straightforward. So, using the ProductPhoto table from the AdventureWorks database, I’ll show you how to use BCS and SharePoint Designer 2010 to build a BDC model that not only reads but also searches BLOB data. If you’d like to follow along, you can download the AdventureWorks database from CodePlex’s SQL Server product samples.
Building the BDC Model—An Overview
A BDC model contains metadata. For an external system, the metadata defines the business entities, operations, and methods available for that application. In this example, you’ll be building a BDC model named ProductPhoto.
The first step in building the ProductPhoto model is to use SharePoint Designer 2010 to create the external content type. The external content type describes the schema and data access capabilities of an external data source and its behavior with SharePoint and Office. In this case, the external content type uses the SpecificFinder and Finder methods (which are Read Item and Read List operations, respectively) to connect to the AdventureWorks database and fetch data from the ProductPhoto table.
Here’s where the process gets a bit tricky. SharePoint Designer 2010 doesn’t support modeling BLOB fields, so you need to manually edit the ProductPhoto model to return BLOB data. Specifically, you need to add the StreamAccessor method. But before you can do so, you need to add a Business Data List Web Part so that you can display the external data in the SharePoint site and export the ProductPhoto model to your desktop for editing.
After you add the StreamAccessor method, you need to import the updated ProductPhoto model back into SharePoint Designer 2010, where you have to update the external content type in the BDC metadata store and update the Business Data List Web Part. After making these updates, you can add search capabilities so that the BLOB data is searchable.
Now that you know the general process for creating a BDC model that reads and searches BLOB data, let’s take a closer look at each step.
Creating the External Content Type
The first step in building the ProductPhoto model is creating the external content type, with the Finder and SpecificFinder operations connecting to the ProductPhoto table in the AdventureWorks database. Open SharePoint Designer 2010, connect to your site, and navigate to the External Content Types section under Site Objects. Click the External Content Type button in the ribbon. Enter ProductPhoto for the name and display name. Click the Click here to discover external data sources and define operations button to configure the external system.
In the Operation Designer dialog box that appears, click the Add Connection button to add an external system connection. Select SQL Server from the External Data Source Type Selection drop-down list and click OK. In the SQL Server Connection dialog box, enter the information that you see in Figure 1. Note that I’m connecting to the local SQL Server machine with the logged-on user’s identity. Make sure the user account you’re using has the appropriate rights to the SQL Server machine. Once you’re successfully connected, you’ll see the external system in Data Source Explorer.
Figure 1: SQL Server Connection configuration
To create the SpecificFinder method, go to the AdventureWorks treeview in Data Source Explorer. Expand the Tables folder and locate the ProductPhoto table. Right-click it and select New Read Item Operation. In the Operation Properties dialog box, click Next.
In the Input Parameters Configuration screen, click Next. In the Return Parameter Configuration screen, deselect all the properties except ProductPhotoID and LargePhotoFileName, as Figure 2 shows. Click Finish to create the Read Item operation.
Figure 2: SpecificFinder method’s return parameters
To create the Finder method, right-click the ProductPhoto table in the AdventureWorks treeview in Data Source Explorer and select New Read List Operation. In the Operation Properties dialog box, click Next. In the Filter Parameters Configuration screen, click Next. Although you’re not going to create a filter for this example, I recommend creating one if you have large result sets. Without a filter, large result sets might exceed the default item threshold count.
In the Input Parameters Configuration screen, click Next. In the Return Parameter Configuration screen, deselect all properties except ProductPhotoID and LargePhotoFileName. Click Finish to create the Read List operation. Click Save to save the ProductPhoto external content type.
As mentioned earlier, you can’t configure the SpecificFinder and Finder methods to return BLOB fields—in this case, the ThumbNailPhoto and LargePhoto fields in Figure 2—in SharePoint Designer 2010. You’ll be manually adding the LargePhoto field later.