What’s our Theme
I have three sessions at SharePoint Conference 2014 and the overarching theme of my sessions and perhaps for the Conference will be centered around Cloud, Apps, and Hybrid. Please visit this blog to see more details about my sessions, I am using this blog post to refine my session script for both the above two, and a lead in to my third #SPC319
This is LONG LONG Post, and I know it, but to maintain the continuity of the dialog and keeping it themed to my SPC Sessions, I needed to do it this way. You may not need all of the information in here depending on your slant ITPro v/s Dev vs Architect. but it is all here if/when you need it.
One Story many Outcomes
So, no matter what my session will be covering, you will notice that I am first starting out by standardizing my data into OData format. I encourage you all to do the same, for a variety of reasons that I will get into more at the Conference, but for this blog post and the sessions, the immediate take away is
- OData is supported in a variety of popular Coding Languages and Platform
- OData is represented in JSON format which is extremely light in it’s Payload
- If you are thinking Multi Form Factor, Multi Device, think OData
- If you want to control your Data over standard HTTP on the fly for full CRUD, think OData
- If you want to Expose/Consume External Data in SharePoint/MS Office you will find it has a very simple OData Connection Wizard ready to use
Even your ITPro needed a Developer to Create the OData URI Feed
Yes, yes, we can have a conversation about External Data, OData without cracking open Visual Studio, but whether it is VS or PHP, or whatever language you want to use, someone had to create the OData URI for you to use, the first of part of this blog post will detail that portion of the task. This post is broken up into sections targeting ITPro activities and Developer activities but like i said, its all connected 🙂 feel free to jump around.
Although there are many ways to create a OData Data Source, MVC, ASP.NET, etc. this post will focus on using Web API 2.0. however since we want this to be Exposed and Consumed by any and everyone we give the URI to, we will host it in the Cloud, in our case we will use Azure. For that to happen, I am starting off with a Windows Azure Cloud Service Template in Visual Studio 2013
We will use the ASP.NET Web Role when the Wizard starts up, Select the Role and click the Arrow to move it to the Right
Id also encourage you to Rename your Project as I have done below
The next set of wizards you will select the WEB API notice how there is NO SECURITY by default, we will change that, because we may not necessarily want to have “all” your EndPoints exposed anonymously without any credentials check.
In my example here I also want to show how you can lock down your Access to your OData feeds so we will turn on Individual Authentication
We will see how to use this later on when we Decorate the Controller class that we want to ensure is protected by Authentication.
Now that we have selected Individual Auth, you will see that it is also reflected in the Wizard Dialog Area. Once you completed the wizard you get a Solution with two project, one a Standard Web API Project the other a Cloud Project
The next thing we need to do is make a connection to Database, our Database is in the Cloud, SQL Azure to be more precise. So for this to happen we will add a ADO.NET Data Entity Model
We will name it appropriately and follow the wizard through by selecting a Model based on a Database and identifying the Azure SQL Database
Once you have completed that, test your connection
We can and will select up to a multiple Data Source entities by selecting as many as identified as seen below
After that is done, you will get an EDMX file with all the relationships
At this time I encourage you to save your project and build it so that the MVC Model can see the entities that are subsequently created
Next we will add Controller that links to the Data Context provided when you created your Entity Data Model. Lets add the controller now
When selecting your Controller choose the Web API 2 OData
We will create multiple Controllers for each Entity we want to expose, for this name your Controller after your Model, Select your Model that the EF created, and select your Data Context as well.
Do this for each of the Entity you want to create, you can see below I created a
Controller. See below image
Actually Coding – And its Not A Lot
So as you see, up to this point, we have not written a stitch of a code, but now we have to. The first thing we will do is modify the WebAPIConfig.cs file in the App_Start folder. In this folder we will set the Routes to the OData sources, we will define the Authentication mechanism and name the Entities. First lets take a look at the file OOB.
First thing we will comment out the config.SuppressDefaultHostAuthentication so I can manage it myself and take away the OOB feature
Next because I am working with OData I will set up the Formatting to receive OData as a PayLoad
Finally, If you are not going to be dealing with API routes, you can comment it out as I did below
Next we will Add Routes for the OData to support the Entities I plan to take work with.
In the end we have a WebAPIConfig .cs file that we see below.
Lets investigate two Controllers, in the Employee Controller we will add the [Authorize] modifier to decorate the Class and this will ensure that you cant call the OData EndPoint URI entity for Employee without passing in some kind of credentials. We will also show the Customer Controller as well that is default OOB and can be called directly.
Above is the Employee Controller notice the [Authorize] Modifier?
Below is the Customer Controller
Test your Work – Locally
Once you have done that we can test this out actually. Locally of course, we have not pushed this to the Cloud yet.
make the call now
As you can see we can call up the Customer End Point above, lets try this with the Employee EndPoint
now make the call
What you have here is a 401 Unauthorized Error Code coming back from the HTTP Headers, this is because as we did above, we decorated the Employee Controller class with [Authorize] and this gives us that added protection. What’s important here to note is in the Cookie/Login section the Authenticate: Bearer token is the culprit here, you will see that it is missing.
To get to the data, we need to create a User and Grant them Permission to the Data, lets see how that is done. First you need to call the Register EndPoint
Notice that we add the Content-Type of “Application/JSON” to the Request Header and we are changing the HTTP verb to a POST rather than the GET. In the Request Body, I am sending over the UserName and Password, then Execute on it
Once you execute on that, if it is successful, you will get a HTTP 200 Response back. Next we need to get the Token, see below for the call below and notice we are sending a different Request Header Content-Type and also Request Body
Provided the call is successful, you should also get a HTTP 200 Response with the Access Token Payload in the JSON Response. Pay Note to that, we will need it soon
NOW, we can make that call to the Employee Entity and now provide the proper Token
Execute on that and provided it is successful, you should have a good response back as we did in the Customer Entity
Publish your Work to Azure as a Cloud Service
Now what we need to do is publish this work so that it is accessible to everyone, how do we do that, we make it available on Azure. Lets see how to do that.
First “Right-Click” on the Project and select Publish, you will get a Wizard like this
Follow it through
Once you have set the configuration elements, click Publish and watch the process go
Once it is complete you should see all Greens 🙂
Lets check to see if it is available in Azure now
And we know it is there when we get results like in the above. Next we will use this OData URI in a SharePoint BCS External Content Type App in Office 365 although we could use this anywhere we please since it is hosted in the Internet(z) 🙂
Use this OData URI in a SharePoint BCS App
Now that we have this OData URI, we can use it anywhere. As mentioned earlier, you can use this in a few Microsoft Office Products like Excel and Word, you can use it as we will here in SharePoint as well. In our specific example we will create a SharePoint App and create a SharePoint External Content Type and External List.
Create your Visual Studio Project
First we need a App Model Project Template
Validate your Office 365 Environment, this is also a SharePoint Hosted App
Once you are complete and click Finish, you will get a project like below.
The next pieces are so simple, as I keep telling folks, there is NO code involve for the simplest ECT. Right click on your project and ADD “Content Types for an External Data Source”
This will bring up a OData Source Wizard, Go ahead and add the Azure Cloud Services URI and give it a name
After you click Next you will get a list of Entities that you can select that are exposed. For this example we will just choose Customer, NOTICE that you “by Default” get a check box to create the associated List Instance.
What you see below is the result from your selection and you now have a External Content Type “Customers.ect” as well as a “Customers” Table inside a NorthWindCloudServiceOData External Content Type (ECT)
Deploying this project will get us the External List served up in Office 365 as you see below.
But it doesn’t have to end there. You can Export the “ECT” as an XML document and give this to anyone to Upload to any
- On Premises SharePoint FARM environment – Add it through Central Admin under the BCS Service Application
- Office 365 Tennant – Add it through the Admin Portal
In this example below we are adding the ECT to the Admin Portal in Office 365. Begin by clicking on “Manage BDC Models and External Content Type”
Locate the ECT File you saved.
Upload the item, now you will see (first on the list) the ECT from what we have created in Visual Studio notice the Name is the same.
Now you can Add a External List from it as you add any other App list.
Below you get a representation of the List as we did when we deployed it from the Visual Studio Project.
Come see this live and in person at SharePoint Conference, I will go into more details, talk about security in greater depth, also include a OData URI that is not from a traditional RDMS, it is actually from a REST API that I don’t own or control, well not as of yet 🙂
See you there.