Nested transactions + SQL ADO .NET Library

[david.rougier]

Hi everyone,

I am using tools from the System.Data.SqlClient namespace to manipulate a database managed by an instance of SQL Server. I am using a single connection open at the launch of my application and closed when the user quits the application. I am searching for a simple way to manage nested transactions programmatically in my context.

A "simple way" means a way as simple as the way I use in VB6 with the DAO library. Here is an example of how I manage nested transactions in VB6 :

Public Sub EnclosingSub()

	On Error GoTo DB_ERROR:

	' Start a transaction.
	BeginTrans
	
	EnclosedSub
	
	' Add rows in the DB which cause an error.
	
	' Commit the transaction.
	CommitTrans
	
DB_ERROR:

	' Rollback the transaction.
	Rollback
	
End Sub

Public Sub EnclosedSub()

On Error GoTo DB_ERROR:

	' Start a transaction.
	BeginTrans
	
	' Add rows in the DB with success.
	
	' Commit the transaction.
	CommitTrans
	
DB_ERROR:

	' Rollback the transaction.
	Rollback

End Sub

The database operations run with success in EnclosedSub but an error is caused by the DB operations in EnclosingSub. Logically, the transaction started from EnclosingSub is rolled back and the operations from EnclosedSub are cancelled.

I know that I can assign an SqlTransaction objet to an SqlCommand's Transaction property. But I do not know how to use the mechanism demonstrated in my example.

Thanks for your future help.

[jmcilhinney]

I am using a single connection open at the launch of my application and closed when the user quits the application.

First things first, don't do that. ADO.NET was designed specifically to not be used that way. You open a connection as late as possible, use it and then close it as early as possible.

As for nested transactions, they are not supported by SqlClient. They are supported in principle by OleDb, but actual support depends on the underlying OLE DB provider. The OLE DB provider for SQL Server does not support nested transactions.

When it comes to manipulating transactions beyond the normal begin, commit and rollback, you need to look at specifying an appropriate isolation level and possibly saving the transaction state. This allows you to do things like partially rollback a transaction and decide whether queries during the transaction read the original data or uncommitted changes.