I recently ran into a problem: How to test some code that was not readily testable without carrying along a database.

It was my own fault, really. I had quickly written a data access layer that was directly dependent on Entity Framework, and was also doing some minor business logic. Because I had a direct dependency on Entity Framework, and had not used dependency injection, I had a problem… How do I write unit tests against those methods that contain business logic that also make direct calls to stored procedures through Entity Framework?

Now there are a number of things that should have been done differently here, and (if I had time) that I would go back and change. I should have separated out the Entity Framework and associated code in another class with an interface, so that dependency could have been mocked in my data access layer. And if I had been following a test driven design (TDD) approach, unit tests would have been written first, and the code would then have been written to make those tests pass. Following TDD would have helped me to avoid some pitfalls. I’m sure none of the readers here have ever taken similare shortcuts (ahem).

Figure 1 shows one of the methods that needed to be refactored. You can see that this code is doing more than one thing — a violation of the Single Responsibility Principle. That’s part of the reason for the testability problem.  And although it’s not obvious here, there’s a serious issue with “Don’t repeat yourself.”  We’ll need to fix both of these problems.

Code Before Refactoring - Figure 1

The first step is to simplify this method by reducing its complexity and its knowlege about how to construct parameters, how to construct a call to and execute a storec procedure.  Here’s the updated code in figure 2.

Code After Refactoring - Figure 2

In Figure 3, you can see that I’ve moved the logic to construct the stored procedure call into a method that itself can be unit tested.  I didn’t show the code for it, but GetNewSqlParm takes care of building parameters for the stored procedure call.  This will also be leveraged in other methods of this class, and can itself have unit tests written against it.

BuildStoredProcCall - Figure 3

Okay, now I have specific methods that are testable and do only one thing — good progress. But back to the main method I need to test.  How do I write tests against this code? How do I supply “fake” data when testing, that takes the place of what would be coming back from the database if the stored procedure were really being called?

ExecuteQuery provides for both needs. It hides the details about how to call a stored procedure, and permits a way to provide fake data to the calling method when we want to unit test.

ExecuteQuery - Figure 4

Notice that ExecuteQuery checks to see if “FakeTestQuery” is set to a value. If so, it provides fake data back to the caller. Here’s the declaration for FakeTestQuery.

Fake Query Type Declaration - Figure 5

“FakeTestQuery” in Figure 5 provides a means where a test can supply a delegate method that supplies fake data for testing purposes.  The method we started out with can now have its logic isolated from the details getting data from the database, and we can test that it behaves as it should.

I’m sure there can be many improvements made beyond what I’ve shown, but we’ve made some serious progress!  We started with code that was directly dependent on having an attached database in order to test it.  We ended up with code that could be unit tested, with the test supplying the data.  We also reduced the complexity of the method we were testing, and introduced reuse of two methods that can be leveraged in other database access calls.