Software QA FYI - SQAFYI

How to: Write a Database Unit Test that Runs within the Scope of a Single Transaction


You can modify unit tests to run within the scope of a single transaction. If you take this approach, you can roll back any changes that the test enacted after the test ends. The following procedures explain how to:

* Create a transaction in your Transact-SQL test script that uses BEGIN TRANSACTION and ROLLBACK TRANSACTION.
* Create a transaction for a single test method in a test class.
* Create a transaction for all test methods in a given test class.

For some procedures in this topic, the Distributed Transaction Coordinator service must be running on the computer on which you run unit tests. For more information, see the procedure at the end of this topic.

To create a transaction using Transact-SQL
1. Open a unit test in the Database Unit Test Designer.
2. Specify the type of script for which you want to create the transaction. For example, you can specify pre-test, test, or post-test.
3. Enter a test script in the Transact-SQL editor.
4. Insert BEGIN TRANSACTION and ROLLBACK TRANSACTION statements, as shown in this simple example. The example uses a database table that is named OrderDetails and that contains 50 rows of data:

UPDATE "OrderDetails" set Quantity = Quantity + 10
RAISERROR('Row count does not equal 50',16,1)


You cannot roll back a transaction after a COMMIT TRANSACTION statement is executed.

For more information about how ROLLBACK TRANSACTION works with stored procedures and triggers, see this page on the Microsoft Web site: ROLLBACK TRANSACTION (Transact-SQL).

To create a transaction for a single test method

In this example, you are using an ambient transaction when you use the TransactionScope type. By default, the Execution and Privileged connections will not use the ambient transaction, because the connections were created before the method is executed. The SqlConnection has an EnlistTransaction method, which associates an active connection with a transaction. When an ambient transaction is created, it registers itself as the current transaction, and you can access it through the Current property. In this example, the transaction is rolled back when the ambient transaction is disposed. If you want to commit any changes that were made when you ran the unit test, you must call the Complete method.

To create a transaction for a single test method
1. In Solution Explorer, right-click the References node in your test project and click Add Reference.

The Add Reference dialog box appears.
2. Click the .NET tab.
3. In the list of assemblies, click System.Transactions and then click OK.
4. Open the Visual Basic or C# file for your unit test.
5. Wrap the pre-test, test and post-test actions as shown in the following Visual Basic code example:

Full article...

Other Resource

... to read more articles, visit

How to: Write a Database Unit Test that Runs within the Scope of a Single Transaction