I'm starting a new project, and trying very very hard to use TDD to drive the design. I've been pushing for years, and finally got approval to spend the extra time on this project to use it while I learn how to do it properly.
This is a new module, to tie into an existing system. Currently, all data access happens through webservices, which for the most part are just a thin wrapper over database stored procedures.
One requirement is that for a given store, I return all Purchase Orders that are considered valid for this application. A PO is considered valid if it's ship date falls withing a given range from the stores opening date (this is for new stores).
Now, I can't put this logic in the application code, as I'm not going to bring back a million POs just to get the dozen that apply to could apply to this store given the constraint above.
I was thinking, I could pass the date range to a GetValidPOs proc, and have it use those values to return the valid POs. But, what if we add another requirement to what is considered a valid PO?
And how do I test this and verify it keeps working? We are not using an ORM, and it's unlikely to happen. And I can't call the DB in my test.
I'm stuck.
My other thought, is have some mocks that return valid data, others that return some bad data, and have the local repository throw an exception if bad data occurs, and test that the exception gets thrown if invalid data is returned by GetValidPOs proc (or the mock used in testing).
Does this make sense? Or is there a better way?
UPDATE:
I am able to use EF it would seem. Now I just need to figure out how to use it, and make it testable, while still being able to rely on stored procedures, and the difficulty of having data scattered across several databases.
Best Answer
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).