How to write Tests for Data Access Layer in .Net?

Question

Is there a framework to write unit test for Data Access layer? There are several issues in this.
1. How to populate your database?
2. How do we make sure that one test is not modifying values in db that can fail another test?

Is there a good framework available which can take care of above issues?

Update: I came across nDBUnit which can take care of infrastructure issues with testing data access layer.

http://code.google.com/p/ndbunit/wiki/QuickStartGuide

Answer 1

I guess I'll chime in, since I really dislike some of the answers so far.

Whether you call it a "unit test" or "integration test" or a "supercalifragilisticexpialidocious test" does not matter one bit in this instance; there's only one valid test for data access components and that it is to test it on actual data. Not production data obviously, but a reasonable facsimile thereof.

The very first thing you need to do is get the database itself under source control. Sadly, there is no framework for this; Microsoft goes part of the way in some versions of VSTS but the end result is still somewhat lacking. At least in today's world, you're going to have to do a lot of this work yourself. But do it, seriously - you won't regret it when a major update gets botched and you need to roll back the DB.

What you put under source control should be everything necessary to generate the newest schema, usually a baseline script, plus "configuration data" scripts (i.e. the contents of enumeration tables), and upgrade scripts to reflect recent schema changes. This gives you almost everything you need to perform "live" testing on a temporary database; your test only needs to download those scripts from source control and run them on a test server and/or a different database instance, usually using SQL Management Objects to run said scripts (SMO can handle GO statements and the like; a regular SqlConnection cannot).

Various tools can help you with the generation of test content in the test database. Probably the most popular is Red Gate's SQL Data Generator. These tools can also generate scripts to create the data, which is what you'll be using in your tests. Or, if you so choose, you can scrub the data from your production database and use SQL Server Management Studio to script whatever data you choose to keep for testing. Either way, keep your test data scripts in source control, same as the schema scripts, and when you need to test your DAL, download these scripts after firing up a DB instance and use them to populate the data.

I wish there were a single framework that would do all of this for you, but with the right collection of tools, libraries, and good development practices, you can make this into a much less painful process.