TDD Concepts – What are the London and Chicago Schools of TDD?

I like to start with a list of Features, and for each Feature write the user stories, then for each story write test descriptions.

Think about the design for a bit, then pick a test description and start coding: red-green-refactor.

Repeat until all tests pass.

Yes, acceptance tests should be considered as part of this, attached to the appropriate story.

This is a major downside of stored procedures in the age of TDD. They have some real upsides, even now, but by definition any test that exercises a stored proc is not a unit test; it's an integration test at best.

The usual solution, assuming the architecture cannot change to use an ORM instead, is to not put these tests in the unit test suite; instead, put the tests in an integration suite. You can still run the test whenever you like to verify it works, but because the cost inherent in setting up the test (initializing a DB with the proper test data) is high and it touches resources your build-bot's unit-test agent may not have access to, it shouldn't be in the unit test suite.

You can still unit-test code that requires the data, by abstracting anything you cannot unit-test (ADO.NET classes) into a DAO class that you can then mock. You can then verify that the expected calls are made by consuming code and reproduce real-world behavior (such as finding no results) allowing testing of various use cases. However, the actual setting up of the SqlCommand to call the stored proc is pretty much the very last thing you can unit-test, by severing command creation from command execution and mocking the command executor. If this sounds like a lot of separation of concerns, it can be; remember, "there is no problem that cannot be solved by another layer of indirection, except for having too many layers of indirection". At some point you must say "enough; I just can't unit-test this, we'll do it in integration".

Other options:

• Test the stored proc using a "short-lived" DBMS instance like SQLite. It's usually easier to do this when using an ORM, but the test can then be done "in-memory" (or with a pre-set database file included with the test suite). Still not a unit test, but it can be run with a high degree of isolation (the DBMS is part of the running process, and not something you connect to remotely that may be in the middle of someone else's conflicting test suite). The downside is that changes to the stored proc can happen in production without the test reflecting the change, so you have to be disciplined about making sure the change is first made in a test environment.

• Consider upgrading to an ORM. An ORM with a Linq provider (virtually all the ones in common use have one) would allow you to define the query as a Linq statement; that statement can then be given to a mocked Repository that has an in-memory collection of test data to apply it to. You can thus verify the query is correct without even touching the DB (you should still run the query in an integration environment, to test that the Linq provider can correctly digest the query).