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:
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE MyDatabase
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()
ScriptFileReader.Close()
ScriptFileReader.Dispose()
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)
MyConnection.Open()
For Each CurBatch As String In SqlBatches
Dim MyCommand As SqlCommand = New SqlCommand(CurBatch.Replace(Environment.NewLine & "GO" & Environment.NewLine, ";"), MyConnection)
MyCommand.ExecuteNonQuery()
MyCommand.Dispose()
Next
MyConnection.Close()
MyConnection.Dispose()
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.