Unit testing filtered lists

I grok the idea of unit testing and why it’s important. I’ve got my head around NUnit and I now develop with tests from the outset.

However, after using this process for over 12 months now I still haven’t seen a decent tutorial or explanation about how to handle one common situation: how do you test filtered lists of data returned from a data source?

The issue is simple: in order to test that the correct data is returned you need known data in the database. To set this up I see two methods – one is to run an SQL script to create the data before the test is run (which needless to say is tedious to and error prone).

The second is to use coded methods for adding data to the database – which presents significant dependencies on the code to run the tests (but dependencies that we can reasonably assume are supported). You still need a clean database for this second approach to work, but a clean db is (usually) fairly simple to set up.

Neither method is particularly attractive, and I’d love to know how other developers handle this situation.

P.S. I’ve decided to stop using innergeek for a while – partly coz I don’t have time to work out what broke in the server change, but partly because mostly what I used to post to it were just links. I use del.icio.us for this now, so I have far less use for it. I will get it up and running for posterity, but it may take a little while.