What is the best way to unit test heavily database dependant .NET middleware? E.g. a process which reads data from multiple databases, manipulates it, and then combines and writes it to other databases?
Should the databases be filled with static data which is somehow reset on each unit test run? Should the whole SQL Server access be somehow mocked? Is it not feasible to unit test such an application in the real world?
I'd recommend mocking the data access level. The benefit of using mocks in this instance include: 1) unit tests will run quicker. If they have to do the full work of connecting to the db, extracting data etc. then the cost will become expensive. Expensive tests = people stop running them/start losing faith in them! 2) You can test a vast range of scenarios without having to go through the hassle of setting up suitable test/static data that you have to ensure is always in the DB before the tests start. 3) taking away the external db system from the equation means you are testing just the .NET code you want to test. No external dependency.
You could either have a data access layer that does pure db interaction, and then mock that. Or, use mock SqlCommands etc in your .NET code.