The Business Context

In many large organizations business data is stored inside of flat files from mainframes or other legacy systems. These systems chug along just fine everyday and support many key elements of the business; they aren’t going away anytime soon. Traditionally, accessing flat file data, making it viewable in a web pages, and making it searchable requires an enormous amount of custom code.

The Solution

New functionality inside SharePoint 2010’s Business Connectivity Services (BCS) makes it extremely easy to connect to flat file data sources (or any other data source), display it in web pages, and make it searchable. This blog post is the first in a 2 part series which describes how to create a SharePoint 2010 BCS .NET Assembly Connector to read data from a flat file and register it as an external content type in SharePoint. Part 2 of this blog post series (coming tomorrow) demonstrates how to register an external content type with the SharePoint search service to make the data searchable.

As you may already know, SharePoint 2010’s BCS provides the ability to connect databases, web services, and WCF services right out of the box. In the last version of SharePoint if we wanted to connect to another type of data source we would create a web service to connect to the data source then connect the BDC to our custom web service. That’s no longer how things are done. Now, a .NET Assembly Connector is used to connect to data sources the BCS does not natively support. A .NET Assembly Connector is a .NET assembly which the BCS uses as a proxy to connect to data sources. The .NET Assembly Connector defines the entities returned from the data source and includes the implementation code to connect to the data source and return the information.

This example shows how a .NET Assembly Connector may be used for read operations, however they may also be used for write operations. I will be posting another blog post on that topic shortly.

Nick Swan is also actively working with the SharePoint 2010 BCS. You can read more posts about this topic his blog:

Create the flat file data source

This example reads data from a flat file located on the SharePoint server. It is unlikely that you will be reading the contents of a flat file located on your SharePoint server, most likely the file will be stored on a file share on your network. To keep the example simple I’m placing the flat file on the SharePoint server.

To create the flat file (data source) follow these steps:

Create the following directory on your SharePoint server

C:DATA

Open your favorite text editor and create a file called flat-file-data-source.txt

6. Click OK
7. Specify the SharePoint site where you would like to deploy and debug the project
8. Click Finish

Project Inventory

Before customizing the project, let’s take a moment to understand the files in it. Here you can see all the files in the Solution Explorer:

The following table describes what the numbered elements in the image above are used for.

Element # In Screenshot Above

Description

1

References to the SharePoint assemblies are automatically added to the project.

2

A feature containing the BDC Model and .NET Assembly Connector.

3

A package (WSP) containing the feature and supporting files.

4

The BDC Model which defines the data source, how to connect to it, how to query it, and what type of information it returns.

5

A .NET class defining an entity the BDC Model returns.

6

A .NET class containing the code used to connect to the data source, query it, and return the entities it contains.

Defining the Entity

Before creating a BDC Model which defines the data source, how to connect to it, how to query it, and what type of information it returns, an entity must be defined. As mentioned in the table above, the Entity1.cs file defines an entity the BDC Model returns. The next step is to create an entity which maps to the data in the flat-file-data-source.txt file. To create the entity follow these steps:

In the Solution Explorer, right click Entity1.cs and select Rename

Rename the file FlatFileEntity.cs

Click Yes

In the Solution Explorer, right click FlatFileEntity.cs and select Open

Delete all the code inside the FlatFileEntity class

Next, create properties in the FlatFileEntity class to map to the data in the flat-file-data-source.txt file. Do this by adding the following code inside the FlatFileEntity class

Defining the Entity Service Class

Once the entity is defined, create the entity service class which queries the data source (flat-file-data-source.txt) and returns entities. The entity service class defines the finder and specific finder methods used to return entities from the data source. The ReadList() method defines the finder method for the BDC Model. The finder method is responsible for returning all the entities in the data source. This method returns an IEnumerable generic collection of entities. In this example the ReadList method returns an IEnumerable collection of FlatFileEntity objects (the entity class, created above).

The ReadItem(string id) method defines the specific finder method for the BDC Model. The specific finder method is responsible for returning a single entity from the data source. The specific finder method returns an object representing the entity. The id parameter passed into the ReadItem(string id) method is used to query the entities in the data source and return the entity whose identifier matches the parameter. In this example the ReadItem(string id) method returns a FlatFileEntity object (the entity class, created above).

To create the entity service class follow these steps:

In the Solution Explorer, right click Entity1Service.cs and select Rename

Rename the Entity to FlatFileEntityService.cs

Click Yes

In the Solution Explorer, right click FlatFileEntityService.cs and select Open

Add a using statement to the FlatFileEntityService.cs file to support file IO with the flat-file-data-source.txt file.

using System.IO;

Next, create a method to return entities from the flat-file-data-source.txt file. This method opens the text file, iterates through each row of data, parses the data, creates an instance of the FlatFileEntity object for each row, sets the properties accordingly, adds the FlatFileEntity object to the IEnumerable generic collection, and finally returns the entire collection. Essentially this method returns strongly typed objects representing the data in the flat-file-data-source.txt file. To create this method add the following code to the FlatFileEntityService class.

As previously mentioned, the assembly which is created when this code is compiled is referred to as a .NET Assembly Connector.

