It was some time since I did a real blog post and I have been fiddling with a specific topic, which I’m going to write about, for quite some time now. I’ve been working an Office 365 Intranet and been doing two conferences lately where I’ve demonstrated Office 365 and Windows Azure integration. One of the challenges (and boy, there are many) of Office 365 and SharePoint Online are access to External Data or services. In a few blog posts I will describe how you can work around these issues using some very simple techniques. All that is to it is that you have to “think outside the box” and not always go down the traditional SharePoint way of doing things.

So let’s get started! How do I in my SharePoint Online solution access remote/LOB data in a SQL Server database, web service or what not? The first and obvious candidate most think of is Business Connectivity Services - didn’t they announce that in Anaheim!? Yea, it can be done and it works - but not as you (and I previously) expected. Steve Fox (MSFT) did show it at the SharePoint Conference 2011 and also wrote a blog post about it. The caveat is that you have to go through the Client Object Model to access the data in the external list, not using the Sandboxed server side API - that dog won’t hunt.

I’m going to do basically the same thing as Steve did but using a slightly different technique - which I find more easy and more straightforward, considering I’m not using the asynchronous Client Object Model but instead uses standard jQuery, jQuery templates JsRender, JSONP and WCF.

This is how we’ll build this little sample. First of all we have a database stored in SQL Azure. This database is surfaced through a WCF endpoint in a Windows Azure Web Role. This WCF service will be consumed by a JavaScript using jQuery and JSONP as transport in a Web Part (I’ll use a Sandboxed Visual Web Part for this) and rendered using JsRender (not jQuery templates which I recently shown in a few sessions).

The data flow

The secret sauce - JSON-P

First of all before digging into how this is actually implemented we need to sort out how we’re actually going to transport data from the WCF endpoint to SharePoint Online.

The best and easiest way is to use JSON notation to transport the data; jQuery has great support for that and it’s very easy to program using JSON structures in JavaScript. The only problem is that SharePoint Online and the WCF service is going to be hosted on different domains - and therefore it will be a cross-domain scripting issue. To overcome this issue JSONP (JSON with Padding) can be used. JSONP is a very clever method. Instead of returning a string from the remote domain and then being parsed by the caller, a JavaScript method is returned, which returns the JSON structure when evaluated, and this returned response is dynamically appended to the calling document as a script tag. Smart huh? Only problem is that we need to fiddle somewhat with our WCF endpoints, and that’s what we’ll discuss next…

Setting up the WCF endpoint

In this sample we need a database with one or more tables and then generate an ADO.NET Entity Data Model from that. That data model is then exposed through a WCF Data Service. WCF Data Services uses the OData protocol and supports JSON format but not JSONP. Fortunately I was not the first one lacking this feature and there is a simple extension you can download to accomplish this. This extension is an attribute that you add to your Data Service class and makes the service support the $format=json and $callback=? query string parameters.

Once you have downloaded and included the class into your project you decorate your DataService class with the JSONPSupportBehavior attribute and it should look something like this:

[JSONPSupportBehavior()]
[System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]
public class Users : DataService {
    public static void InitializeService(DataServiceConfiguration config) {
        config.SetEntitySetAccessRule("Users", EntitySetRights.AllRead);
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
    }
}

Now we’re all set to consume this from SharePoint Online.

Consuming the JSONP enabled WCF endpoint from a Web Part

Create a new Empty Sandboxed SharePoint project and then add a Sandboxed Visual Web Part (make sure that you have the Visual Studio SharePoint Powertools installed to get this Sandbox enabled Visual Web Part). We’re only going to use HTML and JavaScript for this one, so you could do the same thing using a Content Editor Web Part, SharePoint Designer or whatever you prefer.

Note: I’m using the new JsRender technique here, instead of the just recently abandoned jQuery Templates. The JsRender is not even in beta yet so the syntax might/will be changed over time. Read more about the change here.

