MS Enterprise Library DAAB Transactional Fun

In a recent project we went through the process of upgrading the MS Enterprise Library v2 to v3. We were eager to upgrade to v3 because in v2 whenever you made multiple database calls within a .NET transaction, the transaction was being promoted to a distributed transaction (even though the calls were to all to the same database.)

We realized this was happening when we enhanced some code by making it transactional and it starting failing on the production server. The error we were getting had to do with the fact the DTC (Distributed Transaction Coordinator) service was not running. We thought this was strange because even though there were multiple database calls within the transaction, all the calls were to the same database. So why would it need a distributed transaction?

We turned on the DTC service and all was fine but we still wanted to know why this was happening. It turns out that v2 of DAAB was creating multiple connections when making multiple database calls within a transaction – even if they were to the same database. As soon as more than one connection is created within a transaction, the transaction gets promoted. This makes sense from a transaction standpoint if the calls are to different databases.

I had read somewhere that v3 was going to fix this issue by reusing connections to the same database – preventing the promotion from occurring. So when it released we quickly replaced the v2 DLLs with v3. Everything built fine, so we knew at least the syntax for the calls we were making were identical. We thought it would be a smooth upgrade from here on in…BUT as we all know just because something builds does not mean it is going to run.

When we started to test the application, we came across a strange database error that we had not seen before.

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

After some poking around we found that there were several places we were iterating through IDataReaders and then calling code that also created a IDataReader. All of this reader creation code was running in the larger context of transaction created outside of the reader code.

So the issue of v2 creating multiple connections with in a transaction was actually allowing the reader within a reader iteration to function properly. But when v3 changed it so connections to the same database would re-use a single connection – it broke this code that ran fine originally in v2 when it wasn’t transactional and ran fine in v2 when it was transactional (as long as DTC was on).

So the solution we came up with was to convert the outer reader to a DataSet due to its disconnected nature (unlike the IDataReader).  This allowed the connection that was previously tied up by the outer reader to be reused to create the inner readers. Now it all works like a charm – no more transaction promotion and no more reader conflicts.

Now onto the next challenge…

- M