.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

Try using mbunit. It's a .NET testing framework that allows you to fill the database in your setup, and then rollback the changes you did to the database during your tests, restoring the database to its previous condition. There's a quick writeup on it here.

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

Tests for the code that saves to and reads from databases are called Integration Tests. You can use a data generator to generate test data prior to running integration tests. Integration tests don't have to be run as often as unit tests.

Answer 4

• A: If you FlexApplication access your database directly it is not easy to test. You should have a testable interface/layer in between.
• B: Putting data into the database is normal to have in the "TestSetup-Phase".
• C: It should be possible to test an interface who actually triggers the storedprocedure.
  • if it is sprocs that are not used by GUI but only sql-to-sql, it is also systems "out there" that tests sprocs. normally you have a sp_setup and sp_teardown sproc before and after the actual tests

Answer 5

It's funny, I have the same issue on my project. Mocking is probably a good way to go, but I haven't tried that. Generally, we populate our tables with data. I write unit tests that exercise the CRUDL capabilities of a given class. So if I have a Person class, the unit tests inlcude create, read, update, delete and list. These methods tend to call stored procedures (in most cases), so it tests that part of it as well.

There are tools out there that can dump boatloads of test data.

Sql data generator from Red Gate

Let us know what approach worked for you.

Answer 6

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 7

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.