Data Access Component Testing Redux
About a year ago, I wrote about data access component testing using a custom helper class for setting up a shared fixture. Since then, I've standardized on using Installers for fixture setup whenever it makes sense, which it does in this case.
Instead of using the custom SqlServerIntegrationTester helper class, you can use general-purpose Installers to setup the database. In previous posts, I've described a set of SQL Server-based Installers, and in this post, I'll provide an example of how to use these for integration testing. Like my original post on data access component testing, I'll be testing the HoneyAccessor class.
Just like in the previous post, I'll build up an immutable shared fixture consisting of the database itself, and a persistent fresh fixture that contains all the test-specific data. Before the first test executes, I'll build up the immutable shared fixture by creating the database. After each test, I'll tear down the persistent fresh fixture by truncating all tables, and after all tests have completed, I'll delete the database again.
Since there's a fair bit of scaffolding code involved in building the shared fixture, as much as possible of this code should be factored into a reusable class that I'll call SqlFixture - I'll get back to this class in a little while, but here's a sample of a typical integration test and a bit of its structure:
[TestClass]
public class HoneyAccessorTest
{
private readonly SqlFixture fixture_;
public HoneyAccessorTest()
{
this.fixture_ = new SqlFixture();
}
[TestCleanup]
public void TearDownFixture()
{
this.fixture_.ResetTables();
}
[TestMethod]
public void ValidateCreatedBee()
{
HoneyAccessor ha = new HoneyAccessor(this.fixture_.ConnectionString);
Bee b = ha.CreateBee("Birgit", "Worker");
Assert.AreEqual<int>(1, b.Id);
Assert.AreEqual<string>("Birgit", b.Name);
Assert.AreEqual<string>("Worker", b.Type);
// Validate the data in the database using standard ADO.NET
}
}
The most noticable point here is that the test makes heavy use of the SqlFixture class, so let's take a detailed look at that. SqlFixture uses the SQL Server-based Installer classes together with other, test-specific data to setup and tear down the test fixture. All this data is contained in three private member variables:
private string connectionString_;
private HoneySqlAccessInstaller sutInstaller_;
private SqlScriptListInstaller teardownInstaller_;
The connectionString_ variable obviously holds the connection string for the database that will be created as part of the fixture. The SqlFixture constructor sets it up in this way:
SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
scsb.DataSource = "localhost";
scsb.InitialCatalog = "HoneyTest";
scsb.IntegratedSecurity = true;
scsb.Pooling = false;
this.connectionString_ = scsb.ConnectionString;
As you can see, all tests using SqlFixture will use a local database called HoneyTest. This satisfies my integration testing principles well enough, but if you need a more dynamic way of assigning these values, the modifications should be trivial.
The sutInstaller_ variable is the SUT's own Installer. As described in a previous post, the SUT can have its own Installer, which is the case with HoneyAccessor. This custom installer is called HoneySqlAccessInstaller, and it contains installation data for setting up anything HoneyAccessor needs (a SQL Server database).
this.sutInstaller_ = new HoneySqlAccessInstaller();
The teardownInstaller_ variable holds a test-specific custom Installer whose single purpose it is to tear down the fresh fixture. Building this Installer is by far the most complicated part of all the plumbing code:
List<SqlScriptInstaller> teardownInstallers = new List<SqlScriptInstaller>();
teardownInstallers.Add(this.CreateScriptInstaller("RemoveForeignKeys.sql"));
teardownInstallers.Add(this.CreateScriptInstaller("TruncateTables.sql"));
teardownInstallers.Add(this.sutInstaller_.ForeignKeyInstaller);
teardownInstallers.Add(this.sutInstaller_.GlobalDataInstaller);
this.teardownInstaller_ = new SqlScriptListInstaller(teardownInstallers);
this.teardownInstaller_.Context = new InstallContext();
this.teardownInstaller_.Context.Parameters["ConnectionString"] = this.connectionString_;
As described in my former post on data access integration testing, tearing down the fresh fixture requires me to first remove all foreign key relationships, truncate all the tables, recreate the foreign keys and repopulate all global data. This is done by four different T-SQL scripts contained if four different SqlScriptInstallers. Removing foreign keys and truncating tables are test-specific actions, so they are defined in scripts contained in test project itself.
The foreign key relationships, on the other hand, are already defined by the SUT's Installer, so reusing those definitions makes the most sense. This is also the case for the global data, so here I'm also reusing the SUT's Installer.
Since both SqlDatabaseInstaller and SqlScriptInstaller requires the ConnectionString parameter, it's necessary to add this to the teardown Installer's Context.
With all this in place, setting up and tearing down the immutable and persistent fresh fixtures is relatively straightforward.
The CreateTestDatabase method sets up the immutable shared fixture (the database):
public void CreateTestDatabase()
{
this.DeleteTestDatabase();
TransactedInstaller ti = new TransactedInstaller();
ti.Context = new InstallContext();
ti.Context.Parameters["ConnectionString"] = this.ConnectionString;
ti.Installers.Add(this.sutInstaller_.DatabaseInstaller);
ti.Installers.Add(this.sutInstaller_.ScriptListInstaller);
ti.Install(new Hashtable());
}
For good measure I'm first attempting to delete the test database, since an earlier test run may have left the system in a state where the database still exists. The rest is simply a question of reusing the SUT's SqlDatabaseInstaller and SqlScriptListInstaller instances to create the database.
Tearing down the immutable shared fixture is even simpler:
public void DeleteTestDatabase()
{
this.sutInstaller_.DatabaseInstaller.Context = new InstallContext();
this.sutInstaller_.DatabaseInstaller.Context.Parameters["ConnectionString"] = this.ConnectionString;
this.sutInstaller_.DatabaseInstaller.Uninstall(null);
}
Here, I'm simply relying on the fact that calling Uninstall on a SqlDatabaseInstaller deletes the database completely.
While setting up a fresh fixture is the responsibility of each individual test, tearing it down always happens in the same way:
public void ResetTables()
{
this.teardownInstaller_.Install(new Hashtable());
}
If you'll refer back to the sample test in the beginning of this post, you'll notice that the ResetTables method is being called from a method decorated with the TestCleanup attribute, ensuring implicit teardown of the fresh fixture.
While this example may seem a bit hard to follow, it's mainly because responsibilities are factored out into so many different classes, so I've also attached the entire sample project so you can download it and peruse it at your leisure. As usual, the code is provided as is, etc.