Sharepoint 2013 App with an External List sourced from WebAPI and OData

Sharepoint 2013 Apps

Sharepoint has gotten a bad repution over the years. And it is well deserved. I have always hated working with it. But Sharepoint 2013 is differnt. The new App model is developer friendly and is easy to pick up. To completely over simplify a Sharepoint 2013 App – it allows you to write a modern web app using jquery, or knockout, or angular or any other client side javascript framework and plug it into Sharepoint 2013 or Office 2013. So all the goodness of Sharepoint 2013 + all the goodness of the modern web.

Sharepoint 2013 also offers External Lists. An External List is a Sharepoint 2013 abstraction over an external data source. In this case that external data source is a OData service that will be surfacing data in our SQL Azure database.

These two Sharepoint 2013 features provide an excellent opertunity to integrate your web application’s data into your Sharepoint 2013 / Office 365 sites.  Sharepoint 2013 becomes a hub for your back office to integrate data from multiple cloud platforms into a single portal. And OData is the pipe used to connect the data sinks.

Creating an OData service in 5 easy steps

1. In Visual Studion 2012 create a new ASP.NET MVC 4 project. Update all of the NuGet packages and add the Microsoft.AspNet.WebApi.OData package.
2. Add a new Empty WebApi Controller.
3. Open the controller and change the class to inherit from an EntitySetController instead of an ApiController. The EntitySetController takes two arguments the type of the Entity being served and the type of the primary key.
4. Add methods for retrieving a list of items.

(See http://msdn.microsoft.com/en-us/magazine/dn201742.aspx by Julie Lerman for more information. The following code on setting up an Odata service on WebApi was influenced by her article. )

public class AirlinesController : EntitySetController<Airline, int>
{
 private AirlineContext db = new AirlineContext();
 List<Airline> airlines = new List<Airline>();
 public AirlinesController() : base()
 {
     airlines.Add(new Airline() { ID = 1, Description = "southwest flight 1", Name = "flight 1" });
     airlines.Add(new Airline() { ID = 2, Description = "southwest flight 2", Name = "flight 2" });
 }
 public override IQueryable<Airline> Get()
 {
     return airlines.AsQueryable();
 }
}

1. Add routes to the WebApiConfig.cs

public static void Register(HttpConfiguration config)
{
  ODataModelBuilder modelBuilder = new ODataConventionModelBuilder();
  modelBuilder.EntitySet<Airline>("Airlines");
  Microsoft.Data.Edm.IEdmModel model = modelBuilder.GetEdmModel();
  config.Routes.MapODataRoute("ODataRoute", "odata", model);
  config.Routes.MapHttpRoute( name: "DefaultApi", routeTemplate: "api/{controller}/{id}", defaults: new { id = RouteParameter.Optional } );
}

You will need to deploy this to someplace that Sharepoint server will be able to access. Azure is a great cloud platform and works well. Another option is to setup a service bus relay to your local machine. Either way Sharepoint needs to be able to hit your newly created OData Service.

Sharepoint 2013 App

So now we have a OData service that will allow Sharepoint to integrate with our awesome airline web application. (The remainder of the application assumes you have a Office 365 developer account.  If you have MSDN then you got a free Office 365 Dev account or you can sign up for one at http://msdn.microsoft.com/en-us/library/fp179924.aspx)

1. Open Visual Studio 2012 and create a new Sharepoint App (selected the Sharepoint Hosted option)
2. Right click on the project and create a new content type for an external data source aka External Content Type. Point the external content type to your deployed Odata service.
3. Now create a new page in the Pages folder
4. Add the following code:

<script
    type="text/javascript"
    src="//ajax.aspnetcdn.com/ajax/jQuery/jquery-1.7.2.min.js">
</script>
<script
    type="text/javascript"
    src="/_layouts/15/sp.runtime.js">
</script>
<script
    type="text/javascript"
    src="/_layouts/15/sp.js">
</script>
<script>
    $(document).ready(function () {
        // Get the URI decoded URLs.
        var hostweburl = decodeURIComponent(getQueryStringParameter("SPHostUrl"));

        // Get the URI decoded URLs.
        var appweburl = decodeURIComponent(getQueryStringParameter("SPAppWebUrl"));

        jQuery.ajax({
            url: appweburl + "/_api/web/lists/getbytitle('Airlines')/items",
            type: "GET",
            headers: { "Accept": "application/json;odata=verbose" },
            success: function (data, textStatus, xhr) {
                var dataResults = data.d.results;
                var data = "";
                data += dataResults[0].Name;
                $("body").append("<p> the data is " + data + "</p>");
            },
            error: function (xhr, textStatus, errorThrown) {
                alert("error:" + JSON.stringify(xhr));
            }
        });

        // Function to retrieve a query string value.
        // For production purposes you may want to use
        // a library to handle the query string.
        function getQueryStringParameter(paramToRetrieve) {
            var params =
                document.URL.split("?")[1].split("&amp;");
            var strParams = "";
            for (var i = 0; i < params.length; i = i + 1) {
                var singleParam = params[i].split("=");
                if (singleParam[0] == paramToRetrieve)
                    return singleParam[1];
            }
        }
    });
</script>

Now start debugging the solution.  You should get prompted for your Sharepoint user name and password.   Visual Studio will automatically deploy the application to Sharepoint.  There that is it.  Sharepoint 2013 + OData.