Search code examples
sqlsql-server

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 prototype).

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

Solution

  • 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.