odata Archives

So what have I been working on for the past two weeks? Well, other than consulting clients, books and working on my garden, I have also been involved with the Microsoft Learning SharePoint 2013 Advanced Development Microsoft Official Curriculum (MOC) course 20489 that will be available later in the year (sorry no link just yet but it will be here when it is released). I was able to finish up two chapters on Search quickly as that is one of my main fortes, but then decided to take what I though was the middle of two Business Connectivity Services (BCS) chapters. For those of you not familiar with BCS, you can find a great overview here. It turns out, the module was the hardest one! Why? Because it covers things that no one has ever done before (outside of the product team that is).

So what is the big deal about what I worked on? You are probably saying to yourself…BCS has been around for a while right? Well, yes this is very true, and there are several great posts about how to expose external data using SharePoint Designer and Visual Studio using the various BDC model types (Database, WCF, .NET Connectivity and Custom). You can also find how to implement stereotyped methods that support CRUD methods and search indexing (link here). Given all that content, there were a game changing set of features that were added to BCS in SharePoint 2013 that add a whole new level of complexity. These features include:

There are plenty of posts on OData in general (this one from MSDN is pretty awesome if you are just getting started) and a few posts on how to setup a BDC OData model. And although my fellow SharePoint MVP Scot Hillier did a presentation on the subscriber model at the last SharePoint Conference it was only in context of a database model. When it comes to integrating the two features (OData and the subscriber methods) together, that is where a massive black hole exists and is the focus of this blog post.

The first step to getting this whole thing to work is to create an OData service. This is very simple with the tools provided by Visual Studio and steps to do this are provided in this MSDN post

The next step is to build your basic BCS model using the new item template wizard provided in Visual Studio 2012. This has also been nicely blogged about by several of my colleagues and does have an article on MSDN. The important thing to note about the MSDN article I reference is that it is using an OData feed that is hosted by http://services.odata.org. Since you do not own this service, you will not be able to extend it to implement the subscribe and unsubscribe methods that I discuss later in this post. Therefore, you can follow the steps in the article, but use a local instance of your OData service.

Once the service has been generated, you must add some supporting methods to your OData service to accept data from SharePoint when a subscription occurs. There are some gotchas to this. Currently there is no real guidance on how to set this up properly. The little that does exist will point you to mixed signals as to how to successfully setup the communication layers. In my example below, you will see that I am using a GET for the http method. This was the only successful way that I was able to get the method parameters to populate in the web method in the OData service. As you will see later, there are also some very important BDC method properties that must be set in order for all of this to work:

[WebGet]
        public string Subscribe(string DeliveryURL, int EventType, string EntityName, string SelectColumns)
        {
            //HttpRequest req = System.Web.HttpContext.Current.Request;

// Generate a new Guid that will function as the subscriptionId.
string subscriptionId = Guid.NewGuid().ToString();

if (DeliveryURL == null || EventType == null || EntityName == null || SelectColumns == null)
throw new Exception(""Missing parameters");

// This sproc will be used to create the subscription in the database.
string subscribeSproc = "SubscribeEntity";

string sqlConn = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=Pa$$w0rd";

            // Create connection to database.
            using (SqlConnection conn = new SqlConnection(sqlConn))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = subscribeSproc;
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@SubscriptionId", subscriptionId));
                cmd.Parameters.Add(new SqlParameter("@EntityName", EntityName));
                cmd.Parameters.Add(new SqlParameter("@EventType", EventType));
                cmd.Parameters.Add(new SqlParameter("@DeliveryAddress", DeliveryURL));
                cmd.Parameters.Add(new SqlParameter("@SelectColumns", SelectColumns));

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    conn.Close();
                }

                return subscriptionId;
            }
        }

