2 out of 3 rated this helpful - Rate this topic

How to: Create an autohosted app that includes a SQL Server database

apps for Office

Published: September 04, 2012

How to

Create an autohosted app for SharePoint that includes a Windows Azure SQL Database by using the Office Developer Tools for Visual Studio 2012.

Applies to: apps for SharePoint | Office 365 | SharePoint Foundation 2013 | SharePoint Server 2013

In this article
Prerequisites for creating an autohosted app for SharePoint that includes a Windows Azure SQL Database
Develop an autohosted app that includes an ASP.NET web application and a SQL Azure database
Install the autohosted app
Record the database schema version in your database
Next steps
Additional resources

An autohosted app for SharePoint contains at least one Windows Azure Web Site that can be started from a SharePoint Online host web, and may also include SharePoint components on an app web or a Windows Azure SQL Database. The continuing example in this topic includes a Windows Azure SQL Database, but does not include an app web.

Note
The infrastructure for autohosted apps will remain in preview status for some time after SharePoint 2013 releases. Autohosted apps (which includes all apps that depend on Access) will not be accepted by the Office Store during this preview phase. We will update dev.office.com with information about autohosted apps as it becomes available.

Development of an autohosted app is basically the same as development of a provider-hosted app that includes a remote ASP.NET web application or a remote Windows Azure SQL Database. The differences between the two types are in how the remote components are deployed. By using an autohosted app, developers do not have to deploy the remote components separately, and they do not have to design and maintain a system of tenant isolation. These tasks are performed automatically by the SharePoint 2013 installation infrastructure. The differences in development are that some markup in the app manifest is different for autohosted apps and no custom installation logic is required.

Prerequisites for creating an autohosted app for SharePoint that includes a Windows Azure SQL Database

Visual Studio 2012.
Office Developer Tools for Visual Studio 2012.
A SharePoint Online for developers account for testing and debugging. For more information, see Sign up for an Office 365 Developer Site. Autohosted apps in SharePoint 2013 can be installed only on SharePoint Online websites. This may remain true for some time after the release of SharePoint 2013.

Note
For guidance on how to set up a development environment that fits your needs, see Start building apps for Office and SharePoint.

Core concepts to know for creating an autohosted app that includes a SQL Azure database

Before you create an autohosted app that includes a Windows Azure SQL Database, you should first be familiar with the topic How to: Create a basic autohosted app for SharePoint. This includes the section Core concepts to know for creating an autohosted app. You should also be familiar with the basics of creating a Windows Azure SQL Database project in Visual Studio 2012. For more information, see Creating and Managing Databases and Data-tier Applications in Visual Studio.

Develop an autohosted app that includes an ASP.NET web application and a SQL Azure database

In the procedures of this section, you create an app for SharePoint that includes a remote ASP.NET web application and a Windows Azure SQL Database. (Except where noted, the steps are nearly identical to how you would develop the same app for SharePoint as a provider-hosted app.)

To set up the Visual Studio 2012 solution and its elements

