Best Way to Test Against a Database

[jehugaleahsa]

Hello:

What is the best way to test my methods that hit a database?

I believe tests should be as automated as possible. However, I'm not
sure how I can test against a database since changes will modify the
(development) database.

I really don't care whether my SQL does what it's supposed to do, just
that I have my CommandText, CommandType and Parameters set up
correctly.

For instance, I am currently testing my SELECT statements by passing a
bogus primary key. I am thinking I can test my INSERTs and DELETEs
together. Then I can test my update by passing a bogus primary key
again.

However, this seems dirty. I just wanted to see how others have
handled this in past. Any suggestions would be appreciated.

Thanks,
Travis

P.S. - Not that it should make a difference, but I'm using Team Tester.

 

[Michael Justin]

I really don't care whether my SQL does what it's supposed to do, just
that I have my CommandText, CommandType and Parameters set up
correctly.

If your database client library (and the server) supports the concept of
"prepared" statements, this would be a very easy way to verify the
correctness of the SQL statements without actually executing the
command. "Preparing" a statement will send the SQL to the server and the
server can use the SQL to build a query plan. If the SQL is invalid, the
server will complain (by raising an exception).

Usually I test all statements using a list (array) of SQL strings which
will be iterated by the unit test. In a perfect test environment, every
SQL would be wrapped in one test case. The test will then run over all
SQL statements even if some of them fail.


This is my recommended reading for testing:

xUnit Test Patterns: Refactoring Test Code (Addison Wesley Signature
Series) by Gerard Meszaros


Hope this helps(tm)

 

[jehugaleahsa]

If your database client library (and the server) supports the concept of
"prepared" statements, this would be a very easy way to verify the
correctness of the SQL statements without actually executing the
command. "Preparing" a statement will send the SQL to the server and the
server can use the SQL to build a query plan. If the SQL is invalid, the
server will complain (by raising an exception).

Usually I test all statements using a list (array) of SQL strings which
will be iterated by the unit test. In a perfect test environment, every
SQL would be wrapped in one test case. The test will then run over all
SQL statements even if some of them fail.

This is my recommended reading for testing:

xUnit Test Patterns: Refactoring Test Code (Addison Wesley Signature
Series) by Gerard Meszaros

Hope this helps(tm)

How well does this verify the parameters that I set?

 

[Andy]

Hi,

I typically let the statements execute on the server. I have a stored
procedure that populates the database with test data, and another that
puts it back to it's empty, "clean" state. Of course this does
require that this setup script be maintained properly so that as the
schema changes the script will still put the expected data in. If you
try to code so that you stop just sort of letting the command happen,
you'll probably only be able to do that by creating some kind of
branch in your code, so you're not testing the code as it will run.

Just my thoughts.

Andy

 

[Michael Justin]

How well does this verify the parameters that I set?

As I wrote, this approch will verify the correctness of the SQL
statement. To verify the correctness of the results, the query has to be
executed.


As Andy wrote, resetting the database is critical. A clean test
environment has to be in place for every test, to make them repeatable.

The test steps should also be independent, so that it is not neccessary
to run all tests always together in a specific order. This setup makes
database tests very slow.

Every developer will also need his/her own test database, to avoid 'test
run wars'. (If there is a way to test the business logic without
accessing the database, this would be much better. However this does not
mean that database tests are not neccessary in this case.)


Michael

 

[Joachim Van den Bogaert]

Hi,

We use transactions and the [Setup] and [TearDown] methods in NUnit.
You can begin the transaction in the Setup method, test whatever you
like to test in your test
and rollback the transaction in your TearDown method. If anything goes
wrong the db will complain.

If we want to test the result of a query, we do the same, but in our
test method we commit, assert the result, restore the database
by another commit and then begin a new transaction, in order to have
this empty transaction rolled back in the teardown method
and avoid an exception in the TearDown method.

If you have many testfixtures, you can derive all of them from a base
fixture, which for example begins and rolls back the transaction in
Setup and TearDown, so you
don't need to code this for each testfixture.

Joachim

 

[Ethan Strauss]

I have run into the same issue and came up with a way to deal with it that
works but is certainly not ideal.
I have a setup script that first goes out and copies a database file
(generally yesterday's backup so I don't run into access problems with a live
database) to a new location. Then I restore that file to a testing database
and run my tests.

This works most of the time, but it is slow and I frequently run into file
in use type errors. The thing I like about it is that it does not touch my
live or even my development database, so I don't have to worry about the
testing leaving messy footprints all over my data.

Ethan