Game Plan for SharePoint Conference Session 330 and 313 1


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

image

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

image

Id also encourage you to Rename your Project as I have done below

image

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.

image

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

image

We will see how to use this later on when we Decorate the Controller class that we want to ensure is protected by Authentication.

 

image

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

image

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

image

We will name it appropriately and follow the wizard through by selecting a Model based on a Database and identifying the Azure SQL Database

image

next

image

Once you have completed that, test your connection

image

We can and will select up to a multiple Data Source entities by selecting as many as identified as seen below

image

After that is done, you will get an EDMX file with all the relationships

image

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

image

Next we will add  Controller that links to the Data Context provided when you created your Entity Data Model. Lets add the controller now

 

image

When selecting your Controller choose the Web API 2 OData

image

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.

 

image

Do this for each of the Entity you want to create, you can see below I created a

  • Customer
  • Employee
  • Product

Controller.  See below image

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.

Code Snippet
  1. namespace NorthWindAzureOdata
  2. {
  3.     public static class WebApiConfig
  4.     {
  5.         public static void Register(HttpConfiguration config)
  6.         {
  7.             // Web API configuration and services
  8.             // Configure Web API to use only bearer token authentication.
  9.             config.SuppressDefaultHostAuthentication();
  10.             config.Filters.Add(new HostAuthenticationFilter(OAuthDefaults.AuthenticationType));
  11.  
  12.             // Web API routes
  13.             config.MapHttpAttributeRoutes();
  14.  
  15.             config.Routes.MapHttpRoute(
  16.                 name: "DefaultApi",
  17.                 routeTemplate: "api/{controller}/{id}",
  18.                 defaults: new { id = RouteParameter.Optional }
  19.             );
  20.         }
  21.     }
  22. }

First thing we will comment out the config.SuppressDefaultHostAuthentication so I can manage it myself and take away the OOB feature

Code Snippet
  1.  
  2. //Fabian Williams Comment – We need to Comment Out the line below to turn off thw API Authentication
  3. //config.SuppressDefaultHostAuthentication();
  4. //Fabian Williams – End Comment

  Next because I am working with OData I will set up the Formatting to receive OData as a PayLoad

Code Snippet
  1. //Fabian Williams Comment
  2. // Use camel case for JSON data.
  3. config.Formatters.JsonFormatter.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
  4. //Fabian Williams – End Comment

Finally, If you are not going to be dealing with API routes, you can comment it out as I did below

Code Snippet
  1. /*
  2. //Fabian Williams — Comment out the API Route
  3. config.Routes.MapHttpRoute(
  4.     name: "DefaultApi",
  5.     routeTemplate: "api/{controller}/{id}",
  6.     defaults: new { id = RouteParameter.Optional }
  7. );
  8. //Fabian Williams – End Comment
  9. */

Next we will Add Routes for the OData to support the Entities I plan to take work with.

Code Snippet
  1.  
  2. //OData Routes
  3. ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
  4. builder.DataServiceVersion = new Version("3.0");
  5. builder.MaxDataServiceVersion = new Version("3.0");
  6. builder.EntitySet<Customer>("Customer");
  7. builder.EntitySet<Employee>("Employee");
  8. builder.EntitySet<Order>("Order");
  9. builder.EntitySet<Order_Detail>("Order_Detail");
  10. builder.EntitySet<Product>("Product");
  11. config.Routes.MapODataRoute("odata", "odata", builder.GetEdmModel());

In the end we have a WebAPIConfig .cs file that we see below.

Code Snippet
  1. public static void Register(HttpConfiguration config)
  2. {
  3.     // Web API configuration and services
  4.     // Configure Web API to use only bearer token authentication.
  5.  
  6.     //Fabian Williams Comment – We need to Comment Out the line below to turn off thw API Authentication
  7.     //config.SuppressDefaultHostAuthentication();
  8.     //Fabian Williams – End Comment
  9.  
  10.     config.Filters.Add(new HostAuthenticationFilter(OAuthDefaults.AuthenticationType));
  11.     //Fabian Williams Comment
  12.     // Use camel case for JSON data.
  13.     config.Formatters.JsonFormatter.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
  14.     //Fabian Williams – End Comment
  15.     // Web API routes
  16.     config.MapHttpAttributeRoutes();
  17.     //OData Routes
  18.     ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
  19.     builder.DataServiceVersion = new Version("3.0");
  20.     builder.MaxDataServiceVersion = new Version("3.0");
  21.     builder.EntitySet<Customer>("Customer");
  22.     builder.EntitySet<Employee>("Employee");
  23.     builder.EntitySet<Order>("Order");
  24.     builder.EntitySet<Order_Detail>("Order_Detail");
  25.     builder.EntitySet<Product>("Product");
  26.     config.Routes.MapODataRoute("odata", "odata", builder.GetEdmModel());
  27.     /*
  28.     //Fabian Williams — Comment out the API Route
  29.     config.Routes.MapHttpRoute(
  30.         name: "DefaultApi",
  31.         routeTemplate: "api/{controller}/{id}",
  32.         defaults: new { id = RouteParameter.Optional }
  33.     );
  34.     //Fabian Williams – End Comment
  35.     */
  36. }

 

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.

