Create a Provider Hosted SharePoint Add In

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