In Visual Studio 2012, create an App for SharePoint 2013 project from the Office/SharePoint | Apps node (under either C# or Visual Basic) in the templates tree of the New Project wizard. Choose the Autohosted hosting option. In the continuing example of this article, C# is used as the language and FavoriteURLsApp is the project name.
Choose Finish in the wizard. A lot of configuration is performed when the solution opens. See the topic How to: Create a basic autohosted app for SharePoint for information about these automatic configuration steps.
Open the AppManifest.xml file in the manifest designer. The Title element has the project name as its default value. Replace it with something more friendly because this is the name of the app that users see in the UI; for example, Favorite URLs.
Specify a Name for the app. This is an internal name that must contain only ASCII characters and must contain no spaces; for example, FavoriteURLsApp.
Open the web.config file of the web application project and add the element <customErrors mode="Off"/> to the system.web element.

To create the app for SharePoint and ASP.NET web application

Open the AppManifest.xml file in the manifest designer and add a permission request for each permission to the SharePoint host web that the remote web application will need. In the continuing example of this topic, the web application does not call back to the host web. Therefore, no permission requests are needed for this example.

Develop the web application as you would any other ASP.NET web application: Add markup to the Default.aspx page, add code-behind as needed, add JavaScript as needed, add other files as needed, and so on. For the continuing example in this article, take the following steps.

Note
When you add a reference to an assembly to your web application project in Visual Studio, set the Copy Local property of the assembly to True, unless you know that the assembly is already installed on the web server, or you can ensure that it is installed before you deploy your app. The .NET Framework is installed on Windows Azure Web Roles and Windows Azure Web Sites (which are used by autohosted apps for SharePoint). But the SharePoint 2013 client assemblies and the various Microsoft managed code extensions and foundations are not installed. Office Developer Tools for Visual Studio 2012 automatically adds references to some assemblies commonly used in apps for SharePoint and sets the Copy Local property.

Note

When you add a reference to an assembly to your web application project in Visual Studio, set the Copy Local property of the assembly to True, unless you know that the assembly is already installed on the web server, or you can ensure that it is installed before you deploy your app. The .NET Framework is installed on Windows Azure Web Roles and Windows Azure Web Sites (which are used by autohosted apps for SharePoint). But the SharePoint 2013 client assemblies and the various Microsoft managed code extensions and foundations are not installed. Office Developer Tools for Visual Studio 2012 automatically adds references to some assemblies commonly used in apps for SharePoint and sets the Copy Local property.

Open the Default.aspx file and set the value of the Title element to Favorite URLs.

Replace the contents of the body element with the following markup and save the file.

HTML

<body>
    <!-- 
        A bulleted list the user's favorite URLs, which are retrieved from the SQL Azure database. 
        It is toggled to visible only when it has data.
    -->    
    <div runat="server" id="favoritesList" visible="false">
        <asp:Label ID="lblUser" runat="server" Text="" Font-Size="X-Large"></asp:Label>
        <ul>
            <li><a runat="server" id="url1"></a></li>
            <li><a runat="server" id="url2"></a></li>
            <li><a runat="server" id="url3"></a></li>
            <li><a runat="server" id="url4"></a></li>
        </ul>
        <asp:Literal ID="Literal14" runat="server" Text="<br /><br />"></asp:Literal>
    </div>

    <!-- 
        A form in which the user enters a name, which is then looked up in the SQL Azure database.
    -->
    <form id="frmFavorites" runat="server">
        <asp:Label ID="lblShowFavoritesPrompt" runat="server" 
         Text="Please enter your name:"></asp:Label>
        <asp:Literal ID="Literal1" runat="server" Text="<br />"></asp:Literal>
        <asp:TextBox ID="txtBoxAppUserName" runat="server"></asp:TextBox>
        <asp:Literal ID="Literal2" runat="server" Text="<br /><br />"></asp:Literal>
        <asp:Button ID="btnShowFavorites" runat="server" 
         Text="Show Favorite URLs" OnClick="btnShowFavorites_Click" />
    </form>

    <!-- 
        A form in which the user enters a name and up to four favorite URLs. 
        This data is then added to the SQL Azure database.
        The form is toggled visible if the user enters an unknown name in the Show Favorites form.
    -->
    <form id="frmRegister" runat="server" visible="false">
        <asp:Label ID="lblRegisterPrompt" runat="server" ForeColor="Red" 
         Text="Sorry, you don't seem to be a member. Please register."></asp:Label>
        <asp:Literal ID="Literal3" runat="server" Text="<br /><br />"></asp:Literal>
        <asp:Label ID="lblNewName" runat="server" Text="Name:"></asp:Label>
        <asp:Literal ID="Literal10" runat="server" Text="<br /><br />"></asp:Literal>
        <asp:TextBox ID="txtBoxNewUserName" runat="server"></asp:TextBox>
        <asp:Label ID="lblForgotNamePrompt" runat="server" ForeColor="Red" 
         Text="Please enter a name before you press Register." Visible="false"></asp:Label>
        <asp:Literal ID="Literal4" runat="server" Text="<br /><br />"></asp:Literal>
        <asp:Label ID="lblFavoritesPrompt" runat="server" 
         Text="Enter up to four favorite URLs into these boxes. Example: www.microsoft.com"></asp:Label>
        <asp:Literal ID="Literal9" runat="server" Text="<br />"></asp:Literal>
        <asp:TextBox ID="txtBoxFavorite1" runat="server"></asp:TextBox>
        <asp:Literal ID="Literal5" runat="server" Text="<br />"></asp:Literal>
        <asp:TextBox ID="txtBoxFavorite2" runat="server"></asp:TextBox>
        <asp:Literal ID="Literal6" runat="server" Text="<br />"></asp:Literal>
        <asp:TextBox ID="txtBoxFavorite3" runat="server"></asp:TextBox>
        <asp:Literal ID="Literal7" runat="server" Text="<br />"></asp:Literal>
        <asp:TextBox ID="txtBoxFavorite4" runat="server"></asp:TextBox>
        <asp:Literal ID="Literal8" runat="server" Text="<br />"></asp:Literal>
        <asp:Button ID="btnRegister" runat="server" Text="Register" OnClick="btnRegister_Click" />
    </form>
</body>

Open the Default.aspx.cs file and delete the autogenerated Page_Load method.

Add the following using statements to the file.

using System.Data.SqlClient;
using System.Web.Configuration;
using System.Text;

Add basic methods to the Default page class that the remote web application can use to connect to the SQL Azure database that you create in a later step. Two facts must be considered.
- When you are designing, testing, and debugging your app for SharePoint, the SQL Azure database is hosted on your development computer. After the app is installed to a SharePoint Online website, the database is hosted in a hidden SQL Azure account associated with the SharePoint Online tenancy. You cannot know at design time what the connection string for this SQL Azure account will be.
- In the preview period for autohosted apps for SharePoint, the connection information is stored in a setting recorded in the appsettings section (not the connectionStrings section) of the web.config file. This may change in the final release version of the autohosting infrastructure.
To allow for these facts and to minimize changes that you may have to make to your autohosted apps after the final release of the autohosting system, we recommend that you nest the connection logic to the database in the following two methods which you should add to the Default page class.
C#
Copy
protected SqlConnection GetActiveSqlConnection() { return new SqlConnection(GetCurrentConnectionString()); } protected string GetCurrentConnectionString() { return WebConfigurationManager.AppSettings["SqlAzureConnectionString"]; }
You add the setting to the web.config file in a later step.

Add the following event handler for the Show Favorite URLs button to the Default class. This handler tests for a value user name, opens a connection to the database, and sends the name.

protected void btnShowFavorites_Click(object sender, EventArgs e)
{
    // Handle case where user presses the button without first entering a name.
    if (String.IsNullOrEmpty(txtBoxAppUserName.Text))
    {
        lblShowFavoritesPrompt.Text = "Please enter your user name and choose Show Favorite URLs.";
        lblShowFavoritesPrompt.ForeColor = System.Drawing.Color.Red;
        return;
    }
    
    using (SqlConnection conn = GetActiveSqlConnection())
    using (SqlCommand cmd = conn.CreateCommand())
    {
        conn.Open();
        DisplayFavorites(cmd, txtBoxAppUserName.Text);

    }//dispose conn and cmd
}

Add the following method to define and execute the SQL command, and then display the retrieved data.

private void DisplayFavorites(SqlCommand cmd, String appUserName)
{
    cmd.CommandText = 
          String.Format("SELECT * FROM UserFavorites WHERE AppUser='{0}'", appUserName);

    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                RenderDataAsHtmlList(reader);
            }

            // Show the user favorites retrieved from the database.
            SwitchToForm("frmFavorites");
        }
        else // The user is unknown, so show the registration form.
        {
            SwitchToForm("frmRegister");
        }
    }
}

