Category Archives: Unit Testing

Short Walks – XUnit Warning

As with many of these posts – this is more of a “note to self”.

Say you have an assertion that looks something like this in your Xunit test:

Assert.True(myEnumerable.Any(a => a.MyValue == "1234"));

In later versions (not sure exactly which one this was introduced it), you’ll get the following warning:

warning xUnit2012: Do not use Enumerable.Any() to check if a value exists in a collection.

So, Xunit has a nice little feature where you can use the following syntax instead:

Assert.Contains(myEnumerable, a => a.MyValue == "1234");

Using NSubstitute for partial mocks

I have previously written about how to, effectively, subclass using Nsubstitute; in this post, I’ll cover how to partially mock out that class.

Before I get into the solution; what follows is a workaround to allow badly written, or legacy code to be tested without refactoring. If you’re reading this and thinking you need this solution then my suggestion would be to refactor and use some form of dependency injection. However, for various reasons, that’s not always possible (hence this post).

Here’s our class to test:

public class MyFunkyClass
{
    public virtual void MethodOne()
    {        
        throw new Exception("I do some direct DB access");
    }
 
    public virtual int MethodTwo()
    {
        throw new Exception("I do some direct DB access and return a number");

        return new Random().Next(5);
    }
 
    public virtual int MethodThree()
    {
        MethodOne();
        if (MethodTwo() <= 3)
        {
            return 1;
        }
 
        return 2;
    }
}

The problem

Okay, so let’s write our first test:

[Fact]
public void Test1()
{
    // Arrange
    MyFunkyClass myFunkyClass = new MyFunkyClass();
 
    // Act
    int result = myFunkyClass.MethodThree();
 
    // Assert
    Assert.Equal(2, result);
}

So, what’s wrong with that?

Well, we have some (simulated) DB access, so the code will error.

Not the but a solution

The first thing to do here is to mock out MethodOne(), as it has (pseudo) DB access:

[Fact]
public void Test1()
{
    // Arrange
    MyFunkyClass myFunkyClass = Substitute.ForPartsOf<MyFunkyClass>();
    myFunkyClass.When(a => a.MethodOne()).DoNotCallBase();
 
    // Act
    int result = myFunkyClass.MethodThree();
 
    // Assert
    Assert.Equal(2, result);
}

Running this test now will fail with:

Message: System.Exception : I do some direct DB access and return a number

We’re past the first hurdle. We can presumably do the same thing for MethodTwo:

[Fact]
public void Test1()
{
    // Arrange
    MyFunkyClass myFunkyClass = Substitute.ForPartsOf<MyFunkyClass>();
    myFunkyClass.When(a => a.MethodOne()).DoNotCallBase();
    myFunkyClass.When(a => a.MethodTwo()).DoNotCallBase();
 
    // Act
    int result = myFunkyClass.MethodThree();
 
    // Assert
    Assert.Equal(2, result);
}

Now when we run the code, the test still fails, but it no longer accesses the DB:

Message: Assert.Equal() Failure
Expected: 2
Actual: 1

The problem here is that, even though we don’t want MethodTwo to execute, we do want it to return a predefined result. Once we’ve told it not to call the base method, you can then tell it to return whatever we choose (there are separate events – see the bottom of this post for a more detailed explanation of why); for example:

[Fact]
public void Test1()
{
    // Arrange
    MyFunkyClass myFunkyClass = Substitute.ForPartsOf<MyFunkyClass>();
    myFunkyClass.When(a => a.MethodOne()).DoNotCallBase();
    myFunkyClass.When(a => a.MethodTwo()).DoNotCallBase();
    myFunkyClass.MethodTwo().Returns(5);
 
    // Act
    int result = myFunkyClass.MethodThree();
 
    // Assert
    Assert.Equal(2, result);
}

And now the test passes.

TLDR – What is this actually doing?

To understand this better; we could do this entire process manually. Only when you’ve felt the pain of a manual mock, can you really see what mocking frameworks such as NSubtitute are doing for us.

Let’s assume that we don’t have a mocking framework at all, but that we still want to test MethodThree() above. One approach that we could take is to subclass MyFunkyClass, and then test that subclass:

Here’s what that might look like:

class MyFunkyClassTest : MyFunkyClass
{
    public override void MethodOne()
    {
        //base.MethodOne();
    }
 
    public override int MethodTwo()
    {
        //return base.MethodTwo();
        return 5;
    }
}

As you can see, now that we’ve subclassed MyFunkyClass, we can override the behaviour of the relevant virtual methods.

In the case of MethodOne, we’ve effectively issued a DoNotCallBase(), (by not calling base!).

For MethodTwo, we’ve issued a DoNotCallBase, and then a Returns statement.

Let’s add a new test to use this new, manual method:

[Fact]
public void Test2()
{
    // Arrange 
    MyFunkyClassTest myFunkyClassTest = new MyFunkyClassTest();
 
    // Act
    int result = myFunkyClassTest.MethodThree();
 
    // Assert
    Assert.Equal(2, result);
}

