How to Connect a SharePoint Online Provider-Hosted App to Azure SQL Server (Part 2)

This is the second part in a series for creating an app capable of utilizing data on both SharePoint and Azure. This guide will walk you through creating a simple application that will store data on Azure SQL Server and where users will interface with the app on a SharePoint page. The result will be a functional vacation request form.

Before starting this tutorial, please refer to my previous blog to setup the connection between SharePoint and Azure by creating a Provider-hosted app.

Requirements

• Microsoft Azure Subscription
• Office 365 Tenant
• SharePoint Online Site
• SharePoint Online App Catalog
• Visual Studio (2015 used in this tutorial)
• SQL Server Management Studio

Create an Azure SQL Server Database

When creating a new application, I typically start with the model. To create a new SQL Server Database, perform the following steps:

1. Navigate to the Azure portal.
2. Click Create a resource --> databases --> SQL Databases.
3. Complete the form and click Create. Your SQL Database will now be provisioned in the background.

If you have any questions about the setup options, refer to the OuickStart tutorial.

Connect to the Azure SQL Database

The next step in our model creation process is to connect to our new database. Perform the following to connect to the database:

1.  Launch SQL Server Management Studio (SSMS)
2. Server type: Database Engine
3. Server name: YourServerName.database.windows.net
4. Authentication: SQL Server Authentication
5. Provide the admin username and password associated with the SQL server specified in the previous section.
6. Click Connect

If you receive the message “Cannot open server ‘xxxxxxxx’ requested by the login. Client with IP address 'xxx.xxx.xxx.xxx' is not allowed to access the server” follow these steps:

1. On the Azure portal, navigate to the specified SQL Server
2. Under Security, click Firewalls and Virtual Networks
3. Add a new rule with the IP address from the error message
4. Click Save
5. Attempt to connect to the server again through SSMS

Upon connecting to your SQL Server, you should first create a user that will be used by our Provider-Hosted App.

1. In the Object Explorer, first expand the SQL Server and then expand Security
2. Right-click Logins and select New Login...
3. On the new window, replace the text with:

   	USE [ma  ster]
   	GO
   	CREATE LOGIN [vacationuser] WITH PASSWORD = 'YourPasswordGoesHere' 
   	GO

4. Click Execute to run the command.
5. Ensure the message windown displays Command(s) completed successfully.
6. In the Object Explorer, expand the Databases folder.
7. Right click your database and select New Query.
8. On the new window, replace the text with:

   	CREATE USER [vacationuser]  FOR LOGIN  [vacationuser]
   	GO 
   	EXEC sp_addrolemember 'db_datareader', 'vacationuser'
   	GO 
   	EXEC sp_addrolemember 'db_datawriter', 'vacationuser'
   	GO 

9. Click Execute to run the command.
10. Ensure the message window displays Command(s) completed successfully.

Now we have a user with read and write access to our new database. Later we will configure our app to connect to this database by utilizing this login.

Create the Vacation Request Table

Next, we will configure the table to hold our vacation requests. Similarly to the section above, we will use SSMS to create the table. If you’ve closed the window from the above step, right-click Your database and select New Query. Paste the following into the window:

