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

Prerequisites:

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.

    Web App

  • It will take a moment to create app web and display details.

    moment to create app

  • If you click on URL, it will open empty web app. We’ll use this app later to host add-in.

    click on URL

Create SQL Database in Azure

  • In Azure portal, Go to New, Data + Storage, then SQL Database. Provide DB Name.

    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’.

    Select source

  • 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.

     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.

     Client IP address

  • You can see client IP address there. Click on ‘Add Client IP’ link, IP will be added in the panel below. Now click Save.

    Add Client IP

  • We are all set here.

Create Tables in Database

  • Open Visual Studio 2013/2015. In server explorer, select SQL Server Object Explorer.

    Server

  • Click on Add SQL Server button.

    SQL Server

  • 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.

    click Connect

  • Once connected, you can see the ‘mymuzicdb’ in explorer. Navigate to Table, right click and create new table.

    SQL Server Authentication

  • Create table ‘Albums’ with 4 columns as given in snapshot. Use ‘Update’ button to update database.

    mymuzicdb

  • 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.

    MyMuzicSharePointAddIn

  • Provide your SharePoint developer site URL for debugging, select ‘Provider-hosted’ radio button, click Next.

    Provider-hosted

  • Login with your Azure credentials to connect to SharePoint site.

    SharePoint site

  • Select SharePoint online as add-in target, click NEXT.

    SharePoint online

  • Chose project type as ASP.NET MVC Web application, click NEXT.

    Web application

  • For Authentication, chose Windows Azure Access, click Finish.

    Authentication

  • 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.

    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.

    ADO.NET entity data model

  • Select ‘EF Designer from Database’ on next screen, click Next.

    EF Designer from Database

  • 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.

    New Connection

  • On next screen select ‘Yes Include the sensitive‘ radio button, click Next.

    yes include the sensitive

  • Keep the entity framework version as it is.

    entity framwork

  • On next screen, select ‘Albums’ table and click finish

    Albums

  • Entity Model will be created.

    Entity Model

  • 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.

    MVC 5 Controller

  • 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.

    mymuzicdbEntities

  • 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.

    AlbumsController

  • 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.

    Layout

  • Build the project & we are ready to deploy.

  • You can hit F5 to deploy locally to test or can go ahead with Azure deployment.

    deploy locally to test

Deployment to Azure

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

  1. Register add-in in O365.
  2. Configure Azure web site where add-in web will be hosted.
  3. Publish & add SharePoint add-in to O365
  4. 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.

    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.

    provided details

  • 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.

    App Services

  • Select All settings, Application Settings, scroll down to ‘App Settings’ section. Provide ClientId & ClientSecret as key value and click save.

    App Settings

Publish SharePoint add-in to Office 365

  • Go back to Visual studio solution.

  • Project MyMuzicSharePointAddIn, select AppManifest.xml, right click- view code.

    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.

    ClientId

  • Save the add-in project, right click, publish. Click on "Package the add-in".

    package the add in

  • Provide web app URL and app’s client ID and click finish.

    click finish

  • Package will be generated and will open in location folder.

    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’.

    Apps for SharePoint

  • 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.

    package folder

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.

    solution explored

  • Select ‘Microsoft Azure Web Apps’ as target. It will ask for credentials to connect to your Azure subscription.

    Microsoft Azure Web Apps

  • Once connected, select web app you created earlier, click OK. Publishing profile will be loaded.

    Publishing profile

  • Click publish, it will take few minutes to publish to Azure.

    Click publish

  • 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’.

    Add an app

  • Select from your organization, you can find the newly deployed add-in there. Click on this to add.

    organization

  • Click "Trust it" to trust the add-in.

    Click Trust it

  • It will be added to your site, now click on this add-in to open it.

     your site

    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.

    albums page

    index

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

Read more articles on SharePoint:

Next Recommended Readings