Continuous Delivery with TFS / VSTS – A Lap Around the Contoso University Sample Application
In the previous post in this series on Continuous Delivery with TFS / VSTS we configured a sample application for Git in Visual Studio 2015. This post continues with that configuration and also examines some of the more interesting parts of Contoso University before firing it up and taking it for a spin.
Build and Update NuGets
Picking up from where we left off last time in Visual Studio, Team Explorer -- Home should be displaying ContosoUniversity.sln in the Solutions section. Double-click this to open Contoso University and navigate to Solution Explorer. The first thing to do is to hit F6 to build the solution. NuGet packages should be restored and the five projects should build. It's worth checking Team Explorer -- Changes at this point to make sure .gitignore is doing its job properly -- if it is there should be no changes as far as version control is concerned.
For non-production applications I usually like to keep bang up-to-date with the latest NuGets so in Solution Explorer right click the solution and choose Manage NuGet Packages for Solution. Click on the Updates link and allow the manager to do its thing. If any updates are found click to Select all packages and then Update:
Commit (and sync if you like) these changes (see the previous post for a reminder of the workflow for this) and check the solution still builds.
Test the Unit Tests
The solution contains a couple of unit tests as well as some UI tests. In order to just establish that the unit tests work at this stage open Test Explorer (Test > Windows > Test Explorer) and then click on the down arrow next to Playlist : All Tests. Choose Open Playlist File, open ...\ContosoUniversity.Web.UnitTests\UnitTests.playlist and run the tests which should pass, albeit with very poor coverage (Tests > Analyze Code Coverage > All Tests).
Code analysis (a feature formerly known as FxCop) is enabled for every project and reports on any rule violations. I've set projects to use the Microsoft Managed Recommended Rules (note though that rule sets don't exist for SQL Server database Projects and in the case of ContosoUniversity.Database everything is selected) however the code as it exists in GitHub doesn't violate any of those rules. To see this feature in action right-click the ContosoUniversity.Web project and choose Properties. On the Code Analysis tab change the rule set to Microsoft All Rules and build the solution. The Error List window should appear and there should be quite a few warnings -- 85 at the time of writing this post. Reverse the change and the solution should once again build without warnings.
SQL Server Data Tools -- A Better Way to Manage Database Changes
There are several ways to manage the schema changes for an application's database and whilst every technique undoubtedly has its place the one I recommend for most scenarios is the declarative approach. The simple explanation is that code files declare how you want the database to look and then a separate ‘engine' has the task of figuring out a script which will make a blank or existing database match the declaration. It's not a magic bullet since it doesn't cope with all scenarios out-of-the-box however there is usually a way to code around the trickier situations. I explained how I converted Contoso University from an Entity Framework Code Migrations way of managing database changes to a declarative one using SQL Server Data Tools here. If you are new to SSDT and want to learn about it I have a Getting Started post here.
As far as Visual Studio solutions are concerned, the declarative approach using SSDT is delivered by way of SQL Server Database Projects. In Contoso University this is the ContosoUniversity.Database project. This contains CREATE scripts for tables and stored procedures as well as for permissions and security. Not only does it contain scripts that represent the database schema, it also contains scripts that can insert reference data in to a newly created database. The significance of this becomes apparent when you realise that a database project can be used to create a database in LocalDB, which can be made fully functioning by inserting reference data. In many cases this could remove the need for a ‘dev' environment, as the local workstation is the dev environment, database and all. This eliminates the problem of config files containing connection strings specific to developers (and the check-in problems that this can cause) because the connection string for LocalDB is generic. This way of working has the potential to wave goodbye to situations where getting an application working on a development machine requires a lengthy setup script and usually some magic.
To see this in action, within the ContosoUniversity.Database project right-click CU-DEV.publish.xml and choose Publish. In the Publish Database CU-DEV.publish.xml dialog click on Publish:
Now make sure that the ContosoUniversity.Web project is set as the startup project (if not in bold right-click and choose Set as StartUp Project) and hit F5 to run the application. A fully functioning ContosoUniversity should spring to life. Navigating to (for example) Departments should allow you to perform all CRUD activities. How's that for simplicity?
Tour Stops Here
That's it for our tour of Contoso University. Although there are automated UI tests in the solution I'm purposefully not covering them here as they are very much an advanced topic and I'm also planning some changes. That said, if you have FireFox installed on your development machine you can probably make them run. See my post in a previous blog series here.
In the next post we look at getting a basic CI build up-an-running. Happy coding until then!
Cheers -- Graham
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.
- Download the Contoso University application using the link above and unblock and then extract the zip to a convenient temporary location.
- 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.
- 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
- Delete SchoolIniializer.cs from the DAL folder.
- Delete the DatabaseInitializer configuration from Web.config (this will probably be commented out but I'm removing it for completeness' sake):
<context type="ContosoUniversity.DAL.SchoolContext, ContosoUniversity">
<databaseInitializer type="ContosoUniversity.DAL.SchoolInitializer, ContosoUniversity" />
- Remove the Migrations folder and all its contents.
- Expand the ContosoUniversity2 database from the SQL Server Object Explorer window and delete dbo._MigrationHistory from the Tables folder.
- 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)
- Add an SSDP called ContosoUniversity.Database to the solution.
- Import the ContosoUniversity2 database to the new project using default values.
- In the ContosoUniversity.Database properties enable Code Analysis in the Code Analysis tab.
- Create and save a publish profile called CU-DEV.publish.xml to publish to a database called CU-DEV on (LocalDb)\v11.0.
- In Web.config change the SchoolContext connection string to point to CU-DEV.
- Build the solution to check 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:
- In the SQL Server Object Explorer window expand the tree to display the ContosoUniversity2 database tables.
- 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).
- 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:
IF NOT EXISTS (SELECT TOP 1 * FROM dbo.Department)
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
- Save the file in the ReferenceData folder in the format TableName.data.sql and add it to the solution as an existing item.
- 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.
- When all the ReferenceData files have been processed the Script.PostDeployment.sql should look something like:
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.
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:
- 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.
- 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.
- 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
- 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
Getting Started with SQL Server Database Projects
By now hopefully all developers understand the importance of keeping their source code under version control and are actually practising this for any non-throwaway code. That's all fine and dandy for your application, but what about your database? In my experience it's pretty rare for databases to be under version control, probably because in the past the tooling has been inadequate or simply off developer radars. There are a number of tools that can help with database version control but one of most readily accessible for Visual Studio developers is the SQL Server Database Project that can be added to a Visual Studio solution. SQL Server Database Projects are part of the Microsoft SQL Server Data Tools ( SSDT) package, which is obviously aimed at developing against SQL Server. You can start with a blank database but most likely you will already have an existing database in which case the database project has the ability to reverse engineer the schema. The result of this process is a series of files containing CREATE statements for the objects that comprise your database (tables, stored procedures and so on), with the files themselves (usually one per object) organised in a folder structure. Since these are essentially text files just like any other code file you can check them in to version control and have any changes recorded just like you would with, for example, a C# file.
In addition to facilitating version control database projects offer a wealth of extra functionality. A declarative approach is used with database projects, ie you state how you want your database to be via CREATE statements and then another process is responsible for making the schema of one or more target databases the same as the schema of your database project. You can also publish your schema to a new database -- ideal if you need to create a LocalDB version on a new development workstation for example. This is really only the tip of the iceberg and I encourage you to use the resources below as a starting point for learning about database projects and SSDT:
Since SSDT is built-in to Visual Studio 2013 the barrier to getting started is very low. Be sure to check for any updates from within Visual Studio (Tools > Extensions and Updates) before you begin. Finally, anyone who has spotted that the SQL Server installation wizard has an option to install SQL Server Data Tools has every right to be confused, since at one point in time this was also the new name for what was once BIDS (Business Intelligence Developer Studio). If you want to know more then this post and also this one will help clarify. Maybe.
Cheers -- Graham