B
BLUE
I've a TransactionScope in which I select some data and I want to do some
queries for each record retrieved with my select.
I'm using a DataReader and for each record I do factory.CreateCommand() and
then I execute the command, but I get the following exception message:
" There is already an open DataReader associated with this Command which
must be closed first. "
Searching on google I've found I've to create another connection, but when I
open the second connection I get this exception message:
" Failure while attempting to promote transaction "
I've found this explanation on the internet.
When the connection enlists during the Open call, since there are no other
DB transactions associated with the System transaction, the connection
becomes the root and tries to handle the work as a local transaction (in
System.Transactions parlance, this is a Lightweight Transaction).
When the second connection enlists, the first transaction must be promoted
to DTC, but the connection is busy with the reader, so fails.
To work around the issue, either ensure the initial transaction is promoted
to a DTC transaction up front, or make sure the initial connection can talk
to the server when the transaction does get promoted.
I've opened both the connections just before doing anything so nowit works.
It would be better to use a datatable to retrieve the records and then loop
on them (I do not know if a distributed transaction is very expensive or
not)?
Thanks,
Luigi.
queries for each record retrieved with my select.
I'm using a DataReader and for each record I do factory.CreateCommand() and
then I execute the command, but I get the following exception message:
" There is already an open DataReader associated with this Command which
must be closed first. "
Searching on google I've found I've to create another connection, but when I
open the second connection I get this exception message:
" Failure while attempting to promote transaction "
I've found this explanation on the internet.
When the connection enlists during the Open call, since there are no other
DB transactions associated with the System transaction, the connection
becomes the root and tries to handle the work as a local transaction (in
System.Transactions parlance, this is a Lightweight Transaction).
When the second connection enlists, the first transaction must be promoted
to DTC, but the connection is busy with the reader, so fails.
To work around the issue, either ensure the initial transaction is promoted
to a DTC transaction up front, or make sure the initial connection can talk
to the server when the transaction does get promoted.
I've opened both the connections just before doing anything so nowit works.
It would be better to use a datatable to retrieve the records and then loop
on them (I do not know if a distributed transaction is very expensive or
not)?
Thanks,
Luigi.