Using a Windows Azure SQL Database with Autohosted apps for SharePoint

Article
08/13/2012

Update 6/10/2014: This post mentions autohosted apps for SharePoint. The autohosted apps for SharePoint preview program is now closed. For information about the closure of the program see Update on Autohosted Apps Preview Program. For instructions about how to convert an autohosted app to a provider-hosted app, see How to: Convert an autohosted app for SharePoint to a provider-hosted app.

This article is brought to you by Thomas Mechelke, Program Manager for SharePoint Developer Experience team. Thomas has been monitoring our new apps for Office and SharePoint forums and providing help on various topics. In today's post, Thomas will walk you through how to use a Windows Azure SQL Database with autohosted apps for SharePoint, as it is one of the most active thread on the forum. Thanks for reading !

Hi ! My name is Thomas Mechelke. I'm a Program Manager on the SharePoint Developer Experience team. I've been focused on making sure that apps for SharePoint can be installed, uninstalled, and updated safely across SharePoint, Windows Azure, and Windows Azure SQL Database. I have also been working closely with the Visual Studio team to make the tools for building apps for SharePoint great. In this blog post I'll walk you through the process for adding a very simple Windows Azure SQL Database and accessing it from an autohosted app for SharePoint. My goal is to help you through the required configuration steps quickly, so you can get to the fun part of building your app.

Getting started

In a previous post, Jay described the experience of creating a new autohosted app for SharePoint. That will be our starting point.

If you haven't already, create a new app for SharePoint 2013 project and accept all the defaults. Change the app name if you like. I called mine "Autohosted App with DB". Accepting the defaults creates a solution with two projects: the SharePoint project with a default icon and app manifest, and a web project with some basicboilerplate code.

Configuring the SQL Server project

Autohosted apps for SharePoint support the design and deployment of a data tier application (DACPAC for short) to Windows Azure SQL Database. There are several ways to create a DACPAC file. The premier tools for creating a DACPAC are the SQL Server Data Tools, which are part of Visual Studio 2012.

Let's add a SQL Server Database Project to our autohosted app:

Right-click the solution node in Solution Explorer, and then choose Add New Project.
Under the SQL Server node, find the SQL Server Database Project.
Name the project (I called it AutohostedAppDB), and then choose OK.

A few steps are necessary to set up the relationship between the SQL Server project and the app for SharePoint, and to make sure the database we design will run both on the local machine for debugging and in Windows Azure SQL Database.

First, we need to set the target platform for the SQL Server Database project. To do that, right-click the database project node, and then select SQL Azure as the target platform.

Next, we need to ensure that the database project will update the local instance of the database every time we debug our app. To do that, right-click the solution, and then choose Set Startup Projects. Then, choose Start as the action for your database project.

Now, build the app (right-click Solution Node and then choose Build). This generates a DACPAC file in the database output folder. In my case, the file is at /bin/Debug/projectname.dacpac.

Now we can link the DACPAC file with the app for SharePoint project by setting the SQL Package property.

Setting the SQL Package property ensures that whenever the SharePoint app is packaged for deployment to a SharePoint site, the DACPAC file is included and deployed to Windows Azure SQL Database, which is connected to the SharePoint farm.

This was the hard part. Now we can move into building the actual database and data access code.

Building the database

SQL Server Data Tools adds a new view to Visual Studio called SQL Server Object Explorer. If this view doesn't show up in your Visual Studio layout (usually as a tab next to Solution Explorer), you can activate it from the View menu. The view shows the local database generated from your SQL Server project under the node for (localdb)\YourProjectName.

This view is very helpful during debugging because it provides a simple way to get at the properties of various database objects and provides access to the data in tables.

Adding a table

For the purposes of this walkthrough, we'll keep it simple and just add one table:

Right-click the database project, and then add a table named Messages.
Add a column of type nvarchar(50) to hold messages.
Select the Id column, and then change the Is Identity property to be true.

After this is done, the table should look like this:

Great. Now we have a database and a table. Let's add some data.

To do that, we'll use a feature of data-tier applications called Post Deployment Scripts. These scripts are executed after the schema of the data-tier application has been deployed. They can be used to populate look up tables and sample data. So that's what we'll do.

