How to enable nested transactions with ADO.NET and SQL Server?

Question

I have a similar question how to check if you are in a transaction . Instead of checking how to allow nested transactions?

I am using a Microsoft SQL Server database with ADO.NET. I saw examples using T-SQL and examples of starting transactions using begin and using transaction names. When calling connection.BeginTransaction, I call another function on the same connection and call BeginTransaction again, which gives me an exception:

SqlConnection does not support parallel transactions. 

Many Microsoft options seem to allow this, but I can't figure out how to do this with my .mdf file.

How to allow nested transactions with Microsoft SQL Server database using C # and ADO.NET?

Answer 1

SQL Server generally does not support nested transactions. In T-SQL, you can release a BEGIN TRAN inside an earlier BEGIN TRAN , but this is just for convenience. This is only an external transaction that matters. The .NET client for SQL Server ( SqlConnection ) does not even allow you to do this and throws this exception when you try.

Answer 2

It is a common misconception that SQL Server supports nested transactions. Is not. by opening multiple transactions and then calling commit does absolutely nothing. you can easily write some SQL test to try it yourself. The only option here to emulate a nested transaction is to use Savepoints.

I must add that the only thing that matters is when @@ TRAN_COUNT reaches zero - this is the point at which only the external transaction will be committed.

Answer 3

 SqlConnection conn = new SqlConnection(@"Data Source=test;Initial Catalog=test;User ID=usr;Password=pass"); conn.Open(); var com = conn.CreateCommand(); com.CommandText = "BEGIN TRANSACTION"; com.ExecuteNonQuery(); com.CommandText = "BEGIN TRANSACTION"; com.ExecuteNonQuery(); com.CommandText = "INSERT INTO testTable (ParamName,ParamValue) values ('test','test');"; com.ExecuteNonQuery(); com.CommandText = "COMMIT TRANSACTION"; com.ExecuteNonQuery(); com.CommandText = "ROlLBACK TRANSACTION"; com.ExecuteNonQuery(); com.CommandText = "SELECT COUNT(*) FROM testTable "; MessageBox.Show(string.Format("Found {0} rows.", com.ExecuteScalar()));