How to effectively Unit Test a DAL that uses ADO.NET and SQL Server with NUnit?

Question

So you have a DAL in C# that using the Repository pattern and you have an interface for each Repository. It is backed by ADO.NET, MS SQL Server, and Stored Procedure calls.

This is great for stubbing/mocking out the repository where it is being used else where when doing unit tests, and I Love it!

However, I would love to add some unit testing for the DAL itself. Preferably using Rhino Mocks with NUnit. However I am open to switching to MoQ if a solid case could be made that it can do something that Rhino can't in relation to this question.

I would rather not have it talk to any DB during the Unit Tests to keep they more "pure" while still effectively testing the DAL itself. If however you can not really effectively unit test a DAL itself without it talking to a DB, what would be the alternatives that could be in memory replacements or a portable file based replacements that are compatible with of SQL Server and same SqlConnection and SqlCommand calls.

Open to refactoring the DAL if needed to make it easier to inject as long as it doesn't over complicate the design at the same time. Already using Microsoft Unity, for DI with the Repository Pattern.

Answer 1

What exactly would those unit tests test? Considering typical DAL code is rarely anything more than delegating actual work to 3rd party data access code (ADO.NET, EntityFramework, NHibernate).

I haven't used ADO.NET much, but I suppose this NHibernate example is sufficient enough:

public User GetUser(int id)
{
    using (var session = sessionFactory.OpenSession())
    {
        return session.Get<User>(id);
    }
}

Of course, assuming the code is wrote correctly, all the dependencies (sessionFactory namely) would be passed via interfaces and therefore easy to stub, so writing unit test is possible (you'll just have to mock a lot of stuff to make it happen).

Purist approach would be to write such unit test, as it's yet another data point proving your code indeed does what you assume (calling Get). However, note that test like this is rather expensive, as you essentially have to stub entire database access made by your provider (mocking sessionFactory to return mocked session which is then checked for calls made).

This is difficult decision to be made (whether follow purist approach in this case), as you will have to write integration tests with real database anyways. And those tests will cover exactly the same areas as unit tests you'd wrote, but working in real environment (instead of stubbed one).

In my experience, unit testing DAL is usually not worth the benefits it offers, especially when contrasted with its rather high costs (time spent writing code to stub the database environment) for unit test) and existence of proper integration tests suite.

And last, I would advise against using in-memory database in any kind of testing. For the reasons below:

• it is slow
• it has several possible failure points that may be difficult to control (ORM configuration, in-memory database setup, possibly invalid test data)
• gives you false sense of doing integration testing (while you're not; in-memory database might behave entirely different than your real, production one)

Unless you can load up exactly the same database to the memory, stick to proper integration tests (that would be DAL testing priority) with isolated unit tests backup (only if you can afford to write them).