This time I'm genuinely surprised that no one has tackled this and posted it on a blog or to the DBPro or Team System forums.

When you add a unit test for a stored procedure in Visual Studio Team System for Database Professionals (DBPro) for the first time, you get the dialog asking how you want to set up the unit test.

It asks you what unit test project you want to put the new stored procedure unit in to.  It asks you if you want to automatically deploy the database each time you run the unit test.  It asks you if you want to execute any database data generation plan, too.  You set all that data and hit OK and then it creates the new unit test for you, adds some stuff to App.config, and adds a file called DatabaseSetup.cs to your unit test project.  You run the unit test and all is happy and wonderful.

Ok.  Here's where it gets hairy.  Run this unit test as part of a Team Build.

BOOM! Your unit tests fail because it can't find the database project file.  (Whuh?)

Error Message

Database deployment failed. Path 'c:buildAgileTeamProjectAgile Team ProjectBuild 1 VstsTddLabs.NorthwindDatabaseVstsTddLabs.NorthwindDatabase.dbproj' is not a valid path to a database project file.

Error Stack Trace
at Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.DatabaseTestService.DeployDatabaseProject(String databaseProjectFileName, String configuration, String providerInvariantName, String connectionString)
at Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.DatabaseTestService.DeployDatabaseProject()
at VstsTddLabs.VisualStudioUnitTests.DatabaseSetup.IntializeAssembly(TestContext ctx) in c:buildAgileTeamProjectAgile Team ProjectBuild 1SourcesVstsTddLabs.VisualStudioUnitTestsDatabaseSetup.cs:line 55

So, a little hunting around in Reflector in the DBPro unit test framework and I figured out that those values that get written into App.config are important. (Surprise!)

Those relative paths with the ".." are kind of a dead giveaway.  The folder structure when you run from Visual Studio is different from when you run/build on a TFS Team Build Server so those up-one-level ("..") directives are making the unit test framework look for the project in the wrong location.

Ok.  So now how do we fix this problem without having to do open heart surgery on our config files at build time using a custom MSBuild task?

My solution is to add some additional logic to the DatabaseSetup.cs project.  Here's what that file looks like by default.

[AssemblyInitialize()]
public static void IntializeAssembly(TestContext ctx)
{
//   Setup the test database based on setting in the
// configuration file
DatabaseTestClass.TestService.DeployDatabaseProject();
DatabaseTestClass.TestService.GenerateData();
}

All we have to do is put a call to check and adjust the App.config settings before the call to DeployDatabaseProject().  In case you're worried, this is ok because the App.config used to run the test on the build server isn't marked as ReadOnly and isn't version controlled.

[AssemblyInitialize()]
public static void IntializeAssembly(TestContext ctx)
{
CheckDbProPathsAndAdjust();
//   Setup the test database based on setting in the
// configuration file
DatabaseTestClass.TestService.DeployDatabaseProject();
DatabaseTestClass.TestService.GenerateData();
}

So, I added the call to CheckDbProPathsAndAdjust() which opens up app.config and validates the DatabaseProjectFileName and DataGenerationFileName values.  If they don't exist then it tries to modify them to where they'd be on a Team Build server.  If the file exists at the modified location, then app.config gets edited and saved before the DBPro unit test framework tries to load it.

The line of code that really does the fixing is actually quite simple.

path = path.Replace("..\..\..\", "..\..\..\Sources\");

The DBPro project through Team Build actually lives one layer lower in the tree than it does in a Visual Studio build.  So, just adding that additional "Sources" folder reference fixes it.

Here's the rest of the code.

public static void CheckDbProPathsAndAdjust()
{
Configuration config =
ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
ConfigurationSection section = config.GetSection("DatabaseUnitTesting");

if (section == null) return;

DatabaseUnitTestingSection dbUnitTestConfig =
section as DatabaseUnitTestingSection;

if (dbUnitTestConfig == null) return;

string dbProjectFile = dbUnitTestConfig.DatabaseDeployment.DatabaseProjectFileName;
string dataGenPlanFile = dbUnitTestConfig.DataGeneration.DataGenerationFileName;

bool adjustedDbProjectFile = CheckPathAndAdjustForTeamBuild("DatabaseProjectFileName", ref dbProjectFile);
bool adjustedDbGenerationPlanFile = CheckPathAndAdjustForTeamBuild("DataGenerationFileName", ref dataGenPlanFile);

if (adjustedDbGenerationPlanFile == true)

if (adjustedDbProjectFile == true)

if (adjustedDbGenerationPlanFile == true || adjustedDbProjectFile == true)
{
Console.WriteLine("Saving changes to app.config for DBPro.");
config.Save();
}           
}

///

/// Adjusts the file path for team build file/folder structure
///

///
///
/// True if the path value was changed.
public static bool CheckPathAndAdjustForTeamBuild(string fileDescription, ref string path)
{
if (String.IsNullOrEmpty(path) == false)
{
if (File.Exists(path) == false &&
path.StartsWith("..\..\..\") == true)
{
Console.WriteLine("{0} {1} was not found.  Attempting to correct.",
fileDescription, TranslateRelativePathToAbsolutePath(path));

// If we're running in a Team Build then the path is a little different
path = path.Replace("..\..\..\", "..\..\..\Sources\");

Console.WriteLine("Trying {0} at {1}.", fileDescription, TranslateRelativePathToAbsolutePath(path));

if (File.Exists(path) == true)
{
// change where the db project is loaded from
Console.WriteLine("Using corrected {0} at {1}.",
fileDescription,
TranslateRelativePathToAbsolutePath(path));
return true;
}
else
{
Console.WriteLine("Could not find {0} at corrected path {1}.",
fileDescription,
TranslateRelativePathToAbsolutePath(path));
return false;
}
}
else
{
// it exists at the default location
// probably because we're running in Visual Studio
return false;
}
}
else
{
// This kind of file is not configured
// probably no data generation plan or no auto-deploy db at start of unit tests
return false;
}
}

-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?  Contact me via http://www.benday.com.