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.
Simulate nested transactions with
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:
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.
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
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; ...