Transactions with TransactionScope on Sql Compact Edition 3.5

Question

Hi,

I have question I'm developing small application to desktop in win Forms. I'm using SQL CE 3.5 SP1 for the database.

When I make inserts on few tables by few methods I'm using the TansactionScope. When I use this methods alone I want to use normal Transaction from database connection.

Is there anyway to check in called method if connction to DB is now in transaction scope??

The answer to forward this information to method it's not good in the future we will change the implementation of the SQL Ce to normal SQL and then I don't wan't change implementation so this is not good idea.

The solution before I was using in every mthod Transaction from ADO but it was wrong becouse outside was transactionScope which was not rollbacking this tran. SO I decided to delete all ADO transactions and then rollback was ok so I wan't to know when I can use ADO and when not...

Answer 1

I'm not sure if these will work in Compact Edition 3.5, but this what other editions of SQL Server use:

XACT_STATE() reports the transaction state of a session, indicating whether or not the session has an active transaction, and whether or not the transaction is capable of being committed. It returns three values:

• 1, The session has an active transaction. The session can perform any actions, including writing data and committing the transaction.
• 0, There is no transaction active for the session.
• -1, The session has an active transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The session cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The session cannot perform any write operations until it rolls back the transaction. The session can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the session can perform both read and write operations and can begin a new transaction.

@@TRANCOUNT Returns the number of active transactions for the current connection.

• 0, not in a transaction
• 1, in a transaction
• n, in a nested transaction

EDIT base don OP's comments Try this query:

SELECT
    *
    FROM sys.dm_tran_session_transactions
    WHERE session_id=@@SPID

or this to get more info:

SELECT
    *
    FROM sys.dm_tran_session_transactions             s
        INNER JOIN sys.dm_tran_active_transactions    a On s.transaction_id=a.transaction_id
    WHERE s.session_id=@@SPID