Unit tests and database

Question

This question about unit tests sparked another thing that's been bothering me. I've gone back and forth on three ways to do unit tests when hitting a database.

1. Create mock objects and plug them in. This has the advantage of not needing a database, but it's time consuming and I'm not sure how much return on investment I'm getting. I've been getting into IOC and moq a little bit, but it still seems painful.
2. Create a setup and teardown database scripts to create known cases, and test for those. Again, can be time intensive, but still easier to create than mock objects most of the time. And other people at work can still run it assuming they have SQL server on their localhost.
3. Manually check the dev database and modify unit tests. Intensively manual work, but if I have a "test set" that doesn't change, it seems to work OK. On my machine, at least :-).

I know option 1 is the "proper" way to do unit tests, but of the three, that's probably the option I've used the least (although the latest projects have been with IOC, so that door is open to me). I realize a lot of it depends on what exactly is being mocked and what is being tested, but what am I missing here?

If context helps, I'm in a C# shop, writing in-house applications, only a few developers.

Answer 1

The first one shouldn't be that hard if you've got a Data Access Layer that exposes just a handful of IQueryable<T> methods. There's a nice trick you can use for fake data: just store the entity objects in a List<T> and use AsQueryable. I find this easier than moq for the data parts.

Regarding IOC, I take the position that it's currently overused (be aware that my opinion represents the minority of programmers in this regard). You can use a tool like Moles during testing to mock without having to abuse your program's design. I don't use IOC unless the design actually calls for it.