First of all in our user control we need to import the jQuery 1.7 file. Instead of uploading it to our SharePoint site we’ll use a CDN for this (which will boost your overall performance and save you one file to maintain).

<script type="text/javascript" src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.7.min.js"></script>/<

The JsRender.js file is not yet available on any CDN’s so we have to add that JavaScript file into a new Empty Module SPI. You can deploy it to any folder or library, in this case I’m deploying it to the SiteAssets library of the site. Since we’re talking about SharePoint Online here and we cannot guarantee that the file will be in a specific site this JavaScript file must be included dynamically or by using server side code to get the correct Web Url. We’re doing the former one, and you will see the code in just a bit.

The next thing is to define some markup where we would like to show our data from the WCF service. We’ll define a table with an id equal to “list” and add a nice default row, which just says loading (makes it a bit more nice to the user).

<h1>Users</h1>
<table>
    <tbody id='list'>
        <tr><td><img src="/_layouts/images/loadingcirclests16.gif" />Loading data.../</td></tr>
    </tbody>
</table>

After that we need to define the template that JsRender will use when applying the data to the table. This is done in a script tag using the type=“text/x-jquery-tmpl” like this:

<script id="UsersTemplate" type="text/x-jquery-tmpl">
    <tr>             
        <td><b>{{=Name}}</b></td>              
        <td>{{=Company}}</td>         
    </tr>     
</script>

The final thing is to add the script that will load the data from the WCF service and then when it retrieves the response it will use the JsRender template to populate the table.

<script type="text/javascript">
    SP.SOD.executeOrDelayUntilScriptLoaded(doTheRemoteStuff, 'SP.js');
    var rootWeb;

    function doTheRemoteStuff() {
        var clientContext = new SP.ClientContext();
        var siteColl = clientContext.get_site();
        rootWeb = siteColl.get_rootWeb();
        clientContext.load(rootWeb);
        clientContext.executeQueryAsync(succeeded, failed);
    }

    function succeeded() {
        var elm = document.createElement('script');
        elm.type = 'text/javascript';
        elm.src = rootWeb.get_serverRelativeUrl() + '/SiteAssets/JsRender.js';
        document.getElementsByTagName('head')[0].appendChild(elm);

        $.getJSON("http://mycloudapp.cloudapp.net/Users.svc/Users?$filter=email ne ''&$format=json&$callback=?",
            function (data) {
                $("#list").empty();
                $("#list").html($("#UsersTemplate").render(data.d));
            });    
    }
    function failed() {
        SP.UI.Notify.addNotification('Something failed...');
    }
</script>

The script starts with using the Script-On-Demand features of SharePoint 2010 to delay execute the doTheRemoteStuff() function. Once the SP.js file is loaded the function is executed. The function will use Client Object Model to retrieve the Url to the root web, to ensure that this Web Part will work on sub webs as well. This is needed so that we dynamically can add the JsRender.js file that was added to the SiteAssets library in the root web.

Once we have the root web loaded the succeded() method is going to execute. This is where we first dynamically insert the JsRender.js file into the head element, referencing our own JsRender.js file. Once that is done we will use the jQuery getJSON method to get all users from the Data Service (in this case all users that has an e-mail address). To make the response a JSONP response it is important to add $format=json&callback=? to the query string in the getJSON method.

When the response is returned the table is cleared and the rendered data is added as inner HTML of the table. The jQuery extension method $(template).render(data) is the JsRender method that generates the HTML from the template and the JSON strucutre.

Once this project is deployed and activated in the Sandbox and the Web Part is added to a page it will first show you the loading message and then after just a second a nicely table with the data from your SQL Azure backend.

A SharePoint Online Web Part displaying data from a SQL Server Azure database

Summary

This post intends to demonstrate how to solve a very common problem in SharePoint Online - fetching data from LOB systems. I used SQL Azure and a Windows Azure Web Role hosted WCF service, but it can be any kind of JSONP supported WCF service. The holy grail in this case is to JSONP enable the WCF service.

Watch this space for a continuation of similar posts…