how DataReader reads data while updation is done simultaneosly

Question

Hi
DataReader is reading the data and connection is still open. Assume some body else ,simultaneously ,  updated the same data. How the reader reads ,whether latest data  or old data. I am asking this question because, datareader is connected object and connection is open still it completes the reading.

Answer 1

As I understand it, the query is excuted on the server, then the results are returned row by row. So since the query is executed, any changed made after the SqlCommand is excecuted is not picked up by the DataReader. A simple test seems to prove this:

create table ReaderTest(id int identity, txt nvarchar(50))
insert into ReaderTest values ('xxx')
insert into ReaderTest values ('xxx')
insert into ReaderTest values ('xxx')
insert into ReaderTest values ('xxx')
insert into ReaderTest values ('xxx')

            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "select * from ReaderTest";
                SqlDataReader rdr = cmd.ExecuteReader();
                
                while (rdr.Read())
                {
                    if ((int)rdr[0] == 3)
                    {
                        Console.WriteLine("Make change in database: UPDATE ReaderTest SET txt = 'aaa' WHERE id = 5");
                        Console.ReadKey();
                    }
                    Console.WriteLine("{0}, {1}", rdr[0].ToString(), rdr[1].ToString());
                }
                con.Close();
            }

This will still return 'xxx' for all 5 rows.

HTH
//Michael

---

This posting is provided "AS IS" with no warranties.

Answer 2

Hi Michael
Thanks for the reply

Answer 3

Hi Michael
You are right.Datareader is fetching the old data.