How do you unit test a DAL?

I have a Data Access Layer in my application which wraps an ADO.NET data provider. The DAL converts the data returned by the data provider into .NET objects. I've seen a lot of posts advising against unit testing the DAL, but it worries me that so much could go wrong in there - there's lots of looping and casting and null checks.

I had some thoughts about creating a mock DbProvider with something like RhinoMocks, but the number of interfaces I'd have to mock out in each test would be overwhelming, and the number of expectations I'd have to set would make the tests very hard to read. It seems that each test would be more complex than the code it was testing - which would be a disaster from the perspective of the 3 goals of unit testing:

1. Readability
2. Maintainability
3. Trustworthiness

I had an idea to implement a friendly DbProviderFactory to load the sample data from xml. I could plug it in via Dependency Injection in the tests. It should make maintaining the tests much simpler. A trivial example might be:

[TestCase]
public void CanGetCustomer()
{
    var expectedCommand = new XmlCommand("sp_get_customer");
    expectedCommand.ExpectExecuteDataReader(
        resultSet: @"<customer firstName=""Joe"" lastName=""Blogs"" ... />");

    var factory = new XmlProvid开发者_运维知识库erFactory(expectedCommand);

    var dal = new CustomerDal(factory);
    Customer customer = dal.GetCustomer();

    Assert.IsNotNull(customer, "The customer should never be null");
    Assert.AreEqual(
        "Joe", customer.FirstName, 
        "The customer had an unexpected FirstName.");
}

I think this approach - using a friendly DbProvider - might make it easier to test the DAL code. It would have the following advantages:

1. The test data would be in xml, and could be placed in source control along with the unit tests. It could be in external files, or in-line in the tests.
2. I'm not using a real database so this eliminates the statefulness problem. So I don't have to put a database into a known state prior to each test.
3. I don't have to mock out all of the ADO.NET interfaces in each test. I'll code one set of fake implementations which I can re-use all over the codebase.

Could people provide some critique on this idea? Is there already a similar implementation that I could use for this?

Thanks

---

I get into a lot of philosophical arguments about the topic of proper unit testing (no, not integration testing) of data access classes (DALs, DACs, DAOs, Repositories, etc.). Some argue it is pointless since you are doing integration testing. I find tremendous value in unit testing these often neglected units of code. First, in order to properly unit test a data access class, it must be structured correctly and it must draw lines in the sand to which consumers may interact – think interfaces. The data access implementation should have an interface defined that it implements which the consuming application code only has a dependency on. Your choice of infrastructure code (ADO.NET, NHibernate, NDatabase, etc.) should have interfaces which your data access code only has dependencies on. With these infrastructure interfaces (IDBConnection, ISession, IDatabase, etc.) available and leveraged correctly, you can then mock these interfaces in your unit tests using your mocking tool of choice. This leaves you with higher quality data access code which has been unit tested (mocking infrastructure interfaces), integration tested (against a REAL database), and has lower net coupling all around.

One note: In my opinion, a bad code smell to be wary of is when data access related code bleeds past the data access (or persistence) layer. For example, if you see connections, commands, sessions, etc. used higher that the data access class implementation, this reeks of a violation of Separation of Concerns.