Continuous Delivery with TFS: Our Sample Application

In this post that is part of my series on implementing continuous delivery with TFS, we look at the sample application that will be used to illustrate various aspects of the deployment pipeline. I’ve chosen Microsoft’s fictional Contoso University ASP.NET MVC application as it comprises components that need to be deployed to a web server and a database server and it lends itself to (reasonably) easily demonstrating automated acceptance testing. You can download the completed application here and read more about its construction here.

Out of the box Contoso University uses Entity Framework Code First Migrations for database development however this isn’t what I would use for enterprise-level software development. Instead, I recommend using a tool such as Microsoft’s SQL Server Database Tools (SSDT) and more specifically the SQL Server Database Project component of SSDT that can be added to a Visual Studio solution. The main focus of this post is on converting Contoso University to use a SQL Server Database Project and if you are not already up to speed with this technology, I have a Getting Started post here. Please note that I don’t describe every mouse-click below so some familiarity will be essential. I’m using the version of LocalDb that corresponds to SQL Server 2012 below as this is what Contoso University has been developed against. If you want to use the LocalDb that corresponds to SQL Server 2014 ((localdb)\ProjectsV12), then it will probably work but watch out for glitches. So, there is a little bit of work to do to get Contoso University converted, and this post will take us to the point of readying it for configuration with TFS.

Getting Started

1. Download the Contoso University application using the link above and unblock and then extract the zip to a convenient temporary location.
2. Navigate to ContosoUniversity.sln and open the solution. Build the solution which should cause NuGet packages to be restored using the Automatic Package Restore method.
3. From Package Manager Console, issue an Update-Database command (you may have to close down and restart Visual Studio for the command to become available). This should cause a ContosoUniversity2 database (including data) to be created in LocalDb. (You can verify this by opening the SQL Server Object Explorer window and expanding the (LocalDb)\v11.0 node. ContosoUniversity2 should be visible in the Database folder. Check that data has been added to the tables as we’re going to need it.)

Remove EF Code First Migrations

1. Delete SchoolIniializer.cs from the DAL folder.
2. Delete the DatabaseInitializer configuration from Web.config (this will probably be commented out, but I’m removing it for completeness’ sake):
   XML

   <!--<contexts>
     <context type="ContosoUniversity.DAL.SchoolContext, ContosoUniversity">
       <databaseInitializer type="ContosoUniversity.DAL.SchoolInitializer, ContosoUniversity" />
     </context>
   </contexts>-->

3. Remove the Migrations folder and all its contents.
4. Expand the ContosoUniversity2 database from  the SQL Server Object Explorer window and delete dbo._MigrationHistory from the Tables folder.
5. Run the solution to check that it still builds and data can be edited.

Configure the Solution to Work with a SQL Server Database Project (SSDP)

1. Add an SSDP called ContosoUniversity.Database to the solution.
2. Import the ContosoUniversity2 database to the new project using default values.
3. In the ContosoUniversity.Database properties, enable Code Analysis in the Code Analysis tab.
4. Create and save a publish profile called CU-DEV.publish.xml to publish to a database called CU-DEV on (LocalDb)\v11.0.
5. In Web.config, change the SchoolContext connection string to point to CU-DEV.
6. Build the solution to check that there are no errors.

Add Dummy Data

The next step is to provide the facility to add dummy data to a newly published version of the database. There are a couple of techniques for doing this depending on requirements – the one I’m demonstrating only adds the dummy data if a table contains no rows, so ensuring that a live database can’t get polluted. I’ll be extracting the data from ContosoUniversity2 and I’ll want to maintain existing referential integrity, so I’ll be using SET IDENTITY_INSERT ON | OFF on some tables to insert values to primary key columns that have the identity property set. Firstly, create a new folder in the SSDP called ReferenceData (or whatever pleases you) and then add a post deployment script file (Script.PostDeployment.sql) to the root of the ContosoUniversity.database project (note there can only be one of these). Then follow this general procedure for each table:

