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 :

Code:
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.