VistaDB only supports a single open transaction (no nested transactions are supported). It is therefore very useful to be able to determine how many open transactions are present prior to creating one, or in an error handling loop.

Open Transactions in DDA

Given a VistaDB Database like this( IVistaDBDatabase db ) you can query the NestedTransactionLevel property to determine how many transactions are open.

        
   if( db.NestedTransactionLevel > 0 )
   {
      db.RollbackTransaction();
   } 

Open Transactions in SQL

In a SQL Proc you may want to check for transactions using the @@TRANCOUNT system variable.

The query will return 0 for none, or a positive number (only 1 is valid at this time). If no database is open or if the database is in an invalid state -1 will be returned to represent an error condition.

SELECT @@TRANCOUNT; 

A stored procedure capable of handling an exception in a TSQL Proc that contains transactions should also check the TRANCOUNT and rollback if it is found to be greater than 0.

BEGIN TRY
     { sql_statement | statement_block }
END TRY
BEGIN CATCH
-- Do we have a transaction to rollback?
IF @@TRANCOUNT > 0
BEGIN
          ROLLBACK TRANSACTION;
END
END CATCH