DataReader

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I am not able to understand why a datareader needs a connection to the DB
all the time. Here is what I tried.
Sqlcommand cmd = ("select * from table1",con)
// where con is the connection object
1. DataReader dr = cmd.executereader();
2.while(dr.read()) { // do something //}
I have a break point in line 2. when i run my application and once it hits
the break point (i.e line 2) I opened query analyzer and deleted all the rows
in table1. Now, if i continue with the execution of the above program it was
still able to bring all the rows from table1. How does this happen. I even
tried putting commit after deleting the rows but still the datareader is
bringing back the rows. Can any one please explain me this.

Thanks
 
Ravi said:
Hi,
I am not able to understand why a datareader needs a connection to
the DB all the time. Here is what I tried.
Sqlcommand cmd = ("select * from table1",con)
// where con is the connection object
1. DataReader dr = cmd.executereader();
2.while(dr.read()) { // do something //}
I have a break point in line 2. when i run my application and once it
hits the break point (i.e line 2) I opened query analyzer and deleted
all the rows in table1. Now, if i continue with the execution of the
above program it was still able to bring all the rows from table1.
How does this happen. I even tried putting commit after deleting the
rows but still the datareader is bringing back the rows. Can any one
please explain me this.
Click to expand...

A datareader is a forward-only, read-only server side cursor
mechanism, so you can browse through a resultset on the client via the
datareader, and because the data is on the server, in a resultset
object, the connection has to be open, otherwise the data-reader can't
offer the browsing through the resultset, as the data itself is only
transported to the client when you read from the datareader.

Frans

--
 
Frans,

I think you're missing the point here:
Ravi makes a connection reads the first record. Then he deletes all the
records in de database. But still he can read all the records. So why does a
Datareader needs the connection if he doesn't use it to read the records?
 
Marinus said:
Frans,

I think you're missing the point here:
Ravi makes a connection reads the first record. Then he deletes all
the records in de database. But still he can read all the records. So
why does a Datareader needs the connection if he doesn't use it to
read the records?
Click to expand...

Because the resultset is not the table. Databases work like this: you
execute a select statement, the database will collect the data
in-memory and will offer it through the interface to a client, for
example SqlClient. A datareader looks at that in-memory (or if it's
very big, in a temptable, depends on the rdbms flavor) resultset. So
you can remove all the data from a table after a select, that's of no
influence on the resultset already read.

The data-reader is a pointer in that resultset in-memory. You move
through that resultset on the sever by using the datareader on the
client. That requires a connection, and the resultset is kept in memory
for a connection.

Frans


--
 
That was really helpful. I'll read more about "in-memory result set". Any
urls for how this happens in SQL Server.
Thanks
 
That was really interesting. Here the point is Datareader is reading
from the Server Side Inmemory data, not the client side inmemory like
Dataset.
Where can I find more details regarding this?

thanks and regards,
commander

Visit my blogs at : http://dotnetcapsule.blogspot.com
 
Commander said:
That was really interesting. Here the point is Datareader is reading
from the Server Side Inmemory data, not the client side inmemory like
Dataset.
Where can I find more details regarding this?
Click to expand...

Check "API Server Cursors" (among other things) in BOL :)

FB

--
 
Back
Top