Accessing BLOB Data from External Systems Using Business Connectivity Services in SharePoint Server 2010

Article
10/20/2016

Summary: Learn how to use Microsoft Business Connectivity Services (BCS) in Microsoft SharePoint Server 2010 to access and surface BLOB data in the SharePoint user interface and search.

Provided by: Tajeshwar Singh (read his blog), MCM for SharePoint, Microsoft Services

Contents

Overview
Setting Up the Server Environment
Setting Up Business Connectivity Services
Creating the Metadata Model
Importing and Configuring the Model
Using Business Connectivity Services Web Parts
Searching BLOBs
Conclusion
Additional Resources

Download code: Access BLOB Data from External Systems Using BCS (Adventureworks.bdcm)

Overview

Microsoft Business Connectivity Services (BCS) is the new version of Microsoft Office SharePoint Server 2007 Business Data Catalog functionality. New features are added that help retrieve binary large object data (referred to as BLOB data) from external systems and make it available in Microsoft SharePoint Server 2010. This article describes the following:

The functionality that is provided by the StreamAccessor stereotype that is introduced in Business Connectivity Services.
How to use StreamAccessor to retrieve file attachments from external systems for viewing and indexing.
How to write the BDC model that is required to consume BLOB data.
The built-in Web Parts behavior for BLOB data, and how BLOB fields can be indexed by SharePoint Server search.

In this article's scenario, the AdventureWorks database that is hosted in Microsoft SQL Server 2008 is used as an external system that contains the binary data. The BDC metadata model is created with a StreamAccessorMethodInstance to retrieve the BLOB field of type varbinary from SQL Server as an external content type. The BLOB fields are modeled as types that can be read in chunks to help Business Connectivity Services read the stream in chunks, and not load the complete content in memory. This can help prevent out-of-memory conditions. An example of such a type is System.IO.Stream in the Microsoft .NET Framework. An External Data Grid Web Part is configured to show the external items with links to download the BLOB. Finally, Search is configured to crawl the BLOBs and show the results in the SharePoint Server search user interface (UI).

Setting Up the Server Environment

The following are the requirements to run this scenario.

Software Requirements

Ensure that the following is installed on in your server environment:

Microsoft SQL Server 2008 x64
Microsoft SharePoint Server 2010 with Enterprise Client Access License

Create the AdventureWorks Database

This scenario uses the AdventureWorks sample database to represent the external system that contains BLOB data. To download the x64 version of the AdventureWorks sample database from Codeplex, see SQL Server 2008 SR4.

Run the Setup program to create the database. The [Production].[Document] table is used to model the external content type that contains the BLOB field. The Document column contains Microsoft Word documents stored as varbinary data types in the table.

Setting Up Business Connectivity Services

The first step in this scenario is to set up the Business Connectivity Services infrastructure in the SharePoint environment. This step includes setting up the Business Connectivity Services service application, sites to host Business Connectivity Services Web Parts, and profile pages for the external content type.

To set up the infrastructure

Verify that the Business Data Connectivity (BDC) service is running. Create a BDC service application.
Create a web application http://bcs.contoso.com, and create a site collection on the top-level (root) site.
Create a subsite with the URL http://bcs.contoso.com/profile. This is used to host the profile pages for external content types.
Give the account that is used to access the external content type read rights on the AdventureWorks database and on the Production.Document table. In this scenario, you use Passthrough as the authentication mechanism in the metadata model. Therefore, the identity of the user who is connecting to SharePoint is used to authenticate against the SQL Server external system.

If your deployment has the external system (SQL Server) installed on a server different from the SharePoint front-end web server, configure the Kerberos protocol for passing the end-user credentials, or use Secure Store Service to do a single sign-on into the external system.

Creating the Metadata Model

Microsoft SharePoint Designer 2010 provides functionality to create external content types and save them or export them in a metadata model file. You must model the StreamAccessor stereotype manually because it cannot be modeled by using SharePoint Designer. To make things easier and to prepare the basic metadata, it is recommended that you use SharePoint Designer to create the initial metadata model that supports Create, Read, Update, and Delete (CRUD) operations. You can then do additional editing in a text editor such as Notepad or use any XML editor to add the StreamAccessor and any other method stereotypes that are not supported in SharePoint Designer.

In this scenario, you create the initial model by using SharePoint Designer, and then manually add the StreamAccessor stereotype by exporting the model and editing in Notepad or another XML editor.

Note

You can download the complete metadata model file from SharePoint Server 2010 Sample: Access BLOB Data from External Systems Using BCS.

For this scenario, the following three method stereotypes are required:

ReadItem to retrieve fields of a specific external item given the ID of an external item. This can be modeled by using SharePoint Designer.
ReadList to retrieve a list of external items with or without applying a filter. This can be modeled by using SharePoint Designer.
StreamAccessor to read the BLOB field and return the stream of data. This cannot be modeled by using SharePoint Designer; you must use Notepad or another XML editor.

Defining ReadItem and ReadList Stereotypes Using SharePoint Designer

In the following steps, you define the ReadItem operation and the ReadList operation.

To define the ReadItem and ReadList operations

On the server ribbon, in the New group, click External Content Type. This opens a window where you can create an external content type. Set the properties as shown in Table 1.

Table 1. Properties for creating the external content type

Property	Value
Name	ProductDocument
Display Name	ProductDocument
Namespace	http://bcs.contoso.com
Version	1.0.0.0
Office Item Type	Generic List
Offline Sync for External List	Enabled

On the ribbon, in the Views group, click Operations Designer View.
Right-click the Document table to view the options for creating methods.
Click Create New Read Item Operation. In the Operation Name field, click DocumentReadItem, and then click Next.
In Input Parameters Configuration, select the DocumentId data source element, and then select the Map to Identifier property. You use the DocumentId column to uniquely identify the document information. Click Next.
On the Return Parameters page, repeat the same procedure as you did in step 5. Select all the fields except [Document], which contains the BLOB. Click Finish.
Right-click the Document table, and then select Create New Read List. In the Operation Name field, select DocumentReadList. Click Next.
In Filter Parameters Configuration, define a filter of type Limit with a default value of 10 on the DocumentId column, to limit the number of records that are returned. Click Next.
On the Return Parameters page, ensure that all fields are selected except [Document]. Click Finish, and then save the external content type.

Defining the StreamAccessor Stereotype

Because you cannot model this stereotype by using SharePoint Designer, you must export the model that you created in the previous section to a file, and then add the StreamAccessor operation by using Notepad or another XML editor.

To create the StreamAccessor operation

In SharePoint Designer, under Navigation, select External Content Types. The list of external content types appears in the right pane.
In the list, right-click the ProductDocument external content type, and then select the option to export the model. Name the model file, and then save it.

Copy the following XML, and paste it as the last child node of the <Methods> tag in the metadata XML file.

<Method IsStatic="false" Name="DocumentReadStream">
              <Properties>
                <Property Name="BackEndObject" Type="System.String">Document</Property>
                <Property Name="BackEndObjectType" Type="System.String">SqlServerTable</Property>
                <Property Name="RdbCommandText" 
                Type="System.String">SELECT [Document] FROM 
                 [Production].[Document] WHERE [DocumentID] = 
                @DocumentID</Property>
                <Property Name="RdbCommandType" 
                Type="System.Data.CommandType, System.Data, 
                Version=2.0.0.0, Culture=neutral, 
                PublicKeyToken=b77a5c561934e089">Text</Property>
                <Property Name="Schema" Type="System.String">Production</Property>
              </Properties>
              <AccessControlList>
                <AccessControlEntry Principal="contoso\administrator">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                  <Right BdcRight="SetPermissions" />
                  <Right BdcRight="SelectableInClients" />
                </AccessControlEntry>
              </AccessControlList>
              <Parameters>
                <Parameter Direction="In" Name="@DocumentID">
                  <TypeDescriptor TypeName="System.Int32" 
                  IdentifierName="DocumentID" Name="DocumentID" />
                </Parameter>
                <Parameter Direction="Return" Name="DocumentRead">
                  <TypeDescriptor TypeName="System.Data.IDataReader, 
                  System.Data, Version=2.0.0.0, Culture=neutral, 
                  PublicKeyToken=b77a5c561934e089" 
                  IsCollection="true" Name="DocumentRead">
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.Data.IDataRecord, 
                      System.Data, Version=2.0.0.0, Culture=neutral, 
                      PublicKeyToken=b77a5c561934e089" 
                      Name="DocumentReadElement">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.Byte[]" 
                          Name="Document" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Type="StreamAccessor" 
                ReturnParameterName="DocumentRead" 
                ReturnTypeDescriptorPath="DocumentRead[0].Document" 
                Default="true" Name="DocumentReadStream" 
                DefaultDisplayName="Document">
                  <Properties>
                    <Property Name="FileNameField" 
                    Type="System.String">FileName</Property>
                  </Properties>
                  <AccessControlList>
                    <AccessControlEntry 
                    Principal="contoso\administrator">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SetPermissions" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                  </AccessControlList>
                </MethodInstance>
              </MethodInstances>
            </Method>

The StreamAccessor method takes a parameter ID of the external item as input and should return a single field that contains the stream. In this example, the DocumentId field uniquely identifies the external item. The return parameter is a data reader that contains a single column data record. The column name is Document.

Table 2 shows the properties that can be configured on the MethodInstance that define how the BLOB is handled.

Table 2. Properties for StreamAccessor

Property	Description
FileName	If specified, used as the file name for the BLOB. It also appears as the file name when the file is downloaded. The same name is used by all the external items for the external content type. Example: <Property Name="FileName" Type="System.String">document.docx</Property>
MimeType	If specified, used while the BLOB stream is sent for download, and to identify the Multipurpose Internet Mail Extensions (MIME) type. It is also used by SharePoint Server search to identify the type of content, and so that the appropriate IFilter is used to index content. Example: <Property Name="MimeType" Type="System.String">application/vnd.openxmlformats- officedocument.wordprocessingml.document</Property>
MimeTypeField	Specify whether each external item has a MIME type available in one of the external content type fields. It is the name of the TypeDescriptor in the external content type that returns the MIME type from the external system. Example: <Property Name="MimeTypeField" Type="System.String">MimeTypeName</Property>
FileNameField	Specify whether each external item has a file name available in the ECT field. It is the name of the TypeDescriptor in the external content type that returns the file name from the external system. Example: <Property Name="FileNameField" Type="System.String">ResumeFileName</Property>

Business Connectivity Services can infer the MIME type from the extension of the file that is available in the file name. To determine which MIME type should be used for the stream, properties in the metadata model are checked in the following order:

FileNameField
MIMETypeField
MIMEType
FileName

After the system determines the MIME type, it determines the application that is used to open the file by using associations that are defined on the SharePoint front-end server, not on the client computer.

If the user clicks a link, and no FileName/FileNameField property is present, the system shows the MethodInstanceName in the File download dialog box for the file name. (Fallback order is: FileNameField, FileName, LocalizedDisplayName, MethodInstanceName.)

If the system cannot determine the MIME type, it streams down the content to the browser with the MIME type as Unknown. On the client computer, the user is asked to select the program to open the file, as shown in Figure 1.

Figure 1. Program selection prompt

Program selection prompt

The following metadata example shows a StreamAccessor MethodInstance. The type is specified as StreamAccessor and the FileNameField points to the FileName column that is returned from the database table.

                <MethodInstance Type="StreamAccessor"
                ReturnParameterName="DocumentRead"
                ReturnTypeDescriptorPath="DocumentRead[0].Document"
                ReturnTypeDescriptorLevel="1"
                Default="true" Name="DocumentReadStream"
                DefaultDisplayName="Document">
                  <Properties>
                    <Property Name="FileNameField"
                    Type="System.String">FileName</Property>
                  </Properties>
                  ...                    
                </MethodInstance>

Download the full metadata model from SharePoint Server 2010 Sample: Access BLOB Data from External Systems Using BCS.

Importing and Configuring the Model

After you design the model, the next step is to import the model into the Business Connectivity Services metadata store and configure the Profile pages.

To import and configure the model