Add the following method to fill the favorites list with data and make it visible.

private void RenderDataAsHtmlList(SqlDataReader reader)
{
    lblUser.Text = reader["AppUser"].ToString() + "'s Favorite URLs:";

    url1.HRef = "http://" + reader["Favorite1"].ToString();
    url1.InnerText = reader["Favorite1"].ToString();
    url2.HRef = "http://" + reader["Favorite2"].ToString();
    url2.InnerText = reader["Favorite2"].ToString();
    url3.HRef = "http://" + reader["Favorite3"].ToString();
    url3.InnerText = reader["Favorite3"].ToString();
    url4.HRef = "http://" + reader["Favorite4"].ToString();
    url4.InnerText = reader["Favorite4"].ToString();

    favoritesList.Visible = true;
}

Add the following method to switch between the Show Favorites form and the Registration form.

private void SwitchToForm(String formName)
{
    if (formName == "frmFavorites")
    {
        frmRegister.Visible = false;  

        // Blank out data that may be present from the last time the form was visible.
        txtBoxAppUserName.Text = "";  
        lblShowFavoritesPrompt.Text = 
            "Enter another user name to see his or her favorites:";

        frmFavorites.Visible = true; 
    }
    else
    {
        frmFavorites.Visible = false;
        favoritesList.Visible = false;

        // Blank out data that may be present from the last time the form was visible.
        txtBoxNewUserName.Text = "";
        txtBoxFavorite1.Text = "";
        txtBoxFavorite2.Text = "";
        txtBoxFavorite3.Text = "";
        txtBoxFavorite4.Text = "";

        frmRegister.Visible = true; 
    }
}

