Unit Testing and Stored Procedures

Question

How do you unit test your code that utilizes stored procedure calls?

In my applications, I use a lot of unit testing (NUnit). For my DAL, I use the DevExpress XPO ORM. One benefit of XPO is that it lets you use in-memory data storage. This allows me to set up test data in my test fixtures without having an external dependency on a database.

Then, along came optimization! For some parts of our applications, we had to resort to replacing code that manipulated data through our ORM to calling T-SQL stored procedures. That of course broke our nice testable code by adding a new external dependency. We can't just "mock out" the stored procedure call, because we were testing the side effects of the data manipulation.

I already have plans to eventually replace my usage of XPO with LINQ to SQL; LINQ to SQL seems to allow me better querying capabilities than XPO, removing the need for some of the stored procedures. I'm hoping that if I change over to LINQ to SQL, I will be able to have my unit tests use LINQ to Objects to avoid a database dependency. However, I doubt all spocs can be replaced by LINQ to SQL.

Should I:

• bite the bullet and change some of my test fixtures so they create SQL Server databases,
• create database unit tests instead of testing the code,
• or skip testing these isolated incidents because they're not worth it?

I'd also love to hear about your alternative setups where stored procedures peacefully co-exist with your testable code.

Answer 1

The approach I use for this is to encapsulate the the logic layers from the calling of stored procedures behind another method or class. Then you can test the database layer logic separate from testing of the application logic. This way you can create separate unit tests for the client side application logic and integration tests for the server side (database) application logic. Given a piece of code that utilizes a stored procedure call as I have below:

class foo:

    prop1 = 5

    def method1(listOfData):
        for item in listOfData:
            dbobj.callprocedure('someprocedure',item+prop1)

It can be refactored to encapsulate the call to the remote system to it's own method:

class foo:
    prop1 = 5
    def method1(listOfData):
        for item in listOfData:
            someprocedure(item+prop1)

    def someprocedure(value):
        dbobj.callprocedure('someprocedure',value)

Now when you write your unit tests, mock out the someprocedure() class method so that it does not actually make the database call. Then create a separate set of integration tests which require a configured database which calls the actual version of someprocedure() and then verifies that the database is in the correct state.