Data Access Component Testing

  • Article

In a typical n-layer enterprise application, there's almost always a data access layer with one or more data access components (DACs). In some cases, a DAC has complex interactions with its relational data store, and it will be a good idea to subject the DAC to a suite of integration tests. This may not always be the case (e.g. if your data access layer is mostly auto-generated by a tool you trust; or in the future, if you use LINQ), but in some cases, you may have complex logic happening in stored procedures etc. (not that I recommend this as a general approach, but sometimes, this is the case for legacy or similar reasons).

Integration testing of DACs should follow the general principles I've outlined previously, which in this case means that when running the test suite, it should first create the relevant database before executing the tests and drop it again after use. Mapping the principles to this scenario results in the following goals:

  • Configuration should be minimal means that the only requirement to the execution environment is that it has SQL Server installed, and that the test process has SA privileges. This means that the process can create and configure the database automatically as part of the initialization logic.
  • Test cases should be independent means that before each test case executes, the database should be reset to a known state. This means that all tables should be truncated, since this is the only way to ensure that identity columns will have their seed values reset. As it turns out, you can't truncate a table if it is involved in a foreign key relationship, so it's necassary to drop the foreign keys before truncating the tables, and then recreating them afterwards.
  • Tests should be efficient means that we should use the minimum steps required to meet the former requirement. Another way to ensure test case independence would be to drop and recreate the database before each test case, but that would take a lot longer time than just truncating the tables.
  • The test suite should clean up after itself means that when the test suite has executed, the test database should be dropped from the local SQL Server instance.

An example will help to illustrate the point. Consider a database which track bees, flowers, and bees visiting flowers (you know, in the end, it's all about sex). A DAC called HoneySqlAccess provides access to the database. The main class is HoneyAccessor, which has the following interface:

 public class HoneyAccessor

 {

     public HoneyAccessor(string connectionString);

     public Bee CreateBee(string name, string type);

     public Bee ReadBee(int id);

     public void UpdateBee(Bee bee);

     public void DeleteBee(Bee bee);

 }

This class uses standard ADO.NET functionality to access corresponding stored procedures in the database. In this example, this is the class that should be tested (in essence, together with the corresponding stored procedures). Here's a simple test that tests the functionality to create a new Bee in the database:

 [TestMethod]

 public void ValidateCreatedBee()

 {

     HoneyAccessor ha = new HoneyAccessor(myConnectionString);

     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...

 }

Obviously, if this is the only code in the test class, it will not work, since the database specified in the connection string doesn't exist yet. As such, it's necessaray to create the database before the first test case executes.

 [AssemblyInitialize]

 public static void InitializeAssembly(TestContext context)

 {

     HoneyAccessorTest.sqlTester_.CreateTestDatabase();

 }

In this assembly initializer code, I use a static member variable called sqlTester_ to create the database. As it turns out, creating and deleting databases, executing T-SQL scripts etc. lends itselft very well to a reusable library, so I've created a reusable class (available as an attachment to this post) which does just that. Since methods decorated with the AssemblyInitialize and AssemblyCleanup attributes must be static, the sqlTester_ variable must be static as well. It's initialized thusly:

 private readonly static SqlServerIntegrationTester sqlTester_;

 

static HoneyAccessorTest()

{

    HoneyAccessorTest.sqlTester_ = new SqlServerIntegrationTester();

    HoneyAccessorTest.sqlTester_.Server = "localhost";

    HoneyAccessorTest.sqlTester_.TestDatabaseName = "HoneyTest";

 

    // Add T-SQL scripts...

}

This creates a helper object which can create, modify and delete a HoneyTest database on localhost.

Calling CreateTestDatabase creates the database (by issuing a CREATE DATABASE T-SQL statement), but it's also necessary to execute a T-SQL script which creates the tables, relations, stored procedures, etc. This can be defined by adding the name of a script file to sqlTester_'s list of initialization scripts:

 HoneyAccessorTest.sqlTester_.InitializationScripts.Add("HoneySchema.sql");

The InitializationScripts property contains an list of script file names which are executed in order by the CreateTestDatabase method. To make the HoneySchema.sql file available, it is important to add it as a deployment item in the test run configuration.

This will basically ensure that the database is created and the schema populated before the first test case runs, but if you need to populate a few reference tables with data before, you can add more script files to the InitializationScripts list.

According to the general principles outlined above, the test database should be deleted after the test run, which can be done like this:

 [AssemblyCleanup]

 public static void CleanupAssembly()

 {

     HoneyAccessorTest.sqlTester_.DeleteTestDatabase();

 }

The DeleteTestDatabase method deletes the database by issuing a DROP DATABASE T-SQL statement, but it's interesting to note that if the previous connections to the database used connection pooling (which is the default), the database is still in use and can't be deleted. For this reason, connection strings created by SqlServerIntegrationTester have connection pooling disabled. While this is not the recommended practice in a production environment, it doesn't cause noticable performance degredation in this case, since there's only one client (the test suite) connecting to the database. If connecction pooling is not disabled, the database can't be deleted, so if you define you own connection strings in your DAC, remember to disable it in this scenario.

So far, we're meeting the requirements about configuration and clean-up, but there's still no test case independence, since data is persisted in the database; e.g. the ValidateCreatedBee test case outlined above leaves the Bee table containing a newly created bee. When subsequent test cases are executed, this bee is still in the table, so different test cases start with the database in different states, which violates the principle of test case independence. To alleviate that, we can reset the tables before each test case:

 [TestInitialize]

 public void InitializeTest()

 {

     HoneyAccessorTest.sqlTester_.ResetTables();

 }

The ResetTables method executes all scripts in a list of clean-up scripts, similar to the CreateTestDatabase method. By default, there are no clean-up scripts defined in the list, so you must create a script which truncates all the tables, and add it to the list of clean-up scripts:

 HoneyAccessorTest.sqlTester_.CleanUpScripts.Add("TruncateTables.sql");

One gotcha you have to be aware of, however, is that if a table is involved in a foreign key relation, it can't be truncated. However, it's obviously very important to have referential integrity in the database, while, on the other hand, it's also very important to be able to truncate the table so that each test case starts in a known state. To resolve this issue, you should move the DDL statements which define foreign key relations into a separate file, and create a corresponding script file which drops the constraints. As such, both initialization and clean-up lists can then be defined like this:

 // Add T-SQL scripts...

 HoneyAccessorTest.sqlTester_.InitializationScripts.Add("HoneySchema.sql");

 HoneyAccessorTest.sqlTester_.InitializationScripts.Add("CreateForeignKeys.sql");

  

 HoneyAccessorTest.sqlTester_.CleanUpScripts.Add("RemoveForeignKeys.sql");

 HoneyAccessorTest.sqlTester_.CleanUpScripts.Add("TruncateTables.sql");

 HoneyAccessorTest.sqlTester_.CleanUpScripts.Add("CreateForeignKeys.sql");

With these T-SQL scripts in place, the InitializeTest method ensures that each test case starts in the same, known state.

If you are curious about how SqlServerIntegrationTester implements this functionality, you can download the code file from the attachement to this post.

The key points about automating integration tests against SQL Server are these:

  • Modularize your T-SQL scripts so that definition of foreign key constraints are separate from the rest of the database schema.
  • Drop foreign key constraints before truncating tables, then recreate them again after truncation.
  • Truncate tables before each test case to get into a known state.
  • Disable connection pooling to make it possible for the test code to drop the database after the test run.

I've used this approach successfully for years in several projects.

SqlServerIntegrationTester.cs