That’s much cleaner – why not always use manual mocks?

It is much cleaner if you always want MethodThree to return 5. Once you need it to return 2 then you have two choices, either you create a new mock class, or you start putting logic into your mock. The latter, if done wrongly can end up with code that is unreadable and difficult to maintain; and if done correctly will end up in a mini version of NSubstitute.

Finally, however well you write the mocks, as soon as you have more than one for a single class then every change to the class (for example, changing a method’s parameters or return type) results in a change to more than one test class.

It’s also worth mentioning again that this problem is one that has already been solved, cleanly, by dependency injection.

Using Entity Framework with IoC

One thing to bear in mind about using entity framework is that the DbContext object is not thread safe. This threw me when I first discovered it; it also confused why I was getting this error, as I was running in an environment that I thought was pretty much single threaded (in fact, it was an Azure function). I was using IoC and so the DbContext is shared across instances.

Because the error is based on, effectively, a race condition, your mileage may vary, but you’ll typically get one of the following errors:

System.InvalidOperationException: ‘The context cannot be used while the model is being created. This exception may be thrown if the context is used inside the OnModelCreating method or if the same context instance is accessed by multiple threads concurrently. Note that instance members of DbContext and related classes are not guaranteed to be thread safe.’

System.InvalidOperationException: ‘A second operation started on this context before a previous operation completed. Any instance members are not guaranteed to be thread safe.’

I had a good idea what the cause of this might be, but in order to investigate, I set-up a console app accessing Entity Framework; in this case, EF Core.

If you’re here to find a solution, then you can probably scroll down to the section labelled To Fix.

Reproducing the error

To reproduce the error, we need to introduce Unity (I imagine the same would be true of any IoC provider, as the problem is more with the concept than the implementation):

Install-Package Unity

The next step is to abstract away the data access layer, in order to provide a base for our dependency injection:

As you can see, we’re introducing a data access layer – and we’re creating an interface for our DbContext. The idea being that this can subsequently be resolved by Unity. Here are our interfaces:

public interface IDataAccess
{
    List<string> GetData();
 
    void AddData(List<string> newData);
}

public interface IMyDbContext
{
    DbSet<MyData> MyData { get; set; }
 
    void SaveChanges();
}

public interface IDoStuff
{
    void DoStuffQuickly();
}

Finally, we can implement the interfaces:

public class DataAccess : IDataAccess
{
    private readonly IMyDbContext _myDbContext;
 
    public DataAccess(IMyDbContext myDbContext)
    {
        _myDbContext = myDbContext;
    }
    public void AddData(List<string> newData)
    {
        foreach (var data in newData)
        {
            MyData myData = new MyData()
            {
                FieldOne = data
            };
            _myDbContext.MyData.Add(myData);
        }
        _myDbContext.SaveChanges();
    }
 
    public List<string> GetData()
    {
        List<string> data = 
            _myDbContext.MyData.Select(a => a.FieldOne).ToList();
 
        return data;
    }
}

The DbContext:

public class MyDbContext : DbContext, IMyDbContext
{
    public DbSet<MyData> MyData { get; set; }
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string cn = @"Server=.\SQLEXPRESS;Database=test-db . . .";
        optionsBuilder.UseSqlServer(cn);
 
        base.OnConfiguring(optionsBuilder);
    }
 
    void IMyDbContext.SaveChanges()
    {
        base.SaveChanges();
    }
}

Let’s register a class to actually start the process:

public class DoStuff : IDoStuff
{
    private readonly IDataAccess _dataAccess;
    private readonly ILogger _logger;
 
    public DoStuff(IDataAccess dataAccess, ILogger logger)
    {
        _dataAccess = dataAccess;
        _logger = logger;
    }
 
    public void DoStuffQuickly()
    {
        _dataAccess.AddData(new List<string>()
        {
            "testing new data",
            "second test",
            "last test"
        });
 
        Parallel.For(1, 100, (a) =>
        {
            List<string> data = _dataAccess.GetData();
            foreach(string text in data)
            {
                _logger.Log(text);
            }
        });
    }
}

Now let’s register the types in out IoC container:

static void Main(string[] args)
{
    Console.WriteLine("Hello World!");
 
    UnityContainer container = new UnityContainer();
    container.RegisterType<IDataAccess, DataAccess.DataAccess>();
    container.RegisterType<IMyDbContext, MyDbContext>();
    container.RegisterType<IDoStuff, DoStuff>();
    container.RegisterType<ILogger, ConsoleLogger>();
 
    container.Resolve<IDoStuff>().DoStuffQuickly();
}

(Console logger is just a class that calls Console.WriteLine()).

Now, when you run it, you’ll get the same error as above (or something to the same effect).

To fix

One possible fix is to simply instantiate a DbContext as you need it; for example:

