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.
Comments
Anonymous
February 04, 2008
Hi Mark, The project DBTest seems to be missing from the attached zip fileAnonymous
February 06, 2008
Hi Sven Thank you for pointing that out! I've now uploaded an updated version of the zip file that should fix this mistake of mine. Please let me know if you have any (other) issues.Anonymous
April 10, 2008
With LINQ to SQL, a couple of questions quickly arise: Since you can create a data model directly inAnonymous
July 03, 2008
Very interesting article and something I have been looking for, for a while. Would something like this work with Oracle?Anonymous
July 03, 2008
Hi Joe Thank you for your question. I've never worked with Oracle, so I can't answer your question with absolute certainty, but as far as I've understood, Oracle supports the full range of the SQL language, including DDL. My example relies mostly on DDL, so you should be able to apply the same principles to Oracle as well, although you will need to modify the code to use Oracle ADO.NET classes instead of SQL Server ADO.NET classes. In my sample code for SqlScriptInstaller, I also rely on SQL Server Management Objects (SMO) to parse T-SQL scripts, so obviously, you will have to do something else for Oracle, although what that would be, I can't tell you. Basically, my approach just wraps SQL scripts, so if you can script a database on Oracle, you should be able to use the same approach. HTHAnonymous
September 17, 2008
The comment has been removedAnonymous
November 12, 2008
This is exactly what I've been looking for. Very nice approach! Thanks a ton.Anonymous
November 12, 2008
Hi Matt Thanks - I'm glad you found it useful :)