.NET Unit Tests for Reading/Saving data to database

Question

Most things I read about Unit Tests is about testing your classes and their behaviour. But how do you test saving data to a database and reading data from a database. In our project saving and reading data is done through services that are used by a Flex Application (using WebORB as a gateway). For instance, a service reads all users that have access to a certain module. How do you test that the users that are being returned actually are the users having access to that module?

Sometimes being able to test loading data out of database requires that there's data already in the database. In some of our tests we first need to save a lot of testdata to the database before being able to test reading stuff...

The same thing is valid for Stored Procedures. How do you test sp's if there's no data in the database. Reality is that to test certain stored procedures, we need data in ten tables...

thx, Lieven Cardoen

Answer 1

You can have tests for db actions, but try to avoid it if possible, otherwise:

• They will run slower than ordinary tests (more likely they are integration tests)
• They require more setup/teardown work (db/schema/table data)
• They introduce an external dependency on your test framework

It may also be a code smell that your classes are not separating db related work from other work, e.g. business logic. However it may not, we have a framework test which verifies that the automatically generated SQL script returns the expected incremented identity value after inserting new data, AFAIK there is no way to test that this code is working other than to execute it against the db. You could mock it out or just assume that if the SQL matches what you expect then it's ok, but I don't like that assumption since so much other code relies on it.

Depending on your test framework, you should mark these tests as [Database] related, allowing you to separate them from other tests.

Answer 2

this is more an integration-test than an unit-test.

what i do in such cases is that i build a non-persisting-base test which loads data needed for the tests in a test-db and then runs the unit-tests. after that it disposes the current transaction so no data is stored.

biggest problem here is that if your customer has a failure - you cannot run such tests... another problem is that the data in your test-db will be reseted everytime you run such tests.

Answer 3

I agree with @Gambrinus. In general, it's almost impossible to unit test a data layer; the best you can do is provide a strong data layer interface and mock against that in the business layer, then save data quality tests for your integration testing.

I've seen attempts at mocking ORM tools (this one for LINQ amuses me), but they do not test the correctness of a query, only that the query was written in the way the tester thought it should be written. Since the tester is usually the one writing the query against the ORM, this provides no value whatsoever.