Edit

Share via

Deploying an ASP.NET Web Application with SQL Server Compact using Visual Studio or Visual Web Developer: Deploying a SQL Server Database Update - 11 of 12

  • Article

by Tom Dykstra

Download Starter Project

This series of tutorials shows you how to deploy (publish) an ASP.NET web application project that includes a SQL Server Compact database by using Visual Studio 2012 RC or Visual Studio Express 2012 RC for Web. You can also use Visual Studio 2010 if you install the Web Publish Update. For an introduction to the series, see the first tutorial in the series.

For a tutorial that shows deployment features introduced after the RC release of Visual Studio 2012, shows how to deploy SQL Server editions other than SQL Server Compact, and shows how to deploy to Windows Azure Web Sites, see ASP.NET Web Deployment using Visual Studio.

Overview

This tutorial shows you how to deploy a database update to a full SQL Server database. Because Code First Migrations does all the work of updating the database, the process is almost identical to what you did for SQL Server Compact in the Deploying a Database Update tutorial.

Reminder: If you get an error message or something doesn't work as you go through the tutorial, be sure to check the troubleshooting page.

Adding a New Column to a Table

In this section of the tutorial you'll make a database change and corresponding code changes, then test them in Visual Studio in preparation for deploying them to the test and production environments. The change involves adding an OfficeHours column to the Instructor entity and displaying the new information in the Instructors web page.

In the ContosoUniversity.DAL project, open Instructor.cs and add the following property between the HireDate and Courses properties:

[MaxLength(50)]
public string OfficeHours { get; set; }

Update the initializer class so that it seeds the new column with test data. Open Migrations\Configuration.cs and replace the code block that begins var instructors = new List<Instructor> with the following code block which includes the new column:

var instructors = new List<Instructor>
{
    new Instructor { FirstMidName = "Kim",     LastName = "Abercrombie", HireDate = DateTime.Parse("1995-03-11"), BirthDate = DateTime.Parse("1918-08-12"), OfficeHours = "8-9AM, 4-5PM", OfficeAssignment = new OfficeAssignment { Location = "Smith 17" } },
    new Instructor { FirstMidName = "Fadi",    LastName = "Fakhouri",    HireDate = DateTime.Parse("2002-07-06"), BirthDate = DateTime.Parse("1960-03-15"), OfficeAssignment = new OfficeAssignment { Location = "Gowan 27" } },
    new Instructor { FirstMidName = "Roger",   LastName = "Harui",       HireDate = DateTime.Parse("1998-07-01"), BirthDate = DateTime.Parse("1970-01-11"), OfficeHours = "6AM-6PM", OfficeAssignment = new OfficeAssignment { Location = "Thompson 304" } },
    new Instructor { FirstMidName = "Candace", LastName = "Kapoor",      HireDate = DateTime.Parse("2001-01-15"), BirthDate = DateTime.Parse("1975-04-11") },
    new Instructor { FirstMidName = "Roger",   LastName = "Zheng",       HireDate = DateTime.Parse("2004-02-12"), BirthDate = DateTime.Parse("1957-10-12"), OfficeHours = "By appointment only" }
};

In the ContosoUniversity project, open Instructors.aspx and add a new template field for office hours just before the closing </Columns> tag in the first GridView control:

<asp:TemplateField HeaderText="Office Hours">
    <ItemTemplate>
        <asp:Label ID="InstructorOfficeHoursLabel" runat="server" Text='<%# Eval("OfficeHours") %>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
        <asp:TextBox ID="InstructorOfficeHoursTextBox" runat="server" Text='<%# Bind("OfficeHours") %>'
            Width="14em"></asp:TextBox>
    </EditItemTemplate>
</asp:TemplateField>

Build the solution.

Open the Package Manager Console window, and select ContosoUniversity.DAL as the Default project.

Enter the following commands:

add-migration AddOfficeHoursColumn

update-database

Run the application and select the Instructors page. The page takes a little longer than usual to load, because the Entity Framework re-creates the database and seeds it with test data.

Instructors_page_with_office_hours

Deploying the Database Update to the Test Environment

When you use Code First Migrations, the method for deploying a database change to SQL Server is the same as for SQL Server Compact. However, you have to change the Test publish profile because it is still set up to migrate from SQL Server Compact to SQL Server.

