Scripting Database Creation and Fake Data Generation From Data Dude Without Data Dude Installed
Posted by Keith Elder | Posted in .Net | Posted on 30-08-2007
I have Data Dude (aka, Visual Studio for Database Professionals) installed on my local machine. However, my other team members don’t have Data Dude installed. They are running Visual Studio Team System for Developers. This puts us all in a quandary. For example, if I update the database with Data Dude, how do the other developers get the latest updates installed to their local sandbox? Better yet, how do they generate fake data for the database when they don’t Data Dude installed? This is supposed to be addressed in future versions of Data Dude but I needed something now. Here is what I came up with.
For the record, getting the database updated on other team member’s computer’s is rather easy since Data Dude builds a complete SQL file with all of your changes. Using sqlcmd from the command prompt you can replay the contents of this file back into your database to reset it. Initially I created a batch file to do this and here is the results of that:
- sqlcmd.exe -E -S Localhost -d master -w 500 -I -r 1 -b -m-1 -i “DatabaseSchema.(local).MyDatabase.sql”
- cd ..\Scripts\Post-Deployment\
- sqlcmd.exe -E -S Localhost -d master -w 500 -I -r 1 -b -m-1 -i “Script.PostDeployment.sql” -v filepath=”C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA” -v databasename=”MyDatabase”
Running these series of commands from a .bat file re-creates the database just fine. The problem is I didn’t have a way to generate the fake data for the team members. While I was writing a unit test for a stored procedure it hit me. The unit testing tool asked me a series of questions when I created my first unit test. It asked the database name, if I would like the data regenerated each time and if I wanted to clean the data or not. Surely it took that information and put it somewhere so it could see what was going on. I cracked open the app.config file on the unit test project and saw this:
<?xml version=“1.0“ encoding=“utf-8“ ?>
<configuration>
<configSections>
<section name=“DatabaseUnitTesting“ type=“Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Configuration.DatabaseUnitTestingSection, Microsoft.VisualStudio.TeamSystem.Data.UnitTesting, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a“ />
</configSections>
<DatabaseUnitTesting>
<DatabaseDeployment DatabaseProjectFileName=“..\..\..\Database\DatabaseSchema\DatabaseSchema.dbproj“
Configuration=“Debug“ />
<DataGeneration DataGenerationFileName=“..\..\..\Database\DatabaseSchema\Data Generation Plans\FakeData.dgen“
ClearDatabase=“true“ />
<ExecutionContext Provider=“System.Data.SqlClient“ ConnectionString=“Data Source=.;Initial Catalog=Mydatabase;Integrated Security=True;Pooling=False“ />
<PrivilegedContext Provider=“System.Data.SqlClient“ ConnectionString=“Data Source=.;Initial Catalog=Mydatabase;Integrated Security=True;Pooling=False“ />
</DatabaseUnitTesting>
</configuration>
Digging into this I could see there was a custom section called DatabaseUnitTesting. I cracked opened the dll Microsoft.VisualStudio.TeamSystem.Data.UnitTesting using Reflector to see what was exposed and how things were configured. After poking around I created a Windows Console application and added a few references to the project:
Once the references were setup I added the above app.config to the project and then added essentially two lines. One to deploy the database and the other to generate the fake data.
static void Main(string[] args)
{
try
{
Console.WriteLine(“Rebuilding Database…..”);
DatabaseTestClass.TestService.DeployDatabaseProject();
Console.WriteLine(“Generating Fake Data….”);
DatabaseTestClass.TestService.GenerateData();
Console.WriteLine(“Completed.”);
Console.ReadLine();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
}
}
After these few and simple steps I built the project and then ran it. My console window opened and started running. After about 30 seconds it finished.
Holy cow! I just recreated Data Dude’s two main features in a matter of a few lines of code!
The only thing left I had to do is to set the VisualStudio DLLs to copy out to the local directory. This worked on my computer at first since these DLLs were already installed in the GAC. However, the other team members obviously won’t have them in the GAC. On each of the VisualStudio DLLs I right clicked on properties and set the “Copy Local” option to true.
This will force all the required assemblies to be pushed out into the build folder once a build is done. The final build ended up looking like this:
Now everyone on the team can update their local sandboxes and restore the latest fake data when the database is updated. I’m really excited to have this working because we’ve had a lot of trouble keeping database changes in sync on developer machines. Data Dude helps with the problem but only if you have it installed and if you are just a Developer on the team who doesn’t have it installed, there is no reason why you should be left out of all the fun.
In the future this is “supposed” to be built into Visual Studio so Developers won’t have to jump through this many hoops to leverage Data Dude. Until then, this works. I hope those of you that have been struggling with this as I have find this useful.