ADO.NET Cookbook

6.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 OLE DB .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 sample code contains two event handlers:

Form.Load

Sets up the sample by filling a DataTable with the Categories table from the Northwind sample database. The default view of the table is bound to a data grid on the form.

Insert Button.Click

Inserts user-entered data for two Categories records into the Northwind database within a manual transaction. A savepoint is created if the first record insert succeeds. If the insert of the second record fails, the transaction is rolled back to the savepoint and the first record insert is committed; otherwise, both record inserts are committed.

The C# code is shown in Example 6-4.

Example 6-4. File: NestedManualTransactionForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; private const String CATEGORIES_TABLE = "Categories"; private DataTable dt; ...