public List<string> GetData()
{
    using (var myDbContext = new MyDbContext())
    {
        List<string> data =
            myDbContext.MyData.Select(a => a.FieldOne).ToList();
        return data;
    }
    
}

However, the glaring problem that this creates is unit testing. The solution is a simple factory class:

public interface IGenerateDbContext
{
    IMyDbContext GenerateNewContext();
}

public class GenerateDbContext : IGenerateDbContext
{
    public IMyDbContext GenerateNewContext()
    {
        IMyDbContext myDbContext = new MyDbContext();
 
        return myDbContext;
    }
}

We’ll need to make the DbContext implementation disposable:

public interface IMyDbContext : IDisposable
{
    DbSet<MyData> MyData { get; set; }
 
    void SaveChanges();
}

And, finally, we can change the data access code:

public void AddData(List<string> newData)
{
    using (IMyDbContext myDbContext = _generateDbContext.GenerateNewContext())
    {
        foreach (string data in newData)
        {
            MyData myData = new MyData()
            {
                FieldOne = data
            };
            myDbContext.MyData.Add(myData);
        }
        myDbContext.SaveChanges();
    }
}
 
public List<string> GetData()
{
    using (IMyDbContext myDbContext = _generateDbContext.GenerateNewContext())
    {
        List<string> data =
            myDbContext.MyData.Select(a => a.FieldOne).ToList();
        return data;
    }
    
}

Now we can use IoC to generate the DBContext, and therefore it is testable, but we don’t pass the DbContext itself around, and therefore there are no threading issues.

Short Walks – XUnit Tests Not Appearing in Test Explorer

On occasion, there may be a case where you go into Test Explorer, knowing that you have XUnit tests within the solution; the Xunit tests are in a public class, they are public, and they are decorated correctly (for example, [Fact]). However, they do not appear in the Text Explorer.

If you have MS Test tests, you may find that they do appear in the Test Explorer – only the XUnit tests do not.

Why?

To run Xunit tests from the command line, you’ll need this package.

To run Xunit tests from Visual Studio, you’ll need this package.

References

https://xunit.github.io/docs/nuget-packages.html

Short Walks – NSubstitute – Subclassing and Partial Substitutions

I’ve had this issue a few times recently. Each time I have it, after I’ve worked out what it was, it makes sense, but I keep running into it. The resulting frustration is this post – that way, it’ll come up the next time I search for it on t’internet.

The Error

The error is as follows:

“NSubstitute.Exceptions.CouldNotSetReturnDueToNoLastCallException: ‘Could not find a call to return from.”

Typically, it seems to occur in one of two circumstances: substituting a concrete class and partially substituting a concrete class; that is:

var myMock = Substitute.ForPartsOf<MyClass>();

Or:

var myMock = Substitute.For<MyClass>();

Why?

If you were to manually mock out an interface, how would you do that? Well, say you had IMyClass, you’d just do something like this:

public class MyClassMock : IMyClass 
{
	// New and imaginative behaviour goes here
}

All’s good – you get a brand new implementation of the interface, and you can do anything with it. But what would you do if you were trying to unit test a method inside MyClass that called another method inside MyClass; for example:

public class MyClass : IMyClass
{
	public bool Method1()
{
		int rowCount = ReadNumberOfRowsInFileOnDisk();
		Return rowCount > 10;
	}
	
	public int ReadNumberOfRowsInFileOnDisk()
	{
		// Opens a file, reads it, and returns the number of rows
	}
}

(Let’s not get bogged down in how realistic this scenario is, or whether or not this is good practice – it illustrates a point)

If you want to unit test Method1(), but don’t want to actually read a file from the disk, you’ll want to replace ReadNumberOfRowsInFileOnDisk(). The only real way that you can do this is to subclass the class; for example:

public class MyClassMock : MyClass

You can now test the behaviour on MyClass, via MyClassMock; however, you still can’t* override the method ReadNumberOfRowsInFileOnDisk() because it isn’t virtual. If you make the method virtual, you can override it in the subclass.

The same is true with NSubstitute – if you want to partially mock a class in this way, it follows the same rules as you must if you would to roll your own.

Footnotes

* There may, or may not be one or two ways to get around this restriction, but let’s at least agree that they are, at best, unpleasant.

Live Testing in Visual Studio 2017

While working on this project, I decided that I would see how useful the new feature of “Live Testing” is for TDD.

Once you’ve created your suite of tests, turning live testing on gives you some interesting information:

As you can see, there are some slight contradictions here in what it’s telling me. The first test is failing, the test for Count has a line (which indicates not covered). However, code lens is telling me there are two covering tests.

My suspicion here is that live testing is being too clever for its own good; for example, looking at the tests themselves, shows that only the first Enqueue statement is covered:

My thoughts are that the engine works out where the test fails, and so, in the case above, it’s telling me that the tests are not running the line of code that tests the Count functionality. What I’m guessing is that this is because the tests are failing before that.

I can prove this by creating a further unit test:

And, indeed, this now shows coverage:

