May 2011
Volume 26 Number 05
Business Connectivity Services - Consuming External OData Feeds with SharePoint BCS
By Eric White | May 2011
Microsoft Business Connectivity Services (BCS) is a feature of Microsoft Office 2010 and SharePoint 2010 that helps developers and users bring data into SharePoint. Surfacing external data in SharePoint enables users to build composite applications that give them better access to critical information and make their interactions with that information more convenient.
BCS provides three basic mechanisms that you can use to bring external data into SharePoint. First, you can connect to and consume databases via SQL queries. By default, SQL Server is supported. With some work, you can connect to MySQL, Oracle and other database-management systems.
Second, you can consume Web services that expose methods that follow specific patterns for the method prototypes.
Third, you can use the Microsoft .NET Framework and C# or Visual Basic code to connect to data sources. The most common approach is to write a .NET Assembly Connector.
In this article, I’ll show you the third approach: writing a .NET Assembly Connector that consumes an Open Data Protocol (OData) feed.
Why a .NET Assembly Connector?
With the burgeoning proliferation of OData feeds, you may need to consume one of those feeds to enable interesting functionality for your users. BCS doesn’t have a built-in capability to consume OData feeds, but it’s relatively easy to write a .NET Assembly Connector to do so.
This is also a convenient approach for demonstrating how to write a .NET Assembly Connector. Another approach would be to write a custom connector to consume a SQL database, but this is superfluous because BCS can easily consume SQL data sources out of the box. Further, to demonstrate writing a .NET Assembly Connector that consumes a database requires that you install and configure the database appropriately. This is not difficult, but it adds some extra steps and complicates the example. In contrast, writing a .NET Assembly Connector that consumes an existing OData feed could not be simpler.
This example also shows you how to implement Create, Read, Update and Delete (CRUD) operations using OData. You’ll see just how easy this is.
You’ll probably be surprised to see just how little code you need to write in order to create a .NET Assembly Connector that consumes an OData feed. Data-access technologies have come a long way, and OData promises to enable a new level of interoperability between applications that produce and consume data.
Note that SharePoint designer is another approach to model, develop and publish BCS external context types. SharePoint Designer natively supports building business entity models from back-end SQL databases and Web services with relatively flat data structure. Use of SharePoint Designer simplifies (and reduces) BCS development work. However, it does not natively support OData services currently.
OData and SharePoint
OData is a Web protocol for querying and updating data that builds upon existing Web technologies such as HTTP, Atom Publishing Protocol (AtomPub) and JSON. OData is being used to expose and access information from a variety of sources including relational databases, file systems, content management systems and traditional Web sites. For a good introduction to OData, see “Building Rich Internet Apps with the Open Data Protocol” (msdn.microsoft.com/magazine/ff714561), from the June 2010 issue of MSDN Magazine. The article was written by Shane Burgess, one of the program managers in the Data and Modeling Group at Microsoft.
SharePoint Foundation 2010 and SharePoint Server 2010 expose list data as an OData feed. This functionality is enabled by default. If you have a SharePoint site installed at the URL https://intranet.contoso.com, you can retrieve the set of SharePoint lists for the site by entering https://intranet.contoso.com/\_vti\_bin/listdata.svc into your browser.
If your corporate SharePoint 2010 site includes the My Site feature and your alias is, say, ericwhite, you can see the lists exposed on your My Site by entering https://my/sites/ericwhite/_vti_bin/listdata.svc into your browser. In either case, you’ll see an atom feed like the one in Figure 1 displayed in the browser.
.jpg "OData from a SharePoint List")
Figure 1 OData from a SharePoint List
With the .NET Framework, a simple way to consume an OData feed is to use WCF Data Services. You use Visual Studio to add a service reference to the OData feed, and the IDE automatically generates code so you can use strongly typed classes to query and update the feed. I’ll walk you through this process.
For more information about how this works, see the WCF Data Services Developer Center (msdn.microsoft.com/data/bb931106), which has a beginner’s guide and links to resources.
Getting Started
As I mentioned earlier, SharePoint 2010 exposes list data as OData feeds. An easy way to access the feeds is through a .NET Assembly Connector, and I’m going to walk you through the process of building that connector.
This process will create an external content type that you can display and maintain in an external list. This might seem a little bit funny—after you have the example working, you’ll have a SharePoint site that contains two lists with exactly the same data. One of the lists will be a SharePoint list that you create and set up in the usual way. The other list will be an external list that displays the data coming from the OData feed for the first list. If you add or alter records in one of the lists, the changes show up in the other one.
The primary benefit of this approach is that it’s simple to build and run the example. You don’t need to install any infrastructure for the example. All you need is a SharePoint farm for which you have farm administrator access.
If you don’t already have the infrastructure, the easiest way to run the example is to download the 2010 Information Worker Demonstration and Evaluation Virtual Machine (https://www.softpedia.com/get/Internet/Servers/Server-Tools/Information-Worker-Demonstration-and-Evaluation.shtml). The virtual machine, or VM, comes complete with an installed, working copy of SharePoint 2010, Visual Studio 2010, Office 2010 and much, much more. The example demonstrated in this article works without modifications in this VM.
If you have your own SharePoint 2010 development environment, it’s easy to modify this example appropriately (I’ll indicate where as I go along). However, if you’re just getting started with SharePoint development and want to try out a few examples, the VM is the way to go.
The first step in building this example is to become familiar with using OData to manipulate data in a SharePoint list. In this example, you’re connecting to a customer relationship management (CRM) system that exposes a customer list using OData.
First, create a SharePoint list that contains a few records that represent customers.
- Open a browser, browse to a SharePoint site, and create a custom list named Customers.
- Change the name of the Title column to CustomerID.
- Create a new column named CustomerName, with a type of Single line of text.
- Create a new column named Age, with a type of Number.
- Add a few records.
Now, log into the VM as administrator and start Visual Studio 2010. Create a new Windows Console Application. For building these OData samples, it doesn’t matter whether you build for .NET Framework 4 or .NET Framework 3.5. However, when building the .NET Assembly Connector later, you’ll need to target .NET Framework 3.5 because it’s the only version currently supported by SharePoint 2010. To have Visual Studio generate classes with good namespace names, name this project Contoso.
Later in this article, I’ll discuss namespace names for both OData and BCS .NET Assembly Connectors. There are specific things you can do to generate namespace names properly, and in this case, the namespace name will make the most sense if you name the project Contoso.
On the Visual Studio menu, click Project, then click Add Service Reference. Enter the OData service URL of the SharePoint site in the Add Service Reference dialog box. If you’re using the demo VM, the service URL is https://intranet.contoso.com/\_vti\_bin/listdata.svc.
If you’re connecting to a SharePoint site at a different URL, you’ll need to adjust the service URL as appropriate.
Click Go. Visual Studio will attempt to go to the location and download metadata from the SharePoint site. If successful, it will display the service name in the Services list in the Add Service Reference dialog box. Because you’re simulating a CRM system, enter Crm into the Namespace field. Click OK. It’s interesting to examine the generated code. Click the Show All Files button in the Solution Explorer window, then expand the Crm namespace, expand Reference.datasvcmap and open Reference.cs. It’ll look something like this (comments removed for clarity):
namespace Contoso.Crm {
public partial class TeamSiteDataContext :
global::System.Data.Services.Client.DataServiceContext {
...
Because of how you named the project and the service reference namespace, the namespace for the generated classes is Contoso.Crm. The fully qualified name of the class for the Customers list is Contoso.Crm.Customers, which makes sense.
Also note the generated name for the data context. In this case, it’s TeamSiteDataContext. The generated name of this class is based on the name of the SharePoint site that you connect to. In the case of the demo VM, the name of the default site that you connect to is Team Site. If your environment is different, note the name of the data context class so that you can alter code in examples appropriately.
Open Program.cs and update it with the code shown in Figure 2. If you’re not using the demo VM, adjust the OData service URL accordingly. Compile and run the program to see the results of the query. As you can see, it doesn’t take a lot of code to retrieve data from a list using OData.
Figure 2 Updated Code for Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using Contoso.Crm;
class Program {
static void Main(string[] args) {
TeamSiteDataContext dc =
new TeamSiteDataContext(new Uri(
"https://intranet.contoso.com/_vti_bin/listdata.svc"));
dc.Credentials = CredentialCache.DefaultNetworkCredentials;
var customers =
from c in dc.Customers
select new {
CustomerID = c.CustomerID,
CustomerName = c.CustomerName,
Age = c.Age,
};
foreach (var c in customers)
Console.WriteLine(c);
}
}
CRUD Operations with OData
Now let’s try inserting a customer. Your code will need to create a new CustomersItem and initialize its values. Then it calls the TeamSiteDataContext.AddToCustomers method, passing the CustomersItem object as a parameter. Finally, to commit changes you’ll call the TeamSiteDataContext.SaveChanges.
In Program.cs, replace the customers variable declaration and foreach loop with the following code:
CustomersItem cust = new CustomersItem();
cust.CustomerID = "JANE08";
cust.CustomerName = "Jane";
cust.Age = 22;
dc.AddToCustomers(cust);
dc.SaveChanges();
To update a customer, you’ll query the Customers list, retrieving the specific customer to update. Then update properties as appropriate. Call the TeamSiteDataContext.UpdateObject method. Finally, call the TeamSiteDataContext.SaveChanges to commit changes:
CustomersItem cust = dc.Customers
.Where(c => c.CustomerID == "BOB01")
.FirstOrDefault();
if (cust != null) {
cust.CustomerName = "Robert";
dc.UpdateObject(cust);
dc.SaveChanges();
}
To delete a customer, query the Customers list, retrieving the specific customer to delete. Call the TeamSiteDataContext.DeleteObject method. Call the TeamSiteDataContext.SaveChanges to commit changes:
CustomersItem cust = dc.Customers
.Where(c => c.CustomerID == "BILL02")
.FirstOrDefault();
if (cust != null) {
dc.DeleteObject(cust);
dc.SaveChanges();
}
As you can see, altering a SharePoint list using OData is simple.
Building a .NET Assembly Connector
In the process of building the .NET Assembly Connector, you define a class that represents the entity that you’ll expose as an external content type. In this example, you define a Customer class that represents an item in the Customers list. Some methods, such as the method to create a customer, take an instance of this class as an argument. Other methods, such as the method to return all customers in the list, return a collection of instances of this class.
You’ll also configure a BCS model that describes this class in an XML dialect. The infrastructure underlying BCS will use the information in the XML definition of the BCS model so that the external content type is usable from within SharePoint 2010.
If there’s one key point you should take away from this article, it’s this: You must make the model match the actual defined class exactly.
There are tools that help you make the BCS model definition match the actual class. However, the key point is that through one approach or another, you need to carefully validate that the model matches the actual class.
In a more typical BCS implementation, you’ll define many of these classes and model all of them in the BCS model definition. The bulk of the work when implementing a complex BCS solution is to make the model match the classes.
Now let’s build the connector. For this example you’ll just build a read-only .NET Assembly Connector, but once you’ve seen the basics, it should be straightforward to add the rest of the CRUD functionality.
The code download for this article includes the code and the BCS model for CRUD functionality; it works without modification in the demo VM.
Log into your SharePoint development computer as administrator. You must have farm administrator rights to build and deploy a .NET Assembly Connector.
Start Visual Studio 2010. Create a new project. Create a new SharePoint 2010 Business Data Connectivity (BDC) Model application. As before, you must target the .NET Framework 3.5. Name the project Contoso and click OK. Again, we’ll use the project name Contoso, which will be used in the namespace.
In the SharePoint Customization Wizard you can enter a local site URL of a SharePoint site for debugging. In this wizard on the demo VM, the URL is correctly set by default to https://intranet.contoso.com. Change this URL if you’re working with a SharePoint site at a different address. The wizard also lets you know that this project will be deployed as a farm solution. Click Finish. Wait a bit for the wizard to run.
Rename the BDC model nodes in the Solution Explorer from BdcModel1 to ContosoBdcModel.
Next, open the BDC Explorer pane (by default right next to the Solution Explorer). Rename the three BdcModel1 nodes to ContosoBdcModel. In the BDC Explorer, you can’t directly rename each node in the tree control. Instead, you need to select each node and then modify the name in the Properties pane (see Figure 3).
.jpg "Changing Model Names")
Figure 3 Changing Model Names
The next step is to rename the entity and specify the identifier for the entity. Select Entity1 in the BDC Designer. After selecting the entity, you can change its name in the Properties pane. Change its name to Customers, and change its namespace to Contoso.Crm.
In the BDC Designer, click on Identifier1, and change its name to CustomerID. You also need to design the entity in the BDC Explorer. This part needs to be done precisely. If there’s a mismatch between the BDC model and the actual class that you’ll be using, the results are undefined, and error messages are not always illuminating. In some cases, your only clue to what is wrong is that the list Web Part for the external content type won’t load.
Expand the nodes in the BDC Explorer until you can see the Identifier1 node under the id parameter of the ReadItem method. Change its name to CustomerID. Expand the tree until you can see the Entity1 node under the returParameter for the ReadItem method. Change the name of the entity to Customers, and change the type name to Contoso.Crm.Customers, ContosoBdcModel.
You’re going to completely redefine the Customer entity, so delete the Identifier1 and Message nodes from the Customers entity.
Right-click on Customers and click Add Type Descriptor. Rename the name of the new type descriptor to CustomerID. By default, the type of a new type descriptor is set to System.String, which is what you want for this example. In the Properties pane, scroll down until you see the Identifier field. Use the drop-down list to change its value to CustomerID.
Again, right-click on Customers and click Add Type Descriptor. Rename to CustomerName. Its type is System.String, which is correct.
Add another type descriptor, rename to Age, and change its type to System.Int32. After making these changes, the BDC Explorer pane will look like Figure 4. Expand the ReadList node, expand the returnParameter node and rename Entity1List to CustomersList. The type name is set to the following:
System.Collections.Generic.IEnumerable`1[[Contoso.BdcModel1.Entity1, ContosoBdcModel]]
.jpg "The Completed Customers Entity")
Figure 4 The Completed Customers Entity
This syntax, consisting of the back tick followed by the one (`1) is the syntax that represents a generic class with one type parameter. The type that follows in the double square brackets consists of a fully qualified type, as well as the model name of the BDC model that contains the type. Change the type name to:
System.Collections.Generic.IEnumerable`1[[Contoso.Crm.Customer, ContosoBdcModel]]
This corresponds to a type of IEnumerable<Contoso.Crm.Customer>, where the Customer type is found in the ContosoBdcModel.
Delete the Entity1 node that’s a child of the CustomersList node. Copy the Customers entity that you recently configured as a child type descriptor of the returnParameter of the ReadItem method.
Select the CustomersList node that’s under the returnParameter of the ReadList method, and paste the Customers entity.
Return to the Solution Explorer window, and edit the Feature1.Template.xml file. Add a SiteUrl property with a value of the URL of the SharePoint site:
<?xml version="1.0" encoding="utf-8" ?>
<Feature xmlns="https://schemas.microsoft.com/sharepoint/">
<Properties>
<Property Key="GloballyAvailable" Value="true" />
<Property Key="SiteUrl" Value="https://intranet.contoso.com" />
</Properties>
</Feature>
Rename Entity1.cs to Customers.cs. Replace the contents of Customers.cs with the following code, which defines the Customer entity for the assembly connector:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Contoso.Crm {
public partial class Customer {
public string CustomerID { get; set; }
public string CustomerName { get; set; }
public int Age { get; set; }
}
}
Replace the code in CustomersService.cs with the code in Figure 5, which defines the methods that retrieve a single item and retrieve a collection of items.
Figure 5 CustomersService.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Text;
using Contoso.Crm;
namespace Contoso.Crm {
public class CustomersService {
public static Customer ReadItem(string id) {
TeamSiteDataContext dc = new TeamSiteDataContext(
new Uri("https://intranet.contoso.com/_vti_bin/listdata.svc"));
dc.Credentials = CredentialCache.DefaultNetworkCredentials;
var customers =
from c in dc.Customers
where c.CustomerID == id
select new Customer() {
CustomerID = c.CustomerID,
CustomerName = c.CustomerName,
Age = (int)c.Age,
};
return customers.FirstOrDefault();
}
public static IEnumerable<Customer> ReadList() {
TeamSiteDataContext dc = new TeamSiteDataContext(
new Uri("https://intranet.contoso.com/_vti_bin/listdata.svc"));
dc.Credentials = CredentialCache.DefaultNetworkCredentials;
var customers =
from c in dc.Customers
select new Customer() {
CustomerID = c.CustomerID,
CustomerName = c.CustomerName,
Age = (int)c.Age,
};
return customers;
}
}
}
Following the procedures that I described at the beginning of this article, add a service reference to the site, specifying a namespace of Crm. Click OK. Build the solution by clicking Build | Build Contoso. Now deploy the solution by clicking Build | Deploy Contoso.
Give permissions for all authenticated users to access this external content type. Open Central Administration, then click Manage Service Applications. Click Business Data Connectivity Service. Click on the down arrow next to the external content type that you just added, and then click Set Permissions. Click the Browse button just below the text box. Click All Users, then click All Authenticated Users. Click the Add button, then click OK.
Back in the Set Object Permissions window, click the Add button, and then set permissions for Edit, Execute, Selectable In Clients and Set Permissions. Click OK.
Browse to the SharePoint site. Create a new External List. Specify CustomersExt for the name of the list. Click on the external content type browser. In the External Content Type Picker, click on the external content type you just created. If you’re using the demo VM, the content type you just created will be the only external content type in the list. Click OK.
Click the Create button, wait a bit and, if all goes well, you’ll see a new external list that contains the same data as the Customers list.
If you add a record to the regular list, you’ll see it show up in the external list. You can play around with the data a bit. Add, delete or change some records in the regular SharePoint list and see the data show up in the external list.
Pulling the Pieces Together
As you’ve seen, it’s pretty easy to use WCF Data Services to query and modify an OData feed. It’s also a straightforward process to create an external content type via a .NET Assembly Connector. You can connect to just about any data source using a .NET Assembly Connector.
This was just a simple example employing a readily accessible data source, but the pattern is easily extended to any data source with an OData feed. To learn more about BCS customization, see the MSDN Library page “Business Connectivity Services How-tos and Walkthroughs” (msdn.microsoft.com/library/ee557949). There’s also a trove of information about OData at the MSDN Data Developer Center (msdn.microsoft.com/data/bb931106) and the Open Data Protocol site (odata.org).
Eric White is an independent software developer and author with 25 years of experience developing enterprise applications on a variety of platforms. He currently specializes in Microsoft Office technologies, including Open XML, SharePoint and Office client development. You can follow him on Twitter at twitter.com/EricWhiteDev or on his blog at ericwhite.com/blog.
Thanks to the following technical experts for reviewing this article: Ying Xiong