Create an OData data service for use as a BCS external system

Learn how to create an Internet-addressable WCF service that uses OData to send notifications to SharePoint when the underlying data changes. These notifications are used to trigger events that are attached to external lists.

This article describes how to create an ASP.NET Windows Communication Foundation (WCF) Data Service to expose the AdventureWorks 2012 LT sample database. This enables you to access the data through the Open Data protocol (OData). When access is established through OData, you can configure a Business Connectivity Services (BCS) external content type that will enable SharePoint to consume the data from the external database. To further enhance this OData source, you can add service contracts to the WCF service that will enable BCS to subscribe to notifications that indicate that the external data has changed.

Prerequisites for creating the OData service

The following are required to create the OData service in this article:

• A server hosting Internet Information Services (IIS)

• .NET Framework 3.5 or later

• SharePoint

• AdventureWorks 2012 LT script

• AdventureWorks 2012 LT Data

• Visual Studio 2012

• Office Developer Tools for Visual Studio 2012

For information about setting up your development environment, see Set up a general development environment for SharePoint.

Core concepts for creating an OData service

Table 1 lists articles that will help you understand the core concepts of building a WCF service using OData and external content.

Table 1. Core concepts for creating an OData service

Steps involved in creating the external system

The following steps will need to be completed

• Install the AdventureWorks 2012 LT sample database

• Create the OData Service

• Set access permissions

• Test the service

• Add capabilities for additional BCS functionality

Installing the sample database

An external system is usually a database and for that reason, this example shows how to use the AdventureWorks 2012 LT sample database to represent a proprietary datasource.

The following steps will

How to create the WCF OData service

Creating a Windows Communication Foundation (WCF) service that can be accessed through the OData protocol is relatively straightforward. Visual Studio 2012 provides several tools for automatically discovering and modeling the data from various data sources. This allows you to create connections and interfaces to data in SQL Server databases and other types of data stores that can then be worked with programmatically using an extensive object model.

However, for SharePoint to enable BCS to receive notifications from remote systems when the underlying data has changed, you must modify the WCF service to respond to those changes.

To create a new WCF project

1. In Visual Studio, on the File menu, choose New, Project.

2. In the New Project dialog box, choose the Web template, and then choose ASP.NET Web Application.

3. Enter AdventureWorksService for the project name, and choose OK.

4. In Solution Explorer, open the shortcut menu for the ASP.NET project that you just created, and choose Properties.

5. Select the Web tab, and set the value of the Specific port text box to8008.

To define the data model

1. In Solution Explorer, open the shortcut menu for the ASP.NET project, and choose Add New Item.

2. In the Add New Item dialog box, choose the Data template, and then choose ADO.NET Entity Data Model.

3. For the name of the data model, enter AdventureWorks.edmx.

4. In the Entity Data Model Wizard, choose Generate from Database, and then choose Next.

5. Connect the data model to the database by doing one of the following steps, and then choose Next.

• If you do not have a database connection already configured, choose New Connection, and create a new connection.

• If you have a database connection already configured to connect to the Northwind database, choose that connection in the list of connections.

• On the final page of the wizard, select the check boxes for all tables in the database, and clear the check boxes for views and stored procedures.

6. Choose Finish to close the wizard.

To create the data service

1. In Solution Explorer, open the shortcut menu for your ASP.NET project, and then choose Add New Item.

2. In the Add New Item dialog box, choose WCF Data Service.

3. For the name of the service, enter AdventureWorks.

   Visual Studio creates the XML markup and code files for the new service. By default, the code-editor window opens. In Solution Explorer, the service will have the name, AdventureWorks, with the extension .svc.cs or .svc.vb.

4. Replace the comment /* TODO: put your data source class name here */ in the definition of the class that defines the data service with the type that is the entity container of the data model, which in this case is AdventureWorksEntities. The class definition should look like the following:

  
public class AdventureWorks : DataService<AdventureWorksEntities>

By default, when a WCF service is created, it cannot be accessed due to its security configuration. The next step lets you specify who can access it, and what rights they have.

To enable access to data service resources

• In the code for the data service, replace the placeholder code in the InitializeService function with the following.

config.SetEntitySetAccessRule("*", EntitySetRights.All);
config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);

This enables authorized clients to have read and write access to resources for the specified entity sets.

For BCS to receive notifications, there must be a mechanism on the back-end data source that will accept a request to be added and removed from notification subscriptions.

The last step in creating the service is to add service operations for the Subscribe and Unsubscribe stereotypes that are defined in the BDC model.

To add service operations for Subscribe and Unsubscribe stereotypes

• In the AdventureWorks.cs page, add the following string variable declaration.

public string subscriptionStorePath = @"\\\\[SHARE_NAME]\\SubscriptionStore\\SubscriptionStore.xml";

Then add the following two WebGet methods to handle the subscriptions.

[WebGet]
public string Subscribe(string deliveryUrl, string eventType)
{
    string subscriptionId = Guid.NewGuid().ToString();

    XmlDocument subscriptionStore = new XmlDocument();

    subscriptionStore.Load(subscriptionStorePath);

    // Add a new subscription element.
    XmlNode newSubNode = subscriptionStore.CreateElement("Subscription");

    // Add subscription ID element to the subscription element.
    XmlNode subscriptionIdStart = subscriptionStore.CreateElement("SubscriptionID");
    subscriptionIdStart.InnerText = subscriptionId;
    newSubNode.AppendChild(subscriptionIdStart);

    // Add delivery URL element to the subscription element.
    XmlNode deliveryAddressStart = subscriptionStore.CreateElement("DeliveryAddress");
    deliveryAddressStart.InnerText = deliveryUrl;
    newSubNode.AppendChild(deliveryAddressStart);

    // Add event type element to the subscription element.
    XmlNode eventTypeStart = subscriptionStore.CreateElement("EventType");
    eventTypeStart.InnerText = eventType;
    newSubNode.AppendChild(eventTypeStart);

    // Add the subscription element to the root element. 
    subscriptionStore.AppendChild(newSubNode);

    subscriptionStore.Save(subscriptionStorePath);

    return subscriptionId;
}

[WebGet]
public void Unsubscribe(string subscriptionId)
{
    XmlDocument subscriptionStore = new XmlDocument();
    subscriptionStore.Load(subscriptionStorePath);

    XmlNodeList subscriptions = subscriptionStore.DocumentElement.ChildNodes;
    foreach (XmlNode subscription in subscriptions)
    {
        XmlNodeList subscriptionList = subscription.ChildNodes;
        if (subscriptionList.Item(0).InnerText == subscriptionId)
        {
            subscriptionStore.DocumentElement.RemoveChild(subscription);
            break;
        }
    }

    subscriptionStore.Save(subscriptionStorePath);
}

Next steps

To notify SharePoint that changes have been made, you also need to create a service that queries the data source for changes, and then sends notifications to all those subscribed to notifications.

See also

• Business Connectivity Services in SharePoint

• Using OData sources with Business Connectivity Services in SharePoint

• External content types in SharePoint

• External events and alerts in SharePoint

• How to: Create an external content type from an OData source in SharePoint

• Business Connectivity Services programmers reference for SharePoint