What about DB tests?

[jas4th]

Hi,
As systems accessing databases are a major percentage of developments - has anyone any ideas regarding unit testing this. The problems being - how to practically make a DB return to state after test run, to ensure its in expected state before test run, to test the unit of code interacting with the DB functioned correctly - eg updates to hundreds of records. All this without over burdening the developer.
Cheers

[si_the_geek]

It depends on which database system you are using, but a typical way is to set up the database as you want it, and then take a backup. When testing is finished, you simply restore the backup.

The exact method of doing this varies greatly by DBMS (for Access, you would just copy the file).

[jas4th]

Thanks - but with unit testing we must ensure that each unit test can be run independently and in any order/combination - so it would need a mechanism to put it back as before after each test is run - usually one after another in a batch. If the mechanism requires complex code adding - then this could cause additional opportunity for errors being introduced into the unit tests.

[si_the_geek]

True.. I would personally create a separate batch job to do the work, and run that each time it was required. If there are errors then they need to be dealt with, but they can be ironed out over time, and the same code can be used for the next system you are testing.

For an Access database the batch job could be a simple .bat file, whereas for 'proper' databases like SQL Server or Oracle it would probably be best as a stored procedure.