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]

je**********@gmail.com wrote:

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)
--
Michael Justin
SCJP, SCJA
betasoft - Software for Delphiâ„¢ and for the Javaâ„¢ platform
http://www.mikejustin.com - http://www.betabeans.de

[jehugaleahsa]

On May 13, 1:14*pm, Michael Justin <[e-mail]:

[e-mail] wrote:

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)
--
Michael Justin
SCJP, SCJA
betasoft - Software for Delphi™ and for the Java™ platformhttp://www.mikejustin.com-http://www.betabeans.de

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

On May 13, 2:56 pm, "[e-mail]" <[e-mail]>
wrote:

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.

[=?Utf-8?B?RXRoYW4gU3RyYXVzcw==?=]

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