Partager via

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

Comments

  • Anonymous
    November 18, 2006
    Another trick for doing this efficiently is something my colleague Martin Gildenpfennig and I used on a recent project: The key is to use System.EnterpriseServices. In the test setup method, create a distributed COM+ transaction against the database. Then, in the teardown method, roll back this transaction. This way, the individual tests run quickly inside the COM+ transaction and the rolling back ensures that the database remains in unchanged even if the test itself has commited changes to the DB. The beauty of this approach is that it requires only a few lines of code.

  • Anonymous
    November 18, 2006
    Hi Martin Thank you for your comment. This approach was descriped by Roy Osherove in the MSDN Magazine article Know Thy Code: Simplify Data Layer Unit Testing using Enterprise Services. Personally, I've never liked this approach, since it reserves transactions as a unit testing feature, so it wouldn't be possible to test real transactional logic, should you so wish. In any case, even with this approach, you still need to have access to a test database against which you can perform your tests, and I would still maintain that you should automate creation and deletion of this test database. In the beginning of a development project, the database schema will probably be in flux anyway, so automatically creating and deleting the database all the time is preferable to having the same database sit around for the whole time, causing all sorts of bugs to appear. Another reason why I like my approach better has to do with side-benefits: It forces the development team to define their database using T-SQL scripts checked into their source control system. Although this may seem unrelated, I find that often, half the benefit derived from test-driven development is that it forces you to do a lot of other things the right way. In any case, since I've already done all of the hard work, my approach requires you to write less that 20 lines of code (not counting the T-SQL code, but you would need to write that in any case) per test suite.

  • Anonymous
    August 26, 2007
    As an enterprise developer, I've always had SQL Server on my laptop (which is my main development machine),

  • Anonymous
    August 26, 2007
    Very helpful article, thanks for this. I particularly enjoyed the word 'thusly'. Very uncommon.

  • Anonymous
    January 31, 2008
    The comment has been removed

  • Anonymous
    August 14, 2008
    I think we should leave connection pooling to its default setting at the connection string level as this provides more accurate behavior in integration testing with the database. Since SqlServerIntegrationTester is targeting SQL Server in specific, we can use SqlConnection.ClearAllPools() static method instead. Do you think this is a more viable option?

  • Anonymous
    August 25, 2008
    Hi William Thank you for your suggestion. The reason I haven't been using SqlConnection.ClearAllPools so far was due to sheer ignorance on my part. It's definitely a preferrable option, which I've now adopted so I can run my tests with more realistic connection settings. Thanks for the valuable tip :)

  • Anonymous
    September 17, 2008
    The comment has been removed