Nested Transactions with .NET

[Travis Foote]

Are Nested Transactions possible with ADO.NET? Ive read where Ole DB
supports nested transactions, but when I try to implement them I receive
the following error:

OleDbConnection does not support parallel transactions

Here is an example of the code:

public class DAC
{
public DAC()
{
}


private OleDbConnection conn;

public void CreateCompany()
{
conn= new OleDbConnection("Provider=SQLOLEDB;Data
Source=localhost;Initial Catalog=SMC; User ID=SMC_User; Password=smcus3r" );
System.Data.OleDb.OleDbCommand cmdCredit = new
OleDbCommand("create_company", conn );
cmdCredit.CommandType = CommandType.StoredProcedure;
cmdCredit.Parameters.Add( new System.Data.OleDb.OleDbParameter("@Name",
"Versacorp") );

try
{
conn.Open();
}
catch (Exception e)
{
throw e;
}
// Start a new transaction
using ( System.Data.OleDb.OleDbTransaction trans =
conn.BeginTransaction() )
{

// Associate the command object with the transaction
cmdCredit.Transaction = trans;

try
{
cmdCredit.ExecuteNonQuery();

this.InsertCompanyAddress(1);
trans.Commit();
}
catch( Exception ex )
{
// transaction failed
trans.Rollback();
// log exception details . . .
throw ex;
}

}
conn.Close();


}
public void InsertCompanyAddress(int CompanyID)
{
OleDbCommand cmdDebit = new OleDbCommand("insert_company_address", conn );
cmdDebit.CommandType = CommandType.StoredProcedure;
cmdDebit.Parameters.Add( new OleDbParameter("@CompanyID", CompanyID) );




using (OleDbTransaction trans = conn.BeginTransaction() )
{
cmdDebit.Transaction=trans;
try
{


cmdDebit.ExecuteNonQuery();
trans.Commit();
}
catch( Exception ex )
{
// transaction failed
trans.Rollback();
// log exception details . . .
throw ex;
}

}



}
}
}

Any ideas?

Thanks for the help,

T.

 

[Val Mazur]

Hi Travis,

It depends on provider's capabilities to support nested transactions, not on
ADO.NET. As I know OLEDB Provider for SQL Server does not support nesting.
What you could do to implement some sort of nested transactions is to use
BEGIN TRANSACTION and SAVE TRANSACTION SQL statements directly. In this
case you will control transactions using Transact-SQL statements.

 

[Travis Foote]

Thanks Val for the reply. This is disheartening information. I did
research the nested transaction capability of Transact-SQL statements
but prefer to handle this outside the DB. It looks like I will have to
roll my own . Thanks again for clearing this up for me. Now I can
stop my exhaustive search for the answer.