DataReader and Transactions

[Bobby Smith]

Folks,
This is probably a question of best practice.

In many places in my code, i've started a transaction (using the
BeginTransaction() function on my connection object).
I then create a DataReader and start going through my while
(myDataReader.Read()) loop.
Inside of this loop, i need to execute more SQL statements.

I cannot do this on the same connection, because the DataReader is still
open, correct?
But then, to keep my transaction processing, i'd have to create a new
transaction on the new connection, and handle that separately from the
transaction i already created, correct?
And, the sql statements executed on the new connection (and in the new
transaction) would not reflect any data changes that had occurred on the
original connection/transaction, correct?

What is my best practice for handling these situations? Should i just be
doing my transaction starts/ends and connection open/closes in different
places so as to avoid this situation? Or is there something i'm completely
missing?

Any advice would be helpful,

Thanks in advance!

 

[David Browne]

Folks,
This is probably a question of best practice.

In many places in my code, i've started a transaction (using the
BeginTransaction() function on my connection object).
I then create a DataReader and start going through my while
(myDataReader.Read()) loop.
Inside of this loop, i need to execute more SQL statements.

I cannot do this on the same connection, because the DataReader is still
open, correct?
But then, to keep my transaction processing, i'd have to create a new
transaction on the new connection, and handle that separately from the
transaction i already created, correct?
And, the sql statements executed on the new connection (and in the new
transaction) would not reflect any data changes that had occurred on the
original connection/transaction, correct?

What is my best practice for handling these situations? Should i just be
doing my transaction starts/ends and connection open/closes in different
places so as to avoid this situation? Or is there something i'm completely
missing?

You should be using a DataTable instead of a DataReader. A DataReader is a
low-level object, and should not bubble up into your business logic for this
and other reasons.

David

 

[William \(Bill\) Vaughn]

I suggest neither. In this case I would attempt to make the changes on the
server.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

 

[Bobby Smith]

I must admit to not understanding either of the responses to my question.

I don't see any other way that this could be done...

I need to execute a SQL statement (an INSERT) one time for each record in a
subset of a table (the table is large, but in this case the subset is
usually only about 10 records or less, defined by a SELECT statement.).

The DataReader is the most efficient way to iterate through records, right?

Maybe i'm just totally missing something, but what other option do i have
here? This is ASP.NET code, so i don't quite understand what you meant by
"make the changes on the server." They are being done on the server.. (sorry
if i didn't mention that this was ASP.NET).

 

[David Browne]

I must admit to not understanding either of the responses to my question.

I don't see any other way that this could be done...

I need to execute a SQL statement (an INSERT) one time for each record in
a subset of a table (the table is large, but in this case the subset is
usually only about 10 records or less, defined by a SELECT statement.).

The DataReader is the most efficient way to iterate through records,
right?

Maybe i'm just totally missing something, but what other option do i have
here? This is ASP.NET code, so i don't quite understand what you meant by
"make the changes on the server."

On the databsae server. Your web server is a client computer as far as the
databse is concerned

It's best not to iterate rows at all. Relational database servers are
designed to process sets of rows and if you can code your transaction as a
single INSERT or UPDATE statement you will get vastly better performance.

For instance if you need to insert one row into T1 for each row in a subset
of a table T2 you might code a query like

INSERT INTO T1(A,B,C)
SELECT X,Y,Z
FROM T2
WHERE W = 14

David