In this article I will be doing a complete walkthrough of creating a simple SharePoint Provider Hosted App using the following frameworks and technologies: Entity Framework 6, Web API, REST and Angular.js. It assumes you are using Visual Studio 2013 and have an Office 365 tenant and Azure account already set up.
To begin with make sure that you have an up to date version of NuGet Package Manager. You can check your version by going into Visual Studio and clicking on Help > About Microsoft Visual Studio.
You will need version 2.8.6 or higher. I downloaded the latest version from here:
https://visualstudiogallery.msdn.microsoft.com/4ec1526c-4a8c-4a84-b702-b21a8f5293ca
If you are using Visual Studio 2015 you have a compatible version of NuGet and can skip this step.
Start by creating a new project in Visual Studio 2013
Open Visual Studio (this walkthrough uses 2015 but you can use any version from 2012 onwards)
- File > New > Project…
- From the left menu select Templates ‣ Visual C# ‣ Windows
- Select the Class Library project template
- Ensure you are targeting .NET 4.5 or later
- Give the project the Name CustomerOrders.Classes and name the solution CustomerOrders
- Rename Class1.cs file to Customer.cs
Click Yes
Add the following Code to the class
public class Customer
{
public int Id { get; set;}
public string FirstName { get; set;}
public string LastName { get; set;}
public string Email { get; set; }
public string Phone { get; set; }
public List<Order> CustomerOrders { get; set; }}
public class Order
{
public int Id { get; set; }
public string ProductName { get; set; }
public string CustomerId { get; set; }
public Customer Customer { get; set; }
}
Now create a new project also using the Class library template and call it CustomerOrders.DataModel
Here is where we add the Entity Framework references using NuGet package Manager.
Right click on the new project and click on Manage NuGet Packages…
Select Online and then search for Entity. Click on Install beside EntityFramework
Now under your project references you should see EntityFramework and EntityFramework.SqlServer
Rename Class1 to CustomerContext and add the following using statement to the top of the class
using System.Data.Entity;
This class will need to inherit from DbContext so the class definition should look like this:
public class CustomerContext:DbContext
{}
Now add a reference to our CustomerOrders.Classes project and include a using statement
Here is where we add DbSets that map to the classes we created before.
using System.Data.Entity;
using CustomerOrders.Classes;
namespace CustomerOrders.DataModel
{public class CustomerContext:DbContext
{public DbSet<Customer> Customers { get; set; }
public DbSet<Order> Orders { get; set; }}
}
Install the Entity Framework Power Tools Extension under Tools select Extensions and Updates
Search for Entity Framework Power
Click on Download
For more information see: https://msdn.microsoft.com/en-us/data/jj593170
This add in was created by Microsoft and they intend to add these features in future releases of EntityFramework
Once installed it will require you to restart Visual Studio. Make sure to save your work!
Now you must set the CustomerOrders.DataModel to be the Startup project
Now you will be able to View the Entity Data Model if you right click on your class and select Entity Framework > View Entity Data Model (Read-only)
Notice the zero or one – to – many relationship between Customer and Order
We want a one – to – many relationship
To do this add a reference to System.ComponentModel.DataAnnotations to the CustomerOrders .Classes project
Then add a using statement to include the reference and now we can set the Required attribute to your Customer property of the Orders class. Your code should now look like this
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;namespace CustomerOrders.Classes
{public class Customer
{
public Customer() {CustomerOrders = new List<Order>();
}
public int Id { get; set;}
public string FirstName { get; set;}
public string LastName { get; set;}
public string Email { get; set; }
public string Phone { get; set; }
public List<Order> CustomerOrders { get; set; }
}public class Order
{public int Id { get; set; }
public string ProductName { get; set; }
public string CustomerId { get; set; }
[Required]
public Customer Customer { get; set; }
}}
Now when we view the Entity Data model you will see a one – to – many relationship
Creating the database
In Visual Studio open up the NuGet Package Manager Console Window and enable migrations using the following command :
Enable-migrations
You should now see a migrations folder in solution explorer
Back in the Package Manager Console type add-migration “Initial”
Now it’s time to actually create the database in SQL Server
Run the following command in Package Manager Console
update-database -script
This will generate a script file that you can give to your DBA to run
Now run
update-database -verbose
This will actually create the database
To check what it has created add a connection to your localdb in Visual Studio in Server Manager
Check the app.config file to find the server name
You will see now that it has created your tables and constraints:
It also creates a Migration table to track the history. As you modify your entities delta updates are made to the schema
Let’s make a small change to our customer class and see how the database gets updated
Open up our Customer.cs class and add a new Fax property
public class Customer
{public Customer() {
CustomerOrders = new List<Order>();}
public int Id { get; set;}
public string FirstName { get; set;}
public string LastName { get; set;}
public string Email { get; set; }
public string Phone { get; set; }
public string Fax { get; set; }
public List<Order> CustomerOrders { get; set; }
}
In Package Manager type add-migration “Add Fax” then
update-database -verbose
Refresh the Customer table in Server explorer and you will see the new column added
Create a new Console Application Project in the Solution and name it CustomerExe
And add references to the other two projects and then using Package Manager install the Entity Framework in the Console application by typing
Install-package EntityFramework
You need to switch to the CustomerExe as Default project in the screen above
We can now use the app.config in the CustomerExe project and delete the app.config file in the DataModel project
In the Program.cs file add the following code:
using CustomerOrders.Classes;
using CustomerOrders.DataModel;
using System;
using System.Data.Entity;namespace CustomerExe
{
class Program
{
static void Main(string[] args)
{
AddCustomer();
Console.ReadLine();
}private static void AddCustomer()
{
var cust = new Customer {
FirstName = “Patrick”,
LastName = “Clarke”,
Email = “xyz@internet.com”,
Phone = “613 555-5555”,
Fax = “867-5309”
};using(var context = new CustomerContext())
{
context.Database.Log = Console.WriteLine;
context.Customers.Add(cust);
context.SaveChanges();}
}
}
}
Run the console application and ensure that the customer has been added
If you get the following error you will need to update the Views
‘System.Data.Entity.Infrastructure.DbUpdateException’ occurred in EntityFramework.dll
Right click the CustomerContext.cs file and then under Entity Framework choose Generate Views
Check to see that the new Customer has been added to the database
Add the following code to test out the various crud operations using a connected application
using CustomerOrders.Classes;
using CustomerOrders.DataModel;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;namespace CustomerExe
{
class Program
{
static void Main(string[] args)
{
Database.SetInitializer(new NullDatabaseInitializer<CustomerContext>());
AddCustomer();
AddMultipleCustomers();
UpdateCustomers();
GetCustomers();
GetSomeCustomers();
FindCustomer();
DeleteCustomer();
AddCustomerandOrders();
GetCustomerOrders();
Console.ReadLine();
}
private static void AddCustomer()
{
var cust = new Customer {
FirstName = “Patrick”,
LastName = “Clarke”,
Email = “xyz@internet.com”,
Phone = “613 555-5555”,
Fax = “867-5309”};
using(var context = new CustomerContext())
{
context.Database.Log = Console.WriteLine;
context.Customers.Add(cust);
context.SaveChanges();}
}
private static void AddMultipleCustomers()
{var cust1 = new Customer
{
FirstName = “Pete”,
LastName = “Townsend”,
Email = “aaa@internet.com”,
Phone = “613 555-2355”,
Fax = “867-598”
};
var cust2 = new Customer
{
FirstName = “John”,
LastName = “Entwistle”,
Email = “ccc@internet.com”,
Phone = “613 444-5555”,
Fax = “345-5909″
};
using (var context = new CustomerContext())
{
context.Database.Log = Console.WriteLine;
context.Customers.AddRange(new List<Customer> {cust1, cust2});
context.SaveChanges();}
}
private static void GetCustomers()
{
using (var context = new CustomerContext())
{
var customers = context.Customers.ToList();
foreach(var cust in customers)
{
Console.WriteLine(cust.FirstName + ” ” + cust.LastName);
}}
}
private static void GetSomeCustomers()
{
using (var context = new CustomerContext())
{string fname = “P”;
var customers = context.Customers.Where(c => c.FirstName.StartsWith(fname)).OrderBy(c => c.LastName);
foreach (var cust in customers)
{
Console.WriteLine(cust.FirstName + ” ” + cust.LastName);
}}
}
private static void UpdateCustomers()
{
using (var context = new CustomerContext())
{string fname = “P”;
var customer = context.Customers.Where(c => c.FirstName.StartsWith(fname)).OrderBy(c => c.LastName).FirstOrDefault();
customer.Fax = “613 222-6543″;
context.SaveChanges();}
}
private static void FindCustomer()
{using (var context = new CustomerContext())
{
var customer = context.Customers.Find(7);
Console.WriteLine(customer.FirstName + ” ” + customer.LastName);}
}
private static void DeleteCustomer()
{using (var context = new CustomerContext())
{
var customer = context.Customers.FirstOrDefault();
context.Customers.Remove(customer);
context.SaveChanges();}
}
private static void AddCustomerandOrders()
{
var cust = new Customer
{FirstName = “Paul”,
LastName = “Oakenfeld”,
Email = “yyy@internet.com”,
Phone = “613 876-5555”,
Fax = “867-0987”
};var order1 = new Order
{
ProductName = “Sunglasses”
};var order2 = new Order
{
ProductName = “Shoes”
};
using (var context = new CustomerContext())
{
context.Database.Log = Console.WriteLine;
context.Customers.Add(cust);
cust.CustomerOrders.Add(order1);
cust.CustomerOrders.Add(order2);
context.SaveChanges();}
}
private static void GetCustomerOrders()
{
using (var context = new CustomerContext())
{
string lname = “Oakenfeld”;
var cust = context.Customers.Include(c=>c.CustomerOrders)
.Where(c => c.LastName == lname).FirstOrDefault();Console.WriteLine(cust.FirstName + ” ” + cust.LastName);
foreach(var o in cust.CustomerOrders)
{
Console.WriteLine(o.ProductName);
}
}
}
}}
In the Classes project created a new folder called Interfaces and a new class called IModificationHistory
Add the following code to the class
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace CustomerOrders.Classes.Interfaces
{
class ImodificationHistory
{
DateTime DateModified { get; set; }
DateTime DateCreated { get; set; }
bool IsDirty { get; set; }
}
}
Open the Customers.cs file and ensure that the classes implement the ImodificationHistory interface
Your code should now look like this
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using CustomerOrders.Classes.Interfaces;
using System;namespace CustomerOrders.Classes
{
public class Customer : ImodificationHistory
{public Customer() {
CustomerOrders = new List<Order>();
}public int Id { get; set;}
public string FirstName { get; set;}
public string LastName { get; set;}
public string Email { get; set; }
public string Phone { get; set; }
public string Fax { get; set; }
public List<Order> CustomerOrders { get; set; }
public DateTime DateModified { get; set; }
public DateTime DateCreated { get; set; }
public bool IsDirty { get; set; }
}
public class Order : ImodificationHistory
{
public int Id { get; set; }
public string ProductName { get; set; }
[Required]
public Customer Customer { get; set; }
public DateTime DateModified { get; set; }
public DateTime DateCreated { get; set; }
public bool IsDirty { get; set; }
}
}
In our DataModel project open the CustomerContext class and let’s override the OnModelCreating and SaveChanges method to ignore the IsDirty column from being created.
The class should now have the following code:
using System.Data.Entity;
using CustomerOrders.Classes;
using System.Linq;
using System;namespace CustomerOrders.DataModel
{
public class CustomerContext:DbContext
{
public DbSet<Customer> Customers { get; set; }
public DbSet<Order> Orders { get; set; }protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Types().Configure(c => c.Ignore(“IsDirty”));base.OnModelCreating(modelBuilder);
}public override int SaveChanges()
{
foreach (var history in this.ChangeTracker.Entries()
.Where (e => e.Entity is Classes.Interfaces.ImodificationHistory && (e.State == EntityState.Added ||
e.State == EntityState.Modified ))
.Select (e => e.Entity as Classes.Interfaces.ImodificationHistory)
) {history.DateModified = DateTime.Now;
if(history.DateCreated == DateTime.MinValue)
{
history.DateCreated = DateTime.Now;
}
}int result = base.SaveChanges();
foreach(var history in this.ChangeTracker.Entries()
.Where(e => e.Entity is Classes.Interfaces.ImodificationHistory)
.Select (e => e.Entity as Classes.Interfaces.ImodificationHistory)
)
{
history.IsDirty = false;
}
return result;
}}
}
In Package Manager type add-migration “Add Modified History Columns” then
update-database -verbose
Refresh the Customer table in Server explorer and you will see the new column added
Notice that the IsDirty column is not included in the tables
Creating the DisconnectedRepository
In the DataModel project add a new class called DisconnectedRepository
Add the following code:
using System.Data.Entity;
using CustomerOrders.Classes;
using System.Linq;
using System;
using System.Collections.Generic;namespace CustomerOrders.DataModel
{
public class DisconnectedRepository
{
public List<Customer> GetCustomers()
{
using(var context = new CustomerContext ())
{
return context.Customers.AsNoTracking().OrderBy(n => n.LastName).ToList();
}
}public Customer GetCustomerOrders(int id)
{
using (var context = new CustomerContext())
{
return context.Customers.AsNoTracking().Include(n => n.CustomerOrders).FirstOrDefault(n => n.Id == id);}
}public Customer GetCustomerById(int id)
{
using(var context = new CustomerContext ())
{
return context.Customers.AsNoTracking().SingleOrDefault(n => n.Id == id);
}
}public void SaveUpdatedCustomer(Customer cust)
{
using (var context = new CustomerContext())
{
context.Entry(cust).State = EntityState.Modified;
context.SaveChanges();
}
}public void SaveNewCustomer(Customer cust)
{
using(var context = new CustomerContext ())
{
context.Customers.Add(cust);
context.SaveChanges();
}
}public void DeleteCustomer(int id)
{
using (var context = new CustomerContext())
{
var cust = context.Customers .Find (id);
context.Entry(cust).State = EntityState.Deleted;
context.SaveChanges();
}
}public void SaveNewOrder(Order order, int custId)
{
using (var context = new CustomerContext())
{
var cust = context.Customers.Find(custId);
cust.CustomerOrders.Add(order);
context.SaveChanges();
}
}
public void SaveUpdatedOrder(Order order, int custId)
{
using (var context = new CustomerContext())
{
var orderFromDb =
context.Orders.Include(n => n.Customer).FirstOrDefault(o => o.Id == order.Id);
context.Entry(orderFromDb).CurrentValues.SetValues(order);
context.SaveChanges();}
}}
}
Creating the Web API
Select New Project from the Start page. Or, from the File menu, select New and then Project.
In the Templates pane, select Installed Templates and expand the Visual C# node. Under Visual C#, select Web. In the list of project templates, select ASP.NET Web Application. Name the project “CustomerOrders.WebAPI” and click OK.
Select the Web API project template
I unchecked the option to Host it in the cloud and to Add unit tests
If all goes well you Azure will tell you that the website is ready
Add references to our Classes and Datamodel projects to the new WebAPI project
Update the web.config file connection string to the following:
<connectionStrings>
<add name=”CustomerContext” providerName=”System.Data.SqlClient” connectionString=”yourconnectionstring”/>
</connectionStrings>
If you select the database in Server explorer you can find the connection string in the properties window
Set the WebAPI project to be the startup project
Add a new controller in the Controllers folder called CustomersController selecting the Web API 2 Controller with actions, using Entity Framework template
Run the Project to see if the API works as is using Chrome as IE by default does not show the data in the browser
You will need to add /api/Customers to the URL when the browser opens
You should see something like this in your browser
I like to see the output in JSON format so in a post on StackExchange someone noted that if you add the following to your Global.asax file you will get JSON by default
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
GlobalConfiguration.Configure(WebApiConfig.Register);
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);GlobalConfiguration.Configuration.Formatters.XmlFormatter.SupportedMediaTypes.Clear();
}
To help troubleshoot and inspect the JSON I installed the free tool Fiddler http://www.telerik.com/download/fiddler
Using Package Manager run Install-Package Microsoft.AspNet.WebApi.Cors make sure to select the WebAPI project
Open the file App_Start/WebApiConfig.cs. Add the following code to the WebApiConfig.Register method.
public static void Register(HttpConfiguration config)
{
// Web API configuration and services
// Configure Web API to use only bearer token authentication.
config.SuppressDefaultHostAuthentication();
config.Filters.Add(new HostAuthenticationFilter(OAuthDefaults.AuthenticationType));// Web API routes
config.MapHttpAttributeRoutes();config.EnableCors();
config.Routes.MapHttpRoute(
name: “DefaultApi”,
routeTemplate: “api/{controller}/{id}”,
defaults: new { id = RouteParameter.Optional }
);}
I have updated the controller to use our DisconnectedRepository class:
using System.Collections.Generic;
using System.Linq;
using System.Web.Http;
using System.Web.Http.Description;
using CustomerOrders.Classes;
using CustomerOrders.DataModel;
using Newtonsoft.Json;namespace CustomerOrders.WebAPI.Controllers
{
public class CustomersController : ApiController
{DisconnectedRepository db = new DisconnectedRepository();
// GET: api/Customers
public IEnumerable<Customer> Get()
{
return db.GetCustomers();
}// GET: api/Customers/5
[ResponseType(typeof(Customer))]
public IHttpActionResult GetCustomer(int id)
{
Customer customer = db.GetCustomerById(id);
if (customer == null)
{
return NotFound();
}return Ok(customer);
}public void PutCustomer([FromBody] object customer)
{
var asCustomer = JsonConvert.DeserializeObject<Customer>(customer.ToString());
db.SaveUpdatedCustomer(asCustomer);
}public void PutCustomer(int id, [FromBody] Customer customer)
{
db.SaveNewCustomer(customer);
}// DELETE: api/Customers/5
[ResponseType(typeof(Customer))]
public IHttpActionResult DeleteCustomer(int id)
{
db.DeleteCustomer(id);return Ok();
}
private bool CustomerExists(int id)
{
return db.GetCustomers().Count(e => e.Id == id) > 0;
}
}
}
Publishing the WebAPI to Windows Azure
Right click on the CustomerOrders.WebAPI project and then click on Publish
Select Microsoft Azure Websites
Create a new Web Application and a new database server if one does not already exist
Review the various settings and then Click on Publish
Once finished you should see the new website and database in Azure
Install Download Latest SQL Server Data Tools to connect to SQL Server
https://msdn.microsoft.com/en-us/library/mt204009.aspx
Once installed you will be able to manage your Azure SQL Server database from within Visual Studio
Connect to the database. The system will prompt you for your database credentials that you specified earlier.
Using SQL Server Object Explorer expand the Customers table and add some dummy data. Republish the WebAPI project and check that you are seeing the data in JSON format
Creating the Provider Hosted SharePoint Add In
Log into Office 365 and create a new Developer SharePoint site
Under Admin click on SharePoint
Under Site Collections Click on New -> Private Site Collection
Give the Site Collection a the title Customer Orders
Click on OK
Create a new project in the solution. Select Office/SharePoint -> Apps -> App for SharePoint
Give it a name of CustomerOrders.App
Click OK
Specify your new Site Collection URL
Choose Provider-hosted then click on Next
Choose ASP.Net Web Forms Application
Click Next
Select Use Windows Azure Access Control Service (for SharePoint cloud apps)
Click Finish
Once the Project is created (VS might as you to login to Office 365) add references to the following projects
The Visual Studio Engineering team have developed AngularJS intellisense. Download the js file here : https://raw.githubusercontent.com/jmbledsoe/angularjs-visualstudio-intellisense/master/src/Scripts/angular.intellisense.js
And then copy it to
(x86)\Microsoft Visual Studio 12.0\JavaScript\References folder
Using Package Manager type Install-Package AngularJS.Core to download the various angular scripts. Make sure to select the CustomerOrders.AppWeb project. Install Bootstrap by typing Install-Package bootstrap. Install the latest version of JQuery by typing Install-Package jquery
Open the AppManifest.xml file. On the Permissions tab, specify the Site Collection scope and the Read permission level.
Set the CustomerOrders.App project to be the start up project and then hit F5
When prompted click on Yes to trust the self-signed certificate
You will be prompted to log into your Office 365 account and then asked whether or not you trust the new Add In
Click on Trust It
You should see the title of your Add-In in the page.
Click on Deploy your web project
Create a new app in Azure. Give the Site name CustomersApp and this time we do not need a database
Click on Create
Click Publish
Open a browser and navigate to the SharePoint Developer Site Collection and add the following to the site URL:
/_layouts/15/AppRegNew.aspx
http://sitecollection/_layouts/15/AppRegNew.aspx
Generate the GUIDs and paste the values in the corresponding fields in the publishing profile
For the domain you are entering the domain of your azure web i.e http://yourapp.azurewebsites.net/
Click on Finish
Right click on the CustomerOrders.App project and click on publish
Click on Package the app
Add the Client ID you created above
Click on Finish
This will created an .app file
Open your app catalog site collection and upload the .app file you just created
Add the app to the developer site collection you created earlier.
Now if you click on the CusomterOrders.App you should see that the URL now points to your Azure site rather than local host
Log into your Azure Portal and select your CustomersREST web app
Click on Configure
Configure your authentication if you have not done so already
Go back and click on the CustomersApp web app and perform the same steps as above
Click on Configure
Click on Add application
Show All Apps and then select the CustomerREST application
Select the Access CustomersREST Delegated Permission
Add your account as an Owner of the App
Add the solution to GitHub
Right click on the Solution file and click on
Select Git and then click on OK
Install the third party Git commands
Click Install
Click Install
Click Install
Create the Angular.js App
Add the Angular JS Resource NuGet Package to the CustomerOrders.AppWebProject
Create a new folder in the project and call it Common
Add a new Javascript file to the Common folder and name it commmon.services.js
Add the following code to it
(function () {
“use strict”;angular
.module(“common.services”, [“ngResource”])
.constant(“appSettings”,
{
serverPath: “https://customersrest.azurewebsites.net”
});
})();
Use the URL to your WebAPI application in the serverPath listed above
Add a new Javascript file to the Common folder and name it customerResource.js
Add the following code to it
(function () {
“use strict”;angular
.module(“common.services”)
.factory(“customerResource”,
[“$resource”,
“appsettings”,
customerResource])function customerResource($resource, appSettings) {
return $resource(appSettings.serverPath + “/api/customers/:id”);
}}());
Create a new folder called app
Create a Javascript file and name it app.js
(function () {
“use strict”;var app = angular.module(“customerManagement”, [“common.services”]);
}());
Create a folder called customers in the app folder
Create a Javascript file called productListCtrl.js in the customers folder
Create an HTML file called productsListView.html in the customers folder
To be continued…
After the property configurations, we are ready with our Provider Hosted Application. Rebuild the solution to make sure everything is fine.