I never felt comfortable with doing unit tests against live database, alas, I feel it is a necessary evil to contend with, because the feedback you get is much more helpful than using Mock objects for such a task, I mean, c'mon, you need to test that you actually got something into the database, right?

anyway, if there's one thing I noticed is that you get a lot of repetitive code in the unit tests. Usually, I'll  have a class in the data layer that acts as the table manager, allowing basic CRUD work (Create,Retrieve,Update,Delete), and my tests against that class will usually look very much the same:

• Insert a record and make sure you get an id
• make sure the ID is in the DB
• make sure all the inserted values are in the DB
• Update
• make sure all updated values are in the db
• delete
• make sure record is not in database
• and so on...

If there's one thing Test driven development tells you to do it's to refactor the tests after they pass, and so much repetition just seems like a lovely thing to to refactor. so much that  I could not help myself. So, I created  this:

CRUD base classes for unit testing

The crud base classes are contrived of four base classes:

• DataManager: a class to derive from to implement a simple table data manager class that has CRUD ability through simple Update(dataset) method
• SimpleCRUDTestFixture: allows you to do simple CRUD tests on a given DataManager class automatically (will test and assert CRUD using the dataset)
• CRUDTestFixture: derived from SimpleCRUDTestFixture and forces the derived classes to implement manual Insert,Update and delete methods against any tested object(like a recipe that tells you what to test next), yet it does all the grunt work of testing if the methods you implement really succeed. For example, It will force you to implement an InsertDefaultRecord method, which should return the ID of the newly created record in the database, after that , tests will automatically assert that the record is indeed in the database. The same goes for Updates and deletes. So basically, what it does, it is makes you create methods in your test class that test a data manager class and return the outcome for processing in the base class, where all the tests are performed. See the samples for how to do this, very simple and easy.
• TableRelationDataTest: Derive from this class to automatically test foreign key relationships between two tables. This is perfect if you have a table that has a foreign key to a lookup table of values. The base class will automatically test that all the lookup values can actually be inserted into the primary table, and that non lookup values cannot be inserted. The beauty is, there is not one line of code needed for this to be tested. only a few simple properties need to be set in the derived class.

In the zip file you will find the base classes (in VB.NET) that I use in my development, with samples that derive from them.  hope this helps you in your development, and I would love to hear ideas how to enhance this.