Restoring state of database after running a Unit test

[sprash]

Sorry if this is a newbie question.

My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.

I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.

I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.

2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.

I liked the article by Roy Osherove (http://msdn.microsoft.com/msdnmag/
issues/05/06/UnitTesting/#S5) but unfortunately I do not have control
over the transaction because the
transaction was commited by that different third party application.

Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?



Thanks.

 

[Sergey Zyuzin]

Sorry if this is a newbie question.

My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.

I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.

I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.

2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.

I liked the article by Roy Osherove (http://msdn.microsoft.com/msdnmag/
issues/05/06/UnitTesting/#S5) but unfortunately I do not have control
over the transaction because the
transaction was commited by that different third party application.

Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?

Thanks.

Hi,
I think you describe an integration test. In some cases it is possible
to design it in such a way that
test doesn't depend on data already in database. It just creates new
data.

If you just need to test sending functionality I think you need to
mock your 3rd party component in some way.

HTH,
Sergey