0

We are supporting MS SQL Server 2008 (2005 maybe), Oracle as well as Sybase. There is a large script which takes AWHILE, which would build tables, stored procedures, views, triggers, etc. as well as populate tables with what we think is sensible data.

Now we are trying to introduce unit tests to the system, to test stored procedures, mostly, as well as interactions of them. So, I do not expect running statements and stored procedures particularly challenging.

The difficult part is maintaining a proper state of the database before and after the test has run, even if any exceptions occur. Yes, I can utilize the using statement together with 'IDisposable'. The harder part for me is (at least based on what I do and do not yet know about programming): how do I safely restore the state of the MS SQL, Oracle and Syabse database thousands of times over and over? I should not make an assumption that only one user is exercising it at any point in time, although I am willing to consider it. I am hoping that all 3 databases provide mechanisms for that, and I only need to trigger them using the right driver (We tested ODBC already - it seems to work for us).

Please leave your suggestions as well as questions, if you have them.

Improve this question

1 Answer 1

Reset to default
2

There are a few solutions to this problem:

  1. Run the database data creation script after each test. You only need to create the data, not the tables, stored procedures and triggers. These should remain static while running your tests. If data creation is still too time consuming, this won't work.

  2. Isolate the data creation scripts by table and recreate the table data necessary for each test fixture and only run those scripts before each test in that particular feature. This would speed things up, but you're still creating the dataset before each test and could still be too slow to execute before each test.

  3. Best Option: Wrap each test in a transaction. I am assuming all the databases you listed support transactions. Simply, open a transaction, execute your test, verify the results and then rollback the transaction. The rollback will undo any data changes you have made. As long as you use the same connection to execute the stored procedure modifying the data and to verify the results, the changes should be available to the connection, but not committed to the database.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.