Unit Testing and Stored Procedures

Question

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

In my applications, I use a lot of unit testing (NUnit). For my DAL, I am using ORM DevExpress XPO. One of the advantages of XPO is that it allows the use of in-memory data storage. This allows me to customize test data in my test fixtures without external database dependency.

Then, go ahead, there was an optimization! For some parts of our applications, we had to resort to replacing the code that controlled the data through our ORM to invoke T-SQL stored procedures. This, of course, broke our good testable code by adding a new external dependency. We can't just “make fun of” a stored procedure call because we tested the side effects of data manipulation.

I already have plans to eventually replace my use of XPO LINQ to SQL; LINQ to SQL seems to allow me to query features better than XPO, eliminating the need for some stored procedures. I hope that if I switch to LINQ to SQL, I can have my unit tests use LINQ to Objects to avoid database dependency. However, I doubt that all spocs can replace LINQ with SQL.

Should I:

• bite a bullet and modify some of my test fixtures so that they create SQL Server databases,
• create tests of database modules instead of checking code,
• or skip testing these isolated incidents because they are not worth it?

I would also like to hear about your alternative settings where stored procedures coexist peacefully with your test code.

Answer 1

, , . . , ( ). , , :

class foo:

    prop1 = 5

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

:

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

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

, , someprocedure(), . , , someprocedure(), , .