[WebGet]
        public void Unsubscribe(string subscriptionId)
        {
            HttpRequest req = System.Web.HttpContext.Current.Request;

            // This sproc will be used to create the subscription in the database.
            string subscribeSproc = "UnsubscribeEntity";

string sqlConn = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=Pa$$w0rd";

cmd.Parameters.Add(new SqlParameter("@SubscriptionId", subscriptionId));

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

On the BCS side, you need to add the stereotyped methods that will send the data to the web methods you just created in the last step. This includes the EntitySubscriber and EntityUnsubscriber methods. First the let's review the EntitySubscriber method. In the table below, you will notice that I am sending the OData web method parameters in the querystring. You can use the '@' parameter notation just like in regular BDC Models to token replace the values. You should also use HTML encoded '&' to signify the ampersand (this was one of the things that took me a while to figure out). Notice the various method parameters. They include:

ODataEntityUrl – this is appended to the ODataServiceURL property of the LobSystemInstance (note that later when doing an explicit subscription call, the notification callback url will NOT be used)
ODataHttpMethod – the type of HTTP method you will perform (GET, POST, MERGE, etc). I was never able to get POST to work with a Visual Studio generated OData layer, more on that later.
ODataPayloadKind – This is one of the more confusing aspects of OData. You can find the enumeration for the ODataPayloadKind here, but there is very little documentation on how it works between SharePoint and the custom methods you generate on the OData service side. It took me forever to figure out that the "Entity" payload just doesn't work. After running through just about every permutation of Http methods, payloads and formats, I finally found a working combination with the "Property" payload
ODataFormat – This was another painful setting to determine. When you create your OData service, it is expecting a very specific Content-Type http header to be sent, this header is based on the version of Visual Studio you have. I learned this the hard way, but things started to make sense for me after I reviewed this awesome post about how the OData service generation and layers works in Microsoft world and how to customize its behavior after generating it. For more information on OData supported version, check out this post. In several examples, you may see that the format is set to "application/atom+xml". Well, that format is not supported in the OData service! What you will end up with is an http exception being sent to the calling client (in this case SharePoint) that says "Unsupported media type". This is very unfortunate. Why? Because the error occurs last in the call stack of the web method…AFTER your web method code has run and created the subscription successfully! In order to catch this type of event, you must override the HandleException method of the OData service and rollback any subscriptions that were created by using some kind of instance variable! This would apply to anything that happens that would result in an error as the response is being sent back to the client.
ODataServiceOperation – still haven't figured out what this does!
NotificationParserType – this will be explored more below

Here is the working method XML for the Subscribe method:

<Method Name="SubscribeCustomer" DefaultDisplayName="Customer Subscribe" IsStatic="true">
              <Properties>
                <Property Name="ODataEntityUrl" Type="System.String">/Subscribe?DeliveryURL='@DeliveryURL'&EventType=@EventType&EntityName='@EntityName'&SelectColumns='@SelectColumns'</Property>
                <Property Name="ODataHttpMethod" Type="System.String">GET</Property>
                <Property Name="ODataPayloadKind" Type="System.String">Property</Property>
                <Property Name="ODataFormat" Type="System.String">application/json;odata=verbose</Property>
                <Property Name="ODataServiceOperation" Type="System.Boolean">false</Property>
              </Properties>
              <AccessControlList>
                <AccessControlEntry Principal="NT AuthorityAuthenticated Users">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                  <Right BdcRight="SetPermissions" />
                  <Right BdcRight="SelectableInClients" />
                </AccessControlEntry>
              </AccessControlList>
              <Parameters>
                <Parameter Direction="In" Name="@DeliveryURL">
                  <TypeDescriptor TypeName="System.String" Name="DeliveryURL" >
                    <Properties>
                      <Property Name="IsDeliveryAddress" Type="System.Boolean">true</Property>
                    </Properties>
                  </TypeDescriptor>
                </Parameter>
                <Parameter Direction="In" Name="@EventType">
                  <TypeDescriptor TypeName="System.Int32" Name="EventType" >
                    <Properties>
                      <Property Name="IsEventType" Type="System.Boolean">true</Property>
                    </Properties>
                  </TypeDescriptor>
                </Parameter>
                <Parameter Direction="In" Name="@EntityName">
                  <TypeDescriptor TypeName="System.String" Name="EntityName" >
                    <DefaultValues>
                      <DefaultValue MethodInstanceName="SubscribeCustomer" Type="System.String">Customers</DefaultValue>
                    </DefaultValues>
                  </TypeDescriptor>
                </Parameter>
                <Parameter Direction="In" Name="@SelectColumns">
                  <TypeDescriptor TypeName="System.String" Name="SelectColumns" >
                    <DefaultValues>
                      <DefaultValue MethodInstanceName="SubscribeCustomer" Type="System.String">*</DefaultValue>
                    </DefaultValues>
                  </TypeDescriptor>
                </Parameter>
                <Parameter Direction="Return" Name="SubscribeReturn">
                  <TypeDescriptor Name="SubscriptionId" TypeName="System.String" >
                    <Properties>
                      <Property Name="SubscriptionIdName" Type="System.String">SubscriptionId</Property>
                    </Properties>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Type="EventSubscriber" ReturnParameterName="SubscribeReturn" ReturnTypeDescriptorPath="SubscriptionId" Default="true" Name="SubscribeCustomer" DefaultDisplayName="Customer Subscribe">
                  <AccessControlList>
                    <AccessControlEntry Principal="NT AuthorityAuthenticated Users">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SetPermissions" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                  </AccessControlList>
                </MethodInstance>
              </MethodInstances>
            </Method>

Next is the unsubscribe method, notice how SharePoint must pass back the subscription id that lives in the external system. The name of the SubscriptionIdName property will always be SubscriptionId. This subscription id must be saved somewhere, but the question is…where?:

<Method Name="UnSubscribeCustomer" DefaultDisplayName="Customer Unsubscribe">
              <Properties>
                <Property Name="ODataEntityUrl" Type="System.String">/UnSubscribe?SubscriptionId='@SubscriptionId'</Property>
                <Property Name="ODataHttpMethod" Type="System.String">GET</Property>
                <Property Name="ODataPayloadKind" Type="System.String">Property</Property>
                <Property Name="ODataServiceOperation" Type="System.Boolean">false</Property>
              </Properties>
              <AccessControlList>
                <AccessControlEntry Principal="NT AuthorityAuthenticated Users">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                  <Right BdcRight="SetPermissions" />
                  <Right BdcRight="SelectableInClients" />
                </AccessControlEntry>
                <AccessControlEntry Principal="Contosodomain users">
                  <Right BdcRight="Edit" />
                  <Right BdcRight="Execute" />
                  <Right BdcRight="SetPermissions" />
                  <Right BdcRight="SelectableInClients" />
                </AccessControlEntry>
              </AccessControlList>
              <Parameters>
                <Parameter Name="@SubscriptionId" Direction="In">
                  <TypeDescriptor Name="SubscriptionId" TypeName="System.String">
                    <Properties>
                      <Property Name="SubscriptionIdName" Type="System.String">SubscriptionId</Property>
                    </Properties>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="UnSubscribeCustomer" DefaultDisplayName="Customer
             Unsubscribe" Type="EventUnsubscriber" Default="true">
                  <AccessControlList>
                    <AccessControlEntry Principal="NT AuthorityAuthenticated Users">
                      <Right BdcRight="Edit" />
                      <Right BdcRight="Execute" />
                      <Right BdcRight="SetPermissions" />
                      <Right BdcRight="SelectableInClients" />
                    </AccessControlEntry>
                  </AccessControlList>
                </MethodInstance>
              </MethodInstances>
            </Method>

Now that those items are setup, you need to deploy your BCS model and set permissions. This is very common activity so I'll skip the details in this blog post, however I will say that it is annoying that the user that uploads the model is not automatically added (or have an option somewhere to add them on the import page) as a admin with permissions to the model and methods [:(]

Now that the model is deployed, the next step is to enable a feature that enables subscription support, which brings us back to the question brought up before…where does SharePoint store the subscription id of the external system? A list of course! To create this list, there are two features of which you can enable. One is called BCSEvents, the other is called ExternalSubscription. The funny thing about these two features and their relationship is that the BCSEvents feature is made up of a feature activation receiver. That receiver has only one goal: To activate the ExternalSubscription feature. In addition to this interesting design, you will find that the BCSEvents is a hidden feature whereas the ExternalSubscription feature is actually visible in the web features settings page. What does the ExternalSubscription feature do? It creates our list of course! This list is called "External Subscriptions Store". This is a hidden list and can be unhidden using PowerShell, but it exists in the "_private/ExtSubs" folder and has no views from which you can view the data, so again Windows PowerShell is the way to go if you want to see what lives in the list. Here is a screen shot of the columns of the list:

Next you need to create a subscription. This can be done explicitly or implicitly. The explicit way is to make a call to the entity's subscribe method as shown here (as previously pointed out above, the notification callback url is ignored in an OData Model):

function SubscribeEntity() {
    var notificationCallback = new SP.BusinessData.Runtime.NotificationCallback(context, "http://localhost:19739/northwind.svc");
    var url = web.get_url();
    notificationCallback.set_notificationContext(url);
    context.load(notificationCallback);
    var subscription = entity.subscribe(1, notificationCallback, "administrator@contoso.com", "SubscribeCustomer", lobSystemInstance);
    context.load(subscription);
    context.executeQueryAsync(OnSubscribeSuccess, failmethod);
}

//these are the helper methods and variables

var context;
var web;
var user;
var entity;
var lob;
var lobSystemInstance;
var lobSystemInstances;

// This code runs when the DOM is ready and creates a context object which is needed to use the SharePoint object model
$(document).ready(function () {
    context = SP.ClientContext.get_current();
    web = context.get_web();
    context.load(web);

entity = web.getAppBdcCatalog().getEntity("NorthwindModel", "Customers");
context.load(entity);

lob = entity.getLobSystem();
context.load(lob);

lobSystemInstances = lob.getLobSystemInstances();
context.load(lobSystemInstances);

context.executeQueryAsync(GetLobSubscribesystemInstance, failmethod);
});

// Initialize the LobSystemInstance.
function GetLobSubscribesystemInstance() {
    var $$enum_1_0 = lobSystemInstances.getEnumerator();
    while ($$enum_1_0.moveNext()) {
        var instance = $$enum_1_0.get_current();
        lobSystemInstance = instance;
        context.load(lobSystemInstance);
        break;
    }
    context.executeQueryAsync(SubscribeEntity, failmethod);
}

Subscriptions can be one of three types (you can learn more about event types here):

ItemAdded (1)
ItemUpdated (2)
ItemDeleted (3)

Note that there is no event type that supports ItemAdding, ItemUpdating or ItemDeleting. This means you cannot cancel the insertion, update or deletion in the external source, you can only expect to receive notification after the event has occurred.

The implicit way is to create an alert or to setup an event receiver. This means you should setup an external list pointing to your OData model. You can then use the ribbon to create an alert which will in turn execute the web method call to create the subscription. Note that you must setup your outgoing email settings on your farm, or the alert ribbon button will not display! If an error occurs when creating an event receiver, you will be passed the web method exception from your OData service. This can be very helpful for troubleshooting.

NOTE: When you create an external list, there are several items that seems to get cached in the external list's properties that will require you to delete the list and then re-create it. This means that as you are testing your solution, you should create a Windows PowerShell script that will remove your BDC model, re-deploy it, remove the external list and then add it back.

Once this has all been completed, you can now start telling SharePoint that things have changed. As much work as we have done to this point, it is really rather simple compared to the amount of work needed for this component of the eco-system. There are several approaches you could take to do this:

Triggers on the database to populate a table monitored by SQL Server to send events directly to SharePoint
Triggers on the database to populate a table monitored by a windows service
No triggers and just a simple row timestamp monitoring that checks for any insertsupdatesdeletes and sends the notification
Code that sends changes to an event queue like MSMQ or BizTalk that will then send it to SharePoint

Each of these have advantages and drawbacks in terms of time and complexity. No matter what, you need some component that will tell SharePoint that something has changed. In the code samples I provide, you have a simple console application that will allow you to send the notification to SharePoint for testing purposes.

So now that you have something that can send a message to SharePoint, what does that message look like? This process of communication is un-documented anywhere, until now, and is the real meat of this post! It turns out that there are two message parsers that come out of the box with SharePoint. These include an IdentityParser and an ODataEntryContentNotificationParser. The difference between the two is that one only tells SharePoint that a set of identities has changed and the other actually can pass the changed properties of the item to SharePoint. Both requires a completely different style of ATOM message to be sent.

In the case of the IdentityParser, it is looking for a message that looks like the code snippet below. This particular piece of XML must have a valid XPath to "/a:feed/a:entry/a:content/m:properties/b:BcsItemIdentity". If it does not, then any call to "retrieve the item" in your event receiver will fail. The message will be received and the event receiver will execute as long as you don't make calls to the various properties that will not be available without the id. You should also be aware that none of the other items that live outside of the XPath are ever looked at and can be anything you like as they are not validated or used:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed xml:base="http://services.odata.org/OData/OData.svc/"
xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
xmlns:b="http://schemas.microsoft.com/bcs/2012/"
xmlns="http://www.w3.org/2005/Atom">
<entry>
<title type="text">Customers</title>
<id>http://www.northwind.com/customers</id>
<author>
<name>External System</name>
</author>
<content type="application/xml">
<m:properties>
<b:BcsItemIdentity m:type="Edm.String"><CustomerID>ALFKI</CustomerID></b:BcsItemIdentity>
<d:Name>Customer</d:Name>
</m:properties>
</content>
</entry>
</feed>

In the case of the ODataEntryContentNotificationParser, you must pass an XML message that has a valid XPath to "/a:entry/a:link/m:inline/a:entry". The XML node in this XPath must itself be a valid ATOM message. Again, everything that is outside the XPath seems to be ignored and only the embedded ATOM Message is used:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<atom:entry xml:base="http://sphvm-92723:90/WcfDataService2.svc" xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:atom="http://www.w3.org/2005/Atom">
<atom:category term="NorthwindModel.EntitySubscribe" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>
<content type="application/xml">
     <m:properties>
      <d:SubscriptionId m:type="Edm.Int32">1</d:SubscriptionId>
      <d:EntityName>Customers</d:EntityName>
      <d:DeliveryURL>{11}</d:DeliveryURL>
      <d:EventType m:type="Edm.Int32">{12}</d:EventType>
      <d:UserId m:null="true" />
      <d:SubscribeTime m:type="Edm.Binary">AAAAAAAABE4=</d:SubscribeTime>
      <d:SelectColumns>*</d:SelectColumns>
    </m:properties>
</content>
<id>OuterId</id>
<atom:id>http://sphvm-92723:90/WcfDataService2.svc/EntitySubscribes(1)</atom:id>
<atom:link href="EntitySubscribe(1)" rel="self" title="EntitySubscribe"/>
<atom:link href="Customers(2147483647)" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/customers" type="application/atom+xml;type=entry">
    <m:inline>
      <entry xml:base="http://sphvm-92723:90/WcfDataService2.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
        <id>http://sphvm-92723:90/WcfDataService2.svc/Customers('57849')</id>
        <title type="text" />
        <updated>2012-04-30T11:50:20Z</updated>
        <author>
        <name />
        </author>
        <link rel="edit" title="Customer" href="Customers('57849')" />
        <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Orders" type="application/atom+xml;type=feed" title="Orders" href="Customers('57849')/Orders" />
        <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/CustomerDemographics" type="application/atom+xml;type=feed" title="CustomerDemographics" href="Customers('57849')/CustomerDemographics" />
        <category term="NorthwindModel.Customer" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
        <content type="application/xml">
          <m:properties>
            <d:CustomerID>{0}</d:CustomerID>
            <d:CompanyName>{1}</d:CompanyName>
            <d:ContactName>{2}</d:ContactName>
            <d:ContactTitle>{3}</d:ContactTitle>
            <d:Address>{4}</d:Address>
            <d:City>{5}</d:City>
            <d:Region>{6}</d:Region>
            <d:PostalCode>{7}</d:PostalCode>
            <d:Country>{8}</d:Country>
            <d:Phone>{9}</d:Phone>
            <d:Fax>{10}</d:Fax>
          </m:properties>
        </content>
      </entry>
    </m:inline>
</atom:link>
<title>New Customer entry is added</title>
<updated>2011-07-12T09:21:53Z</updated>
</atom:entry>

In addition to the two out of the box parsers, there is a setting that specifies "Custom". By implementing our own NotificationParser, we can format the message in a much more simple and efficient way such as JSON. The main method to implement is the ChangedEntityInstance method. As part of this parser, you will be passed the message byte array in the initialization and it would be your responsibility to parse the message and pass back the entity instance.

public abstract class NotificationParser
{
    // Methods
    protected NotificationParser()
    {
    }

    public void Initialize(NameValueCollection headers, byte[] message, IEntity entity, ILobSystemInstance lobSystemInstance)
    {
        if (message == null)
        {
            message = new byte[0];
        }
        if (entity == null)
        {
            throw new ArgumentNullException("entity");
        }
        if (lobSystemInstance == null)
        {
            throw new ArgumentNullException("lobSystemInstance");
        }
        this.NotificationHeaders = headers;
        this.NotificationMessage = message;
        this.Entity = entity;
        this.LobSystemInstance = lobSystemInstance;
    }

    // Properties
    public virtual IEntityInstance ChangedEntityInstance
    {
        get
        {
            Identity changedItemIdentity = this.ChangedItemIdentity;
            return this.Entity.FindSpecific(changedItemIdentity, this.LobSystemInstance);
        }
    }

    public abstract Identity ChangedItemIdentity { get; }
    protected IEntity Entity { get; private set; }
    protected ILobSystemInstance LobSystemInstance { get; private set; }
    public NameValueCollection NotificationHeaders { get; private set; }
    public byte[] NotificationMessage { get; private set; }
}

Summary:

Now that you have all the pieces, you can download the code I have placed on the code.msdn.microsoft.com site here. This code has a BCS OData model fully working with the subscriber methods. As code generation techniques have become more common place, OData layers generated via Visual Studio are more common as well. It will be well worth implementing these new BDC method stereotypes in your OData model and in your OData services to provide the ability to be notified when data changes in your remote systems!

Tag: odata

BCS, OData and Subscriptions – How to get it working!