Transaction control for LINQ to SQL repositories

Covering a legacy system with tests is always an interesting task:

10 You want as much of your codebase covered before you are confident enough to start changing the system itself,
20 but you can not mock out external dependencies such as databases without touching too much code…
30 GOTO 10

Okay, how do we break out of this loop?

One way to do so would be to not mock the database out: just have your tests clean up after themselves. Easy, right?

[TestClass]
public class Testing
{
[TestInitialise]
public void Init()
{
var tran = new ScopedTransaction();
}
[TestMethod]
public void Test()
{
using (var db = new DbContext())
{
db.ExecuteCommand("DELETE FROM tblEmployee;");
}
}
}

Kind of. What if at some point we’d like to have one test in a class commit something?
I guess we could save transaction handle as a field and refer back to it. But I still feel transaction control should not be part of TestInitialise routine – we’re not exactly setting up tests here, we’re setting up the environment itself. And having to define a decorated method everywhere we need this trick seems repetitive. So is there a cleaner way of doing it that does not clutter our Init() method and still does the job?

A quick peek into TestMethodAttribute reveals – there actually is

using System;

namespace Microsoft.VisualStudio.TestTools.UnitTesting
{
///

The test method attribute.

[AttributeUsage(AttributeTargets.Method, AllowMultiple = false)]
public class TestMethodAttribute : Attribute
{
///

Executes a test method.

///The test method to execute. /// An array of TestResult objects that represent the outcome(s) of the test.
/// Extensions can override this method to customize running a TestMethod.
public virtual TestResult[] Execute(ITestMethod testMethod)
{
return new TestResult[1]
{
testMethod.Invoke((object[]) null)
};
}
}
}

Turns out Microsoft have thoughtfully made TestMethodAttribute.Execute virtual meaning we can override it should we be inclined to do so:

[AttributeUsage(AttributeTargets.Method)]
public class TransactionTestMethodAttribute : TestMethodAttribute
{
///

Executes a test method. Wrapped in Transaction Scope. In the end the thansaction gets discarded effectively rolling eveything back

///The test method to execute. /// An array of TestResult objects that represent the outcome(s) of the test.
public override TestResult[] Execute(ITestMethod testMethod)
{
using (new TransactionScope())
return base.Execute(testMethod);
}
}

With above test attribute our tests look a bit better now:

[TestClass]
public class Testing
{
[TransactionTestMethodAttribute]
public void Test()
{
using (var db = new DbContext())
{
db.ExecuteCommand("DELETE FROM tblEmployee;");//that's it folks, everyone is fired
}
}
}

We can take it a step further

and have other actions wrap around our tests. For example we opted to temporarily disable all triggers in the database, well, because we could:

[AttributeUsage(AttributeTargets.Method)]
public class NoTriggersTransactionTestMethodAttribute : TransactionTestMethodAttribute
{
///
///

Executes a test method. Against a database with disabled triggers

///The test method to execute. /// An array of TestResult objects that represent the outcome(s) of the test.
public override TestResult[] Execute(ITestMethod testMethod)
{
var db = new DbContext();//no using(dbcontext) here in favor of try..finally
try
{
db.ExecuteCommand("sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'");
return base.Execute(testMethod);
}
finally
{
db.ExecuteCommand("sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'");
db.Dispose();//note we need to call this, as we opted to not go for c# using(dbcontext) syntax
}
}