Add a script to the database project. That brings up a dialog box with several script options. Select Post Deployment Script, and then choose Add.

Use the script editor to add the following two lines:

delete from Messages

insert into Messages values ('Hello World!')

The delete ensures the table is empty whenever the script is run. For a production app, you'll want to be careful not to wipe out data that may have been entered by the end user.

Then we add the "Hello World!" message. That's it.

Configuring the web app for data access

After all this work, when we run the app we still see the same behavior as when we first created the project. Let's change that. The app for SharePoint knows about the database and will deploy it when required. The web app, however, does not yet know the database exists.

To change that we need to add a line to the web.config file to hold the connection string. For that we are using a property in the <appSettings> section named SqlAzureConnectionString.

To add the property, create a key value pair in the <appSettings> section of the web.config file in your web app:

The SqlAzureConnectionString property is special in that its value is set by SharePoint during app installation. So, as long as your web app always gets its connections string from this property, it will work whether it's installed on a local machine or in Office 365.

You may wonder why the connection string for the app is not stored in the <connectionStrings> section. We implemented it that way in the preview because we already know the implementation will change for the final release, to support geo-distributed disaster recovery (GeoDR) for app databases. In GeoDR, there will always be two synchronized copies of the database in different geographies. This requires the management of two connection strings, one for the active database and one for the backup. Managing those two strings is non-trivial and we don't want to require every app to implement the correct logic to deal with failovers. So, in the final design, SharePoint will provide an API to retrieve the current connection string and hide most of the complexity of GeoDR from the app.

I'll structure the sample code for the web app in such a way that it should be very easy to switch to the new approach when the GeoDR API is ready.

Writing the data access code

At last, the app is ready to work with the database. Let's write some data access code.

First let's write a few helper functions that set up the pattern to prepare for GeoDR in the future.

GetActiveSqlConnection()

GetActiveSqlConnection is the method to use anywhere in the app where you need a SqlConnection to the app database. When the GeoDR API becomes available, it will wrap it. For now, it will just get the current connection string from web.config and create a SqlConnection object:

// Create SqlConnection.

protected SqlConnection GetActiveSqlConnection()

{

return new SqlConnection(GetCurrentConnectionString());

}

GetCurrentConnectionString()

GetCurrentConnectionString retrieves the connection string from web.config and returns it as a string.

// Retrieve authoritative connection string.

protected string GetCurrentConnectionString()

{

return WebConfigurationManager.AppSettings["SqlAzureConnectionString"];

}

As with all statements about the future, things are subject to change—but this approach can help to protect you from making false assumptions about the reliability of the connection string in web.config.

With that, we are squarely in the realm of standard ADO.NET data access programming.

Add this code to the Page_Load() event to retrieve and display data from the app database:

// Display the current connection string (don't do this in production).

Response.Write("<h2>Database Server</h2>");

Response.Write("<p>" + GetDBServer() + "</p>");

// Display the query results.

Response.Write("<h2>SQL Data</h2>");

using (SqlConnection conn = GetActiveSqlConnection())

{

using (SqlCommand cmd = conn.CreateCommand())

{

conn.Open();

cmd.CommandText = "select * from Messages";

using (SqlDataReader reader = cmd.ExecuteReader())

{

while (reader.Read())

{

Response.Write("<p>" + reader["Message"].ToString() + "</p>");

}

}

We are done. This should run. Let's hit F5 to see what happens.

It should look something like this. Note that the Database Server name should match your connection string in web.config.

Now for the real test. Right-click the SharePoint project and choose Deploy. Your results should be similar to the following image.

The Database Server name will vary, but the output from the app should not.

Using Entity Framework

If you prefer working with the Entity Framework, you can generate an entity model from the database and easily create an Entity Framework connection string from the one provided by GetCurrentConnectionString() . Use code like this:

// Get Entity Framework connection string.

protected string GetEntityFrameworkConnectionString()

{

EntityConnectionStringBuilder efBuilder =

new EntityConnectionStringBuilder(GetCurrentConnectionString());

return efBuilder.ConnectionString;

}

We need your feedback

I hope this post helps you get working on the next cool app with SharePoint, ASP.NET, and SQL. We'd love to hear your feedback about where you want us to take the platform and the tools to enable you to build great apps for SharePoint and Office.

Leave us your comments!