Steve Eichert recently wrote a very good post about test driven development with a database.

Unit-testing a database is one of the hardest parts of unit testing. I've been working on it as part of my current project for a while, and I've come up with some good stuff but haven't had a chance to abstract it out yet.  I'll share what I have available right now though (mostly tips and tricks).

If you are unit-testing anything other than the Data Access Layer (DAL), then definitely check out a mock unit-testing program.  That way, you can unit test a business service method without hitting the database, even if the method calls the database internally.  Currently, NMock (from ThoughtWorks developers) and DotNetMockObjects (on SourceForge.net) are two good .NET implementations.  MockObjects is a generic unit testing framework that does a lot of work in establishing mock testing standards and interfaces, and DotNetMockObjects is just one implementation of it.

If you are unit-testing the DAL, you need to be able to automatically add the test data that the unit tests depend on.  Here is where the DRY principle comes into play.  You need to have a single source of data that will populate your database and something that holds the expected values.  For example, if you are using a DataSet, the DataSet should be populated from the exact same data that fills the database (i.e., you should be able to change the source of the data in one place, re-initialize the database, and all unit tests will still pass).  You can then use that DataSet and determine what data would be returned, updated, deleted, or inserted.  Then compare the DataSet returned by the DAL method that actually queries the database.  And since you do not know what order the unit-tests are executed in (and it should not matter anyway) always leave the database in the same state as it entered the unit test, excepting things like Identity fields. 

What I do is keep object arrays of the data I expect in the database.  By changing an entry in an XML configuration file in my UnitTests project, I can cause the database to be initialized with all of the test data.  I do have to make sure the database is empty, which is a simple DTS package to create, and then I backup the empty database and keep it around until the database schema changes.  I also keep a backup of the database with the current unit test data already filled in, in case I write something that inserts rows that I forget to delete or deletes rows I need.  That way I am back up and running in 10 seconds or so, versus reinitializing the entire database, which can take a minute or two.  These object arrays also populate typed DataSets that I use for expected values.  Then, I just compare the expected typed DataRow, typed DataSet, or DataView with the actual returned DataRow, DataSet, or DataView.  It only takes me 10 minutes to figure out what data I need, code the object array, create a call to a method that will insert the object array's data into the table the next time I initialize the database, and create DataSets of expected values.

Anyone interested should also check out the resources I mentioned in this post.

Always work on a local database.  It is a software configuration management best practice that developers should work in private workspaces until they are ready to integrate.  There are patterns for workspaces (607k PDF) that explain why this is a good thing and how it interrelates with other patterns (such as smoke test, daily build, integration, etc.).  After it works locally, then developers integrate.  They should integrate locally as well.  For example, if using Visual SourceSafe, when the developer is done working he should get latest version and merge any changes on his copy.  Then he should make sure all unit tests still pass before checking the code in.  We developed a "Build Breaker" award that someone would get if 2-party verification proved that a developer had checked in a broken build.  To get rid of the award, at the next daily meeting the developer would have to say "Hi, my name is X, and I'm a build breaker."  There is an excellent book that we used to develop our CM policies, Software Configuration Management Patterns (Associates link). It also translates the patterns in the book, which are tool-agnostic, to what exactly you need to do in your particular tool (i.e., SourceSafe or ClearCase).