Take control of "building" your database (3/3)

Published 17 July 07 10:23 PM | dmckinstry

Part 3 - Database Testing during build

If you haven’t caught the first two parts of this series, check out the first part which describes why database builds are interesting and how to approach it for your team and the second part which details how to approach database builds using Team Build.

One place that teams of developers often collide is in the data tier.  Team Edition for Database Professionals facilitates better team development and Team Build can enable developers to use a version controlled database definition.  A great way to verify that updates haven’t broken existing functionality or collided with changes from another developer is through unit tests.  This is true in the middle tier and also in the data tier.  MSDN already provides basic instructions on how to create database unit tests.  It also provides information on how to use data generation to prepare your database for reproducible test runs.  I’m not going to cover those topics but instead I’d like to explain how to use Team Build along with the aforementioned to automate integration testing at the data tier.

Team Build already provides the ability to automatically run unit tests as part of the build process.  The Team Build testing process is described in MSDN and will work with database unit tests as well as any other Team System supported test.  So if the answer is simply create a test list that includes your database unit tests, why am I even blogging about it?  Because I believe the integration process of automated build and unit test is insufficient if you can’t guarantee the data you’re testing against.

Performing Data Generation in Team Build

When you install Visual Studio Team Edition for Database Professionals on your build server you gain access to the custom MSBuild tasks that are used by Visual Studio.  This includes several custom build and deploy tasks that we masked in my previous post by calling MSBuild directly against the database project file.  Like those hidden build and deployment tasks there is also a custom task to execute a data generation plan.  Unlike those tasks, I propose that you call this task directly in your team build or custom targets file.

You can gain access to the data generation task from Microsoft by referencing it in your custom targets or TFSBuild.proj file.  Since it is installed into the GAC, you don’t need to specify the location, just the strongly named assembly.  Note that directly accessing this task isn’t documented by Microsoft.  By directly accessing it, you are putting your custom scripts at risk if Microsoft updates this in a service pack or other upgrade and doesn’t use a compatible binding.  Assuming you’re not scared off by this, here is the UsingTask statement to reference the data generator task:

<UsingTask
  TaskName="DataGeneratorTask"
  AssemblyName="Microsoft.VisualStudio.TeamSystem.Data.Tasks, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

Once it is referenced, you can access it in your XML as follows:

<DataGeneratorTask
  ConnectionString="MyConnectionString"
  SourceFile="$(SolutionRoot)\DBProject\Data Generation Plans\MyGenerator.dgen"
  PurgeTablesBeforePopulate="True" /> 

The connection string (i.e. “MyConnectionString”) references a standard SQL Server connection string.  This is in contrast to the connection string information for the build process in the previous post that must handle the initial catalog separately.  You can, of course, specify whether you want to purge the tables before running the plan by specifying the PurgeTablesBeforePopulate flag.

So when should you insert this magic snippet of XML? If you followed my recommendation from my previous post and performed the database build during AfterCompile target, you are ready to populate the database immediately after that point.

One final comment is that you should consider parameterizing your connection information either as MSBuild properties or items.  Items will give you more flexibility as the same custom targets file will be able to build and deploy multiple databases during the same build if needed.  Assuming an item named “Database” with metadata for ConnectionString, InitialCatalog, ProjectFile, and GenerationFile, the whole AfterCompile target will look something like the following:

<Target Name="AfterCompile">
  <MSBuild
    Projects="%(Database.ProjectFile)"
    Properties="Configuration=Default;OutDir=$(SolutionRoot)\..\binaries\Default\;TargetDatabase=%(Database.InitialCatalog);TargetConnectionString=%(Database.ConnectionString)"
    Targets="Build;Deploy" />

  <DataGeneratorTask
    Condition=" ‘%(Database.GenerationFile)’ != ‘’ “
    ConnectionString="%(Database.ConnectionString);Initial Catalog=%(Database.InitialCatalog)"
    SourceFile="%(Database.GenerationFile)"
    PurgeTablesBeforePopulate="True" />
</Target>
 

This is just a starting point and with the magic that is MSBuild, you can really make the database buld process your own.  Hopefully you’ve found this series of posts helpful.  I’ll try to turn the next few around a little quicker.

Enjoy!

Comments

No Comments

This Blog

Microsoft VSTS Blogs

MSDN Forums

VSTS Community Blogs

Syndication