Integration tests - Rollbacking complex transactions

For integration testing purpose, we are savin data&reading data back from SQL. To avoid 'trash' in the test database, it runs in a transaction and rolled back.

While running this transaction, it throws 'TransactionScope error' exception:

using (var transaction = new TransactionScope()) 
{
    // saving (submitchanges)
    // reading (linq2sql select to get saved data)   // 'Transaction has aborted' was thrown

    // rollback
} 

When using explicit connection/transaction handling it works well - but the code is ugly.

What can I do?

Environment: .NET 3.5/C#, MSSQL2k8

Detailed exception:

System.Transactions.TransactionAbortedException : The transaction has aborted. ----> System.Transactions.TransactionPromotionException开发者_JS百科 : Failure while attempting to promote transaction. ----> System.Data.SqlClient.SqlException : There is already an open DataReader associated with this Command which must be closed first.*

I've struggled with this question for a number of years. There are basically two extremes you have to choose from:

1. Cluttering your code with test specific transaction and rollback code, which is error prone, and
2. Doing a full reset of the database between every test.

Notice I said "reset", not "restore"...it doesn't have to be that intensive. I've come up with a number of approaches, including detaching and reattaching a canonical testing mdf file (much faster than a restore, at least it was then), executing a data dump reset script (truncating or deleting all the tables and resetting them).

Lately, I've been looking at RedGate's Sql Compare...I'm still not exactly sure how to put that together. But ultimately, I went all the way down the transaction / rollback path and decided that it introduced too much un-unit tested code into the testing equation.

If you are not testing DB structure, then I'd suggest removing the DB from the equation all together and go for dependency injection approach. If you are using LINQ to SQL, not stored procs, this means you may actually control where the data of interest come from, i.e. in normal circumstances it will be database, and under testing, it can be predefined collections of objects. In order to abstract usage from the data, you have to define a data provider interface/abstract class with a bunch of IQuariable<T> properties. The default implementation would link them with the database tables, the test implementation would generate them in memory. All you have to do now is to inject one or another implementation instance.