Code Snippet
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Data.Entity;
  5. using System.Data.Entity.Infrastructure;
  6. using System.Linq;
  7. using System.Net;
  8. using System.Net.Http;
  9. using System.Web.Http;
  10. using System.Web.Http.ModelBinding;
  11. using System.Web.Http.OData;
  12. using System.Web.Http.OData.Routing;
  13. using NorthWindAzureOdata;
  14.  
  15. namespace NorthWindAzureOdata.Controllers
  16. {
  17.     /*
  18.     To add a route for this controller, merge these statements into the Register method of the WebApiConfig class. Note that OData URLs are case sensitive.
  19.  
  20.     using System.Web.Http.OData.Builder;
  21.     using NorthWindAzureOdata;
  22.     ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
  23.     builder.EntitySet<Employee>("Employee");
  24.     builder.EntitySet<Order>("Order");
  25.     config.Routes.MapODataRoute("odata", "odata", builder.GetEdmModel());
  26.     */
  27.     //Fabian Comment — Add the Authorise Decorator to ensure that you must log in to see this Data
  28.     [Authorize]
  29.     public class EmployeeController : ODataController
  30.     {
  31.         private FabianNorthwindEntities db = new FabianNorthwindEntities();
  32.  
  33.         // GET odata/Employee
  34.         [Queryable]
  35.         public IQueryable<Employee> GetEmployee()
  36.         {
  37.             return db.Employees;
  38.         }
  39.  
  40.         // GET odata/Employee(5)
  41.         [Queryable]
  42.         public SingleResult<Employee> GetEmployee([FromODataUri] int key)
  43.         {
  44.             return SingleResult.Create(db.Employees.Where(employee => employee.EmployeeID == key));
  45.         }
  46.  
  47.         // PUT odata/Employee(5)
  48.         public IHttpActionResult Put([FromODataUri] int key, Employee employee)
  49.         {
  50.             if (!ModelState.IsValid)
  51.             {
  52.                 return BadRequest(ModelState);
  53.             }
  54.  
  55.             if (key != employee.EmployeeID)
  56.             {
  57.                 return BadRequest();
  58.             }
  59.  
  60.             db.Entry(employee).State = EntityState.Modified;
  61.  
  62.             try
  63.             {
  64.                 db.SaveChanges();
  65.             }
  66.             catch (DbUpdateConcurrencyException)
  67.             {
  68.                 if (!EmployeeExists(key))
  69.                 {
  70.                     return NotFound();
  71.                 }
  72.                 else
  73.                 {
  74.                     throw;
  75.                 }
  76.             }
  77.  
  78.             return Updated(employee);
  79.         }
  80.  
  81.         // POST odata/Employee
  82.         public IHttpActionResult Post(Employee employee)
  83.         {
  84.             if (!ModelState.IsValid)
  85.             {
  86.                 return BadRequest(ModelState);
  87.             }
  88.  
  89.             db.Employees.Add(employee);
  90.             db.SaveChanges();
  91.  
  92.             return Created(employee);
  93.         }
  94.  
  95.         // PATCH odata/Employee(5)
  96.         [AcceptVerbs("PATCH", "MERGE")]
  97.         public IHttpActionResult Patch([FromODataUri] int key, Delta<Employee> patch)
  98.         {
  99.             if (!ModelState.IsValid)
  100.             {
  101.                 return BadRequest(ModelState);
  102.             }
  103.  
  104.             Employee employee = db.Employees.Find(key);
  105.             if (employee == null)
  106.             {
  107.                 return NotFound();
  108.             }
  109.  
  110.             patch.Patch(employee);
  111.  
  112.             try
  113.             {
  114.                 db.SaveChanges();
  115.             }
  116.             catch (DbUpdateConcurrencyException)
  117.             {
  118.                 if (!EmployeeExists(key))
  119.                 {
  120.                     return NotFound();
  121.                 }
  122.                 else
  123.                 {
  124.                     throw;
  125.                 }
  126.             }
  127.  
  128.             return Updated(employee);
  129.         }
  130.  
  131.         // DELETE odata/Employee(5)
  132.         public IHttpActionResult Delete([FromODataUri] int key)
  133.         {
  134.             Employee employee = db.Employees.Find(key);
  135.             if (employee == null)
  136.             {
  137.                 return NotFound();
  138.             }
  139.  
  140.             db.Employees.Remove(employee);
  141.             db.SaveChanges();
  142.  
  143.             return StatusCode(HttpStatusCode.NoContent);
  144.         }
  145.  
  146.         // GET odata/Employee(5)/Employees1
  147.         [Queryable]
  148.         public IQueryable<Employee> GetEmployees1([FromODataUri] int key)
  149.         {
  150.             return db.Employees.Where(m => m.EmployeeID == key).SelectMany(m => m.Employees1);
  151.         }
  152.  
  153.         // GET odata/Employee(5)/Employee1
  154.         [Queryable]
  155.         public SingleResult<Employee> GetEmployee1([FromODataUri] int key)
  156.         {
  157.             return SingleResult.Create(db.Employees.Where(m => m.EmployeeID == key).Select(m => m.Employee1));
  158.         }
  159.  
  160.         // GET odata/Employee(5)/Orders
  161.         [Queryable]
  162.         public IQueryable<Order> GetOrders([FromODataUri] int key)
  163.         {
  164.             return db.Employees.Where(m => m.EmployeeID == key).SelectMany(m => m.Orders);
  165.         }
  166.  
  167.         protected override void Dispose(bool disposing)
  168.         {
  169.             if (disposing)
  170.             {
  171.                 db.Dispose();
  172.             }
  173.             base.Dispose(disposing);
  174.         }
  175.  
  176.         private bool EmployeeExists(int key)
  177.         {
  178.             return db.Employees.Count(e => e.EmployeeID == key) > 0;
  179.         }
  180.     }
  181. }