1. In the SQL Server Object Explorer window, expand the tree to display the ContosoUniversity2 database tables.
2. Right click a table and choose View Data. From the table’s toolbar, click the Script icon to create the T-SQL to insert the data (SET IDENTITY_INSERT ON | OFF should be added by the scripting engine where required).
3. Amend the script with an IF statement so that the insert will only take place if the table is empty. The result script should look similar to the following:
   SQL

   IF NOT EXISTS (SELECT TOP 1 * FROM dbo.Department)
   
   BEGIN
   SET IDENTITY_INSERT [dbo].[Department] ON
   INSERT INTO [dbo].[Department] ([DepartmentID], [Name], [Budget], 
   [StartDate], [InstructorID]) VALUES (1, N'Temp', 
   CAST(0.0000 AS Money), N'2014-05-31 15:19:00', NULL)
   INSERT INTO [dbo].[Department] ([DepartmentID], [Name], 
   [Budget], [StartDate], [InstructorID]) VALUES (2, N'English', 
   CAST(350000.0000 AS Money), N'2007-09-01 00:00:00', 9)
   INSERT INTO [dbo].[Department] ([DepartmentID], [Name], [Budget], 
   [StartDate], [InstructorID]) VALUES (3, N'Mathematics', 
   CAST(100000.0000 AS Money), N'2007-09-01 00:00:00', 10)
   INSERT INTO [dbo].[Department] ([DepartmentID], [Name], [Budget], 
   [StartDate], [InstructorID]) VALUES (4, N'Engineering', 
   CAST(350000.0000 AS Money), N'2007-09-01 00:00:00', 11)
   INSERT INTO [dbo].[Department] ([DepartmentID], [Name], [Budget], 
   [StartDate], [InstructorID]) VALUES (5, N'Economics', 
   CAST(100000.0000 AS Money), N'2007-09-01 00:00:00', 12)
   SET IDENTITY_INSERT [dbo].[Department] OFF
   END

4. Save the file in the ReferenceData folder in the format TableName.data.sql and add it to the solution as an existing item.
5. Use the SQLCMD syntax to call the file in the post deployment script file. (The order the table inserts are executed will need to cater for referential integrity. Person, Department, Course, CourseInstructor, Enrollment and OfficeAssignment should work.) When editing Script.PostDeployment.sql, the SQLCMD Mode toolbar button will turn off Transact-SQL IntelliSense and stop ‘errors’ from being highlighted.
6. When all the ReferenceData files have been processed, the Script.PostDeployment.sql should look something like:

   :r ".\ReferenceData\Person.data.sql"
   GO
   :r ".\ReferenceData\Department.data.sql"
   GO
   :r ".\ReferenceData\Course.data.sql"
   GO
   :r ".\ReferenceData\CourseInstructor.data.sql"
   GO
   :r ".\ReferenceData\Enrollment.data.sql"
   GO
   :r ".\ReferenceData\OfficeAssignment.data.sql"
   GO

   You should now be able to use CU-DEV.publish.xml to actually publish a database called CU-DEV to LocalDB that contains both schema and data and which works in the same way as the database created by EF Code First Migrations.

Finishing Touches

For the truly fussy among us (that’s me) that like neat and tidy project names in VS solutions, there is an optional set of configuration steps that can be performed:

1. Remove the ContosoUniversity ASP.NET MVC project from the solution and rename it to ContosoUniversity.Web. In the file system, rename the containing folder to ContosoUniversity.Web.
2. Add the renamed project back in to the solution and from the Application tab of the project’s Properties, change the Assembly name and Default namespace to ContosoUniversity.Web.
3. Perform the following search and replace actions:

   namespace ContosoUniversity > namespace ContosoUniversity.Web
   	using ContosoUniversity > using ContosoUniversity .Web
   	ContosoUniversity.ViewModels > ContosoUniversity.Web.ViewModels
   	ContosoUniversity.Models > ContosoUniversity.Web.Models

4. You may need to close the solution and reopen it before checking that nothing is broken and the application runs without errors.

That’s it for the moment. In the next post in this series, I’ll explain how to get the solution under version control in TFS and how to implement continuous integration.

Cheers – Graham

The post Continuous Delivery with TFS: Our Sample Application appeared first on Please Release Me.