Database interaction code

Question

Hi there

I'm writing a small application that works with a database and I can't figure how to test this interaction. My application relies on 5 stored procedures that I have decided to encapsulate them in a class. This class offers 5 public methods that execute the procedures and, when necessary, convert the results into collections of objects.

I really can't figure out how to test my code in a simple way and I think that probably I made some mistakes.

What do you think about my design? Is there a better way to handle these cases?

Thank you very much for your help

EDIT: Among my stored procedures there are only "insert" and "select" queries.

Answer 1

Why not just call each of the methods, which in turns, calls the stored procedures and examine the results by iterating through the collections? If there are many results, limit the number that come back for testing purposes.

Now if you are trying to verify the quality of the data, I don't think this is the right way to do it.

Answer 2

I often employ automated integration testing when dealing with databases and logic therein. The general idea is to set up a test database using the schema for your application. In the test script, you can populate the test database. When you have everything set up, use your class with the test database to test that everything is working OK. Alter the data and test again.

You'll need to take care to create and destroy the test database during each test run. You don't want data from previous tests, or else you can't precisely test the stored procedures.

Since there are more players in integration testing than there are in unit testing, you'll have to do more setup and cleanup work.

You could test your class using unit tests, mocking the database connection, but that would not verify that the stored procedures are working properly. Instead of writing unit tests for stored procedures, it's often easier to just do integration tests.

Answer 3

In general, I'd recommend against testing things against a live database for a few reasons:

1. Executing SQL can make your tests take a long time
2. Data can change under your nose, resulting in broken tests even with no visible code changes. You want your tests to be isolated and deterministic, so they pass or fail only when your code changes.
3. If you have code which updates a database, you need to roll back your changes or your next test run could result in a false positive or false negative.

So for testing, you want to fake out your database. Start with something like this:

public interface IDataRepository {
    Customer GetCustomerByName(string name);
    void SaveCustomer(Customer c);
    SecurityToken Login(string username, string password);
}

class DatabaseRepository : IDataRepository { ... } // invokes your stored procedures

If your classes need something from the database, pass an IDataRepository into your object's constructor.

Nice thing about this setup is how you can create a FakeDataRepository implementing the same interface -- it doesn't invoke anything, only returns hard-coded data. You can return data for the happy case, for the exception case, and other needs.

In other words, you're not testing the interaction between your classes and database at all -- the point of a unit test is to test a single piece of functionality without caring about the other moving parts in your app.

---

Now if you need to test your stored procedures, you should can write tests for your DatabaseRepository class directly.

You need to re-set your database to its original state after each test -- so you either run everything in a transaction and rollback, or you create a new database with scratch data on each test run. I prefer the latter approach -- its just too easy to leave a transaction open or forget to roll it back, and therefore destroy all of your test data.

Database tests like this can take an arbitrarily long time to execute, so you're probably best creating a separate project strictly for testing your database (especially if you have a few hundred stored procedures).

I'd think of this as more a system or integration test, not a unit test.

Answer 4

First you should get your self a developer database. You could use an in-memory database, but I find life a lot better to have a real database instance living somewhere in the dev environment. To be clear on this – your developer database and not a shared instance.

Next you need to make sure that this class can have its settings injected. At a minimum that means an extra constructor that takes a connection string so during test you can point it at your developer database.

Your code has three items that need to be tested. The first is object marshalling; the second is hitting the database and finally you have exception handing. It may be worth testing each item on its own. If so split the marshalling in to their own methods taking a DataRow or ResultSet and make them public.

The second item to test bit that hits the database (an integration test, but a damn useful one – and the closer the dev database is to live the better. If you use Oracle in live, use Oracle as your developer database). This means you need to set up your database. I currently manually keep the schema up to date and automate the setup of data using a tool like dbUnit. This can clear data, populate tables with XML and validate tables against an XML file.

Finally you have exception handling, to do this you need to inject something other than the connection settings, but rather a IDbConnection or Connection, so you can for the connection to throw an exception when you need it too (this assumes you do something special to the exceptions, if you just log ‘em and let them bubble out it might not be worth testing.