Monday 13 August 2012

Reflection in unit tests

The idea of using reflection to generate unit test for factories came to me while attempting to write simple unit tests for a factory class. The aim was to ensure that any changes to the class in future did not break my code, I started with something like the code below:

[TestFixture]
public class BlogExampleTests
{
 private TransactionFactory factory;

 [SetUp]
 public void SetUp()
 {
  factory = new TransactionFactory();
 }

 [Test]
 public void ShouldCorrectlyReturnTransactionA()
 {
  ITransaction transaction = null;
  Assert.DoesNotThrow(() => transaction = factory.GetTransactionA());

  Assert.IsNotNull(transaction);
 }

 [Test]
 public void ShouldCorrectlyReturnTransactionB()
 {
  ITransaction transaction = null;
  Assert.DoesNotThrow(() => transaction = factory.GetTransactionB());

  Assert.IsNotNull(transaction);
 }

 [Test]
 public void ShouldCorrectlyReturnTransactionC()
 {
  ITransaction transaction = null;
  Assert.DoesNotThrow(() => transaction = factory.GetTransactionC());

  Assert.IsNotNull(transaction);
 }
}

Technically this works but I expect the factory to have approximately 200 methods when completed, so to get full coverage would be 200 test methods as well. The next move to improve these tests was to move all of these to one method using an array/list of delegates and pass the list into the test method like below:

public delegate ITransaction MyDelegate();

[TestFixture]
public class BlogExampleTests
{
 private static readonly TransactionFactory Factory = new TransactionFactory();
 private static object[] delegates = new[]
  {
   new MyDelegate(Factory.GetTransactionA),
   new MyDelegate(Factory.GetTransactionB),
   new MyDelegate(Factory.GetTransactionC)
  };

 [Test, TestCaseSource("delegates")]
 public void ShouldCorrectlyReturnTheTransactions(MyDelegate method)
 {
  ITransaction transaction = null;
  Assert.DoesNotThrow(() => transaction = method.Invoke());

  Assert.IsNotNull(transaction);
 }
}

This solution above is better than the first solution, although it still requires a new delegate to be created every time a method is added to the factory class. Then the idea occurred to me that not only could I save myself a lot of keystrokes but also ensure that the convention of the factory is followed in future by using reflection to look at the class and get all of the methods.

[TestFixture]
public class BlogExampleTests
{
 private TransactionFactory factory;
 private static readonly Type FactoryType = typeof(TransactionFactory);

 [SetUp]
 public void SetUp()
 {
  var parameters = new Type[0];
  var constructor = FactoryType.GetConstructor(parameters);
  factory = (TransactionFactory)constructor.Invoke(new object[0]);
 }

 [Test]
 public void ShouldBeAbleToCreateAllTypes([ValueSource("GetFactoryTransactions")] MethodInfo method)
 {
  ITransaction transaction = null;

  Assert.DoesNotThrow(() => transaction = method.Invoke(factory, null) as ITransaction);
  Assert.IsNotNull(transaction);
 }

 IEnumerable GetFactoryTransactions()
 {
  return FactoryType.GetMethods(
   BindingFlags.Instance | 
   BindingFlags.Public | 
   BindingFlags.DeclaredOnly);
 }
}

In summary when someone adds or modifies the transaction factory it will automatically test that method and in this case ensure that the method does not require any parameters. Another good usage of unit tests using reflection is ensuring that every implementation that uses a certian interface (i.e. IDomainService) can be resolved using dependency injection.

SQL Parameter sniffing

At work we were having a problem with a few stored procedures that seemed to stop working randomly for some queries using SQL Server 2005. The stored procedure work correctly with one set of parameters, while with another set would timeout and fail.

The initial way that we bypassed this problem was that when this circumstance was found, we would drop and recreate the stored procedure and this seemed to fix it. As you can imagine it wasn't an ideal situation for an enterprise application because the problem would need to be found and reported before we would even know it existed but we struggled to find any proposed solutions to the problem.

After much investigation it was suggested that this could be caused by parameter sniffing. SQL Server does this by tracking what happens to the parameters passed into the stored procedure when creating the stored procedure and working out the execution plan. So the execution plan generated by SQL server was good most of the time but the plan would contain situations where the procedure would fail.

Below is a sample stored procedure that allows parameter sniffing. This is not one of the stored procedures that did have the problem, but a stored procedure where parameter sniffing occurs:

CREATE PROCEDURE Customer_Search
 @FirstName varchar(100),
 @LastName varchar(100)
AS
BEGIN

 SELECT 
  CustomerID,
  Firstname,
  Lastname,
  Username,
  Email
 FROM
  Customer
 WHERE
  Firstname LIKE ('%' + @FirstName + '%') AND
  LastName LIKE ('%' + @LastName + '%')
END
GO

With the following addition of two variables it prevents SQL Server from having the ability to perform parameter sniffing:

CREATE PROCEDURE Customer_Search
 @FirstName varchar(100),
 @LastName varchar(100)
AS
BEGIN
 -- Variables added to prevent problems that were occuring with parameter sniffing
 DECLARE 
  @FName VARCHAR(100),
  @LName VARCHAR(100)
   
 SELECT
  @FName = @FirstName,
  @LName = @LastName

 SELECT 
  CustomerID,
  Firstname,
  Lastname,
  Username,
  Email
 FROM
  Customer
 WHERE
  Firstname LIKE ('%' + @FName + '%') AND
  LastName LIKE ('%' + @LName + '%')
END
GO

By implementing the small change demonstrated above on a couple of troublesome stored procedures, this fixed our problem with our stored procedures randomly not working with certain parameters. This proved that the problem was caused by parameter sniffing and left me wondering how many other people are out there with the same problem.