Deploying a Database Update

Latest Approach

PM> Enable-Migrations –EnableAutomaticMigrations

This command has added a Migrations folder to our project. This new folder contains a Configuration class. This class allows you to configure how Migrations behaves for your context. For this walkthrough we will just use the default configuration.

Automatic Migration

Code First Migrations has two commands that you are going to become familiar with.

• Add-Migration will scaffold the next migration based on changes you have made to your model.
• Update-Database will apply any pending changes to the database.

We are going to avoid using Add-Migration (unless we really need to) and focus on letting Code First Migrations automatically calculate and apply the changes. Let’s use Update-Database to get Code First Migrations to push the changes to our model to the database.

Run the Update-Database –Verbose command in Package Manager Console.

Adding a Code Based Migration

Now let’s look at something we might want to use a code-based migration for.

Let’s add a Rating property to the Blog class

public int Rating { get; set; }

We could just run Update-Database to push these changes to the database. However, we're adding a non-nullable Blogs.Rating column, if there is any existing data in the table it will get assigned the CLR default of the data type for new column (Rating is integer, so that would be 0). But we want to specify a default value of 3 so that existing rows in the Blogs table will start with a decent rating.

Let’s use the Add-Migration command to write this change out to a code-based migration so that we can edit it. The Add-Migration command allows us to give these migrations a name, let’s just call ours AddBlogRating.

Run the Add-Migration AddBlogRating command in Package Manager Console.

In the Migrations folder we now have a new AddBlogRating migration. The migration filename is pre-fixed with a timestamp to help with ordering. Let’s edit the generated code to specify a default value of 3 for Blog.Rating (Line 10 in the code below)

The migration also has a code-behind file that captures some metadata. This metadata will allow Code First Migrations to replicate the automatic migrations we performed before this code-based migration. This is important if another developer wants to run our migrations or when it’s time to deploy our application.

namespace MigrationsAutomaticDemo.Migrations 
{ 
    using System; 
    using System.Data.Entity.Migrations; 
     
    public partial class AddBlogRating : DbMigration 
    { 
        public override void Up() 
        { 
            AddColumn("Blogs", "Rating", c => c.Int(nullable: false, defaultValue: 3)); 
        } 
         
        public override void Down() 
        { 
            DropColumn("Blogs", "Rating"); 
        } 
    } 
}

Our edited migration is looking pretty good, so let’s use Update-Database to bring the database up-to-date.

Run the Update-Database command in Package Manager Console.

Back to Automatic Migrations

We are now free to switch back to automatic migrations for our simpler changes. Code First Migrations will take care of performing the automatic and code-based migrations in the correct order based on the metadata it is storing in the code-behind file for each code-based migration.

Let’s add a Post.Abstract property to our model

public string Abstract { get; set; }

Now we can use Update-Database to get Code First Migrations to push this change to the database using an automatic migration.

Run the Update-Database command in Package Manager Console.

---

Overview

In this tutorial, you make a database change and related code changes, test the changes in Visual Studio, then deploy the update to the test, staging, and production environments.

The tutorial first shows how to update a database that is managed by Code First Migrations, and then later it shows how to update a database by using the dbDacFx provider.

Deploy a database update by using Code First Migrations

In this section,

• You add a birth date column to the Person base class.
• Then you update the page that displays instructor data so that it displays the new column.
• Finally, you deploy the changes to test, staging, and production.

Add a column to a table in the application database

In the *.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)]
[Display(Name = "Birth Date")]
public DateTime? BirthDate { get; set; }

Next, update the Seed method so that it provides a value for the new column. Open Migrations\Configuration.cs and 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"), 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"), 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") }
};

Build the solution, and then open the Package Manager Console window. Make sure that *.DAL is still selected as the Default project.

In the Package Manager Console window, select *.DAL as the Default project, and then enter the following command:

add-migration AddBirthDate

When this command finishes, Visual Studio opens the class file that defines the new DbMIgration class, and in the Up method you can see the code that creates the new column. The Up method creates the column when you are implementing the change, and the Down method deletes the column when you are rolling back the change.

AddBirthDate_migration_code

Build the solution, and then enter the following command in the Package Manager Console window (make sure the ContosoUniversity.DAL project is still selected):

update-database

The Entity Framework runs the Up method and then runs the Seed method.

Display the new column in the Instructors page

In the project, open the relevant 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="Hire Date" SortExpression="HireDate">
    <ItemTemplate>
        <asp:Label ID="InstructorHireDateLabel" runat="server" Text='<%# Eval("HireDate", "{0:d}") %>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
        <asp:TextBox ID="InstructorHireDateTextBox" runat="server" Text='<%# Bind("HireDate", "{0:d}") %>' Width="7em"></asp:TextBox>
    </EditItemTemplate>
</asp:TemplateField>
<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>
<asp:TemplateField HeaderText="Office Assignment" SortExpression="OfficeAssignment.Location">
    <ItemTemplate>
        <asp:Label ID="InstructorOfficeLabel" runat="server" Text='<%# Eval("OfficeAssignment.Location") %>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
        <asp:TextBox ID="InstructorOfficeTextBox" runat="server"
            Text='<%# Eval("OfficeAssignment.Location") %>' Width="7em"
            OnInit="InstructorOfficeTextBox_Init"></asp:TextBox>
    </EditItemTemplate>
</asp:TemplateField>

Run the application and click the Instructors link.

Close the browser.

Deploy the database update

1. In Solution Explorer select the project.
2. In the Web One Click Publish toolbar, click the Test publish profile, and then click Publish Web. (If the toolbar is disabled, select the project in Solution Explorer.)
3. Visual Studio deploys the updated application, and the browser opens to the home page.
4. Run the Instructors page to verify that the update was successfully deployed.
5. When the application tries to access the database for this page, Code First updates the database schema and runs the Seed method. When the page displays, you see the expected Birth Date column with dates in it.
6. In the Web One Click Publish toolbar, click the Staging publish profile, and then click Publish Web.
7. Run the Instructors page in staging to verify that the update was successfully deployed.
8. In the Web One Click Publish toolbar, click the Production publish profile, and then click Publish Web.
9. Run the Instructors page in production to verify that the update was successfully deployed.

For a a real production application update that includes a database change you would also typically take the application offline during deployment by using App Offline.htm, as you saw in the previous tutorial.

Deploy a database update by using the dbDacFx provider

In this section, you add a Comments column to the User table in the membership database and create a page that lets you display and edit comments for each user. Then you deploy the changes to test, staging, and production.

1. Add a column to a table in the membership database
2. In Visual Studio, open SQL Server Object Explor

er.

1. Expand (localdb)\v11.0, expand Databases, expand aspnet-ContosoUniversity (not aspnet-ContosoUniversity-Prod) and then expand Ta

bles.

1. If you don't see (localdb)\v11.0 under the SQL Server node, right-click the SQL Server node and click Add SQL Server. In the Connect to Server dialog box enter (localdb)\v11.0 as the Server name, and then click Connect.
2. If you don't see aspnet-ContosoUniversity, run the project and log in using the admin credentials (password is devpwd), and then refresh the SQL Server Object Explorer window.
3. Right-click the Users table, and then click View Designer.
4. In the designer, add a Comments column and make it nvarchar(128) and nullable, and then click Update.
5. In the Preview Database Updates box, click Update Database.

Create a page to display and edit the new column

1. In Solution Explorer, right-click the Account folder in the project, click Add, and then click New Item.
2. Create a new Web Form Using Master Page and name it UserInfo.aspx. Accept the default Site.Master file as the master page.
3. Copy the following markup into the MainContent Content element (the last of the 3 Content elements):

    <h2>User Information</h2>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>" 
        SelectCommand="SELECT UserId, UserName, Comments FROM [Users]" 
        UpdateCommand="UPDATE [Users] SET [UserName] = @UserName, [Comments] = @Comments WHERE [UserId] = @UserId">
        <DeleteParameters>
            <asp:Parameter Name="UserId" Type="Object" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="UserId" Type="Object" />
            <asp:Parameter Name="UserName" Type="String" />
            <asp:Parameter Name="Comments" Type="String" />
        </UpdateParameters>
    </asp:SqlDataSource>

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="UserId" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:CommandField ShowEditButton="True" />
            <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
            <asp:BoundField DataField="Comments" HeaderText="Comments" SortExpression="Comments" />
        </Columns>
    </asp:GridView>

1. Right-click the UserInfo.aspx page and click View in Browser.
2. Log in with your admin user credentials (password is devpwd) and add some comments to a user to verify that the page works correctly.
3. Close the browser.

Deploy the database update

To deploy by using the dbDacFx provider, you just need to select the Update database option in the publish profile. However, for the initial deployment when you used this option you also configured some additional SQL scripts to run: those are still in the profile and you'll have to prevent them from running again.

1. Open the Publish Web wizard by right-clicking the ContosoUniversity project and clicking Publish.
2. Select the Test profile.
3. Click the Settings tab.
4. Under DefaultConnection, select Update database.
5. Disable the additional scripts that you configured to run for the initial deployment:
6. Click Configure database updates.
7. In the Configure Database Updates dialog box, clear the check boxes next to Grant.sql and aspnet-data-dev.sql.
8. Click Close.
9. Click the Preview tab.
10. Under Databases and to the right of DefaultConnection, click the Preview database link. The preview window shows the script that will be run in the destination database to make that database schema match the schema of the source database. The script includes an ALTER TABLE command that adds the new column.
11. Close the Database Preview dialog box, and then click Publish.
12. Visual Studio deploys the updated application, and the browser opens to the home page.
13. Run the UserInfo page (add Account/UserInfo.aspx to the home page URL) to verify that the update was successfully deployed. You'll have to log in by entering admin and devpwd.
14. Data in tables is not deployed by default, and you didn't configure a data deployment script to run, so you won't find the comment that you added in development. You can add a new comment now in staging to verify that the change was deployed to the database and the page works correctly.
15. Follow the same procedure to deploy to staging and production.
16. Don't forget to disable the extra scripts. The only difference compared to the Test profile is that you will disable only one script in the Staging and Production profiles because they were configured to run only aspnet-prod-data.sql.

Taken From

---

http://www.asp.net/mvc/overview/deployment/visual-studio-web-deployment/deploying-a-database-update