Check if a connection is in a transaction

I am getting a SqlConnection does not support parallel transactions. exception and this answer mentions its when a connection tries to open two transactions. This is exactly what i am doing. I thought nested transactions were ok (i was using sqlite for the pro开发者_C百科totype).

How do i check if the connection is already in a transaction? I am using Microsoft SQL Server Database File.

---

After some searching, I found this other Stack Overflow question. It turns out that you cannot nest transactions in ADO.NET. When you try, you probably end up starting two unrelated transactions, which gives the parallel transactions error.

To see if a connection is currently in a transaction, you could:

var com = yourConnection.CreateCommand();
com.CommandText = "select @@TRANCOUNT";
var trancount = com.ExecuteScalar();

This returns the number of nested transactions.

Note that you can nest transactions manually, without using the SqlTransaction object. For example:

var com = yourConnection.CreateCommand();
com.CommandText = "BEGIN TRANSACTION";
com.ExecuteNonQuery();
com.CommandText = "BEGIN TRANSACTION";
com.ExecuteNonQuery();
com.CommandText = "INSERT INTO TestTable (name) values ('Joe');";
com.ExecuteNonQuery();
com.CommandText = "COMMIT TRANSACTION";
com.ExecuteNonQuery();
com.CommandText = "ROlLBACK TRANSACTION";
com.ExecuteNonQuery();

com.CommandText = "SELECT COUNT(*) FROM TestTable";
Console.WriteLine("Found {0} rows.", com.ExecuteScalar());

This prints 0, because the nested transaction was aborted entirely.

---

Are you doing this from multiple threads? If so, then asking won't help because between the time you ask and the time you begin a new transaction, some other thread could have begun its own transaction. You will want to use a connection pool to avoid this sort of race condition.

---

You can check whether a transaction is already open by checking if cmd.Transaction is null.

Then wrap your code accordingly so if a transaction is already open, then the calling function owns that transaction and will commit it / roll it back appropriately.

//begin transaction unless one was already started
bool newTransaction = cmd.Transaction == null;
if (newTransaction) cmd.Transaction = cmd.Connection.BeginTransaction();

try {
    // do Stuff Here
    cmd.ExecuteNonQuery();
    cmd.ExecuteNonQuery();

    // commit if it's our to commit
    if (newTransaction) cmd.Transaction.Commit();

} catch (SqlException ex) {
    if (newTransaction && cmd.Transaction != null) cmd.Transaction.Rollback();
    throw;
}

Then the parent function can pass in a command and optionally choose to begin it's own transactional block, or if not, one will be created and commited by the called function.