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

Question

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

I am using Microsoft SQL Server Database with ADO.NET. I have seen examples using T-SQL and examples starting transactions using begin and using transaction names. When calling connection.BeginTransaction, I call another function in the same connection, and it calls BeginTransaction again which gives me the exception:

SqlConnection does not support parallel transactions.

It appears many Microsoft variants allow this, but I can't figure out how to do it with my .mdf file.

How do I allow nested transactions with a Microsoft SQL Server Database using C# and ADO.NET?

Answer 1

SQL Server as a whole does not support nested transactions. In T-SQL you can issue a BEGIN TRAN inside an earlier BEGIN TRAN but this is just for convenience. It's only the outer transaction that counts. The .NET client for SQL Server (SqlConnection) does not even allow you to do that and throws this exception when you try.