Rails-Style Test Database Creation in ASP.NET MVC

One of the great things about Ruby on Rails is that it uses three databases: development (for dev), test (for unit and integration tests), and production. The test database is actually created prior to running any of the tests; it uses migrations to build the schema, and plain-text (YAML) files to populate test data as you specified.

Pretty cool.

This is actually possible in ASP.NET MVC (as of MVC3), with a bit of work. You will need:

  • Your ASP.NET MVC application (of course)
  • MigratorDotNet (or your migrations tool of choice)
  • Visual Studio Test Framework(or your testing tool of choice)
  • A bit of practice and ingenuity!

Here’s how it works. Given that you already have your development environment working, along with your set of migrations to create your schema tables (including the core ASP.NET tables if you’re using the built-in login module), you just need to create a once-per-test-run method that will recreate the database and run the migrations.

Sounds complicated? It’s not. Here’s how it would look (with the tool stack listed above):


[AssemblyInitialize] // Run once per test run
public static void RecreateDatabase(TestContext context)
{
DataContext dataContext = new DataContext(connectionString);

// Drop all the foreign keys; they can prevent tables from being dropped
dataContext.ExecuteCommand(@"DECLARE @sqlForeignKeys VARCHAR(MAX)

SELECT @sqlForeignKeys = ISNULL(@sqlForeignKeys,'') +
'ALTER TABLE dbo.[' + OBJECT_NAME(FK.parent_object_id) + '] DROP CONSTRAINT [' + FK.name + '];' + CHAR(10)
FROM SYS.FOREIGN_KEYS FK

EXEC(@sqlForeignKeys)");

// Drop all tables
dataContext.ExecuteCommand("EXEC sp_MSforeachtable @command1 = \"DROP TABLE ?\"");

// Run migrations to rebuilt schema
Assembly migrationsAssembly = Assembly.Load("..."); // main application assembly
Migrator.Migrator m = new Migrator.Migrator("SqlServer", connectionString, migrationsAssembly);
m.MigrateToLastVersion();
}

The code is pretty straight forward: we drop all the foreign keys first, then the tables. We use sp_MSforeachtable, which is a stored procedure that iterates over all tables. (We drop foreign keys first, because you can’t drop a table that has a foreign key pointing to one of its columns.)

Then, we get a hold of our main assembly (the one containing the migrations), and we run those.

And everything just works! The AssemblyInitialize attribute ensures that this code is only run once, when the test assembly is loaded.

And just like that, you have your very own Rails-style database being recreated on-the-fly for running tests. (Depending on your flavour of ORM, you can even add your own seed data here for testing.)

About Ashiq Alibhai, PMP

Ashiq has been coding C# since 2005. A desktop, web, and RIA application developer, he's touched ASP.NET MVC, ActiveRecord, Silverlight, NUnit, and all kinds of exciting .NET technologies. He started C# City in order to accelerate his .NET learning.
This entry was posted in Core .NET, Web and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *