Geeks With Blogs


Dylan Smith ALM / Architecture / TFS

I've been continuing my research and experimentation with TDD lately.  For my tests that actually hit against the database (as opposed to the ones where I mock the database layer), I need to ensure that the database is in a reproducable state to ensure that the test itself is reproducable.  I figured the best way to do this was to just re-create the database automatically every time my unit tests ran (well the ones that hit against the database anyways).

I need to recreate not only the database schema but a known set of sample data too that my tests can assume will be present and base their assumptions off.

I decided that this is an excellent opportunity to use the new Visual Studio Team System For Database Professionals (aka DataDude).  So I added a database project to my solution, and imported the database schema I want to use.  Then I created a post-deployment script that will generate the sample data.  I really wish that DataDude would include a feature that could generate all these INSERT statements for me automatically by importing it along with the schema.

So now that I have this database project with all my schema, and script to create the sample data, how do I make it auto-deploy with each unit test.  This turned out to be a little trickier than I had hoped, but once I figured it out it's not so bad.

The first thing I needed to do was get the sql script that the database project generates just right.  I went into the build properties and set it to be a “New” build (as opposed to an update build).  I also checked the option to generate a CREATE DATABASE statement as part of the script.  I built the project and took a look at the script.  I immediately noticed a problem.  It generates the CREATE DATABASE statement, but it doesn't have any logic to deal with the situation where the database already exists.  I would want it to do a DROP DATABASE if the database already existed before executing the CREATE DATABASE.

No problem I thought, there is a pre-deployment script feature in DataDude, I can just add some script there with the DROP DATABASE logic.  So off I went and added some logic to check to see if my database existed, and if so drop it.  I rebuilt the project and took a look at the resulting sql.  Well, I was surprised to see that my pre-deployment script didn't actually come first in the script as I had assumed it would.  The CREATE DATABASE section still came before my pre-deployment script, thus defeating the whole purpose.

After a little help from the MSDN forums I found a solution.  Just turn off the build option that adds the CREATE DATABASE statement, and do that myself as part of my pre-deployment script.  A few minutes later I had it working.  I soon noticed another problem however.  If anybody else had a connection open to the database the DROP DATABASE statement would fail with the error that the database is in use.  After a little digging around in SQL Books Online I discovered the solution:



This essentially kills any other connections, rolling back any pending transactions, then drops the database.

So now I have this sql script that is generated every time I build the solution.  The next hurdle is to figure out how to get it to run on demand with my unit tests.  I used the Database Test Configuration option from the Test menu   Here you can choose a database project from the solution to be the “database project for schema deployment“.  This is a feature that was added as part of Data Dude.  At this point in time I believe the only thing it does is stick a couple of entries into the app.config file and then ignores them.  I assume this is going to do something in the future which would make this scenario much more streamlined.  For my purposes though, it is still helpful as one of the config entries it makes is the relative path to the .dbproj frile from the directory that the unit tests execute in.

I wrote a helper function that will read in this config entry, tack on the path from the dbproj file to the actual .sql file we're interested in.  Then it reads it into memory, and loops through it chopping it up when it hits a GO and executing it in chunks (this is the only way I could get this to work.  Please leave a comment if you know of an easier way).  Here is the entire function:

Public Shared Sub InitDatabase()
    Dim DBProjPath As String = System.Configuration.ConfigurationManager.AppSettings("dbschema")
    Dim ScriptPath As String = IO.Path.GetFullPath(IO.Path.GetDirectoryName(DBProjPath) & "\Sql\NewDb\TestDatabase.sql")
    Dim ScriptFileReader As System.IO.StreamReader = New System.IO.StreamReader(ScriptPath)
    Dim RawSqlScript As String = ScriptFileReader.ReadToEnd()
    Dim MyConnection As SqlConnection = New SqlConnection("pooling=false;user id=sa;data source=localhost;persist security info=True;password=****")
    Dim Delimiters() As String = {Environment.NewLine & "GO" & Environment.NewLine}
    Dim SqlBatches() As String = RawSqlScript.Split(Delimiters, StringSplitOptions.RemoveEmptyEntries)

    For Each CurBatch As String In SqlBatches
        Dim MyCommand As SqlCommand = New SqlCommand(CurBatch.Replace(Environment.NewLine & "GO" & Environment.NewLine, ";"), MyConnection)
End Sub

All I need to do is call InitDatabase() from my unit tests that hit the database, and it will automatically re-create the database and any sample data from scratch every time the unit test is run.

Does anybody else out there take a similar approach?  Let me know how you guys do things differently or how I can improve this approach.

Posted on Wednesday, July 12, 2006 5:42 PM | Back to top

Comments on this post: Database Deployment with Unit Tests + Data Dude

# re: Database Deployment with Unit Tests + Data Dude
Requesting Gravatar...

I'm excited to see you putting Team Edition for DB Pros to use! Keep blogging about your experiences!

I wanted to give you some of my thoughts on what you are trying to do. So we equally ackowledge that one of the most critical aspects of database unit testing is managing the data on the database. So we created a test data generation tool that allows you to create data generation plans that will generate data that adheres to your database constraints and business rules. We have optimized database unit testing for use with this tool. So the idea is that you create a database project, you then create a data generation plan. Then you write your unit tests. Now when you run your unit tests, it should automatically deploy your database, generate test data on the database, and run your tests. The data that is generated is repeatable, so you can have your tests count on that data being there. You also note correctly that the automatically deployment feature is still underway. Right now we just write the information out the config file, but we will then do the deployment automatically for you. This has just not yet been implemented yet.

You also suggest another fine and dandy way of dealing with test data - that is scripting out the inserts for data that you already have and putting it in the post-deployment script. If you do this, then when we support automatic deployment, the test data will automatically be inserted into your database after you run your tests. You can also use our tool to help get the insert statements. If you use data compare and have it compare one db with the data and one db without the data, you can then create a sync script that has all the data in it. I agree we can make this experience a little bit more seamless, but we have focused on seamless integration with our test data generation tool.

I like your little helper code you have there! But the idea is that our product should automate all those tasks so you don't have to. Once we have implemented automatic deployment during unit testing, you should be able to press F5 on your test project and it will automatically do the build, deploy, generate data, and run tests as I describe above. You can also perform all these tasks at the command-line using MSBuild, since there are MSBuild tasks defined for build, deploy, and generate data.

Sachin Rekhi
Program Manager
Team Edition for Database Professionals
Left by Sachin Rekhi on Jul 17, 2006 2:32 PM

# re: Database Deployment with Unit Tests + Data Dude
Requesting Gravatar...
Thanks for the input Sachin. I did a little more digging, and I feel I have a somewhat better understanding of the features now. I had briefly looked at the data generation plans before, but it had appeared to me like they were mostly of use if you wanted to generate a bunch of random data, which we typically do not want. However, after reading an article on Cameron Skinners blog ( it looks like there's alot more to it than that. I will be taking another look into Data Generation plans shortly and will be sure to blog about my experiences.

About the automatic deployment, as I alluded to in my post, I did notice that it appears there are features on the way that will make this scenario much simpler. The question that I would have though, is that I notice all the code to do the initialization and cleanup for a database unit test is auto-genned for you, but what about if you're creating a regular application unit test, but you also need the database auto-deployed. Do you have to write yourself all the plumbing code that is normally genned for you with a db unit test? Do you have to re-create your application unit tests as db unit tests? Or is there an easy way to have this done perhaps declaratively through the use of attributes on my test?

Left by Dylan on Jul 18, 2006 9:21 AM

Your comment:
 (will show your gravatar)

Copyright © Dylan Smith | Powered by: