Microsoft ASP. NET site deployment guide (11): deploy SQL Server database updates

1. Summary

This section describes how to deploy a Database Upgrade to the full SQL Server database. It is different from the Database Upgrade and deployment in Chapter 9th (Chapter 9th is deployed to the SQL Server Compact database ).

Reminder: check the Troubleshooting page if an error message or some functions are abnormal during operations in this section.

2. Add a new column to the table

In this section, modify the database and update the corresponding code. before deploying it to the test and production environments, test it in Visual Studio. The modified content is to add an OfficeHours column to the Instructor object, and thenInstructorsIt is displayed on the page.

 

Open the uctor. cs file in the ContosoUniversity. DAL project and add the following code between HireDate and Courses:

 

[MaxLength (50)]

Public string OfficeHours {get; set ;}

 

 

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

 

View Code

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 "},

New Instructor {FirstMidName = "Fadi", LastName = "Fakhouri", HireDate = DateTime. Parse (""), BirthDate = DateTime. Parse ("1960-03-15 ")},

New Instructor {FirstMidName = "Roger", LastName = "Harui", HireDate = DateTime. parse ("1998-07-01"), BirthDate = DateTime. parse ("1970-01-11"), OfficeHours = "6AM-6PM "},

New uctor {FirstMidName = "Candace", LastName = "Kapoor", HireDate = DateTime. Parse (""), 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 the Instructors. aspx file and add the following code before </Columns>:

 

<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>

 

Run the program and access the Instructors page. The operation is a little slow, because the Entity Framework needs to re-create the database and insert data:

 

3. Create an SQL script for database updates

Modify and deploy the SQL Server Compact database in the previous chapter, and then deploy the test and production environments in the same way. However, this method is not supported in this chapter. You need to create an SQL script to update the database, because the full version of the SQL Server database cannot be deployed through replication like the SQL Server Compact database. Chapter 2 indicates that you can compare databases and then automatically create database update scripts. This section provides you with the generated scripts.

 

I want to know what Code First has done for the database and re-open it.Server Explorer, You need to add a connection to connect to the new database SchoolDev.

Server ExplorerRight-clickData Connections, SelectAdd Connection, InAdd ConnectionDialog BoxServer NameInput. \ SQLExpress, and then openSelect or enter a database nameSelectSchoolDevDatabase.

 

ExpandSchoolDev->Tables->Person->Columns, You can seeOfficeHoursColumn.

 

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

 

InServer ExplorerRight-clickSchoolDevDatabase, selectClose Connection.

The following script adds a column to the Person table, table, and copies these scripts and saves themAddOfficeHoursColumn. SQLFileSolutionFilesIn the solution folder.

BEGIN TRANSACTION

 

SET QUOTED_IDENTIFIER ON

 

SET ARITHABORT ON

 

SET NUMERIC_ROUNDABORT OFF

 

SET CONCAT_NULL_YIELDS_NULL ON

 

SET ANSI_NULLS ON

 

SET ANSI_PADDING ON

 

SET ANSI_WARNINGS ON

 

COMMIT

 

BEGIN TRANSACTION

 

GO

 

Create table dbo. Tmp_Person

 

(

 

PersonID int not null identity (15, 1 ),

 

LastName nvarchar (50) not null,

 

FirstName nvarchar (50) not null,

 

BirthDate datetime NULL,

 

HireDate datetime NULL,

 

OfficeHours nvarchar (50) NULL,

 

EnrollmentDate datetime NULL,

 

Discriminator nvarchar (128) NOT NULL

 

) ON [PRIMARY]

 

GO

 

Alter table dbo. Tmp_Person SET (LOCK_ESCALATION = TABLE)

 

GO

 

SET IDENTITY_INSERT dbo. Tmp_Person ON

 

GO

 

If exists (SELECT * FROM dbo. Person)

 

EXEC ('insert INTO dbo. Tmp_Person (PersonID, LastName, FirstName, BirthDate, HireDate, EnrollmentDate, Discriminator)

 

SELECT PersonID, LastName, FirstName, BirthDate, HireDate, EnrollmentDate, Discriminator FROM dbo. Person WITH (holdlock tablockx )')

 

GO

 

SET IDENTITY_INSERT dbo. Tmp_Person OFF

 

GO

 

Alter table dbo. coursepolicuctor

 

Drop constraint Course_Instructors_Target

 

GO

 

Alter table dbo. Department

 

Drop constraint Department_Administrator

 

GO

 

Alter table dbo. Enrollment

 

Drop constraint Student_Enrollments

 

GO

 

Alter table dbo. OfficeAssignment

 

Drop constraint Instructor_OfficeAssignment

 

GO

 

Drop table dbo. Person

 

GO

 

EXECUTE sp_rename N 'dbo. Tmp_Person ', N 'person', 'object'

 

GO

 

Alter table dbo. Person ADD CONSTRAINT

 

PK _ Person _ 000000000000002C PRIMARY KEY CLUSTERED

 

(

 

PersonID

 

) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

 

 

 

GO

 

COMMIT

 

BEGIN TRANSACTION

 

GO

 

Alter table dbo. OfficeAssignment ADD CONSTRAINT

 

Instructor_OfficeAssignment FOREIGN KEY

 

(

 

PersonID

 

) REFERENCES dbo. Person

 

(

 

PersonID

 

) ON UPDATE NO ACTION

 

ON DELETE NO ACTION

 

 

GO

 

Alter table dbo. OfficeAssignment SET (LOCK_ESCALATION = TABLE)

 

GO

 

COMMIT

 

BEGIN TRANSACTION

 

GO

 

Alter table dbo. Enrollment ADD CONSTRAINT

 

Student_Enrollments FOREIGN KEY

 

(

 

PersonID

 

) REFERENCES dbo. Person

 

(

 

PersonID

 

) ON UPDATE NO ACTION

 

ON DELETE CASCADE

 

 

GO

 

Alter table dbo. Enrollment SET (LOCK_ESCALATION = TABLE)

 

GO

 

COMMIT

 

BEGIN TRANSACTION

 

GO

 

Alter table dbo. Department ADD CONSTRAINT

 

Department_Administrator FOREIGN KEY

 

(

 

PersonID

 

) REFERENCES dbo. Person

 

(

 

PersonID

 

) ON UPDATE NO ACTION

 

ON DELETE NO ACTION

 

 

GO

 

Alter table dbo. Department SET (LOCK_ESCALATION = TABLE)

 

GO

 

COMMIT

 

BEGIN TRANSACTION

 

GO

 

Alter table dbo. CourseInstructor ADD CONSTRAINT

 

Course_Instructors_Target FOREIGN KEY

 

(

 

PersonID

 

) REFERENCES dbo. Person

 

(

 

PersonID

 

) ON UPDATE NO ACTION

 

ON DELETE CASCADE

 

 

GO

 

Alter table dbo. CourseInstructor SET (LOCK_ESCALATION = TABLE)

 

GO

 

COMMIT

You can also create a script to update the data in the OfficeHours column. However, after the program is deployed, the user will update the value.

4. update the database to the test environment.

For the test environment, the schema script, data insertion script, and authorization script used during the first deployment cannot be executed any more. Here you only need to execute the newly created SQL script.

OpenProject PropertiesWindow SelectionPackage/Publish WebTab to make sure thatConfigurationSelectActive (Test)OrTest.

Make sure you have selectedInclude all databases configured in Package/Publish SQL tab.

 

SelectPackage/Publish SQLTab to make sure thatConfigurationOrTest, InDatabase EntriesIn the table, deselectDefaultConnection-DeploymentBecause the membership database does not need to be updated.

InDatabase EntriesIn the table, selectSchoolContext-DeploymentTo configure the School database.

InDatabase Entry DetailsUnselect panelDatabase ScriptsThe two rows in the table are canceled.Pull data and/or schema from an existing database.

InDatabase ScriptsClick in tableAdd Script, In the pop-upSelect FileSelectAddOfficeHoursColumn. SQLFile, clickOpen.

 

After completion, the Package/Publish SQL tab should be like this:

 

InSolution ConfigurationsSelectTest, InPublishSelectTest, ClickPublish Web. (If you have customized your Visual Studio to disable these toolbar settings, refer to Chapter 8th)

 

Visual Studio deploys the modified program andOutputThe window shows successful information.

 

Run the program again to verify that the deployment result is normal. At this time, you will see a new office hours column. When you edit a javasuctor, the value of Office Hours is added to this column.

 

 

5. Update the deployment database to the production environment

In addition to uploading files before deploymentApp_offline.htmFile, except for deleting the file after deployment, other deployment processes are the same as those for deploying the test environment.

After the configuration is complete, the Package/Publish SQL tab should be like this:

 

After that, you can deploy it to the production environment. InSolution ConfigurationsSelectRelease, InPublishSelectProduction, ClickPublish Web. (If you have customized your Visual Studio to disable these toolbar settings, refer to Chapter 8th)

 

Visual Studio deploys the modified program andOutputThe window shows successful information.

 

Before testing, you need to deleteApp_offline.htmFile.

Re-open the program and verify that the deployment is normal. Display, the Office Hours column shows the edited data, that is, the deployment is OK.

 

Now you have successfully deployed a program that contains database updates. You have tried the same SQL step when deploying it to SQL Server Express and SQL Server.

6. More information

Now, ASP. the teaching materials from the NET program to a third-party provider have all ended. For example, to deploy the ContosoUniversity program using a deployment package, see: using a Web Deploy package to deploy to IIS on the dev box and to a third party host. For more similar articles, see ASP. NET Deployment Content Map.

7. Thank you

Thanks to the following list of people who have made outstanding contributions to this tutorial:

• Alberta to Poblacion, MVP & MCT, Spain
• Jarod Ferguson, Data Platform Development MVP, United States
• Harsh Mittal, Microsoft
• Kristina Olson, Microsoft
• Mike Pope, Microsoft
• Mohit Srivastava, Microsoft
• Raffaele Rialdi, Italy
• Rick Anderson, Microsoft
• Sayed Hashimi, Microsoft (twitter: @ sayedihashimi)
• Scott Hanselman (twitter: @ shanselman)
• Scott Hunter, Microsoft (twitter: @ coolcsh)
• Sr bought an Bo bought oviic, Serbia
• Vishal Joshi, Microsoft (twitter: @ vishalrjoshi)

 

Uncle Tom