Home/Web Forms/Tutorials/Chapter 9. Deployment/Deployment to a Hosting Provider/Visual Studio Web Deployment - Deploying a Database Update

Overview

Sooner or later an application update will involve changes to a database (either schema or data or both) as well as to code. In this tutorial you'll make a database change and related code changes, test the changes in Visual Studio, then deploy the update to both the test and production environments.

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 you'll add a birth date column to the Person base class for the Student and Instructor entities. You'll also update the pages that display student and instructor data so that they display the new column.

In the ContosoUniversity.DAL project, open Person.cs and add the following property at the end of the Person class (there should be two closing curly braces following it):

[DisplayFormat(DataFormatString = "{0:d}", ApplyFormatInEditMode = true)]
[Required(ErrorMessage = "Birth date is required.")]
[Display(Name = "Birth Date")]
public DateTime? BirthDate { get; set; }

Next you need to update the initializer class so that it provides a value for the new column. Open SchoolInitializer.cs and find the code block that begins var students = new List<Student>. Replace that block with the following code, which includes birth date information for the students:

var students = new List<Student>
{
    new Student { FirstMidName = "Carson",   LastName = "Alexander", EnrollmentDate = DateTime.Parse("2005-09-01"), BirthDate = DateTime.Parse("1990-01-01") },
    new Student { FirstMidName = "Meredith", LastName = "Alonso",    EnrollmentDate = DateTime.Parse("2002-09-01"), BirthDate = DateTime.Parse("1989-01-15") },
    new Student { FirstMidName = "Arturo",   LastName = "Anand",     EnrollmentDate = DateTime.Parse("2003-09-01"), BirthDate = DateTime.Parse("1988-02-01") },
    new Student { FirstMidName = "Gytis",    LastName = "Barzdukas", EnrollmentDate = DateTime.Parse("2002-09-01"), BirthDate = DateTime.Parse("1987-03-15") },
    new Student { FirstMidName = "Yan",      LastName = "Li",        EnrollmentDate = DateTime.Parse("2002-09-01"), BirthDate = DateTime.Parse("1985-11-11") },
    new Student { FirstMidName = "Peggy",    LastName = "Justice",   EnrollmentDate = DateTime.Parse("2001-09-01"), BirthDate = DateTime.Parse("1970-11-21") },
    new Student { FirstMidName = "Laura",    LastName = "Norman",    EnrollmentDate = DateTime.Parse("2003-09-01"), BirthDate = DateTime.Parse("1992-10-11") },
    new Student { FirstMidName = "Nino",     LastName = "Olivetto",  EnrollmentDate = DateTime.Parse("2005-09-01"), BirthDate = DateTime.Parse("1986-06-06") }
};

Replace the code block that begins var instructors = new List<Instructor> with the following code block which includes birth date information:

var instructors = new List<Instructor>
{
    new Instructor { FirstMidName = "Kim",     LastName = "Abercrombie", HireDate = DateTime.Parse("1995-03-11"), BirthDate = DateTime.Parse("1918-08-12") },
    new Instructor { FirstMidName = "Fadi",    LastName = "Fakhouri",    HireDate = DateTime.Parse("2002-07-06"), BirthDate = DateTime.Parse("1960-03-15") },
    new Instructor { FirstMidName = "Roger",   LastName = "Harui",       HireDate = DateTime.Parse("1998-07-01"), BirthDate = DateTime.Parse("1970-01-11") },
    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") }
};

In the ContosoUniversity project, open Instructors.aspx and add a new template field to display the birth date. Add it between the ones for hire date and office assignment:

<asp:TemplateField HeaderText="Birth Date" SortExpression="BirthDate">
    <ItemTemplate>
        <asp:Label ID="InstructorBirthDateLabel" runat="server" Text='<%# Eval("BirthDate", "{0:d}") %>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
        <asp:TextBox ID="InstructorBirthDateTextBox" runat="server" Text='<%# Bind("BirthDate", "{0:d}") %>'
            Width="7em"></asp:TextBox>
    </EditItemTemplate>
</asp:TemplateField>

(Note: If code indentation gets out of sync, you can press CTRL-K and then CTRL-D to automatically reformat the file.)

In Students.aspx, add a new dynamic field for birth date immediately before the one for enrollment date:

<asp:DynamicField DataField="BirthDate" HeaderText="Birth Date" SortExpression="BirthDate" />