Above is the Employee Controller notice the [Authorize] Modifier?

Below is the Customer Controller

Code Snippet
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Data.Entity;
  5. using System.Data.Entity.Infrastructure;
  6. using System.Linq;
  7. using System.Net;
  8. using System.Net.Http;
  9. using System.Web.Http;
  10. using System.Web.Http.ModelBinding;
  11. using System.Web.Http.OData;
  12. using System.Web.Http.OData.Routing;
  13. using NorthWindAzureOdata;
  14.  
  15. namespace NorthWindAzureOdata.Controllers
  16. {
  17.     /*
  18.     To add a route for this controller, merge these statements into the Register method of the WebApiConfig class. Note that OData URLs are case sensitive.
  19.  
  20.     using System.Web.Http.OData.Builder;
  21.     using NorthWindAzureOdata;
  22.     ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
  23.     builder.EntitySet<Customer>("Customer");
  24.     builder.EntitySet<Order>("Order");
  25.     config.Routes.MapODataRoute("odata", "odata", builder.GetEdmModel());
  26.     */
  27.  
  28.  
  29.     public class CustomerController : ODataController
  30.     {
  31.         private FabianNorthwindEntities db = new FabianNorthwindEntities();
  32.  
  33.         // GET odata/Customer
  34.         [Queryable]
  35.         public IQueryable<Customer> GetCustomer()
  36.         {
  37.             return db.Customers;
  38.         }
  39.  
  40.         // GET odata/Customer(5)
  41.         [Queryable]
  42.         public SingleResult<Customer> GetCustomer([FromODataUri] string key)
  43.         {
  44.             return SingleResult.Create(db.Customers.Where(customer => customer.CustomerID == key));
  45.         }
  46.  
  47.         // PUT odata/Customer(5)
  48.         public IHttpActionResult Put([FromODataUri] string key, Customer customer)
  49.         {
  50.             if (!ModelState.IsValid)
  51.             {
  52.                 return BadRequest(ModelState);
  53.             }
  54.  
  55.             if (key != customer.CustomerID)
  56.             {
  57.                 return BadRequest();
  58.             }
  59.  
  60.             db.Entry(customer).State = EntityState.Modified;
  61.  
  62.             try
  63.             {
  64.                 db.SaveChanges();
  65.             }
  66.             catch (DbUpdateConcurrencyException)
  67.             {
  68.                 if (!CustomerExists(key))
  69.                 {
  70.                     return NotFound();
  71.                 }
  72.                 else
  73.                 {
  74.                     throw;
  75.                 }
  76.             }
  77.  
  78.             return Updated(customer);
  79.         }
  80.  
  81.         // POST odata/Customer
  82.         public IHttpActionResult Post(Customer customer)
  83.         {
  84.             if (!ModelState.IsValid)
  85.             {
  86.                 return BadRequest(ModelState);
  87.             }
  88.  
  89.             db.Customers.Add(customer);
  90.  
  91.             try
  92.             {
  93.                 db.SaveChanges();
  94.             }
  95.             catch (DbUpdateException)
  96.             {
  97.                 if (CustomerExists(customer.CustomerID))
  98.                 {
  99.                     return Conflict();
  100.                 }
  101.                 else
  102.                 {
  103.                     throw;
  104.                 }
  105.             }
  106.  
  107.             return Created(customer);
  108.         }
  109.  
  110.         // PATCH odata/Customer(5)
  111.         [AcceptVerbs("PATCH", "MERGE")]
  112.         public IHttpActionResult Patch([FromODataUri] string key, Delta<Customer> patch)
  113.         {
  114.             if (!ModelState.IsValid)
  115.             {
  116.                 return BadRequest(ModelState);
  117.             }
  118.  
  119.             Customer customer = db.Customers.Find(key);
  120.             if (customer == null)
  121.             {
  122.                 return NotFound();
  123.             }
  124.  
  125.             patch.Patch(customer);
  126.  
  127.             try
  128.             {
  129.                 db.SaveChanges();
  130.             }
  131.             catch (DbUpdateConcurrencyException)
  132.             {
  133.                 if (!CustomerExists(key))
  134.                 {
  135.                     return NotFound();
  136.                 }
  137.                 else
  138.                 {
  139.                     throw;
  140.                 }
  141.             }
  142.  
  143.             return Updated(customer);
  144.         }
  145.  
  146.         // DELETE odata/Customer(5)
  147.         public IHttpActionResult Delete([FromODataUri] string key)
  148.         {
  149.             Customer customer = db.Customers.Find(key);
  150.             if (customer == null)
  151.             {
  152.                 return NotFound();
  153.             }
  154.  
  155.             db.Customers.Remove(customer);
  156.             db.SaveChanges();
  157.  
  158.             return StatusCode(HttpStatusCode.NoContent);
  159.         }
  160.  
  161.         // GET odata/Customer(5)/Orders
  162.         [Queryable]
  163.         public IQueryable<Order> GetOrders([FromODataUri] string key)
  164.         {
  165.             return db.Customers.Where(m => m.CustomerID == key).SelectMany(m => m.Orders);
  166.         }
  167.  
  168.         protected override void Dispose(bool disposing)
  169.         {
  170.             if (disposing)
  171.             {
  172.                 db.Dispose();
  173.             }
  174.             base.Dispose(disposing);
  175.         }
  176.  
  177.         private bool CustomerExists(string key)
  178.         {
  179.             return db.Customers.Count(e => e.CustomerID == key) > 0;
  180.         }
  181.     }
  182. }

 

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.

 image

