I've been digging in to the GDR release of Visual Studio Team System Database Edition (aka. DBPro GDR, "data dude") this week.  One of the things that I wanted to see is whether GDR projects were easier to use from inside a Team Foundation Server 2008 (TFS2008) Team Build.  (Answer: yes, a little easier.)

What I wanted to do was to first deploy the database, run a database data generation plan, and then run my database unit tests.  One of the difficulties is related to file paths -- when you run from Visual Studio the paths are one way and when you run the build from Team Build the paths are another way.  (For more information, check out this blog post.)  Another difficulty is how the GDR edition of DBPro does deployments -- you now have the option to either deploy only to a .sql file or to deploy to a .sql file and then write the changes into a database.

Buck Hodges has a great post describing how to get around these and other problems.  I wanted to take what Buck describes and turn it into:

1. something I could re-use
2. something that is easy to understand (No offense, Buck.  That post pretty much melts my brain.)
3. something that would allow me to keep my Desktop build settings separate from my Team Build settings for the DB Pro GDR project

In order to do this, I created two Team Build Tasks: PrepareDatabaseProjectForDeploy and PrepareAppConfigForDatabaseUnitTests.  Here's a sample of using these tasks from the Team Build "BeforeTest" target.

    <**PrepareDatabaseProjectForDeploy**         PathToProjectFile="$(SolutionRoot)Gdr20090410.DatabaseGdr20090410.Database.dbproj"         BuildConfiguration="Debug"         TargetDatabaseName="GdrDeployedFromBuild"         ConnectionString="Data Source=.;User ID=sa;Password=Sa\_password;Pooling=False"         DeployToDatabase="True" />    

<PrepareAppConfigForDatabaseUnitTests
PathToAppConfig="$(OutDir)Gdr20090310.DatabaseUnitTests.dll.config"
UpdateDatabaseProjectPath="True"
UpdateDataGenerationPlanPath="True"
UpdateExecutionConnectionString="True"
UpdatePrivilegedConnectionString="True"
PathToProjectFile="$(SolutionRoot)Gdr20090410.DatabaseGdr20090410.Database.dbproj"
PathToDataGenerationPlan="$(SolutionRoot)Gdr20090410.DatabaseData Generation PlansDataGenerationPlan1.dgen"
ExecutionConnectionString="Data Source=.;User ID=sa;Password=Sa_password;Pooling=False;Initial Catalog=GdrDeployedFromBuild"
PrivilegedConnectionString="Data Source=.;User ID=sa;Password=Sa_password;Pooling=False;Initial Catalog=GdrDeployedFromBuild" />

PrepareDatabaseProjectForDeploy sets up all the required settings to deploy your database project from Team Build.  NOTE: This task just prepares the settings.  In order to actually do the deploy you need to make a call to MSBuild (see above).

• PathToProjectFile -- path to your DBPro project file (*.dbproj)
• BuildConfiguration -- what kind of build are you running?  Debug, Release, etc.
• TargetDatabaseName -- what is the name of the database that you want to deploy to
• ConnectionString -- the connection string to connect to the database service
• DeployToDatabase -- if this value is True, the deploy action will create a .sql schema create script and then execute the deploy against a real database.  If the value is False, then only the .sql file will be created.

PrepareAppConfigForDatabaseUnitTests updates all settings in your database unit test project's .config file so that you can run your database data generation plans and run your database unit tests.

• PathToAppConfig -- the path to the config file for your unit tests.  This is going to be assembly_name.dll.config file in the Binaries directory NOT the app.config file in the Sources directory.
• PathToProjectFile -- the path to your DBPro GDR project file
• PathToDataGenerationPlan -- the path to your data generation plan (*.dgen)
• ExecutionConnectionString -- connection string for executing the unit tests
• PrivilegedConnectionString -- connection string for executing the dgen plan and, if configured in your unit test project, the optional database deploy
• UpdateDatabaseProjectPath -- true = override the value, false = use the default value from app.config
• UpdateDataGenerationPlanPath -- true = override the value, false = use the default value from app.config
• UpdateExecutionConnectionString -- true = override the value, false = use the default value from app.config
• UpdatePrivilegedConnectionString -- true = override the value, false = use the default value from app.config

Here's a link to download the code for the Team Build Tasks, a sample Visual Studio Team System DBPro 2008 GDR project, and a sample Team Build script.  The sample Team Build script assumes that you've deployed the MSBuild tasks to C:Program Files (x86)MSBuildBendayDatabaseBuild on your build server.

Enjoy.

-Ben

-- Looking for Team Foundation Server or Visual Studio Team System training or mentoring?  Need some help doing the Team Foundation Server installation or maybe some customizations to make TFS work smoothly within your development process?  Check out our VSTS/TFS Training Course outline here or contact me via http://www.benday.com.