I’ve recently been looking into the different ways in which you can integrate SharePoint Online (SPO) with Windows Azure—especially around integrating client-side code and services deployed in Azure as this tends to be a more prominent pattern. If you’ve been following my recent blog-posts, then you’ve certainly seen this.

Of late, though, I’ve been spending some time with JSON and JSONP (JSONP is JSON with Padding and supports cross-domain data loading/scripting by dynamically injecting a callback script through the use of ?callback=? at the end of the service URI reference in the client code). This is because I talk a lot about WCF services (and often refer to cross domain policy files if you use Silverlight), but in reality if we’re to think about the Web in the broader sense, then we need to ensure we’re also talking about JSON. Hence, this blog discusses how you can create a REST service and then expose that service with JSON formatting to a Web part in SPO that accepts the cross-domain call using JSONP. Take the following diagram, for example, which shows a SPO (or what could be a SP on-premises site) that consumes a REST service that consumes data—both of which are deployed to Windows Azure. While this blog post only discusses the SPO client app, you could certainly use the REST service in other clients as well.


To successfully get the sample that is discussed above working, at a high level you’ll need to do the following:

  1. Create a SQL Database with some sample data in it (1 table for training code would work).
  2. Create a REST wrapper service around that SQL Database.
  3. Ensure the REST wrapper service is “JSONified.”
  4. Ensure you’ve added the proper JSON formatter class to the REST service.
  5. Create a web part that leverages jQuery/JSONP to parse the JSON being returned from the service call.

There’s a lot of guidance on the above, so I’ll call out the places where we tweaked the code with references back to some original walkthroughs/docs.

The Data

In this blog post, I’ll assume that you’ve got some data in a SQL Database already created. If you’ve not done this before, you can go here for overview information and here for specific Create SQL script direction. In this example, I’ve got a small table of Speaker data in it. The below screenshot shows the entity data model used in the example.


Once you’ve created your database, you can now create a service that wraps around that SQL Azure Database.

The Service

You have a couple of ways to build out a REST service. For those of you using more mainstream techniques, you can create an ASP.NET project and add a WCF Data Service item. You then add an entity data model (similar to the above schema), and by configuring the reference in the core service class you create a REST service for your SQL Database data. More recent methods use the Web API, which is a great way to leverage the MVC model/templates to build out REST services quickly and easily. If you’re new to the ASP.NET Web API, check this out for more details. (For this sample, I used VS 11 Beta with the MVC 4 templates and Azure 1.7 SDK/Tools installed. I then created a new Cloud project, selected the MVC 4 template and then selected the Web API method. After that, I added a controller for the Speaker entity data model. If you’re new to this and Web API, there is a great TechEd session here.) Bolded are the core verbs/URI paths; both a GET that either return all Speakers or a specific speaker.

namespace MyFirstWebAPI.Controllers


public class SpeakerController : ApiController


private TechEdEntities db = new TechEdEntities();

// GET api/Speaker

public IEnumerable<Speaker> GetSpeakers()


return db.Speakers.AsEnumerable();


// GET api/Speaker/5

public Speaker GetSpeaker(int id)


Speaker speaker = db.Speakers.Single(s => s.ID == id);

if (speaker == null)


throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));


return speaker;



Regardless of whether you use WCF Data Services or Web API, you need to ensure you format the return data from your REST service you need to handle JSONP. A good primer on how to do this can be found in this excellent blog post. Directly using Alex’s model, we leveraged this pattern and then simply created a client application that ingested the service with this custom JSONP formatter. The core JsonpMediaTypeFormatter class is below.

using System.Net.Http.Formatting;

using System.Net.Http.Headers;

using System.Threading.Tasks;

using System;

using System.IO;

using System.Web;

using System.Net;

public class JsonpMediaTypeFormatter : JsonMediaTypeFormatter


private string callbackQueryParameter;

public JsonpMediaTypeFormatter()



        SupportedMediaTypes.Add(new MediaTypeHeaderValue("text/javascript"));

        MediaTypeMappings.Add(new UriPathExtensionMapping("jsonp", DefaultMediaType));


public string CallbackQueryParameter


get { return callbackQueryParameter ?? "callback"; }

set { callbackQueryParameter = value; }


public override Task WriteToStreamAsync(Type type, object value, Stream stream, HttpContentHeaders contentHeaders, TransportContext transportContext)


string callback;

if (IsJsonpRequest(out callback))


return Task.Factory.StartNew(() =>


var writer = new StreamWriter(stream);

                writer.Write(callback + "(");


base.WriteToStreamAsync(type, value, stream, contentHeaders, transportContext).Wait();







return base.WriteToStreamAsync(type, value, stream, contentHeaders, transportContext);



private bool IsJsonpRequest(out string callback)


        callback = null;

if (HttpContext.Current.Request.HttpMethod != "GET")

return false;

        callback = HttpContext.Current.Request.QueryString[CallbackQueryParameter];

return !string.IsNullOrEmpty(callback);



The Web API was wrapped in a Windows Azure cloud project, which configures the service to be deployed to Azure. Note that you can start with a Windows Azure Cloud project as I noted above or you can add it later on depending on what project template you’re using. (I would recommend starting with it if you’re planning on deploying the app to the cloud.) When you’ve deployed the REST service to Windows Azure, you can then use the REST URI to access data. For example, by inputting a URI similar to the below


you’d return a single JSON object that looks like the following (this is returned by adding a “1” at the end of the REST URI):


"$id":"1","ID":1,"Name":"Steve Fox","Title":"Director","Presentation":"Overview of Azure","Skill_Level":200,"Abstract":"Overview of Azure","Rating":3,"Comments":"Awesome Demos!","EntityKey":{"$id":"2","EntitySetName":"Speakers","EntityContainerName":"TechEdEntities","EntityKeyValues":[{"Key":"ID","Type":"System.Int32","Value":"1"}]}


Using a combination of semantic HTML and jQuery, you’re able to parse the JSON/JSONP and then make some use of it in the web part.

The Client

So, most importantly the question is does this work within SPO—which has HTTPS enabled and securing the SPO sites. Here you can create a sandboxed visual web part and format the web part control with the following markup. You’ll note that I’m referencing the jQuery libraries directly from within my SPO site collection. The heavy lifting here is done by the $(document).ready() function, which uses the getJSON method which uses the core ajax function to make the call to the REST endpoint and then handle the parsing of the JSON object. Here, I’ve used the each and append methods to add all returned elements by default to a <list> object.

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>
<%@ Assembly Name="Microsoft.Web.CommandUI, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Import Namespace="Microsoft.SharePoint" %>
<%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="SalesDataWebPart.ascx.cs" Inherits="SPSalesDemo.SalesDataWebPart.SalesDataWebPart" %>

   <script src=https://myo365site.sharepoint.com/sites/sofa/grades/JavaScript/jsrender.js type="text/javascript"></script>
    <script type="text/javascript" src=
    <script type="text/javascript">

        $(document).ready(function () {

            var spList = $("#speakerList");
            var i = 0;
            var speaker = new Object;

            $.getJSON(http://myazureservicenamespace.cloudapp.net/api/Speaker/1/?callback=?, function (data) {
                $.each(data, function (index, value) {
                    spList.append($("<li>" + index.valueOf(i) + ": " + value.valueOf(i) + "</li>"));


        Speaker Information (Cross-Domain)
    <br />
    <li id="speakerList"></li>

The markup is pretty bare-bones, and you could use some formatting or additional logic to filter out unwanted properties from the JSON object. You can see below what the JSON returns by viewing the Locals in IE’s F12 debugging experience. You could also use some jQuery data templating called data linking—for more information on this go here.


While not exactly exciting, the above JSON object and client-side code parses and renders the data in the following way. Here you can see that I’ve just accepted everything coming back off the wire, but you’d want to either serialize into a local object or use another method to filter out the pieces of data that you wouldn’t want to expose (e.g. $id and EntityKey).


So, we’ve proved it works. Does that mean you should use JSONP? While it is is powerful and does provide you with the ability to use a different method than using Silverlight or jQuery for WCF service calls, it also comes with a risk: because JSONP injects script at runtime within your page, you should have a high degree of trust in the service code. Also, there are limitation of JSONP such as poorer error handling. You’d also want to run some tests around performance to ensure you get the best possible user experience when comparing cloud-based services.


In summary, this blog post discussed the use of JSONP/JSON to manage cross-domain service calls. It also discussed using the MVC 4 Web API to build and deploy your REST services. You could think of this as an additional pattern for integrating services and SPO using Windows Azure. Others I’ve discussed include Silverlight or JavaScript and leveraging the SP Client Object Model to integrate with SharePoint.

One thought to throw out there is where cross-origin resource sharing (CORS) fits into the above. There is a great blog post here that discusses how adding a wildcard Header amendment such as the below could enable cross-domain scripting as well.

msg.Headers.Add( “Access-Control-Allow-Origin”, “*” );

This comes at a lower custom code price (i.e. no need for custom JSON formatters) because it is supported at the browser level. The problem is that not every browser supports CORS in the same way.

More on this in the future.

Happy (forthcoming) 4th!