C# While reading data with DataReader cannot update same record

Question

i have a problem while reading data with DataReader cannot update same record with same connection. Its getting timeout.

Here is my code listed below:

mycon = new DBManager(DataProvider.SqlServer,ConnectionStr);

mycon.Open();

while (mycon.DataReader.Read())
{
    Id = mycon.DataReader["ID"].ToString(); 

    sql.Length = 0; 

    sql.Append("Update [Table1] Set [Name] = XXX Where [ID] = " + Id + "");

    mycon.ExecuteNonQuery(CommandType.Text, sql.ToString());
}

and my connection settings are

Connect Timeout=30;MultipleActiveResultSets=true;pooling=yes

already fixed that problem using DataTable and looping on it. not cause any problem but i want to use datareader. Anyone can help me about that.

Take a moment to read through the editing help in the help center. Formatting on Stack Overflow is different than on other sites. The better your post looks, the easier it is for others to read and understand it. — gunr2171
– gunr2171, Commented Nov 19, 2021 at 13:04
Probably your answer is here: stackoverflow.com/questions/1538661/… — Steve
– Steve, Commented Nov 19, 2021 at 13:09
Why would you want do this? You can just issue a single joined UPDATE command to do the whole thing in bulk. Also, you should not be caching DataReader, you need to dispose it as soon as you're done. I don't care what DBManager does, it's wrong to hold these objects in a cache — Charlieface
– Charlieface, Commented Nov 19, 2021 at 15:25
@Charlieface my purpose is avoiding any extra usage of memory. its a quartz application which is run every 8 second so time is valued. if there was a 1 row could be sense to dispose. But MultipleActiveResultSets=true gives me a oportunity the do multiple commands execute. Anyway as Steve's link says query is locking till end up datareader. — ferhat
– ferhat, Commented Nov 22, 2021 at 12:00

Sarin · Accepted Answer · 2021-11-19 14:12:41Z

0

While a DataReader is open, the Connection is in use exclusively by that DataReader. You cannot execute any commands for the Connection

Reference

You can either use a different connection for the update or collect the ids in a List and perform the update after the reader is closed.

answered Nov 19, 2021 at 14:12

Sarin

1,30511 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Over a year ago

Not quite correct. If the connection string contains the key/value pair MultipleActiveResultSets=true (A.K.A. MARS) the connection (at least an Sql Server Connection) could execute another command, and the OP has shown its connectionstring contains MARS

Collectives™ on Stack Overflow

C# While reading data with DataReader cannot update same record

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related