Create A Provider Hosted SharePoint Add-In With SQL Database in Azure

Prerequisites:

Microsoft Azure Account: If you do not have Azure account, open a free Azure account. It comes with one-month free trial.
Office 365 subscription
Visual Studio 2013/2015: You need to have Visual Studio installed on your machine.
Install the Azure SDK for Visual Studio 2015 or the Azure SDK for Visual Studio 2013.

Let’s start:

Create Azure Web application:

Login to Azure portal with your Azure Account.
Go to New, Web + Mobile, then Web App. Provide a unique name, and click Create.
It will take a moment to create app web and display details.
If you click on URL, it will open empty web app. We’ll use this app later to host add-in.

Create SQL Database in Azure

In Azure portal, Go to New, Data + Storage, then SQL Database. Provide DB Name.
In server configuration - You can either select an existing server OR select ‘Create a new server’. In this project I am using an existing Database Server I created earlier. Now select database, and then click Create.

For new server: Server name can be any unique name, user name & password – a new user name and password to connect to DB server, Location as south-east Asia.

Select source – ‘Blank database’, Pricing – leave it to default ‘Standard S0’, Resource Group – create new as ‘webappsample’. Click ‘Create’.
It will take few minutes to create the DB and it will be visible in SQL Databases where you can see the properties and manage settings.
Now we need to allow Client IP address to connect to SQL DB server. In left navigation, click on All resources, then select the SQL DB server we created, click all settings and select Firewall.
You can see client IP address there. Click on ‘Add Client IP’ link, IP will be added in the panel below. Now click Save.
We are all set here.

Create Tables in Database

Open Visual Studio 2013/2015. In server explorer, select SQL Server Object Explorer.
Click on Add SQL Server button.
Provide the SQL server name you’ve already created. Select ‘SQL Server Authentication’ and enter user name & password you provided while creating database server in Azure, click Connect.
Once connected, you can see the ‘mymuzicdb’ in explorer. Navigate to Table, right click and create new table.
Create table ‘Albums’ with 4 columns as given in snapshot. Use ‘Update’ button to update database.
Now table is ready to use.

Create provider hosted SharePoint add-in

Open Visual Studio, from file menu, click New, then Project.
Under templates, select Visual C#, Office/SharePoint, then SharePoint Add-in.
Provide an appropriate project name, in my case its ‘MyMuzicSharePointAddIn’, click OK.
Provide your SharePoint developer site URL for debugging, select ‘Provider-hosted’ radio button, click Next.
Login with your Azure credentials to connect to SharePoint site.
Select SharePoint online as add-in target, click NEXT.
Chose project type as ASP.NET MVC Web application, click NEXT.
For Authentication, chose Windows Azure Access, click Finish.
In the solution, you can find two projects,

1. MyMuzicSharePointAddIn – This is SharePoint add-in and will be deployed in SharePoint farm.

2. MyMuzicSharePointAddInWeb – This is web application and will be deployed in Azure. Build the solution.
Now, create a new ADO.NET entity model: Go to Project, Models, Right click on Models, Add -> New Item. Select ‘ADO.NET entity data model’ from Data, give some name, and click Add.
Select ‘EF Designer from Database’ on next screen, click Next.
On next screen, create a new data connection; click ‘New Connection’. Provide correct Azure database server name. Select ‘SQL Server authentication’ and provide valid user name & password. It will take few seconds to load databases in this server, once loaded, select mymuzicdb. Click Ok.
On next screen select ‘Yes Include the sensitive‘ radio button, click Next.
Keep the entity framework version as it is.
On next screen, select ‘Albums’ table and click finish
Entity Model will be created.
Build the project.
Now to create Controller; Go to Controllers folder, right click, Add-> Controller.

• Select ‘MVC 5 Controller with views using Entity Framework’ controller template, click Add.
On next screen- In Model class drop down, select Album. Select ‘mymuzicdbEntities’ from Data context drop down. Ensure all 3 views check boxes are selected, click Add.
It will add a controller class ‘AlbumsController’. Do not change anything in this class as it’s already generated by entity framework with all required actions.
Now we need to modify view to link. Navigate to Project, Views, then Shared. Open _Layout.cshtmal and add one <li> as given in snapshot below.
Build the project & we are ready to deploy.
You can hit F5 to deploy locally to test or can go ahead with Azure deployment.

Deployment to Azure

Deployment of SharePoint hosted add-in to azure involves mainly 4 steps,

Register add-in in O365.
Configure Azure web site where add-in web will be hosted.
Publish & add SharePoint add-in to O365
Publish app web to Azure.

Register SharePoint Add-in to Office 365

Log into your Office 365 developer site with admin account.
Now we need to open add-in registration page, to do this append /_layouts/15/appregnew.aspx to root url of your developer site, it will open registration page.
Click on Generate buttons for both Client ID & Client secret. Copy generated client ID & secret to a notepad, this will be used later.
Provide title as ‘MyMuzicSharePointAddIn’. Enter Azure web app we created earlier as App Domain (e.g. mymuzic.azurewebsites.net). In redirect URI, enter Azure App full url (e.g.- https://mymuzic.azurewebsites.net).
Click Create button.
The app identifier will be created with provided details, click OK.

Configure Azure web app

Login to Azure portal with your Azure Account.
Go to ‘App Services’, select your web app you created earlier.
Select All settings, Application Settings, scroll down to ‘App Settings’ section. Provide ClientId & ClientSecret as key value and click save.

Publish SharePoint add-in to Office 365

Go back to Visual studio solution.
Project MyMuzicSharePointAddIn, select AppManifest.xml, right click- view code.
Change StartPageurl; replace ~remoteAppUrl/ with azure web app url with https (e.g. - https://mymuzic.azurewebsites.net/).
Update ClientId with the client id we generated earlier. Save manifest file.
Save the add-in project, right click, publish. Click on "Package the add-in".
Provide web app URL and app’s client ID and click finish.
Package will be generated and will open in location folder.
Open your O365 SharePoint app store, URL will be like https://<your site>.sharepoint.com/sites/appstore.
In quick launch, select ‘Apps for SharePoint’ and then click ‘New’.
Browse to the package folder to choose package file, select add-in file ‘MyMuzicSharePointAddIn.app’, click OK. It will upload the add-in to app store.

Publish web app to Azure

Go back to Visual studio solution.
In solution explore, select MyMuzicSharePointAddInWebproject, right click -> Publish. It will open publish web dialog box.
Select ‘Microsoft Azure Web Apps’ as target. It will ask for credentials to connect to your Azure subscription.
Once connected, select web app you created earlier, click OK. Publishing profile will be loaded.
Click publish, it will take few minutes to publish to Azure.
Once it’s published, it will be opened in browser but you may get error as deployment is not yet complete.

Add this add-in to SharePoint

Go to your office 365 portal, open any of your SharePoint site as admin. From site settings, click on ‘Add an app’.
Select from your organization, you can find the newly deployed add-in there. Click on this to add.
Click "Trust it" to trust the add-in.
It will be added to your site, now click on this add-in to open it.

Note: You may get some exception when you open it for first time, in that case try after some time and refresh it.
Your SharePoint provider hosted add-in is now ready to use. You can add, edit, delete albums on albums page.

Hope this helps to understand the complete build & deployment cycle.