Add the handler for the Register button. This method adds the new user's name and favorites to the database.

protected void btnRegister_Click(object sender, EventArgs e)
{
    // Handle case where the user presses the Register button when the 
    // name textbox is empty.
    if (String.IsNullOrEmpty(txtBoxNewUserName.Text))
    {
        lblForgotNamePrompt.Visible = true;
        return;
    }

    lblForgotNamePrompt.Visible = false;

    // Create a connection to the database, and then create and execute
    // the SQL command.
    using (SqlConnection conn = GetActiveSqlConnection())
    using (SqlCommand cmd = conn.CreateCommand())
    {
        conn.Open();

        cmd.CommandText = 
         String.Format("INSERT INTO UserFavorites VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')", 
        txtBoxNewUserName.Text, 
        txtBoxFavorite1.Text, 
        txtBoxFavorite2.Text, 
        txtBoxFavorite3.Text, 
        txtBoxFavorite4.Text);

        cmd.ExecuteNonQuery();

        DisplayFavorites(cmd, txtBoxNewUserName.Text);

    }//dispose conn and cmd
}

To create the SQL Azure database

In Solution Explorer, open the shortcut menu for the top (solution) node and choose Add, New Project.
In the New Project wizard, browse to SQL Server and choose the SQL Server Database Project. Give the project a name and choose the OK button. For the continuing example, use FavoriteURLsDB.
In Solution Explorer, open the shortcut menu for the new project and choose Properties.
In the Properties pane, choose the Settings tab. In the Target Platform drop-down list, to choose SQL Azure.
In Solution Explorer, open the shortcut menu for the top (solution) node and then choose Set Startup Projects.
In the dialog box that opens, ensure that the Multiple Startup Projects option button is enabled and set the Action for the database project to Start.

Create the database as you would any other SQL Azure database project. For the continuing example, follow these steps:

In Solution Explorer, open the shortcut menu for the database project and choose Add, Table.
Name the table UserFavorites and then choose Add.
By default, an ID column is added to the table. Choose this column in the table designer, and then in the Properties pane expand the Identity Specification node and set the Is Identity property to true.
Use the table designer to add a column called AppUser and set its data type to nvarchar(50). (Do not use the reserved term "user" as a column name in SQL Server or SQL Azure databases.)
Add four more columns to the table, named Favorite1, Favorite2, Favorite3, and Favorite4. Set the data type of them all to nvarchar(50). Figure 1 shows how the table should look in the table designer.

