I am trying to setup a unit test (NUnit) for some database functions such that I can start a transaction in my unit test and rollback the transaction at the end, thus keeping the database in the same state and the end of the test that it was at the beginning of the test.

The problem is that my production code already creates a transaction and so I am unsure how to set up this test. My production code looks something like this:

            try
            {
                adapter.StartTransaction(System.Data.IsolationLevel.Serializable, "SaveTransaction");
                SaveMedia(adapter, userId);

                adapter.Commit();
                m_mediaId = m_mediaEntity.MediaId;
            }
            catch (Exception e)
            {
                adapter.Rollback();
                throw;
            }

In my unit test code, I'm creating a the adapter and also starting a transaction. The code above dies on the StartTransaction call b/c there is already a transaction started. I can check first to see if a transaction is in progress and only create the transaction if not already started. But since the commit call executes, the rollback() call in my test code will not actually remove the entity just saved. I suppose I could also modify the code not to commit if the transaction was not created here, but that all seems a bit hokey.

So I'm looking for a better way to have my unit test set up a transaction such that it can be rolled back at the end to remove any inserted data - but this mechanism needs to work when the production code also creates a transaction.

Any ideas? Do I need to use savepoints in the transactions? Are there better ways to accomplish this?

Thanks, Beth

IMHO, how you've setup the test setup/teardown isn't recommended. This is because it's undefined how the 3 elements which make up your test: Setup, test and teardown are executed. They should be ATOMIC, which means that the Setup should set things up but NOT in such a way that it sets up STATE which is used in the TEST. The Test is unaware of any state, has no reliance on order or whatsoever, a test is atomic.

Setup and Teardown could be used in the following way: - Setup inserts data into the database. - Test then runs, test uses data in the database - Teardown deletes all data in the database

This way, Test doesn't rely on any object/state created in Setup or Teardown.

Your message doesn't answer my question though, unless you are recommending NOT to create a transaction at all and roll it back at the end of the test (regardless if this code were in the Setup/Teardown, or just part of the test itself).

Is there a way to create a transaction outside a method that also creates a transaction?

You could perhaps investigate the use of transaction savepoints

ADO.NET does not support nested transactions, so there is no way to create a transaction outside of a method which already creates one. Your solution to check if one already exists and use that just feels wrong, as it means you are modifying your program code to work around your test code - it should be the other way round.

But I think what David is saying is exactly "Don't create a transaction in your unit test" - it is simply not the way unit tests are meant to work

Matt