Using BCS to Read BLOB Data

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.

Adding a Business Data List Web Part

Now that you’ve created the ProductPhoto external content type, you need to add a Business Data List Web Part so that the external data is displayed in the SharePoint site. Note that Business Data List Web Parts are available only in the Enterprise edition of SharePoint Server 2010. Alternatively, you can create an external list to display the external data, which is available in all editions. For this example, let’s add a Business Data List Web Part.

In SharePoint Designer 2010, browse to your SharePoint site’s home page. Click the Edit button. In the ribbon, click Insert, Web Part. Select Business Data from the Categories drop-down list, then choose Business Data List from the Web Parts drop-down list. Click Add to add the Web Part.

To customize the Web Part, Click Open tool pane. Select the ProductPhoto external content type from the external item picker. Click OK. The external data is now displayed in the Web Part, as Figure 3 shows. Save the page.

Figure 3: External data before adding the BLOB data

Exporting the ProductPhoto Model

Before you can manually add BLOB support to the ProductPhoto external content type, you need to export the model from SharePoint Designer 2010 to your desktop. In SharePoint Designer 2010, navigate to the External Content Types section under Site Objects. Select the ProductPhoto External Content Type. Click the Export BDC Model button on the ribbon. Enter ProductPhoto as the name in the Export BDC dialog box, and click OK. Save the ProductPhoto.bdcm on your desktop, as shown in Figure 4.

Figure 4: BDC model exported to the desktop

Adding the StreamAccessor Method

At this point, you need to add a StreamAccessor method so that the ProductPhoto external content type can read a stream of bytes. Begin by double-clicking the ProductPhoto.bdcm file on your desktop to open the model. If you have Microsoft Visual Studio 2010, the model will open in it by default. However, you can open the model in any XML or text editor if you don’t have Visual Studio 2010 installed. For this example, let’s use Visual Studio 2010.

Figure 5: SpecificFinder and Finder methods in the BDC model’s code

In Visual Studio 2010, click XML Editor. If you’re prompted to close the file, click Yes and open it again. Under the tag, you’ll find the two methods you created in SharePoint Designer 2010 (see Figure 5). Take a look at these methods to see how they’re defined, then add the following code before the tag:

This code adds a new StreamAccessor method named PhotoReadStream. When passed a photo ID as a parameter, this method returns the photo as stream of bytes. To complete this method, you need to add code that defines its properties, permissions, parameter, and method instance.

First, you need to add the properties that define the external system by adding the code in Figure 6 before the tag. In this code, RdbCommandText defines the SQL query to execute against the external system.

Next, you need to define the permissions for the PhotoReadStream method by adding the code in Figure 7 after the tag. In the code

Principal="contosoadministrator"

replace contoso with your domain’s name and administrator with the name of the user account under which you built the ProductPhoto model in SharePoint Designer 2010.

Now you need to add the ProductPhotoID parameter, which will be used to pass the photo ID to the method. To do this, add the code in Figure 8 after the tag.

Finally, you need to add the method instance, which describes how to use the method, method type, parameters, and data source properties. To add the method instance, put the code in Figure 9 after the tag. Once again, you need to replace contoso with your domain’s name and administrator with the name of the user account under which you built the ProductPhoto model.

The StreamAccessor method instance has several properties that define how the BLOB is handled. In this example, you’re adding only one property: the FileNameField property, which is the TypeDescriptor that will return the filename for the BLOB content. Other properties you can add are:

Figure 10 shows the complete code for the PhotoReadStream method. Save the ProductPhoto.bdcm file.

Figure 10: StreamAccessor method in its entirety

Importing the Updated ProductPhoto Model

To apply the changes you just made, you need to import the updated ProductPhoto model to the BDC metadata store. In SharePoint Designer 2010, browse to the Central Administration site. Click Application Management, select Manage service applications, and choose Business Data Connectivity Service. Figure 11 shows the ProductPhoto external content type in the BDC metadata store.

Figure 11: Old version of the external content type in the BDC metadata store

Select ProductPhoto and click the Delete button in the ribbon to delete this old version of the external content type. Click the Import button in the ribbon, click Browse, and select the ProductPhoto.bdcm file on your desktop. Click the Import button. If it was imported successfully, you’ll see the message BDC Model was successfully imported displayed along with any warnings. Click OK.

In addition to updating the BDC model, you need to update the Business Data List Web Part. Browse to the SharePoint site where you added the Business Data List Web Part previously and delete that Web Part. Add the Business Data List Web Part again to the page and select ProductPhoto as the external content type. You’ll now see the Display stream fields option because you added the StreamAccessor operation.

Figure 12: External data after adding the BLOB data

As Figure 12 shows, the Web Part now includes the LargePhoto field. When you click a photo’s Click here to download link, the MIME type is identified and you’re asked whether you want to open or save the BLOB content. Click Open to view the image. Figure 13 shows a sample image.

Figure 13: Downloaded BLOB image

Adding Search Capabilities

The last step is adding search capabilities by creating a Line of Business Data content source, crawling the external data, and indexing the streams. To enable a BLOB search in the ProductPhoto model, you need to:

You should also try searching for an external item and verify the results returned. Clicking an item in the search results will open the Profile Page, which will display the details of the external item along with the BLOB field. For more information about adding search capabilities to external data, see “Configuring SharePoint Server 2010 Search for External Content Types, Part 1” and “Configuring SharePoint Server 2010 Search for External Content Types, Part 2”.

A Big Step in the Right Direction

Although adding BLOB support isn’t straightforward, it’s a big step in the right direction. The process is much easier in SharePoint 2010 than in Microsoft Office SharePoint Server 2007 (MOSS 2007) because of BCS’s new ability to read BLOB data. And BCS’s out-of-the-box Business Data List Web Parts makes it easier to display BLOB data from external content types.

Figure 6: Code to add properties to the StreamAccessor method

ProductPhoto  SqlServerTable  SELECT \[LargePhoto\]    FROM \[Production\].\[ProductPhoto\] WHERE \[ProductPhotoID\] = @ProductPhotoID  Text  Production

Figure 7: Code to add permissions to the StreamAccessor Method

Figure 8: Code to add a parameter to the StreamAccessor method

Figure 9: Code to add the method instance to the StreamAccessor method

          LargePhotoFileName