Figure 1. UserFavorites table
In Solution Explorer, open the shortcut menu for the database project and choose Add, Script.
In the Add New Item dialog box, choose Script (Not in build), give the script an appropriate name, and then choose Add.

In the script editor, add the following code. This script runs every time that you press F5 and every time that the app for SharePoint is installed on a website. The first line empties the table and the next two add sample data to it.

SQL

delete from UserFavorites
insert into UserFavorites values ('Bob', 'www.microsoft.com', 'msdn.microsoft.com', -
  'www.msn.com', 'www.bing.com')
insert into UserFavorites values ('Mary', 'www.bing.com', 'www.microsoft.com/en-us/download', -
  'msdn.microsoft.com', 'www.msn.com')

Note
In a production app for SharePoint where sample data is not needed, you would remove these lines just before publishing the version that you release. However, do not remove the script entirely. To learn why, see Record the database schema version in your database later in this article.

To tell the three projects about one another

Your solution has three projects. To produce an installable app package, each project must have some knowledge of the other two.
- The app for SharePoint project must know about the remote web application project so that it includes the web application's Web Deploy package inside the app package.
- The app for SharePoint project must know about the SQL Azure database project so that it includes the database's DACPAC inside the app package.
- The remote web application must know the connection string for the SQL Azure database.
The first of these tasks was done automatically by Office Developer Tools for Visual Studio 2012 when you created the autohosted app for SharePoint project. The Web Project property of the app for SharePoint project is set to the name of the web application project.
Take the following steps to tell the app for SharePoint about the SQL Azure database:
1. In Solution Explorer, open the shortcut menu for the database project and choose Build. This creates a DACPAC file that is named <project_name>.dacpac in the /bin/Debug folder of the database project.
2. Link the database project to the app for SharePoint project by choosing the app for SharePoint in Solution Explorer and setting the SQL Package property to the name of the database project.
Take the following steps to tell the web application about the connection string for the database.
1. Open the web.config file of the web application project and add the following markup to the appsettings section (not the connectionStrings section):
  XML
  Copy
  <add key="SqlAzureConnectionString" value="Data Source=<instance_name>;Initial Catalog=<database_name>;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False" />
2. The Data Source value (the <instance_name> placeholder) has to be set to the local instance that was created by Visual Studio when you added the database project to the solution. This is the SQL Server instance that is used when you choose the F5 key. (This value is set, by the autohosting infrastructure when the app is installed to a SharePoint website, to the SQL Azure instance that is associated with the tenancy.) To find the local instance value, use the View menu to open the SQL Server Object Explorer window as shown in Figure 2. In this case the instance name is (localdb)\Projects.
  
  Figure 2: SQL Server Object Explorer
3. Replace the placeholder <database_name> with the name of the database. In the continuing example, this is FavoriteURlsDB.

To test and debug the app for SharePoint

To test the app for SharePoint, choose the F5 key in Visual Studio 2012. The web application is deployed to IIS Express at localhost, the SQL Azure database is installed to the local SQL Server instance, and the app for SharePoint is installed to the SharePoint Online for developers website that you specified as the test website when you created the app for SharePoint project. The first time that you choose F5, you are prompted by SharePoint to grant the permissions that the app for SharePoint requests. The Site Contents page of your target SharePoint website opens, and you see the new app listed there.
Choose the app for SharePoint, and the remote web application opens to the page that is specified as the Start Page in the AppManifest.xml file. Use the web application as needed to verify that it is working. For the continuing example, take the following steps:
1. Choose the Show Favorite URLs button without a entering a name to see what kind of error that you get.
2. Enter Mary or Bob in the Name box of the start page and choose the Show Favorite URLs button to display a list of URLs.
3. Enter a different name to see how unknown users are prompted to register.
4. Register the new user and add some URLs to the form. Then choose Register to see the new user's favorites displayed.
5. Close the app and then relaunch it. Enter the same new user's name again and choose the Show Favorite URLs button to verify that the new user was added permanently to the database.

