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

[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.

 

[Miha Markic]

Hi,

Or perhaps use Sql provider's transaction (via
SqlConnection.BeginTransaction/SqlTransaction.Commit/Rollback).
Distributed transactions are expensive...

 

[BLUE]

Or perhaps use Sql provider's transaction (via

SqlConnection.BeginTransaction/SqlTransaction.Commit/Rollback).

I'm using a provider indipendent DAL: I can use providers' transactions or
SQL transaction statements in the class that hide provider dependance.
Both te solution are provider dependent since DB2, MySQL, Postgre use START
TRANSACTION, SQL Server uses BEGIN TRANSACTION and Oracle starts a
transaction when the first query starts as SQL92 says).

I think I'll use providers' transactions but using them can I open two
connections under a single transaction?
That is if I do not use TransactionScope the transaction never promote to
distributed?


Thanks,
Luigi.

 

[Miha Markic]

Hi blue,

I'm using a provider indipendent DAL: I can use providers' transactions or
SQL transaction statements in the class that hide provider dependance.
Both te solution are provider dependent since DB2, MySQL, Postgre use
START TRANSACTION, SQL Server uses BEGIN TRANSACTION and Oracle starts a
transaction when the first query starts as SQL92 says).

I think I'll use providers' transactions but using them can I open two
connections under a single transaction?

Good point, you would have to use two transactions in such case (if this is
feasible for your operation at all).
However I was thinkg more like if you need just to read data and you don't
care about transaction in reading, you can avoid using transaction for
reading at all.

That is if I do not use TransactionScope the transaction never promote to
distributed?

Right. Unless there is some sort of external mechanism such as
TransactionScope.

Thinking about situation though, perhaps is the best to avoid using multiple
connections and use ado.net's transactions (to avoid using DTC) - they are
not provider specific and you can handle them using provider indpendent
code.

 

[BLUE]

You are right!

I'm doing a select on Table1 and then I have a loop on the datareader: for
each record I do many queryies to preform insert or update on 4 other tables
(some selects are involved of course).

Table1 is used only to insert records (each application insert a new record
so each concurrent access does not affect the others as if there was no
concurrency).

From time to time I need to update the 4 other tables with data from Table1
where Date < X (X = Now) and after insertion I'll delete the inserted
records.

I've thought I can do the select outside the transactionscope, but:
- if I loop on the datareader inside the transactionscope, a distributed
transaction occurs?
- when I have to delete inserted records I cannot do DELETE FROM Table1
WHERE Date < X since between te select and the entering in the transaction
scope, a concurrent access could have inserted a row that matches the where
clause.
I have to do a "DELETE INSERTED ROW" instead of a global delete right?


Thanks a lot,
Luigi.