A test-driven design style for database-based applications with VSTS

If you are designing an application test-drive using Microsoft Visual Studio 2005 Team Edition for Testers whose logic includes access to a database, then consider the following mechanism for the database related assertions (also known as specifications: SpecificationByExample):

For a simple example, if you have a business object (also known as processor, unit of work, façade, etc.) —the kind of business objects suggested by Microsoft for application architecture since Windows DNA— called PatchOperation that receives patch installation status messages over the network from one or thousands of clients then an initial and possible TestMethod for it could be like this:

 string PCNetworkID="PCx";
string PatchID="Patch123";

//this data structure is what the invocation receives
//both locally or over the network, sample values follow:
InstallStatusInfo status=new InstallStatusInfo();
status.PatchID=PatchID;
status.Status="Installed";

//PatchOperation is the business object,
//the following two lines appears in 
//production code exactly like this:
PatchOperation patchoperation=new PatchOperation();
patchoperation.ReceiveInstallStatus(PCNetworkID,status);

//Asserting post-conditions:
DataTable result = Util.SqlQuery("SELECT ... WHERE pc=@PCNetworkID ...");
Assert.AreEqual(1,result.Rows.Count);
Assert.AreEqual("Installed",result.PatchStatus);
// etc.

In order to fully grasp the relative value of the code above, there are a number of assumptions about the perspective from which the observer could be seeing it; I will defer those clarifications for a later post by now check this preamble, by now let's cover the overall database test mechanism.

Chances are that the unit test assumes a particular database state before executing, like particular rows on particular tables, but the same state must be deleted before the next unit test starts. In that case, lets think in terms of a base class for all database-related unit tests:

 /// 
/// Base clase for database-based application design.
/// Intented to be derived.
/// Derived test classes must override InsertTestData method.
/// Derived test classes may override GetIsolationLevel method.
/// Derived test classes must use TransactionalContext in TestContext property.
/// TransactionalContext is under "tx" key in TestContext property.
/// 
[TestClass]
public abstract class DatabaseTestClass
{
  [TestInitialize]
  public virtual void Setup()
  {
    IsolationLevel isolation = GetIsolationLevel();
    TransactionalContext tx = TransactionalContext.Create(isolation);
    TestContext.Properties["tx"] = tx;
    InsertTestData();
  }

  protected abstract void InsertTestData();
  protected virtual IsolationLevel GetIsolationLevel() { return IsolationLevel.Serializable; }

  [TestCleanup]
  public virtual void Clean()
  {
    TransactionalContext tx = TestContext.Properties["tx"] as TransactionalContext;
    tx.Transaction.Rollback();
    tx.Connection.Close();
  }

  private TestContext testContextInstance;
  public TestContext TestContext
  {
    get { return testContextInstance; }
    set { testContextInstance = value; }
  }
}

The TransactionalContext class usually is part of a data access assembly or namespace whose types are mainly data access components:

 public enum TransactionalContextMode
{
  Read,
  Write
}

public class TransactionalContext
{
  public SqlConnection Connection;
  public SqlTransaction Transaction;

  public static TransactionalContext Create()
  {
    return Create(TransactionalContextMode.Write);
  }

  public static TransactionalContext Create(IsolationLevel isolation)
  {
    TransactionalContext result = new TransactionalContext();
    string dbconn = System.Configuration.ConfigurationManager.AppSettings["conn"];
    result.Connection = new SqlConnection(dbconn);
    result.Connection.Open();
    result.Transaction = result.Connection.BeginTransaction(isolation);
    return result;
  }

  public static TransactionalContext Create(TransactionalContextMode mode)
  {
    switch (mode)
    {
      case TransactionalContextMode.Read:
        return Create(IsolationLevel.RepeatableRead);
      case TransactionalContextMode.Write:
        return Create(IsolationLevel.Serializable);
      default:
        throw new Exception("TransactionalContextMode undefined");
    }
  }
}

So the initial unit test above could be written like this:

 [TestClass]
public class StatusReception : DatabaseTestClass
{
  protected override void InsertTestData()
  {
    Util.SqlExecute(this, "EXECUTE Computer_Insert 'PCx'");
    Util.SqlExecute(this, "EXECUTE PCStatus_SetStatus 'PCx',NULL ");
  }

  [TestMethod]
  public void StatusReception_flat()
  {
    TransactionalContext tx = TestContext.Properties["tx"] as TransactionalContext;

    string PCNetworkID="PCx";
    string PatchID="Patch123";

    InstallStatusInfo status=new InstallStatusInfo();
    status.PatchID=PatchID;
    status.Status="Installed";

    PatchOperation patchoperation=new PatchOperation();
    patchoperation.ReceiveInstallStatus(tx,PCNetworkID,status);

    DataTable result = Util.SqlQuery("SELECT ... WHERE pc=@PCNetworkID ...");
    Assert.AreEqual(1,result.Rows.Count);
    Assert.AreEqual("Installed",result.PatchStatus);
    // etc.
  }
}

At the beginning of each TestMethod, the SetUp method from the base class is invoked and the Transactional context is created and stored as part of the TestContext object, from which the TestMethod can get it in order to propagate that very same Transactional context to the business objects under test.
Later, the Clean method from base class ins invoked and the transactions is rolled back, leaving the database in the same state as of the beginning of the test case.