CREATE TABLE [dbo].[VacationRequest](  
		[id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, 
      	[username] [nvarchar](max) NOT NULL,  
        [startdate] [datetime] NOT NULL,
    	[enddate] [datetime] NOT NULL 
) 
GO  

Click execute and verify the message Command(s) completed successfully is displayed. If you refresh the object explorer pane, you should see the below table definition:

SharePoint App Enhancements

In the previous tutorial in this series, we created a simple ‘hello world’ app. We’ll now make improvements to create a user interface. The first step will be to setup our connection to the database (model).

1. On the Web project, right-click the Models folder, and select Add --> New Item…
2. Expand the Visual C# node, select the Data node, and choose ADO.NET Entity Data Model.
3. Give your model a name and click Add.
4. Choose EF Designer from database --> Next.
5. Click New Connection…
6. Select Microsoft SQL Server, then select Continue.
7. Enter your server name mydb.database.windows.net.
8. Set Authentication to SQL Server Authentication.
9. Enter the credentials for the vacationuser we created earlier.
10. Check Save My Password.
11. Click Test Connection, and verify Test Connection Succeeded is displayed.
12. Under Connect to a Database, select our Vacation Request database.
13. Click OK.
14. Select Yes, include the sensitive data in the connection string.
15. Click Next.
16. Select your preferred entity framework version, then click Next.
17. For Which database objects do you want to include in your model?, expand Tables, then dbo, and check for VacationRequest.
18. Click Finish.

The next step is to setup our view with a form to submit vacation request data. To accomplish this, open the solution in Visual Studio and edit the file WebProject/Views/ClientWebPart1/Index.cshtml.

Replace the body content with:

	<h2>Vacation Request Form</h2> 
	<form method="post"> 
		<label for="startdate">Start Date</label> 
		<input type="date" name="StartDate" />
		<label for="enddate">End Date</label>
		<input type="date" name="EndDate" />        
		<input type="submit" value="Submit"/>    
	</form> 

When the user hits submit, this will post the form to the web server. We can handle this request in the controller WebProject/Controllers/ClientWebPart1Controller.cs. Replace the file with the following: 

using Microsoft.SharePoint.Client;
using SharePointAddIn1Web.Models;
using System;
using System.Web.Mvc;

namespace SharePointAddIn1Web.Controllers
{
	public class ClientWebPart1Controller : Controller
	{
		public ActionResult Index()
		{
			return View();
		}
        
		[HttpPost]
		[SharePointContextFilter]
		public ActionResult Index(VacationRequest vacationRequest)
		{
			user spUser = null;
            
			var spContext = 
SharePointContextProvider.Current.GetSharePointContext(HttpContext);
        
			using (var clientContext = spContext.CreateUserClientContextForSPHost())			{
				if (clientContext != null)
				{
					spUser = clientContext.Web.CurrectUser;
                    
					clientContext.Load(spUser, user => user.Title);
                    
					clientContext.ExecuteQuery();
				}
			}
			using (var vacationModel = new VacationRequestEntities())
			{
				vacationModel.VacationRequests.Add(new Models.VacationRequest
				{
					username = spUser.Title,
					startdate = vacationRequest.StartDate,
					enddate = vacatonRequest.Endate
				});
				vacationModel.SaveChanges();
			}
			return View();
		}
        
    }
        
	public class VacationRequest 
	{
		public DateTime StartDate { get; set; }
		public DateTime EndDate { get; set; }
	}
} 

Get the Publish Profile

After your new app has been provisioned, the next step is to obtain the publish profile. We will use this to deploy our web application changes.

Select your newly created web app

In the Overview tab, click Get Publish Profile. This should download a .PublishSettings file to your computer.

Deploy application 

Now that we’re finished coding our application, it’s time to deploy our web application to Azure. To accomplish this, perform the following: 

Right-click SharePoint Add-in --> Publish.

Click Deploy your web project.

A dialog should open on the Preview tab. Change to the Connection tab and verify the connection works by clicking Validate Connection.

Ensure the other data is entered correctly and click Publish.

The web project may take a few minutes to deploy. You should see details about the deployment progress in the output window.  

Application testing 

Now we can navigate back to the SharePoint page created in the previous tutorial. The UI should now display our form and look like this:

Enter a start and end date, then click submit. 

Now, if we navigate back to SSMS, we can query the table for our new record. Right-click dbo.VacationRequest, then click Select Top 200 Rows. A new window should appear with the following:

Conclusion 

Our provider hosted app is now capable of writing to an Azure SQL Server Database. From here, you can create additional controllers, views, and webparts to extend the capabilities of your provider hosted app. Some examples could include adding a dashboard to display vacation requests, or you could establish an approval process for administrators to view and approve vacation requests.

Find out more about DMC's SharePoint and Microsoft Consulting Services.