In StudentsAdd.aspx, add a new bound field for birth date immediately before the one for enrollment date:

<asp:BoundField DataField="BirthDate" HeaderText="Birth Date" 
    SortExpression="BirthDate" /> 

In BLL\SchoolBL.cs, the switch statement in the GetStudents method supports the column sorting functionality for the Students page. Add two new case statements immediately before the default statement to support sorting the new birth date column:

case "BirthDate":
    students = students.OrderBy(s => s.BirthDate);
    break;
case "BirthDate DESC":
    students = students.OrderByDescending(s => s.BirthDate);
    break;

Run the application and select the Students page. You might notice that the page takes a little longer than usual to load. This is because the Entity Framework recognizes that the model has changed, so it drops and re-creates the database, then seeds it with the test data specified in your initializer class. When the page loads, you see that it has the new birth date field.

Select the Add Students and Instructors pages to verify that you see the new field.

Deploying the Database Update to the Test Environment

In your production site, your users have been entering data, and you want to preserve that data when you deploy this change to production. You don't actually need to preserve the data in the test environment. However, you want to use the same method for deploying to the test environment that you'll use later to deploy to production, to make sure that the method works correctly. Therefore, you'll deploy to the test environment using a method that closely follows what you'll do for production database. For production, here's an outline of the procedure you'll follow (omitting the app_offline.htm steps):

• Download School-Prod.sdf from the production site to the App_Data folder in your Visual Studio project.
• Manually apply the schema change to it, and manually update the data.
• Upload School-Prod.sdf back to the production site.
• Deploy the project.

(As an alternative approach, instead of copying the file back to the wwwroot folder manually, you could let Visual Studio do it as part of deployment. But then you would have to change deployment settings in project properties in order to specify that the School database should be copied but the membership databse should be excluded. With this method you do not need to change any deployment settings, and you can continue to deploy just by using one-click publish with existing settings for all site updates.)

Applying the Schema Change

For deployment to the Test environment, you don't need to worry about getting an up-to-the-minute copy of the database, so you can update the copy of School-Prod.sdf that you already have in the project without copying down the current copy of the database.

To begin, look at the changes that the Entity Framework made to your School database, so that you can manually make the same change to your test and production databases. In Solution Explorer, double-click App_Data\School.sdf to open a connection to the School database in Server Explorer.

In Server Explorer, expand School-Dev.sdf, expand Tables, expand Person, and expand Columns. You see the new BirthDate column that Code First created after you added a BirthDate property to the Person entity:

In the Properties window, you can see that the data type of this column is datetime. In this case, the name and type is all that you will need in order to replicate the change manually.

In Server Explorer, right-click School-Dev.sdf and select Close Connection.

(This is just one example of a simple database change. In a real application, database changes are often more complex, of course. This tutorial isn't focused on how to make and track database changes, but on how database changes affect deployment procedures. The general approach is this: figure out what database changes you expect from changes to the data model, look at what Code First has done to the database, and then replicate those changes manually.)

The next step is to manually make the same change in the School-Prod.sdf file.

In Server Explorer, expand School-Prod.sdf, expand Tables, expand Person, and expand Columns and you'll see the old table structure, this time without the new BirthDate column.

Right-click Person and select Edit Table Schema to display the Edit Table dialog box.

In the Edit Table dialog box, click the line below Discriminator to enter information for a new column, and then enter the following:

• Column Name: BirthDate
• Data Type: datetime

The default values for Length, Allow Nulls, Unique, and Primary Key are fine as they are.

Click OK.

The database will now work correctly with the new code. But the position of the BirthDate column in the test database (and later in the production database as well) does not exactly match what Entity Framework Code First has created in the development database. The Entity Framework drops and re-creates the table, and orders the columns in the database to match the code. However, the Visual Studio tool for SQL Server Compact you're using to update the existing table only allows you to put new columns at the end of the column list. As you can imagine, if you make additional database updates this way, it won't be long before the test and production databases look very different from the development database, even if they work the same way with the code.

As you work with your databases, you might also run into other limitations of the Visual Studio tool for SQL Server Compact, or you might not be able to replicate a particular change that was done automatically by Code First. If this becomes an issue for you, you have several options, such as the following:

• You can use the database manipulation feature of WebMatrix, which has more features than the SQL Server Compact tooling in Visual Studio.
• You can use more full-featured third-party or open source tools, such as the SQL Server Compact Toolbox and SQL Compact data and schema script utility that are available on the CodePlex site.
• You can write and run your own DDL (data definition language) scripts to manipulate the database schema.
• You can migrate to SQL Server Express and full SQL Server. With these you can use SQL Server Management Studio, which offers a full range of database manipulation features, and you can use Visual Studio 2010 SQL Server database projects or the Database Publishing Wizard to automatically generate scripts. (You might have other reasons for migrating to SQL Server anyway, as explained in the Deploying SQL Server Compact Databases tutorial.)
• You can use Entity Framework Code First Migrations, which automates the process of migrating existing databases to match a new data model. As this tutorial is being written, this software has not yet been released. However, a prerelease version is available for installation in a NuGet package. For the latest information on its availability, see the Entity Framework team blog.

Updating the Data

The test database now has a structure that will work with your code, but you need to add the instructor birth dates to it. You could create and run a SQL script or enter the data manually. For this tutorial a script has been created for you, and you'll run that.

In Server Explorer, right-click the Person table of the School-Prod.sdf database and select New Query.

Close the Add Table dialog box when it appears, then copy the following SQL statements and paste them into the SQL pane of the query window:

UPDATE  Person SET   BirthDate = '1918-08-12' WHERE PersonID = 9;
UPDATE  Person SET   BirthDate = '1960-03-15' WHERE PersonID = 10;
UPDATE  Person SET   BirthDate = '1970-01-11' WHERE PersonID = 11;
UPDATE  Person SET   BirthDate = '1975-04-11' WHERE PersonID = 12;
UPDATE  Person SET   BirthDate = '1957-10-12' WHERE PersonID = 13;

Right-click the SQL pane and select Execute SQL.

When you get the Query Definitions Differ dialog box, click Continue. This message just tells you that the query can't be represented graphically in the designer pane.

The query runs, and a dialog box confirms that 5 rows were updated.

In Server Explorer, right-click the Person table again and select Show Table Data to see that the instructors now all have birth dates.

The test database is now ready to deploy.

Copying the Database to the Test Site

Use Windows Explorer to copy School-Prod.sdf from the App_Data folder in the ContosoUniversity project to the App_Data folder in the test site (C:\inetpub\wwwroot\contosouniversity\App_Data), overwriting the copy of School-Prod.sdf that is already in the destination folder.

Deploying the Code Changes that Go With the Database Change

You are now ready to deploy the code update. In the Solution Configurations drop-down box, select Test for build configuration and in the Publish profile drop-down box select Test . Click Publish Web. (If you can't do this in the toolbars because you have have customized toolbars, see the Deploying a Code-Only Update tutorial for an alternative method.)

Visual Studio deploys the updated application and reports success in the Output window.

You can now and run the application at http://localhost/contosouniversity to verify that the update was successfully deployed. You see the birth date with actual dates in the Instructors page.

You can also run the Students and Add Students pages to verify that the update was successful.

Deploying the Database Update to the Production Environment

You can now deploy to production. You'll duplicate the process you used in test, except for downloading the production database first and using app_offline.htm to prevent users from using the site and thus updating the dataabase while you're deploying changes. For production deployment perform the following steps:

• Upload the app_offline.htm file to the production site.
• Download School-Prod.sdf from the production site to your local App_Data folder, and copy it to a backup location (see the note below).
• Manually add the new column.
• Run the update script. (In a real site you might have to change the script before running it, since the data in the production site might have changed since the script was originally created.)
• Upload the updated School-Prod.sdf file to the production site.
• Publish from Visual Studio.

The last step is the same as what you did in the previous tutorial for a code-only update. In the Solution Configurations drop-down box, select Release and in the Publish profile drop-down box select Production. Then click Publish Web.

Visual Studio deploys the updated application and reports success in the Output window.

Before you can test to verify successful deployment, you have to remove the app_offline.htm file.

You can now open a browser and browse your public site to verify that the update was successfully deployed.

You've now deployed an application update that included a database change to both test and production. The next tutorial shows you how to migrate your database from SQL Server Compact to SQL Server Express and full SQL Server.

1. ← PreviousVisual Studio Web Deployment - Deploying a Code-Only Update
2.  You're ViewingVisual Studio Web Deployment - Deploying a Database Update
3. → NextVisual Studio Web Deployment - Migrating to SQL Server