0

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.

Peter Csala
  • 17,736
  • 16
  • 35
  • 75
ferhat
  • 1
  • 4
  • Take a moment to read through the [editing help](//stackoverflow.com/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 Nov 19 '21 at 13:04
  • Probably your answer is here: https://stackoverflow.com/questions/1538661/datareader-behaviour-with-sql-server-locking – Steve Nov 19 '21 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 Nov 19 '21 at 15:25
  • 1
    @Steve Yeah its explain to problem thank you. – ferhat Nov 22 '21 at 08:28
  • @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 Nov 22 '21 at 12:00
  • If time is valued then you should certainly choose an efficient implementation. There is no need to read data and then execute individual updates, you can just do something like `Update t! Set [Name] = 'XXX' FROM [Table1] t1 JOIN OtherQuery q ON q.ID = t1.ID`. What is your outer query, then I can give you a proper answer? And you *always* need to dispose Sql objects. Holding them in a cache is likely to cause memory leaks and hold connections open for too long. – Charlieface Nov 22 '21 at 12:05
  • Outer query gives me multiple row. Rest of the codes does multiple tasks like posting somewhere and arithmetic logic and has to be done 1 by 1. I just want to avoid casting any object(like datatable,list,arry etc..) but seems like its not posible like that way. – ferhat Nov 22 '21 at 12:37

1 Answers1

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.

Sarin
  • 1,255
  • 11
  • 14
  • Not quite correct. If the connection string contains the key/value pair [_MultipleActiveResultSets=true_](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-multiple-active-result-sets) (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 – Steve Nov 22 '21 at 09:38