issue using datareader with transactions

[ChrisB]

Hello All:

I am attempting to write data access code that is transactional and seem to
be running into some issues.

A simplified version of the logic I am using is as follows:

1. Create connection object.
2. Create transaction object that references connection object.
3. Create and populate datareader using transactional object (call A).
4. While(datareader.Read)
{
Perform some action using datareader . . .

Make another call to the database using value from current record loaded
into datareader (call B - repeated once for each object in datareader).
Additional code here . . .
}
5. Commit transaction.

The issue is occuring during step 4. When I make the call to the database
in the while loop, I would like the call to be covered under the existing
transaction, therefore I am using the transaction object created earlier.
From what I understand, however, only one datareader may be associated with
a given connection at a time.

My dilemma is as follows:
1. If the data reader is closed before making call B, I will lose the data
stored in the reader from call A.
2. If a new data reader is created for call B, call B will no longer be
associated with the same connection/transaction as call A.

Is there a way to have calls A and B within the same transaction?

Thanks for any input!

Chris

 

[William Ryan]

Can you load the values you need from the first DataReader's read into an
ArrayList for example, or custom connection, then close the first
datareader. At that point, with the same connection, walk to
collection/arraylist/whateverdataholder and then fire the second queries.

 

[Kathleen Dollard]

Chris,

If you think of an array list or DataSet as a bucket and the
Connection/DataReader as the spigot and pipe, it may make more sense why
this won't work the way you're trying to do it.

William's appraoch is good - off load into in memory storage of some
flavor - or you can use COM transactions. It sounds like what you're doing
might be a candidate for DTS instead of pushing it through .NET.

Kathleen

 

[ChrisB]

Thanks for the advice William and Kathleen.

I ended up transferring my data to an ArrayList, which worked out well.

Unless I am overlooking something, I must say that it is surprising that it
is not possible to disassociate a datareader with a connection (allowing
creation of another datareader) without losing access to the information
that it contains - that would have made the solution much simpler.

Thanks again!

Chris

 

[William \(Bill\) Vaughn]

That's because a DataReader is not a pipe, it's a wire. That is, it's a data
pathway to the data being queued up on the data source for you to read. Once
you cut the wire, the cached data on the server is lost. In this version of
ADO only one operation is permitted on a connection. In the next version,
some providers (like SQL Server) will support more than one operation at a
time. It's really not a good idea to hold on to a DataReader anyway and
perform operations on the rows it's returning one at a time. If this is your
strategy, I suggest using a DataSet or better yet, server-side processing.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Kathleen Dollard]

Bill,

I use the analogy of a pipe. What's the difference between a pipe and a wire
in this context. In both cases, if you cut the wire or pipe, you don't get
any more good stuff. I use the pipe analogy simply because its easier to go
to the bucket analogy and works well for piping from one bucket or one pipe
into another when looking at the broader context of streams. So, I'm curious
as to why you don't think a DataReader is a pipe.

 

[Kathleen Dollard]

Chris,

Yes, and I'm convinced the fastest way to get data out of your DataReader
and get it shut is using GetValues. Of course then you have to work to get
it wrapped in a way that you're not embedding structural details throughout
your code.

 

[William \(Bill\) Vaughn]

A pipe holds water (data) and the valve controls its flow--a wire is simply
a connection to energy (data). Yes, if the pipe is broken the water (data)
flows out so it's really a matter of semantics.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________