ADO.NET 3.5 Cookbook, 2nd Edition

7.3. Nesting Manual Transactions with the SQL Server .NET Data Provider

Problem

You need to create a nested transaction using the SQL Server .NET data provider, but the Begin() command that you need is only available with the OLEDB .NET data provider. The SQL Server data provider appears to provide no built-in support for nested transactions. You want to nest transactions when using it.

Solution

Simulate nested transactions with savepoints when using the SQL Server .NET data provider, manage and control the lifetime of the SqlTransaction class, and create the required exception handling.

The solution requires a table named NestManualTransaction in the AdoDotNet35Cookbook database. Execute the T-SQL code in Example 7-4 to create the table.

Example 7-4. Create table NestManualTransaction

USE AdoDotNet35Cookbook
GO
CREATE TABLE Nest ManualTransaction (
    Id int NOT NULL,
    Field1 nvarchar(50) NULL,
    Field2 nvarchar(50) NULL,
  CONSTRAINT PK_Nest ManualTransaction PRIMARY KEY CLUSTERED
    ( Id ASC )
)

The solution creates a parameterized insert command that inserts a single record into the NestManualTransaction table. A transaction is started and a record inserted with Id = 1. A savepoint named SavePoint1 is created. Next, an attempt is made to insert two records with Id = 2 and Id = null within a try-catch block. The insert of the Id = null record fails and the transaction is rolled back to SavePoint1 in a catch block and the transaction is committed. The contents of the NestManualTransaction table ...