Install the autohosted app

When you have completed local debugging, the app for SharePoint can now be uploaded to the app catalog of a SharePoint Online tenancy. For additional debugging, you can deploy the app directly from Visual Studio 2012 to a SharePoint Online website, if it was created by using the Developer Site site definition. To do this, open the shortcut menu of the app project in Solution Explorer and choose Deploy.

Tenant administrators can install the app on their SharePoint Online tenancy by using the following procedure.

To install the autohosted app

Log on to Microsoft SharePoint Online as a tenant administrator.
On the Admin drop-down menu at the top of the page, choose SharePoint.
On the SharePoint Administration Center page, choose apps, App Catalog. If you have not already created an app catalog site collection, you are prompted to create one.
After the app catalog site collection is created, open it, and choose Apps for SharePoint
On the App Catalog page, choose the new item link.
On the Add a document form, browse to your app for SharePoint package and choose the OK button. An item property form opens.
Fill out the form as needed and choose Save. The app for SharePoint is saved in the catalog.
Navigate to any website in the tenancy and choose Site Contents to open the Site Contents page.
Choose Add an App, and on the Add an App page, find the app. If there are too many to scroll through, you can enter any part of the app title (that you entered for Title in the AppManifest.xml file) into the search box.
When you find the app, choose the Details link below it, and then on the app details page that opens, choose Add It.
You are prompted grant permissions to the app. Choose Trust It in the dialog box.
The Site Contents page opens, and the app is listed. For a short time, a message below the title indicates that it is being added. When this message disappears, you can choose the app title to start the app. (You may have to refresh the page before the message disappears.) In the continuing example of this article, because the Start Page was set to the URL of the remote web application's Default.aspx page, that page opens.

Record the database schema version in your database

You may want to update your app for SharePoint in the future, and that update might include changes to the database. Because you can't access the database in an autohosted app with either SQL Server Management Studio or the Windows Azure SQL Database portal, you will need to include the update logic in a script that is part of the DACPAC. The update logic can be a lot simpler if you have recorded the version of the database somewhere in the database itself, so we recommend that you do that in the first version of the app. The following procedure shows one way to do that. Consider giving the schema the same version number of its parent app for SharePoint.

To record the database schema version in the database

In Solution Explorer, right-click the database project, and choose Add, Table. Give it an appropriate name, such as SchemaMetadata.
When the table designer opens, add a column named SchemaVersion. Set the data type to nvarchar(19). Configure it to allow null values.
If there is already a data script in your database project, add the following as the top line of the script.
Copy
INSERT INTO SchemaMetadata VALUES ('1.0.0.0')
If there isn't a data script in your project, in Solution Explorer, right-click the database project and choose Add, Script.
In the Add Item dialog box, choose Script (Not in build). Give it an appropriate name and select Add.
Select the app for SharePoint project in Solution Explorer, and then set the SQL Data Script property of the project to the path to the script you just added.
Add the line in step 3 as the first line of the script.

For more information about updating apps, and especially autohosted apps that include a Windows Azure SQL Database, see App for SharePoint update process, How to: Update apps for SharePoint, and http://msdn.microsoft.com/en-us/library/office/apps/dn265913#UpdatingSQLAzureComponent.

Next steps

This article demonstrated how to create a simple autohosted app for SharePoint with a remote web application. As next steps, consider the following:

Localizing your app for SharePoint for other cultures. For more information, see How to: Localize apps for SharePoint.
Including custom SharePoint components in the app and configuring the app to access the app web instead of the host web. For more information, see How to: Create a cloud-hosted app that includes a custom SharePoint list and content type.

Additional resources

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD