I have been working on some logic in a C# application that uses transactions, trying to prevent them from escalating to the Microsoft Distributed Transaction Coordinator (MSDTC). Apparently, if you have two DML operations (inserts, updates, deletes) within the same TransactionScope, then the MSDTC gets involved if they use different connections, even if the connection strings are identical.

The solution is to make sure all the SQL within the scope uses the same connection, which I was able to accomplish. However, I then ran into a different problem. I had selected a set of rows and was looping through them using a SqlDataReader, and for each row I needed to fetch some details rows (actually in several levels) using a different SqlDataReader and a different SqlCommand. Before my logic that made everything use the same connection, that worked just fine. But after the change, I got an exception saying:

System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first

The problem is actually that there is another command on the same connection using a different SqlDataReader (not another command with the same SqlDataReader, that is not the problem). This is not allowed by default. The solution turned out to be very easy. You can allow something called MARS (Multiple Active Result Sets). Once that is turned on, you are allowed to have different SqlDataReaders processing different results sets on the same connection.

To turn it on, just add this to your connection string:

MultipleActiveResultSets=True

That is all it takes! See here for a good article on the topic.