The first step is to remove the connection string transformations that you created in the previous tutorial. These are no longer needed because you'll specify connection string transformations in the publish profile, as you did before you configured the Package/Publish SQL tab for migration to SQL Server.

Open the Web.Test.config file and remove the connectionStrings element. The only remaining transformation in the Web.Test.config file is for the Environment value in the appSettings element.

Now you can update the publish profile and publish to the test environment.

Open the Publish Web wizard, and then switch to the Profile tab.

Select the Test publish profile.

Select the Settings tab.

Click enable the new database publishing improvements.

In the connection string box for SchoolContext, enter the same value that you used in the Web.Test.config transformation file in the previous tutorial:

Data Source=.\SQLExpress;Initial Catalog=School-Test;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True

Select Execute Code First Migrations (runs on application start). (In your version of Visual Studio, the check box might be labeled Apply Code First Migrations.)

In the connection string box for DefaultConnection, enter the same value that you used in the Web.Test.config transformation file in the previous tutorial:

Data Source=.\SQLExpress;Initial Catalog=aspnet-Test;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True

Leave Update database cleared.

Click Publish.

Visual Studio deploys the code changes to the test environment and opens the browser to the Contoso University home page.

Select the Instructors page.

When the application runs this page, it tries to access the database. Code First Migrations checks if the database is current, and finds that the latest migration has not been applied yet. Code First Migrations applies the latest migration, runs the Seed method, and then the page runs normally. You see the new Office Hours column with the seeded data.

Instructors_page_with_OfficeHours_Test

Deploying the Database Update to the Production Environment

You have to change the publish profile for the production environment also. In this case you'll remove the existing profile and create a new one by importing an updated .publishsettings file. The updated file will include the connection string for the SQL Server database at Cytanium.

As you saw when you deployed to the test environment, you no longer need connection string transforms in the Web.Production.config transformation file. Open that file and remove the connectionStrings element. The remaining transformations are for the Environment value in the appSettings element and the location element that restricts access to Elmah error reports.

Before you create a new publish profile for production, download an updated .publishsettings file the same way you did earlier in the Deploying to the Production Environment tutorial. (In the Cytanium control panel, click Web Sites, and then click the contosouniversity.com website. Select the Web Publishing tab, and then click Download Publishing Profile for this web site.) The reason you are doing this is to pick up the database connection string in the .publishsettings file. The connection string wasn't available the first time you downloaded the file, because you were still using SQL Server Compact and hadn't created the SQL Server database at Cytanium yet.

Now you can update the publish profile and publish to the production environment.

Open the Publish Web wizard, and then switch to the Profile tab.

Click Manage Profiles, and then delete the Production profile.

Close the Publish Web wizard to save this change.

Open the Publish Web wizard again, and then click Import.

On the Connection tab, change Destination URL to the appropriate value if you are using a temporary URL.

Click Next.

On the Settings tab, click enable the new database publishing improvements.

In the connection string drop-down list for SchoolContext, select the Cytanium connection string.

Selecting_Cytanium_connection_string

Select Execute Code First migrations (runs on application start).

In the connection string drop-down list for DefaultConnection, select the Cytanium connection string.

Select the Profile tab, click Manage Profiles, and rename the profile from "contosouniversity.com - Web Deploy" to "Production".

Close the publish profile to save the change, then open it again.

Click Publish. (For a real production website, you would copy app_offline.htm to production and put it in your project folder before publishing, then remove it when deployment is complete.)

Visual Studio deploys the code changes to the test environment and opens the browser to the Contoso University home page.

Select the Instructors page.

Code First Migrations updates the database the same way it did in the Test environment. You see the new Office Hours column with the seeded data.

Instructors_page_with_OfficeHours_Prod

You have now successfully deployed an application update that included a database change, using a SQL Server database.

More Information

This completes this series of tutorials on deploying an ASP.NET web application to a third-party hosting provider. For more information about any of the topics covered in these tutorials, see the ASP.NET Deployment Content Map on the MSDN web site.

Acknowledgements

I would like to thank the following people who made significant contributions to the content of this tutorial series:

Previous Next