As I start to add the functionality, I can quickly see that the tests are now passing:

Changing the way code is written

The interesting thing here is that is promotes two things:
1. Defined tests – if a test covers more than one thing, it will show as uncovered initially
2. Tests first – obviously, there need to be tests to show test coverage

Caveats

1. Annoyingly, by default, you continually have to opt in for live unit testing. It only starts when you select to “Start” live unit testing, and this must be done each time you launch VS. This is configurable, and can be switched on by default in options:

2. If the build fails, it shows nothing.

3. Live testing is only available in Visual Studio Enterprise.

NUnit

One thing that only occurred to me as notable while I was writing this is that the tests that I’ve written are in NUnit. So the Live Testing clearly works with none MSTests, and probably works with anything that’ll show up in Test Explorer / Code Lens.

References

https://blogs.msdn.microsoft.com/visualstudio/2017/03/09/live-unit-testing-in-visual-studio-2017-enterprise/

NUnit TestCaseSource

While working on this project, I found a need to abstract away a base type that the unit tests use (in this instance, it was a queue type). I was only testing a single type (PriorityQueue); however, I wanted to create a new type, but all the basic tests for the new type are the same as the existing ones. This led me to investigate the TestCaseSource attribute in NUnit.

As a result, I needed a way to re-use the tests. There are definitely multiple ways to do this; the simplest one is probably to create a factory class, and pass in a string parameter. The only thing that put me off this is that you end up with the following test case:

        [TestCase("test", "test9", "test", "test2", "test3", "test4", "test5", "test6", "test7", "test8", "test9"]
        [TestCase("a1", "a", "a1", "b", "c", "d", "a"]
        public void Queue_Dequeue_CheckResultOrdering(
            string first, string last, params string[] queueItems)
        {

Becoming:

        [TestCase("PriorityQueue", "test", "test9", "test", "test2", "test3", "test4", "test5", "test6", "test7", "test8", "test9"]
        [TestCase("PriorityQueue2", "test", "test9", "test", "test2", "test3", "test4", "test5", "test6", "test7", "test8", "test9"]
        [TestCase("PriorityQueue", "a1", "a", "a1", "b", "c", "d", "a"]
        [TestCase("PriorityQueue2", "a1", "a", "a1", "b", "c", "d", "a"]
        public void Queue_Dequeue_CheckResultOrdering(
            string queueType, string first, string last, params string[] queueItems)
        {

This isn’t very scaleable when adding a third or fourth type.

TestCaseSource

It turns out that the (or an least an) answer to this is to use NUnit’s TestCaseSource attribute. The NUnit code base dog foods quite extensively, so that is not a bad place to look for examples of how this works; however, what I couldn’t find was a way to mix and match. To better illustrate the point; here’s the first test that I changed to use TestCaseSource:

        [Test]
        public void Queue_NoEntries_CheckCount()
        {
            // Arrange
            PQueue.PriorityQueue<string> queue = new PQueue.PriorityQueue<string>();

            // Act
            int count = queue.Count();

            // Assert
            Assert.AreEqual(0, count);
        }

Which became:

        [Test, TestCaseSource(typeof(TestableQueueItemFactory), "ReturnQueueTypes")]
        public void Queue_NoEntries_CheckCount(IQueue<string> queue)
        {
            // Arrange


            // Act
            int count = queue.Count();

            // Assert
            Assert.AreEqual(0, count);
        }

(For completeness, the TestableQueueItemFactory is here):

    public static class TestableQueueItemFactory
    {
        public static IEnumerable<IQueue<string>> ReturnQueueTypes()
        {
            yield return new PQueue.PriorityQueue<string>();
        }
    }

However, when you have a TestCase like the one above, there’s a need for the equivalent of this (which doesn’t work):

        [Test, TestCaseSource(typeof(TestableQueueItemFactory), "ReturnQueueTypes")]
        [TestCase("test", "test9", "test", "test2", "test3", "test4", "test5", "test6", "test7", "test8", "test9")]
        [TestCase("a1", "a", "a1", "b", "c", "d", "a")]
        public void Queue_Dequeue_CheckResultOrdering(string first, string last, params string[] queueItems)
        {

A quick look at the NUnit code base reveals these attributes to be mutually exclusive.

Compromise

By no means is this a perfect solution, but the one that I settled on was to create a second TestCaseSource helper method, which looks like this (along with the test):

        private static IEnumerable Queue_Dequeue_CheckResultOrdering_TestCase()
        {
            foreach(var queueType in TestableQueueItemFactory.ReturnQueueTypes())
            {
                yield return new object[] { queueType, "test", "test9", new string[] { "test", "test2", "test3", "test4", "test5", "test6", "test7", "test8", "test9" } };
                yield return new object[] { queueType, "a1", "a", new string[] { "a1", "b", "c", "d", "a" } };
            }
        }

        [Test, TestCaseSource("Queue_Dequeue_CheckResultOrdering_TestCase")]
        public void Queue_Dequeue_CheckResultOrdering(
            IQueue <string> queue, string first, string last, params string[] queueItems)
        {

As you can see, the second helper method doesn’t really help readability, so it’s certainly not a perfect solution; in fact, with a single queue type, this makes the code more complex and less readable. However, When a second and third queue type are introduced, the test suddenly becomes resilient.

YAGNI

At first glance, this may appear to be an example of YAGNI. However, in this article, Martin Fowler does state:

Yagni only applies to capabilities built into the software to support a presumptive feature, it does not apply to effort to make the software easier to modify.

Which, I believe, is what we are doing here.

References

http://www.smaclellan.com/posts/parameterized-tests-made-simple/

http://stackoverflow.com/questions/16346903/how-to-use-multiple-testcasesource-attributes-for-an-n-unit-test

https://github.com/nunit/docs/wiki/TestCaseSource-Attribute

http://dotnetgeek.tumblr.com/post/2851360238/exploiting-nunit-attributes-valuesourceattribute

https://github.com/nunit/docs/wiki/TestCaseSource-Attribute

Testing for Exceptions using the Arrange Act Assert Pattern in C# 7

Unit testing massively benefits from following the Arrange / Act / Assert pattern. I’ve seen tests that are not written in this way, and they can be sprawling and indecipherable, either testing many different things in series, or testing nothing at all except the .Net Framework.

I recently found an issue while trying to test for an exception being thrown, which is that Nunit (and probably other frameworks) test for an exception by accepting a delegate to test. Here’s an example:

        [Test]
        public void Test_ThrowException_ExceptionThrown()
        {
            // Arrange
            TestClass tc = new TestClass();

            // Act / Assert
            Assert.Throws(typeof(Exception), tc.ThrowException);
        }

We’re just testing a dummy class:

    public class TestClass
    {
        public void ThrowException()
        {
            throw new Exception("MyException");
        }
    }

C# 7 – Inline functions

If you look in the references at the bottom, you’ll see something more akin to this approach:

        public void Test_ThrowException_ExceptionThrown2()
        {
            // Arrange
            TestClass tc = new TestClass();

            // Act
            TestDelegate throwException = () => tc.ThrowException();            

            // Assert
            Assert.Throws(typeof(Exception), throwException);
        }

However, since C# 7, the option on a local function has arrived. The following has the same effect:

        [Test]
        public void Test_ThrowException_ExceptionThrown3()
        {
            // Arrange
            TestClass tc = new TestClass();

            // Act
            void CallThrowException()
            {
                tc.ThrowException();
            }

            // Assert
            Assert.Throws(typeof(Exception), CallThrowException);
        }

I think that I, personally, still prefer the anonymous function for this; however, the local function does present some options; for example:

        [Test]
        public void Test_ThrowException_ExceptionThrown4()
        {
            void CallThrowException()
            {
                // Arrange
                TestClass tc = new TestClass();

                // Act
                tc.ThrowException();
            }

            // Assert
            Assert.Throws(typeof(Exception), CallThrowException);
        }

Now I’m not so sure that I still prefer the anonymous function.

References

http://stackoverflow.com/questions/33897323/nunit-3-0-and-assert-throws

https://pmbanugo.wordpress.com/2014/06/16/exception-testing-pattern/

http://stackoverflow.com/questions/24070115/best-approach-towards-applying-the-arrange-act-assert-pattern-when-expecting-exc

Designing and Debugging Database Unit Tests

There are many systems out there in the wild, and some new ones being written, that use database logic extensively. This article discusses how and why these pieces of logic should be tested, along with whether they should exist at all.

In general, for unit tests, it’s worth asking the question of what, exactly, is being tested, before starting. This is especially true in database tests; for example, consider a test where we update a field in a database, and then assert that the field is what it has been set to. Are you testing your trigger logic, or are you simply testing Microsoft SQL Server works?

The second thing to consider is whether or not it makes any sense to use testable database logic in new code. That is, say we have a stored procedure that:
– Takes a product code
– Looks up what the VAT is for that product
– Calculates the total price
– Writes the result, along with the parameter and the price to a new table

Does it make sense for all that logic to be in the stored procedure, or would it make more sense to retrieve the values needed via one stored procedure, do the calculation in a testable server-side function, and call a second procedure to write the data?

FIRST

Unit testing a database is a tricky business. First of all, if you have business logic in the database then it, almost by definition, depends on the state of the data. You obviously can simply run unit tests against the database and change the data, but let’s have a look at the FIRST principles, and see where database tests are inherently difficult.

Fast

It depends exactly what is meant by fast, but in comparison to a unit test that asserts some logic in C# code, database tests are slow (obviously, in comparison to conducting the test manually, they are very fast). Realistically, they are probably going to be sufficiently slow to warrant taking them out of your standard unit test suite. A sensible test project (that is, one that tests some actual code) may contain a good few hundred tests, let’s assume they all take 200ms – that means that 300 tests take a total of 60 seconds!

One thing that conducting DB tests does give you is an idea as to how fast (or slow) they actually are:

Isolated

It’s incredibly difficult to produce a database unit test that is isolated because, by its nature, a database had dependencies. Certainly, if anything you’re testing is dependent on a particular data state (for example, in the case above, the product that we are looking for must exist in a table, and have a VAT rate) then, unless this state is set-up in the test itself, this rule is broken.

Repeatable

Again – this isn’t a small problem with databases. Should I change Column A to test a trigger on the table, am I then able to change it again. What if the data is in a different state when I run the unit tests from the last time – I might get rogue fails, or worse, rogue passes. What happens if the test crashes half way through, how do we revert?

Self-verifying

In my example before, I changed Column A in order to test a trigger, and I’ll maybe check something that is updated by the trigger. Providing that the assertion is inside the test, the test is self-verifying. Obviously, this is easier to do wrong in a database context, because if I do nothing, the data is left in a state that can be externally verified.

Timely

This refers to when a test is written. There’s nothing inherent about database tests that prevent them from being written before, or very shortly after the code is written. However, see the comment above as to whether new code written like this makes sense.

Problems With A Database Test Project

Given what we’ve put above, let’s look at the outstanding issues that realistically need to be solved in order to use database tests:

1. Deployment. Running a standard code test will run the code wherever you are; however, a database test, whichever way you look at it, needs a database before it runs.

2. Rollback. Each test needs to be isolated, and so there needs to be a way to revert to the database state before the tests began.

3. Set-up. Any dependencies that the tests have, must be inside the test; therefore, if a table needs to have three rows in it, we need to add those rows within the test.

4. Assertion. What are we testing, and what makes sense to test; each test needs a defined purpose.

Example Project

In order to explore the various possibilities when setting up a database project, I’m going to use an example project:

Let’s start with some functionality to test. I’m going to do it this way around for two reasons: having code to test better illustrates the problems faced by database tests, and it is my belief that much of the database logic code is legacy and, therefore, already exists.

Here’s a new table, and a trigger that acts upon it:

CREATE TABLE [dbo].[SalesOrder]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [ProductCode] NCHAR(10) NOT NULL, 
    [NetAmount] DECIMAL(18, 2) NULL, 
    [Tax] DECIMAL(18, 2) NULL, 
    [TotalAmount] DECIMAL(18, 2) NULL, 
    [Comission] DECIMAL(18, 2) NULL
)
GO
 
CREATE TRIGGER SalesOrderAfterInsert ON SalesOrder
AFTER INSERT, UPDATE
AS
BEGIN
	DECLARE @CalcTax Decimal(18,2) 
	DECLARE @CalcComission Decimal(18,2) 
     
	SELECT @CalcTax = INSERTED.NetAmount * 0.20 FROM INSERTED
	SELECT @CalcComission = INSERTED.NetAmount * 0.10 FROM INSERTED
	 
    UPDATE S
    SET S.Tax = @CalcTax,
		S.Comission = @CalcComission,
		S.TotalAmount = S.NetAmount + S.Tax
	FROM INSERTED, SalesOrder S
    WHERE S.Id = INSERTED.Id
END
GO

This is for the purpose of illustration, so obviously, there are things here that might not make sense in real life; however, the logic is very testable. Let’s deploy this to a database, and do a quick manual test:

Once the database is published, we can check and test it in SSMS:

Quick edit the rows:

And test:

At first glance, this seems to work well. Let’s create a test:

[TestMethod]
public void CheckTotalAmount()
{
    using (SqlConnection sqlConnection = new SqlConnection(
        @"Data Source=TLAPTOP\PCM2014;Initial Catalog=MySqlDatabase;Integrated Security=SSPI;"))
    {
        sqlConnection.Open();
        using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
        {
            sqlCommand.CommandText = "INSERT INTO SalesOrder (Id, ProductCode, NetAmount) " +
                "VALUES (2, 'test', 10)";
            sqlCommand.ExecuteNonQuery();
        }
 
        using (SqlCommand sqlCommandCheck = sqlConnection.CreateCommand())
        {
            sqlCommandCheck.CommandText = $"SELECT TotalAmount FROM SalesOrder WHERE Id = 1";
            decimal result = decimal.Parse(sqlCommandCheck.ExecuteScalar().ToString());
 
        }
    }
}

Okay – there are a number of problems with this test, but let’s pretend for a minute that we don’t know what they are; the test passes:

Let’s run it again, just to be sure:

Oops.

Let’s firstly check this against the test principles that we discussed before.
1. Is it fast? 337ms means that we can run 3 of these per second. So that’s a ‘no’.
2. Is it Isolated? Does is have a single reason to fail – and can it live independently? If we accept that the engine itself is a reason to fail, but ignore that, then we can look specifically at the test, which asserts nothing. What’s more, it is doing two separate things to the DB, so both can fail realistically.
3. Is it Repeatable? Clearly not.
4. Is it self-verifying? No – it isn’t, because we have no assertions in it. Although we know that on the first run, both queries worked, we don’t know why.
5. Timely – well, we did write it directly after the code, so that’s probably a tick.

So, we know that the second run didn’t work. A quick look at the DB will tell us why:

Of course, the test committed a transaction to the database, as a result, any subsequent runs will fail.

The Solution

What follows is a suggested solution for this kind of problem, along with the beginnings of a framework for database testing. The tests here are using MSTest, but the exact same concept is easily achievable in Nunit and, I imagine, every other testing framework.

Base Test Class

The first thing is to create a deployment task:

The deployment task might look a little like this:

public static bool DeployDatabase(string projectFile)
{
    ILogger logger = new BasicFileLogger();
 
    Dictionary<string, string> globalProperties = new Dictionary<string, string>()
    {
        { "Configuration", "Debug" },
        { "Platform", "x86" },
        { "SqlPublishProfilePath", @"MySqlDatabase.publish.xml" }
    };
 
    ProjectCollection pc = new ProjectCollection(
        globalProperties, new List<ILogger>() { logger }, ToolsetDefinitionLocations.Registry);
        
    BuildParameters buildParameters = new BuildParameters(pc);            
    BuildRequestData buildRequestData = new BuildRequestData(
        projectFile, globalProperties, null, new string[] { "Build", "Publish" }, null);
 
    BuildResult buildResult = BuildManager.DefaultBuildManager.Build(
        buildParameters, buildRequestData);
 
    return (buildResult.OverallResult == BuildResultCode.Success);
}

Publish Profiles

This uses a publish profile. These are basically XML files that tell the build how to publish your database; here’s an example of one:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>MySqlDatabase</TargetDatabaseName>
    <DeployScriptFileName>MySqlDatabase.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=TLAPTOP\PCM2014;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>

You can get Visual Studio to generate this for you, by selecting to “Deploy…” the database, and then selecting “Save Profile As…”:

Database Connection

Now that we’ve deployed the database, the next step is to connect. One way of doing this is to configure the connection string in the app.config of your test project:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="MySqlDatabase" 
         connectionString="Data Source=TLAPTOP\PCM2014; Initial Catalog=MySqlDatabase; Integrated Security=true" />
  </connectionStrings>
</configuration>

You can then connect using the following method:

ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["MySqlDatabase"];
 
_sqlConnection = new SqlConnection(connectionString.ConnectionString);
_sqlConnection.Open();

This sort of functionality could form the basis of a base test class; for example:

[TestClass]
public class BaseTest
{
    protected SqlConnection _sqlConnection;
 
    [TestInitialize]        
    public virtual void SetupTest()
    {
        ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["MySqlDatabase"];
        _sqlConnection = new SqlConnection(connectionString.ConnectionString);
        _sqlConnection.Open();
    }
 
    [TestCleanup]
    public virtual void TearDownTest()
    {
        _sqlConnection.Close();
    }
}

Transactions

So, we now have a deployment task, and a connection, the next step is to run the tests in a way in which they are repeatable. The key here is to use transactions. Going back to the base class, we can wrap this functionality into a method that can simply be inherited by all unit tests.

public class BaseTest
{
    protected SqlConnection _sqlConnection;
    protected SqlTransaction _sqlTransaction;
 
 
    [TestInitialize]
    public virtual void SetupTest()
    {
        ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["MySqlDatabase"];
        _sqlConnection = new SqlConnection(connectionString.ConnectionString);
        _sqlConnection.Open();
        _sqlTransaction = _sqlConnection.BeginTransaction();
    }
 
    [TestCleanup]
    public virtual void TearDownTest()
    {
        _sqlTransaction.Rollback();
        _sqlConnection.Close();
    }
}

Refactor The Base Class

Let’s put all this together, and remove some parts that can be separated into a common helper class:

public class ConnectionHelper
{
    SqlConnection _sqlConnection;
    SqlTransaction _sqlTransaction;
 
    public SqlConnection OpenTestConnection()
    {
        ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["MySqlDatabase"];
 
        _sqlConnection = new SqlConnection(connectionString.ConnectionString);
        _sqlConnection.Open();
        _sqlTransaction = _sqlConnection.BeginTransaction();
 
        return _sqlConnection;
    }
 
    public SqlCommand UseNewTestCommand()
    {
        SqlCommand sqlCommand = _sqlConnection.CreateCommand();
        sqlCommand.Transaction = _sqlTransaction;
        return sqlCommand;
    }
 
    public void CloseTestConnection()
    {
        _sqlTransaction.Rollback();
        _sqlConnection.Close();
    }
}

The base test now looks like this:

[TestClass]
public class BaseTest
{
    protected ConnectionHelper _connectionHelper;
 
    [ClassInitialize]
    public virtual void SetupTestClass()
    {
        DatabaseDeployment.DeployDatabase(@"..\MySqlDatabase\MySqlDatabase.sqlproj");
    }
 
    [TestInitialize]
    public virtual void SetupTest()
    {
        
        _connectionHelper = new ConnectionHelper();
        _connectionHelper.OpenTestConnection();
    }
 
    [TestCleanup]
    public virtual void TearDownTest()
    {
        _connectionHelper.CloseTestConnection();
    }
}

In Summary

We now have a base test class that will deploy the database, establish a new connection, and transaction; and then, on completion of the test, will roll back the transaction. Here’s what the above test now looks like:

[TestClass]
public class UnitTest2 : BaseTest
{
    [TestMethod]
    public void CheckTotalAmount3()
    {
 
        // Arrange
        using (SqlCommand sqlCommand = _connectionHelper.UseNewTestCommand())
        {
            sqlCommand.CommandText =
                "INSERT INTO SalesOrder (Id, ProductCode, NetAmount) " +
                "VALUES (2, 'test', 10)";
            sqlCommand.ExecuteNonQuery();
        }
 
        // Act
        using (SqlCommand sqlCommand = _connectionHelper.UseNewTestCommand())
        {                
            sqlCommand.CommandText = $"SELECT TotalAmount FROM SalesOrder WHERE Id = 2";
            decimal result = decimal.Parse(sqlCommand.ExecuteScalar().ToString());

            // Assert
            Assert.AreEqual(12, result);
        }
    }
}

Debugging Unit Tests

The idea behind the framework described above is that the data is never committed to the database; as a consequence of this, the tests are repeatable, because nothing ever changes. The unfortunate side-effect here is that debugging the test is made more difficult as, if it fails, it is not possible to see directly which changes have been made. There’s a couple of ways around this. One of which is to simply debug the test, and then manually fire a commit, look at the data and continue. However, a SQL expert recently introduced me to a concept of “Dirty Reads”.

Dirty Reads

Dirty reads are achieved by issuing the following command the SQL Server:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

This allows you to see changes in the database which are still pending (that is, they have yet to be committed). What this means is that you can see the state of the data as it currently is, it also doesn’t place a lock on the data. One of the big issues with using this methodology is that you can see half committed transactions; of course, in this instance, that’s exactly what you want! Let’s debug our unit test:

Now let’s have a look at the SalesOrder table:

Not only does this not return anything, it doesn’t return at all. We’ve locked the table, and held it in a transaction. Let’s apply our dirty read and see what happens:

Instantly, we get the SalesOrder. If we now complete the test and run the query again, the data is gone:

References

https://pragprog.com/magazines/2012-01/unit-tests-are-first

http://stackoverflow.com/questions/13843990/how-can-i-programatically-publish-a-sql-server-database-project

https://social.msdn.microsoft.com/Forums/vstudio/en-US/ec95c513-f972-45ad-b108-5fcfd27f39bc/how-to-build-a-solution-within-c-net-40-?forum=msbuild

http://stackoverflow.com/questions/10438258/using-microsoft-build-evaluation-to-publish-a-database-project-sqlproj

https://msdn.microsoft.com/en-us/library/microsoft.build.framework.ilogger.aspx

http://stackoverflow.com/questions/10438258/using-microsoft-build-evaluation-to-publish-a-database-project-sqlproj

https://msdn.microsoft.com/en-us/library/hh272681(v=vs.103).aspx

Using MSTest DataRow as a Substitute for NUnit TestCase

I used to believe that Nunit’s TestCase test (that is, an ability to define a test and then simply pass it alternate parameters) was denied MSTest users. It appears that this is, at least now, fallacious.

The following article implies that this is a recent change:

Taking the MSTest Framework forward with “MSTest V2”

This particular example is in a UWP application:

        [DataTestMethod]
        [DataRow(1, 2, 3, 6)]
        [DataRow(8, 2, 3, 13)]
        [DataRow(8, 5, 3, 12)]
        public void AddNumbers(int num1, int num2, int num3, int total)
        {
            Assert.AreEqual(num1 + num2 + num3, total);
        }

Will result in a failing test, and:

        [DataTestMethod]
        [DataRow(1, 2, 3, 6)]
        [DataRow(8, 2, 3, 13)]
        [DataRow(8, 5, 3, 16)]
        public void AddNumbers(int num1, int num2, int num3, int total)
        {
            Assert.AreEqual(num1 + num2 + num3, total);
        }

Results in a passing one.

If you want additional information relating to the test, you can use this syntax:

        [DataTestMethod]
        [DataRow(1, 2, 3, 6, DisplayName = "First test")]
        [DataRow(8, 2, 3, 13, DisplayName = "Second test")]
        [DataRow(8, 5, 3, 15, DisplayName = "This will fail")]
        public void AddNumbers(int num1, int num2, int num3, int total)
        {
            Assert.AreEqual(num1 + num2 + num3, total);
        }

Given the constant problems that I have with finding the correct NUnit test adaptor, and trying to work out which are the right libraries, I think, despite coming late to this party, MS might actually drag people back to MSTest with this.