After the entity and entity service are defined it is possible to create the BDC Model which defines how to connect to the data source, how to query it, and what type of information it returns. The BDC Explorer and the BDC Model designer are used to define the BDC Model. Both of these new VS 2010 components are described below.

BDC Explorer

Visual Studio 2010 now includes the BDC Explorer (shown below) which may be used to create the BDC Model.

The BDC Explorer works with the Properties window to allow you to inspect and modify the BDC Model. The screenshot below demonstrates this. When Identifier 1 is selected in the BDC Explorer the corresponding properties are displayed in the Properties window.

BDC Model Designer

Visual Studio 2010 now includes the BDC Model Designer (shown below) which is used to create the BDC Model.

The BDC Model Designer works just like the BDC Explorer with the Properties window.

Defining the BDC Model

In this series of steps the mappings between the .NET Assembly Connector and the BDC are defined.

To begin, open the BDC Explorer by right clicking on the BDCModel1.bdcm file in the Solution Explorer and selecting Open. You will notice the BDC Model Designer opens.

Then, in the View Menu, select Other Windows and select BDC Explorer.

The first thing to do is to rename the BDC Model. To rename the BDC Model, select BDCModel1 in the BDC Explorer. There are many nodes in the BDC Explorer named BDCModel1 by default, select the highlighted one shown below.

In the Properties window change the Name property to FlatFileBDCModel

The next thing to do is to rename the LOB System. To rename the LOB System, select BDCModel1 in the BDC Explorer. There are many nodes in the BDC Explorer named BDCModel1 by default, select the highlighted one shown below.

In the Properties window change the Name property to FlatFileLOBSystem

The next thing to do is to rename the LOB System Instance. To rename the LOB System Instance, select BDCModel1 in the BDC Explorer. There are many nodes in the BDC Explorer named BDCModel1 by default, select the highlighted one shown below.

In the Properties window change the Name property to FlatFileLOBSystemInstance

The ShowInSearchUI property on the FlatFileLOBSystemInstance must be set to allow the FlatFileLOBSystemInstance to be crawled and searched by the SharePoint search service. This property does not have to be set if the LOBSystemInstance will not be crawled or searched. To set the ShowInSearchUI property on the FlatFileLOBSystemInstance click the FlatFileLOBSystemInstance node in the BDC Explorer.

Then, in the Properties window click the button in the Custom Properties row.

In the Properties Editor, add the ShowInSearchUI property, with a data type of System.String and a value of x.

Click OK

After the LOB System and LOB System Instance are configured the Entity and its properties are configured.

Rename the entity in the BDC Model to match the entity defined in the .NET Assembly Connector. To do this, select Entity1 in the BDC Explorer.

In the Properties window change the Name property to FlatFileEntity

After the Entity and its properties are configured the methods and parameters are configured.

The RootFinder property on the finder method must be set to specify the finder method used to enumerate the items to crawl. This property does not have to be set if the finder method is not used for crawling. To set the RootFinder property on the finder method click the ReadList node in the BDC Explorer.

Then, in the Properties window click the button in the Custom Properties row.

In the Properties Editor, add the RootFinder property, with a data type of System.String and a value of x.

The next step is to set the identifier for finder method return parameter in the entity. To do so first select the Identifier1 node under the finder method return parameter for the FlatFileEntity (highlighted below).

Then, in the Properties window change the Name property to ID

Next, set the identifier for the specific finder return parameter in the entity. To do so first select the Identifier1 node under the specific finder method return parameter for the FlatFileEntity (highlighted below).

Then, in the Properties window change the Name property to ID

Finally, set the identifier for the specific finder input parameter in the entity. To do so first select the Identifier1 node under the specific finder method for the FlatFileEntity (highlighted below).

Then, in the Properties window change the Name property to ID

In the BDC Model Designer, right click Identifier1 and select Rename

Rename the Identifier to ID

After the Identifier is configured the remaining parameters are configured

First, remove the message parameters since they are not needed. To remove the message parameter from the finder method select the message node in the BDC Explorer, right click, and select Delete.

To remove the message parameter from the specific finder method select the message node in the BDC Explorer, right click, and select Delete.

Next, add the parameters which map to the data in the flat-file-data-source.txt file. Use steps 29-31 to add the parameters listed in the table below.

Parameter Name

Data Type

Company

System.String

FirstName

System.String

LastName

System.String

Address

System.String

City

System.String

State

System.String

ZipCode

System.String

Phone

System.String

LastUpdated

System.DateTime

In the BDC Explorer, right click Entity1 and select Add Type Descriptor

In the Properties window change the Name property to match the name of the property you are adding from the table above.

In the Properties window change the Type Name property to match the data type of the property you are adding from the table above.

Once all the parameters are added the BDC Model looks like this in the BDC Explorer:

Next, copy the parameters from the finder method to the specific finder method. To do this perform steps 33-35 for each return parameter in the finder method.

Note: The ID parameter is already configured and does not need to be copied from the finder method to the specific finder method.

In the BDC Explorer select the parameter to copy, right click, and select Copy.

In the BDC Explorer select the Entity1 node under the returnParameter node for the specific finder method, right click, and select Paste.

After all the parameters are copied the BDC Model looks like this in the BDC Explorer:

Next, edit the data type of the LastUpdated return parameter in the specific finder method. To do this, select the LastUpdated return parameter for the specific finder method.

In the Properties window change the Type Name property to System.String

After the remaining parameters are configured the finder method instance properties are configured

Configuring method instance properties may be done with the BDC Method Details window which Visual Studio 2010 now includes. Selecting a method in the BDC Designer displays the corresponding method instance in the BDC Method Details window, as seen in the screenshot below.

The BDC Method Details window works just like the BDC Explorer and the BDC Model Designer do with the Properties window.

The RootFinder property on the finder method instance must be set to specify the finder method instance used to enumerate the items to crawl. This property does not have to be set if the finder method instance is not used for crawling. To set the RootFinder property on the finder method instance first click the ReadList node in the BDC Method Details window (highlighted in the screenshot below).

Then, in the Properties window click the button in the Custom Properties row.

In the Properties Editor, add the RootFinder property, with a data type of System.String and a value of x.

The LastModifiedTimeStampField property on the finder method instance is set for two reasons.

1. So search results will display the last time the data was updated
2. So the search service knows if the data has been changed when executing incremental crawls.

This property does not have to be set if the finder method instance is not used for crawling. To set the RootFinder property on the finder method instance first click the ReadList node in the BDC Method Details window (highlighted in the screenshot below).

Then, in the Properties window click the button in the Custom Properties row.

In the Properties Editor, add the LastModifiedTimeStampField property, with a data type of System.String and a value of LastUpdated

Quick Cleanup

In the Solution Explorer right click the FlatFileEntityService.cs class and select Open.

The designer sometimes adds some unneeded code to the class that must be removed. Locate the following code and delete it. If you cannot locate this code then move on to the next step.

Deploy and Test

Deploying and testing the .NET Assembly Connector is a piece of cake!

Deploy

In the Build menu, select Deploy Solution.

At this point Visual Studio 2010 compiles the .NET Assembly Connector code and packages the BDC Model and the feature which deploys both of these items into a WSP. The WSP is deployed to the SharePoint web site specified when the project was created. The feature in the WSP is automatically activated to register the External Content Type associated with the .NET Assembly Connector.

Test

Open the SharePoint web site where Visual Studio 2010 deployed the WSP.

Log in as a user who has the permission to create lists.

Click the Site Actions menu

Select View All Site Content

Click Create

In the Filter By section of the page click Lists

Select External List

Click the Create button

In the Name textbox enter Flat File Data

Click the button to browse the external content types.

Select the FlatFileLOBSystemInstance in the External Content Type Picker

Click OK

Click Create

View the data from the flat-file-data-source.txt file in the external list!

Where’s the XML?

I’m sure you noticed the lack of XML during this blog post. If you are curious where the XML is that defines the BDC Model follow these steps:

In the Solution Explorer, right click the BdcModel1.bdcm file and select Open With…

Hello Todd, Great post. I’m not able to duplicated I get the following error. Is it possible you could post the VS project?

Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

Hi!!!thanks a lot for the tutorial, it’s really helpfull, but just a comment…I did it in a brand new sharepoint environment and I had a security trouble with the Business Data Connectivity Service. I just had to add the user into the security list, maybe would be good to add it into the steps..

Sweet article my friend. A requirement recently came up with my client to search data from a classic .asmx web service. Your article is detailed enough that I could adapt the problem domain to m y own from your code. Thanks for posting such a detailed description.

One constructive comment. The first time I went through this article, I didn’t quite get the point that ‘ID’ should be the identifying field in my data, not specifically ‘ID’. Adding some clarification to that may help newbies to BDC.

Wonderful article – thanks so much I was looking for the Part 2 based upon your comment: "Check back tomorrow for part two in this series which demonstrates how to register an external content type with the SharePoint search service to make the data searchable."

The Business Data Catalog (BDC) has been renamed to Business Data Connectivity in the SP2010 release. Business Data Connectivity is one component which makes up the Business Connectivity Services. !BCSArchitecture.png! Comes with WSS4….

The Business Data Catalog (BDC) has been renamed to Business Data Connectivity in the SP2010 release. Business Data Connectivity is one component which makes up the Business Connectivity Services. !BCSArchitecture.png! Comes with WSS4….

Anyway, Have you tried to configure incremental crawl on this example?

I think you may need to __BdcLastModifiedTimestamp property but I am still not success for configuring incremental crawl.

Regards,

About Todd

I'm (Todd Baginski), an nine-time Microsoft SharePoint Server MVP, O365 and SharePoint Top 25 Influencer, and Sitecore Certified Professional Developer whose blog focuses on Microsoft SharePoint, Office 365, Azure, Mobile, Office, and cloud technologies. I am the VP of SharePoint and Mobility at Canviz Consulting where I lead our SharePoint and Mobility divisions. In my free time I enjoy playing with my son, relaxing with my wife, and playing a variety of sports like skiing, lacrosse, hockey, and softball.