In SharePoint Central Administration, select the option to manage a Business Connectivity Services service application. In the list of available metadata models, select the model that you created earlier by using SharePoint Designer, and then delete it. This is required because the model file cannot be imported if there is an existing model that has the same name.
Follow the steps in the Import a BDC Model section of Manage BDC Models (SharePoint Server 2010) (Microsoft TechNet) for importing the metadata model in a service application. The full metadata model is available for download at the end of this article.
Assign execute permissions to the account that you will use to access methods on the external content type, as shown in the Set permissions on a BDC model section of Manage BDC Models (SharePoint Server 2010).
Create a profile page host site at http://bcs.contoso.com/profile, and then create a profile page for the ProductDocument external content type by following the steps in the Specify the Profile Page Host for a Business Data Connectivity Service Application Instance section of Manage External Content Types (SharePoint Server 2010) (Microsoft TechNet). Ensure that the users who will view the external content type and the account that is used to crawl the external content type have read access to the profile host site.

Using Business Connectivity Services Web Parts

Functionality is implemented in the Business Data Web Parts to handle BLOB fields. While it is rendering an external item, if the Business Connectivity Services runtime encounters a field for which a StreamAccessor method is defined, Web Parts render a link to download the stream as a file.

If a user clicks the Click here to download link, the Business Connectivity Services runtime requests the file (stream) from the line-of-business (LOB) system. When the whole stream is retrieved, Business Connectivity Services determines the appropriate MIME type and passes that to the client's default web browser. This prompts the user to either save the file, or to open it by using the default application that is registered for that file extension. If there is no associated default application, the user is prompted to select one.

This functionality is provided natively, and no customization is required in the UI. The Click here to download link navigates the request to the downloadexternaldata.aspx page in the LAYOUTS folder, which reads the stream and sends it to the browser.

To configure the Web Part to show the external content type with the stream field

Navigate to http://bcs.contoso.com/profile, and then add a Business Data List Web Part. Specify ProductDocument as the external content type, as shown in Figure 2.

Figure 2. Web Part properties
After you save the page, it refreshes, and the List Web Part retrieves data from the external system and displays the list, as shown in Figure 3. Notice the Document column, which shows the link to download the BLOB.

Figure 3. External Items in the Business Data List Web Part

All Business Connectivity Services Web Parts are stream-aware and show this behavior when the external content type implements the StreamAccessor methods.

If an external list is created by using an external content type with a stream field, the list does not show the link to download the content.

Searching BLOBs

After an external content type is defined, SharePoint Search can crawl external data and index the streams. The following are prerequisites for enabling BLOB search:

An external content type is created with the StreamAccessor method defined for the BLOB to be indexed.
SharePoint Search Service is started, and the service application is created for search.
A content source of type Line of Business Data is defined with the AdventureWorksLobSystemInstance external system as the external data source to crawl.
A profile page is created for the ProductDocument external content type at http://bcs.contoso.com/profile.
The crawl account has rights to connect to the AdventureWorks database, and it has read permissions for the content at http://bcs.contoso.com/profile.

When you have all these prerequisites, start a full crawl of the content source. Ensure that the content is crawled successfully by verifying the crawl log.

Search for any term that is in the content of the document, and verify whether the results are returned for it. In this example, search for the term cycle. You will receive one result from the external system, as shown in Figure 4. The resulting item URL is the profile page that was created in step 4 in Importing and Configuring the Model earlier in this article.

Figure 4. Search results

Search results

Clicking the link in the search results takes you to the profile page where you can see details of the external item. Notice that the Business Data Item Web Part displays the link to download the BLOB file, as shown in Figure 5.

Figure 5. Product Document profile page

Product Document profile page

If you click the Click here to download link, a File Download dialog box appears, as shown in Figure 6. You can save the file or open it to view.

Figure 6. File download dialog box

File Download dialog box

Conclusion

Until now, accessing BLOB data from external systems was not easy in SharePoint Server. In Microsoft SharePoint Server 2010, the new features that are added to Web Parts, SharePoint Server search, and the Business Connectivity Services object model enable you to use Business Connectivity Services to create sophisticated applications that can access BLOB data from external systems.

Additional Resources

For more information about Business Connectivity Services, see the following resources: