Microsoft ASP. NET Site Deployment Guide (11): Deploy SQL Server database updates

1. Overview

This section shows you how to deploy a database upgrade to a full version of the SQL Server database. The database upgrade deployment differs from Chapter 9th (Chapter 9th is the deployment to the SQL Server Compact database).

Reminder: Please check the troubleshooting page If there is an error message or some functionality is not working according to the actions in this section.

2. Add a new column to a table

This section, make a database modification and the corresponding code update, test in Visual Studio before deploying to the test and production environment. The modification is to add a officehours column to the instructor entity and then Instructors the page.

Open the Instructor.cs file in the Contosouniversity.dal project and add the following code between HireDate and courses:

[MaxLength ()]  Public string Get set; }

Update the initializer class to add test data. Open the SchoolInitializer.cs file to replace the code block starting with var instructors = new List<instructor> :

View Code

In the Contosouniversity project, open the instructors.aspx file and add the following code before </Columns>:

<Asp:templatefieldHeaderText= "Office Hours">    <ItemTemplate>        <Asp:labelID= "Instructorofficehourslabel"runat= "Server"Text= ' <%#Eval ("officehours")%>' ></Asp:label>    </ItemTemplate>    <EditItemTemplate>        <Asp:textboxID= "Instructorofficehourstextbox"runat= "Server"Text= ' <%#Bind ("officehours")%>' Width= ' 14em ></Asp:textbox>    </EditItemTemplate></Asp:templatefield>

Running the program, accessing the instructors page, runs a little bit slower because the Entity Framework needs to recreate the database and insert the data:

3. Creating SQL scripts for database updates

The SQL Server Compact database in the previous section modifies the deployment and then deploys the test and production environment in the same way, but in this way, you need to create a SQL script to update the database because the full version of the SQL Server database does not pass through SQL Server-like The Compact database is deployed in a replicated manner. The 9th chapter indicates that a database update script can be created by comparing the database and then automatically. This section provides you with the scripts that have been produced.

To find out what code first did for the database, reopen Server Explorer, and you need to add a connection connection to the new database Schooldev.

In Server Explorer , right-click Data Connections, select add Connection, and in the Add Connection dialog box, In Server Name , enter. \SQLExpress, and then open the Select or enter a database Name drop-down menu to select schooldev databases.

ExpandColumns,Tables, schooldev, you can see the officehours column.

In the Properties window, you can see that the data type of this column is nvarchar, and the length is 50.

In the Server Explorer , right-click the schooldev database and select Close Connection.

The following script is to add a column to the person table, the table, copy these scripts to save as addofficehourscolumn.sql files, and put them in the solutionfiles schema folder.

View Code

You can also create a script to update the data for the Officehours column, but the user will update the value itself after the program is deployed.

4. Deploy database updates to the test environment

For the test environment, the schema script that was used at the first deployment, the data insertion script, and the empowerment script are no longer executed. Here you just need to execute the newly created SQL script.

Open the Project Properties window, select the package/publish Web tab, and make sure that the Configuration box is selected for Active (Test) or Test.

Make sure that the Include all databases configured in Package/publish SQL tab entry is selected.

Select the package/publish SQL tab to make sure that the Configuration or Testis in the Database Entries table, deselect Defaultconnection-deployment, because you do not need to update this membership database.

In the database Entries table, select the schoolcontext-deployment row To set the configuration information for the school database.

Deselect the 2 rows in the database Scripts table in the database Entry Details panel to cancel the pull data and/or schema from an existing Database item.

Click Add Scriptin the Database Scripts table and select addofficehourscolumn.sql in the pop-up Select File dialog box. File, click Open.

When you're done, the Package/publish SQL tab should look like this:

In the solution configurations drop-down menu, select Test, select testin the Publish profile drop-down menu, click Publish Web. (If you have customized your Visual Studio to make these settings unavailable for these toolbars, refer to the 8th chapter)

Visual Studio deploys the modified program and displays the success information in the Output window.

Rerun the program to verify that the deployment results are normal, and you'll see a new Office Hours column, and when you edit a instructor, the value of Office Hours is added to the column.

5. Deploy a database update to a production environment

In addition to uploading the app_offline.htm file before deployment, removing the file after deployment, the other deployment process and the process of deploying the test environment are identical.

After the configuration is complete, the Package/publish SQL tab should look like this:

When you are finished, you can deploy to the production environment. Select Release in the solution configurations drop-down menu, select Production in the Publish drop -down menu, click Publish Web. (If you have customized your Visual Studio to make these settings unavailable for these toolbars, refer to the 8th chapter)

Visual Studio deploys the modified program and displays the success information in the Output window.

Before testing, you need to delete the app_offline.htm file.

Reopen the program to verify that the deployment is healthy. Display, the edited data is displayed on the Office hours column, that is, the deployment is OK.

Now that you have successfully deployed a program with database updates, try the same SQL footsteps when you deploy to SQL Server Express and SQL Server.

6. More information

At this point, the teaching materials for deploying the ASP to third-party providers are all over, such as how to deploy the Contosouniversity program using a deployment package, see: Usinga Web deploy to deploy to IIS On the Dev box and to a third party host. For more similar articles, see: ASP. NET Deployment Content Map.

Copyright NOTICE: This article for Bo Master http://www.zuiniusn.com original article, without Bo Master permission not reproduced.

Microsoft ASP. NET Site Deployment Guide (11): Deploy SQL Server database updates