make the call now

image

As you can see we can call up the Customer End Point above, lets try this with the Employee EndPoint

 image

now make the call

image

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

image

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

image

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

image

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

image

NOW, we can make that call to the Employee Entity and now provide the proper Token

image

Execute on that and provided it is successful, you should have a good response back as we did in the Customer Entity

image

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

image

Follow it through

image

Once you have set the configuration elements, click Publish and watch the process go

image

Once it is complete you should see all Greens 🙂

image

Lets check to see if it is available in Azure now

image

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

image

Validate your Office 365 Environment, this is also a SharePoint Hosted App

image

Once you are complete and click Finish, you will get a project like below.

image

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”

image

This will bring up a OData Source Wizard, Go ahead and add the Azure Cloud Services URI and give it a name

image

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.

image

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)

image

Deploying this project will get us the External List served up in Office 365 as you see below.

 

image

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
Code Snippet
  1. <?xml version="1.0" encoding="utf-16"?>
  2. <Model xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Name="NorthWindAzureOdata" xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog">
  3.   <LobSystems>
  4.     <LobSystem Name="NorthWindCloudServiceOdata" Type="OData">
  5.       <Properties>
  6.         <Property Name="ODataServiceMetadataUrl" Type="System.String">http://nwindazureodata.cloudapp.net/odata/$metadata</Property>
  7.         <Property Name="ODataServiceMetadataAuthenticationMode" Type="System.String">PassThrough</Property>
  8.         <Property Name="ODataServicesVersion" Type="System.String">2.0</Property>
  9.       </Properties>
  10.       <AccessControlList>
  11.         <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
  12.           <Right BdcRight="Edit" />
  13.           <Right BdcRight="Execute" />
  14.           <Right BdcRight="SelectableInClients" />
  15.           <Right BdcRight="SetPermissions" />
  16.         </AccessControlEntry>
  17.       </AccessControlList>
  18.       <LobSystemInstances>
  19.         <LobSystemInstance Name="NorthWindCloudServiceOdata">
  20.           <Properties>
  21.             <Property Name="ODataServiceUrl" Type="System.String">http://nwindazureodata.cloudapp.net/odata</Property>
  22.             <Property Name="ODataServiceAuthenticationMode" Type="System.String">PassThrough</Property>
  23.             <Property Name="ODataFormat" Type="System.String">application/atom+xml</Property>
  24.             <Property Name="HttpHeaderSetAcceptLanguage" Type="System.Boolean">true</Property>
  25.           </Properties>
  26.         </LobSystemInstance>
  27.       </LobSystemInstances>
  28.       <Entities>
  29.         <Entity Name="Customer" DefaultDisplayName="Customer" Namespace="NorthWindAzureOdata" Version="1.0.0.0" EstimatedInstanceCount="2000">
  30.           <Properties>
  31.             <Property Name="ExcludeFromOfflineClientForList" Type="System.String">False</Property>
  32.           </Properties>
  33.           <AccessControlList>
  34.             <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
  35.               <Right BdcRight="Edit" />
  36.               <Right BdcRight="Execute" />
  37.               <Right BdcRight="SelectableInClients" />
  38.               <Right BdcRight="SetPermissions" />
  39.             </AccessControlEntry>
  40.           </AccessControlList>
  41.           <Identifiers>
  42.             <Identifier Name="CustomerID" TypeName="System.String" />
  43.           </Identifiers>
  44.           <Methods>
  45.             <Method Name="CreateCustomer" DefaultDisplayName="Create Customer" IsStatic="false">
  46.               <Properties>
  47.                 <Property Name="ODataEntityUrl" Type="System.String">/Customer</Property>
  48.               </Properties>
  49.               <AccessControlList>
  50.                 <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
  51.                   <Right BdcRight="Edit" />
  52.                   <Right BdcRight="Execute" />
  53.                   <Right BdcRight="SelectableInClients" />
  54.                   <Right BdcRight="SetPermissions" />
  55.                 </AccessControlEntry>
  56.               </AccessControlList>
  57.               <Parameters>
  58.                 <Parameter Name="@CustomerID" Direction="In">
  59.                   <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" CreatorField="true" />
  60.                 </Parameter>
  61.                 <Parameter Name="@CompanyName" Direction="In">
  62.                   <TypeDescriptor Name="CompanyName" DefaultDisplayName="CompanyName" TypeName="System.String" CreatorField="true" />
  63.                 </Parameter>
  64.                 <Parameter Name="@ContactName" Direction="In">
  65.                   <TypeDescriptor Name="ContactName" DefaultDisplayName="ContactName" TypeName="System.String" CreatorField="true" />
  66.                 </Parameter>
  67.                 <Parameter Name="@ContactTitle" Direction="In">
  68.                   <TypeDescriptor Name="ContactTitle" DefaultDisplayName="ContactTitle" TypeName="System.String" CreatorField="true" />
  69.                 </Parameter>
  70.                 <Parameter Name="@Address" Direction="In">
  71.                   <TypeDescriptor Name="Address" DefaultDisplayName="Address" TypeName="System.String" CreatorField="true" />
  72.                 </Parameter>
  73.                 <Parameter Name="@City" Direction="In">
  74.                   <TypeDescriptor Name="City" DefaultDisplayName="City" TypeName="System.String" CreatorField="true" />
  75.                 </Parameter>
  76.                 <Parameter Name="@Region" Direction="In">
  77.                   <TypeDescriptor Name="Region" DefaultDisplayName="Region" TypeName="System.String" CreatorField="true" />
  78.                 </Parameter>
  79.                 <Parameter Name="@PostalCode" Direction="In">
  80.                   <TypeDescriptor Name="PostalCode" DefaultDisplayName="PostalCode" TypeName="System.String" CreatorField="true" />
  81.                 </Parameter>
  82.                 <Parameter Name="@Country" Direction="In">
  83.                   <TypeDescriptor Name="Country" DefaultDisplayName="Country" TypeName="System.String" CreatorField="true" />
  84.                 </Parameter>
  85.                 <Parameter Name="@Phone" Direction="In">
  86.                   <TypeDescriptor Name="Phone" DefaultDisplayName="Phone" TypeName="System.String" CreatorField="true" />
  87.                 </Parameter>
  88.                 <Parameter Name="@Fax" Direction="In">
  89.                   <TypeDescriptor Name="Fax" DefaultDisplayName="Fax" TypeName="System.String" CreatorField="true" />
  90.                 </Parameter>
  91.                 <Parameter Name="@CreateCustomer" Direction="Return">
  92.                   <TypeDescriptor Name="CreateCustomer" DefaultDisplayName="CreateCustomer" TypeName="Microsoft.BusinessData.Runtime.DynamicType">
  93.                     <TypeDescriptors>
  94.                       <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" ReadOnly="true" />
  95.                       <TypeDescriptor Name="CompanyName" DefaultDisplayName="CompanyName" TypeName="System.String" />
  96.                       <TypeDescriptor Name="ContactName" DefaultDisplayName="ContactName" TypeName="System.String" />
  97.                       <TypeDescriptor Name="ContactTitle" DefaultDisplayName="ContactTitle" TypeName="System.String" />
  98.                       <TypeDescriptor Name="Address" DefaultDisplayName="Address" TypeName="System.String" />
  99.                       <TypeDescriptor Name="City" DefaultDisplayName="City" TypeName="System.String" />
  100.                       <TypeDescriptor Name="Region" DefaultDisplayName="Region" TypeName="System.String" />
  101.                       <TypeDescriptor Name="PostalCode" DefaultDisplayName="PostalCode" TypeName="System.String" />
  102.                       <TypeDescriptor Name="Country" DefaultDisplayName="Country" TypeName="System.String" />
  103.                       <TypeDescriptor Name="Phone" DefaultDisplayName="Phone" TypeName="System.String" />
  104.                       <TypeDescriptor Name="Fax" DefaultDisplayName="Fax" TypeName="System.String" />
  105.                     </TypeDescriptors>
  106.                   </TypeDescriptor>
  107.                 </Parameter>
  108.               </Parameters>
  109.               <MethodInstances>
  110.                 <MethodInstance Name="CreateCustomer" Type="Creator" ReturnParameterName="@CreateCustomer" ReturnTypeDescriptorPath="CreateCustomer">
  111.                   <AccessControlList>
  112.                     <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
  113.                       <Right BdcRight="Edit" />
  114.                       <Right BdcRight="Execute" />
  115.                       <Right BdcRight="SelectableInClients" />
  116.                       <Right BdcRight="SetPermissions" />
  117.                     </AccessControlEntry>
  118.                   </AccessControlList>
  119.                 </MethodInstance>
  120.               </MethodInstances>
  121.             </Method>
  122.             <Method Name="ReadSpecificCustomer" DefaultDisplayName="Read Specific Customer" IsStatic="false">
  123.               <Properties>
  124.                 <Property Name="ODataEntityUrl" Type="System.String">/Customer(CustomerID='@CustomerID')</Property>
  125.               </Properties>
  126.               <AccessControlList>
  127.                 <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
  128.                   <Right BdcRight="Edit" />
  129.                   <Right BdcRight="Execute" />
  130.                   <Right BdcRight="SelectableInClients" />
  131.                   <Right BdcRight="SetPermissions" />
  132.                 </AccessControlEntry>
  133.               </AccessControlList>
  134.               <Parameters>
  135.                 <Parameter Name="@CustomerID" Direction="In">
  136.                   <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" />
  137.                 </Parameter>
  138.                 <Parameter Name="@Customer" Direction="Return">
  139.                   <TypeDescriptor Name="Customer" DefaultDisplayName="Customer" TypeName="Microsoft.BusinessData.Runtime.DynamicType">
  140.                     <TypeDescriptors>
  141.                       <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" ReadOnly="true" />
  142.                       <TypeDescriptor Name="CompanyName" DefaultDisplayName="CompanyName" TypeName="System.String" />
  143.                       <TypeDescriptor Name="ContactName" DefaultDisplayName="ContactName" TypeName="System.String" />
  144.                       <TypeDescriptor Name="ContactTitle" DefaultDisplayName="ContactTitle" TypeName="System.String" />
  145.                       <TypeDescriptor Name="Address" DefaultDisplayName="Address" TypeName="System.String" />
  146.                       <TypeDescriptor Name="City" DefaultDisplayName="City" TypeName="System.String" />
  147.                       <TypeDescriptor Name="Region" DefaultDisplayName="Region" TypeName="System.String" />
  148.                       <TypeDescriptor Name="PostalCode" DefaultDisplayName="PostalCode" TypeName="System.String" />
  149.                       <TypeDescriptor Name="Country" DefaultDisplayName="Country" TypeName="System.String" />
  150.                       <TypeDescriptor Name="Phone" DefaultDisplayName="Phone" TypeName="System.String" />
  151.                       <TypeDescriptor Name="Fax" DefaultDisplayName="Fax" TypeName="System.String" />
  152.                     </TypeDescriptors>
  153.                   </TypeDescriptor>
  154.                 </Parameter>
  155.               </Parameters>
  156.               <MethodInstances>
  157.                 <MethodInstance Name="ReadSpecificCustomer" Type="SpecificFinder" Default="true" ReturnParameterName="@Customer" ReturnTypeDescriptorPath="Customer">
  158.                   <AccessControlList>
  159.                     <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
  160.                       <Right BdcRight="Edit" />
  161.                       <Right BdcRight="Execute" />
  162.                       <Right BdcRight="SelectableInClients" />
  163.                       <Right BdcRight="SetPermissions" />
  164.                     </AccessControlEntry>
  165.                   </AccessControlList>
  166.                 </MethodInstance>
  167.               </MethodInstances>
  168.             </Method>
  169.             <Method Name="ReadAllCustomer" DefaultDisplayName="Read All Customer" IsStatic="false">
  170.               <Properties>
  171.                 <Property Name="ODataEntityUrl" Type="System.String">/Customer?$top=@LimitCustomers</Property>
  172.               </Properties>
  173.               <AccessControlList>
  174.                 <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
  175.                   <Right BdcRight="Edit" />
  176.                   <Right BdcRight="Execute" />
  177.                   <Right BdcRight="SelectableInClients" />
  178.                   <Right BdcRight="SetPermissions" />
  179.                 </AccessControlEntry>
  180.               </AccessControlList>
  181.               <FilterDescriptors>
  182.                 <FilterDescriptor Name="LimitFilter" DefaultDisplayName="LimitFilter" Type="Limit" />
  183.               </FilterDescriptors>
  184.               <Parameters>
  185.                 <Parameter Name="@LimitCustomers" Direction="In">
  186.                   <TypeDescriptor Name="LimitCustomers" DefaultDisplayName="LimitCustomers" TypeName="System.Int32" AssociatedFilter="LimitFilter">
  187.                     <Properties>
  188.                       <Property Name="LogicalOperatorWithPrevious" Type="System.String">None</Property>
  189.                       <Property Name="Order" Type="System.String">0</Property>
  190.                     </Properties>
  191.                     <DefaultValues>
  192.                       <DefaultValue MethodInstanceName="ReadAllCustomer" Type="System.Int32">100</DefaultValue>
  193.                     </DefaultValues>
  194.                   </TypeDescriptor>
  195.                 </Parameter>
  196.                 <Parameter Name="@Customers" Direction="Return">
  197.                   <TypeDescriptor Name="Customers" DefaultDisplayName="Customers" TypeName="Microsoft.BusinessData.Runtime.IDynamicTypeEnumerator" IsCollection="true">
  198.                     <TypeDescriptors>
  199.                       <TypeDescriptor Name="Customer" DefaultDisplayName="Customer" TypeName="Microsoft.BusinessData.Runtime.DynamicType">
  200.                         <TypeDescriptors>
  201.                           <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" ReadOnly="true" />
  202.                           <TypeDescriptor Name="CompanyName" DefaultDisplayName="CompanyName" TypeName="System.String" />
  203.                           <TypeDescriptor Name="ContactName" DefaultDisplayName="ContactName" TypeName="System.String" />
  204.                           <TypeDescriptor Name="ContactTitle" DefaultDisplayName="ContactTitle" TypeName="System.String" />
  205.                           <TypeDescriptor Name="Address" DefaultDisplayName="Address" TypeName="System.String" />
  206.                           <TypeDescriptor Name="City" DefaultDisplayName="City" TypeName="System.String" />
  207.                           <TypeDescriptor Name="Region" DefaultDisplayName="Region" TypeName="System.String" />
  208.                           <TypeDescriptor Name="PostalCode" DefaultDisplayName="PostalCode" TypeName="System.String" />
  209.                           <TypeDescriptor Name="Country" DefaultDisplayName="Country" TypeName="System.String" />
  210.                           <TypeDescriptor Name="Phone" DefaultDisplayName="Phone" TypeName="System.String" />
  211.                           <TypeDescriptor Name="Fax" DefaultDisplayName="Fax" TypeName="System.String" />
  212.                         </TypeDescriptors>
  213.                       </TypeDescriptor>
  214.                     </TypeDescriptors>
  215.                   </TypeDescriptor>
  216.                 </Parameter>
  217.               </Parameters>
  218.               <MethodInstances>
  219.                 <MethodInstance Name="ReadAllCustomer" Type="Finder" Default="true" ReturnParameterName="@Customers" ReturnTypeDescriptorPath="Customers">
  220.                   <AccessControlList>
  221.                     <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
  222.                       <Right BdcRight="Edit" />
  223.                       <Right BdcRight="Execute" />
  224.                       <Right BdcRight="SelectableInClients" />
  225.                       <Right BdcRight="SetPermissions" />
  226.                     </AccessControlEntry>
  227.                   </AccessControlList>
  228.                 </MethodInstance>
  229.               </MethodInstances>
  230.             </Method>
  231.             <Method Name="UpdateCustomer" DefaultDisplayName="Update Customer" IsStatic="false">
  232.               <Properties>
  233.                 <Property Name="ODataEntityUrl" Type="System.String">/Customer(CustomerID='@CustomerID')</Property>
  234.               </Properties>
  235.               <AccessControlList>
  236.                 <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
  237.                   <Right BdcRight="Edit" />
  238.                   <Right BdcRight="Execute" />
  239.                   <Right BdcRight="SelectableInClients" />
  240.                   <Right BdcRight="SetPermissions" />
  241.                 </AccessControlEntry>
  242.               </AccessControlList>
  243.               <Parameters>
  244.                 <Parameter Name="@CustomerID" Direction="In">
  245.                   <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" UpdaterField="true" />
  246.                 </Parameter>
  247.                 <Parameter Name="@CompanyName" Direction="In">
  248.                   <TypeDescriptor Name="CompanyName" DefaultDisplayName="CompanyName" TypeName="System.String" UpdaterField="true" />
  249.                 </Parameter>
  250.                 <Parameter Name="@ContactName" Direction="In">
  251.                   <TypeDescriptor Name="ContactName" DefaultDisplayName="ContactName" TypeName="System.String" UpdaterField="true" />
  252.                 </Parameter>
  253.                 <Parameter Name="@ContactTitle" Direction="In">
  254.                   <TypeDescriptor Name="ContactTitle" DefaultDisplayName="ContactTitle" TypeName="System.String" UpdaterField="true" />
  255.                 </Parameter>
  256.                 <Parameter Name="@Address" Direction="In">
  257.                   <TypeDescriptor Name="Address" DefaultDisplayName="Address" TypeName="System.String" UpdaterField="true" />
  258.                 </Parameter>
  259.                 <Parameter Name="@City" Direction="In">
  260.                   <TypeDescriptor Name="City" DefaultDisplayName="City" TypeName="System.String" UpdaterField="true" />
  261.                 </Parameter>
  262.                 <Parameter Name="@Region" Direction="In">
  263.                   <TypeDescriptor Name="Region" DefaultDisplayName="Region" TypeName="System.String" UpdaterField="true" />
  264.                 </Parameter>
  265.                 <Parameter Name="@PostalCode" Direction="In">
  266.                   <TypeDescriptor Name="PostalCode" DefaultDisplayName="PostalCode" TypeName="System.String" UpdaterField="true" />
  267.                 </Parameter>
  268.                 <Parameter Name="@Country" Direction="In">
  269.                   <TypeDescriptor Name="Country" DefaultDisplayName="Country" TypeName="System.String" UpdaterField="true" />
  270.                 </Parameter>
  271.                 <Parameter Name="@Phone" Direction="In">
  272.                   <TypeDescriptor Name="Phone" DefaultDisplayName="Phone" TypeName="System.String" UpdaterField="true" />
  273.                 </Parameter>
  274.                 <Parameter Name="@Fax" Direction="In">
  275.                   <TypeDescriptor Name="Fax" DefaultDisplayName="Fax" TypeName="System.String" UpdaterField="true" />
  276.                 </Parameter>
  277.               </Parameters>
  278.               <MethodInstances>
  279.                 <MethodInstance Name="UpdateCustomer" Type="Updater">
  280.                   <AccessControlList>
  281.                     <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
  282.                       <Right BdcRight="Edit" />
  283.                       <Right BdcRight="Execute" />
  284.                       <Right BdcRight="SelectableInClients" />
  285.                       <Right BdcRight="SetPermissions" />
  286.                     </AccessControlEntry>
  287.                   </AccessControlList>
  288.                 </MethodInstance>
  289.               </MethodInstances>
  290.             </Method>
  291.             <Method Name="DeleteCustomer" DefaultDisplayName="Delete Customer" IsStatic="false">
  292.               <Properties>
  293.                 <Property Name="ODataEntityUrl" Type="System.String">/Customer(CustomerID='@CustomerID')</Property>
  294.               </Properties>
  295.               <AccessControlList>
  296.                 <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
  297.                   <Right BdcRight="Edit" />
  298.                   <Right BdcRight="Execute" />
  299.                   <Right BdcRight="SelectableInClients" />
  300.                   <Right BdcRight="SetPermissions" />
  301.                 </AccessControlEntry>
  302.               </AccessControlList>
  303.               <Parameters>
  304.                 <Parameter Name="@CustomerID" Direction="In">
  305.                   <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" />
  306.                 </Parameter>
  307.               </Parameters>
  308.               <MethodInstances>
  309.                 <MethodInstance Name="DeleteCustomer" Type="Deleter">
  310.                   <AccessControlList>
  311.                     <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">
  312.                       <Right BdcRight="Edit" />
  313.                       <Right BdcRight="Execute" />
  314.                       <Right BdcRight="SelectableInClients" />
  315.                       <Right BdcRight="SetPermissions" />
  316.                     </AccessControlEntry>
  317.                   </AccessControlList>
  318.                 </MethodInstance>
  319.               </MethodInstances>
  320.             </Method>
  321.           </Methods>
  322.         </Entity>
  323.       </Entities>
  324.     </LobSystem>
  325.   </LobSystems>
  326. </Model>

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”

image

Locate the ECT File you saved.

image

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.

image

Now you can Add a External List from it as you add any other App list.

image

Below you get a representation of the List as we did when we deployed it from the Visual Studio Project.

image

Summary

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.


Leave a comment

Your email address will not be published. Required fields are marked *

One thought on “Game Plan for SharePoint Conference Session 330 and 313