How to: Write a SQL Server 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.

Prerequisites

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

To create a transaction using Transact-SQL

1. Open a unit test in the SQL Server Unit Test Designer. (Double click the source code file for the unit test to display the 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:

   BEGIN TRANSACTION TestTransaction
   UPDATE "OrderDetails" set Quantity = Quantity + 10
   IF @@ROWCOUNT!=50
   RAISERROR('Row count does not equal 50',16,1)
   ROLLBACK TRANSACTION TestTransaction
   

   Note

   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:

       <TestMethod()> _
       Public Sub dbo_InsertTable1Test()
   
           Using ts as New System.Transactions.TransactionScope( System.Transactions.TransactionScopeOption.Required)
               ExecutionContext.Connection.EnlistTransaction(Transaction.Current)
               PrivilegedContext.Connection.EnlistTransaction(Transaction.Current)
   
               Dim testActions As DatabaseTestActions = Me.dbo_InsertTable1TestData
               'Execute the pre-test script
               '
               System.Diagnostics.Trace.WriteLineIf((Not (testActions.PretestAction) Is Nothing), "Executing pre-test script...")
               Dim pretestResults() As ExecutionResult = TestService.Execute(Me.PrivilegedContext, Me.PrivilegedContext, testActions.PretestAction)
               'Execute the test script
   
               System.Diagnostics.Trace.WriteLineIf((Not (testActions.TestAction) Is Nothing), "Executing test script...")
               Dim testResults() As ExecutionResult = TestService.Execute(ExecutionContext, Me.PrivilegedContext, testActions.TestAction)
   
               'Execute the post-test script
               '
               System.Diagnostics.Trace.WriteLineIf((Not (testActions.PosttestAction) Is Nothing), "Executing post-test script...")
               Dim posttestResults() As ExecutionResult = TestService.Execute(Me.PrivilegedContext, Me.PrivilegedContext, testActions.PosttestAction)
   
               'Because the transaction is not explicitly committed, it
               'is rolled back when the ambient transaction is 
               'disposed.
               'To commit the transaction, remove the comment delimiter
               'from the following statement:
               'ts.Complete()
   
       End Sub
       Private dbo_InsertTable1TestData As DatabaseTestActions
   

   Note

   If you are using Visual Basic, you must add Imports System.Transactions (in addition to Imports Microsoft.VisualStudio.TestTools.UnitTesting, Imports Microsoft.VisualStudio.TeamSystem.Data.UnitTesting, and Imports Microsoft.VisualStudio.TeamSystem.Data.UnitTest.Conditions) If you are using Visual C#, you must add using System.Transactions (in addition to the using statements for Microsoft.VisualStudio.TestTools, Microsoft.VisualStudio.TeamSystem.Data.UnitTesting, and Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Conditions). You must also add a reference to your project to those assemblies.

To create a transaction for all test methods in a test class

To create a transaction for all test methods in a test class

1. Open the Visual Basic or C# file for your unit test.

2. Create the transaction in TestInitialize, and dispose of it in TestCleanup, as shown in the following Visual C# code example:

   TransactionScope _trans;
   
           [TestInitialize()]
           public void Init()
           {
               _trans = new TransactionScope();
               base.InitializeTest();
           }
   
           [TestCleanup()]
           public void Cleanup()
           {
               base.CleanupTest();
               _trans.Dispose();
           }
   
           [TestMethod()]
           public void TransactedTest()
           {
               DatabaseTestActions testActions = this.DatabaseTestMethod1Data;
               // Execute the pre-test script
               // 
               System.Diagnostics.Trace.WriteLineIf((testActions.PretestAction != null), "Executing pre-test script...");
               ExecutionResult[] pretestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PretestAction);
               // Execute the test script
               // 
               System.Diagnostics.Trace.WriteLineIf((testActions.TestAction != null), "Executing test script...");
               ExecutionResult[] testResults = TestService.Execute(this.ExecutionContext, this.PrivilegedContext, testActions.TestAction);
               // Execute the post-test script
               // 
               System.Diagnostics.Trace.WriteLineIf((testActions.PosttestAction != null), "Executing post-test script...");
               ExecutionResult[] posttestResults = TestService.Execute(this.PrivilegedContext, this.PrivilegedContext, testActions.PosttestAction);
   
           }
   

To start the Distributed Transaction Coordinator service

Some procedures in this topic use types in the System.Transactions assembly. Before you follow these procedures, you must make sure that the Distributed Transaction Coordinator service is running on the computer where you run the unit tests. Otherwise, the tests fail, and the following error message appears: "Test method ProjectName.TestName.MethodName threw exception: System.Data.SqlClient.SqlException: MSDTC on server 'ComputerName' is unavailable".

To start the Distributed Transaction Coordinator service

1. Open Control Panel.

2. In Control Panel, open Administrative Tools.

3. In Administrative Tools, open Services.

4. In the Services pane, right-click the Distributed Transaction Controller service, and click Start.

   The status of the service should update to Started. You should now be able to run unit tests that use System.Transactions.

See Also

Creating and Defining SQL Server Unit Tests