My Resume

  • My Resume (MS Word) My Resume (PDF)


Affiliations

  • Microsoft Most Valuable Professional
  • INETA Community Champion
  • Leader, NJDOTNET: Central New Jersey .NET User Group

Sunday, November 16, 2008

Easier Automated Database Testing with SQL Express

Scenario

I've got a project in which I actually have full create scripts for my database such that I can build a whole new instance from the bottom up.  I've also got some automated unit/integration tests that I want to run against this database, complete with a bunch of scripts that can build some test data for me (unrealistic, I know...  but bear with me :).  Also, I really don't want to have to worry about configuring connection strings just for my tests - I just want some database available to me when I need it that I can wail on with requests and gets cleaned up for me when I'm done.  Finally, I want to keep my tests as isolated as possible, which to me means a file-based SQL Express database; that way, I can attach, detach, and delete as much as I want with as little exposure and impact to the rest of my build system as possible.

Solution

My solution to the above scenario I found myself in was to create a helper class called TestDatabase whose job is to give me a database when I need one, provide me with a clean version of my test data before each test I run, and clean up after me when I'm done.  To this end, I started searching for how to create a file-based SQL Express database using code, and came up with Louis DeJardin's great blog post that walked me right though it.  After I had that, it was a simple matter of whipping up the class, shown below (Note: this is only a partial listing.  You can get the full listing from my code repository):

TestDatabase.cs (partial listing)
public class TestDatabase : IDisposable
{
private readonly string connectionString;
private readonly string databaseFilename;

public string ConnectionString { get { return connectionString; } }
public string Schema { get; set; }
public string TestDataScript { get; set; }

public TestDatabase(string databaseFilename, string schema, string testData)
{
this.databaseFilename = databaseFilename;
connectionString = string.Format(
@"Server=.\SQLEXPRESS; Integrated Security=true;AttachDbFileName={0};",
Path.GetFullPath(databaseFilename));
Schema = schema;
TestDataScript = testData;
}

public void Dispose()
{
DeleteDatabaseFiles();
}

public void RecreateTestData()
{
EnsureDatabaseCreated();

if (!string.IsNullOrEmpty(TestDataScript))
ExecuteQuery(TestDataScript);
}

// Create a new file-based SQLEXPRESS database
// (Credit to Louis DeJardin - thanks! http://snurl.com/5nbrc)
protected void CreateDatabase()
{
var databaseName = Path.GetFileNameWithoutExtension(databaseFilename);

using (var connection = new SqlConnection(
"Data Source=.\\sqlexpress;Initial Catalog=tempdb;" +
"Integrated Security=true;User Instance=True;"))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText =
"CREATE DATABASE " + databaseName +
" ON PRIMARY (NAME=" + databaseName +
", FILENAME='" + databaseFilename + "')";
command.ExecuteNonQuery();

command.CommandText =
"EXEC sp_detach_db '" + databaseName + "', 'true'";
command.ExecuteNonQuery();
}
}

// After we've created the database, initialize it with any
// schema we've been given
if (!string.IsNullOrEmpty(Schema))
ExecuteQuery(Schema);
}
}


Let's analyze the things we've got going on here:


  1. First, we've got the CreateDatabase() method (lines 34-61) - basically ripped right from Louis's blog post linked above - which does the magic of creating a file-based SQL Express database.  It all boils down to a "CREATE DATABASE" and "EXEC sp_detach_db" call on the local SQL Express instance's tempdb database, which everyone has access to.  Then when that's all done, I execute the schema script that the tester passed in to build the database schema and finish the initial setup.
  2. Now that the database has been created and initialized with its schema, we can run some tests against it!  Problem is, at this point it's just an empty database...  Fortunately for us, we've got the RecreateTestData() method, which just executes the TestDataScript against the current database, allowing us to easily populate whatever test data we want!  This script should include everything it needs to clean out the database and rebuild it from scratch with a new set of clean data.
  3. Built-in connection string management.  As you can see, our constructor takes in a database filename, builds a connection string out of it, and then exposes that connection string to our testers via a read-only property.  That is one less connection string that our test project has to worry about managing in its app.config (or whatever), which is pretty nice and clean, IMHO!
  4. Finally, our big finale:  cleaning up after ourselves!  You can see that TestDatabase implements IDisposable, allowing us to create a Dispose() method which cleans up after everything we've done - namely, deleting the database files we've created along the way.  This means that after everything is said and done, we've left not one footprint of our presence on the build system.

Now, after we've got our TestDatabase class available, our unit tests become as easy as this:


public void SomeCoolDatabaseDrivenServiceTest()
{
var mySchema = System.IO.File.ReadAllText("mySchema.sql");
var testData = System.IO.File.ReadAllText("testData.sql");
using (var db = new TestDatabase("TestDatabase.mdf", mySchema, testData))
{
db.Initialize();
var service = new MyService(db.ConnectionString);
service.DoSomething();
}
}

Of course, individual tests can have even less code if you manage the test database outside of the test by using your test framework's setup and teardown methods.  For example, if I had a whole slew of tests against the same database (which is usually always the case), the test class would start out like this:


TestDatabase database;

public void ClassInitialize()
{
var mySchema = System.IO.File.ReadAllText("mySchema.sql");
var testData = System.IO.File.ReadAllText("testData.sql");
database = new TestDatabase("TestDatabase.mdf", mySchema, testData);
database.Initialize(true);
}

public void TestInitialize()
{
// Rebuild the test data from scratch before EVERY test
database.RecreateTestData();
}

public void ClassCleanup()
{
database.Dispose();
}

Now that we have all of that setup and teardown logic out of the way, we can focus on what we're actually testing, so then that test I showed you before becomes a simple one-liner (as it would have been if we were just passing in a connection string from a configuration file):


public void SomeCoolDatabaseDrivenServiceTest()
{
// No TestDatabase setup - just use its connection string!
var service = new MyService(database.ConnectionString);
service.DoSomething();
}

What's cool about this is that not only do we not have to worry about where to get our connection string from, our entire suite of test data is also being rebuilt for us before every test is run!

Try It Out For Yourself!


If you like what you've seen in this post and want to try it out for yourself, you can grab the full source file (complete with in-line comments and unit tests) from my repository: TestDatabase.cs.  Just drop it in your project and start using it!
Note: The full source file has unit tests included. If you don't want them, you can simply delete them without affecting the main class.


As always, I'd love to hear your comments and feedback on all this.  If you've found this useful or - better yet - if you have a better way of doing it, please let me know!