CODEWITHSUNDEEP
c#ado.net
user441521
user441521

Reputation: 6998

Committing a transaction while inside a datareader loop

I'm looping inside a reader and updating a different table the reader is reading from. Every 1000 loops I want to commit() the transation but it's giving the error:

The transaction operation cannot be performed because there are pending requests working on this transaction.

I've seen 2 other answers and one of them said close the reader but we obviously can't do that as we're using that data from the reader to run an update statement and update another table.

So the question is, if you create a transaction on a connection, then you have a datareader looping over some sql command, how can I, within the datareader loop, run an update command and commit() the transaction inside the datareader loop?

Upvotes: 1

Views: 1360

Answers (1)

Cee McSharpface
Cee McSharpface

Reputation: 8725

You have two options to achieve this:

1) Open a second connection and use it for the transactional updates.

2) Use the "multiple active resultsets" option of SQL Server:

If two batches are submitted under a MARS connection, one of them containing a SELECT statement, the other containing a DML statement, the DML can begin execution within execution of the SELECT statement.

Source: https://msdn.microsoft.com/en-us/library/h32h3abf(v=vs.110).aspx

Upvotes: 2

Related Questions