DataReader

[Ravi]

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.executeread er();
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

[Frans Bouma [C# MVP]]

Re: DataReader

Ravi wrote:
[color=blue]
> 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.executeread er();
> 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.[/color]

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

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

[Marinus Holkema]

Re: DataReader

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?

"Frans Bouma [C# MVP]" wrote:
[color=blue]
> Ravi wrote:
>[color=green]
> > 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.executeread er();
> > 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.[/color]
>
> 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
>
> --
> ------------------------------------------------------------------------
> Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
> My .NET blog: http://weblogs.asp.net/fbouma
> Microsoft MVP (C#)
> ------------------------------------------------------------------------
>[/color]

[Frans Bouma [C# MVP]]

Re: DataReader

Marinus Holkema wrote:
[color=blue]
> 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?[/color]

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
[color=blue]
>
> "Frans Bouma [C# MVP]" wrote:
>[color=green]
> > Ravi wrote:
> >[color=darkred]
> > > 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.executeread er();
> > > 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.[/color]
> >
> > 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.[/color][/color]


--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

[Ravi]

Re: DataReader

That was really helpful. I'll read more about "in-memory result set". Any
urls for how this happens in SQL Server.
Thanks

"Frans Bouma [C# MVP]" wrote:
[color=blue]
> Marinus Holkema wrote:
>[color=green]
> > 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?[/color]
>
> 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
>[color=green]
> >
> > "Frans Bouma [C# MVP]" wrote:
> >[color=darkred]
> > > Ravi wrote:
> > >
> > > > 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.executeread er();
> > > > 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.
> > >
> > > 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.[/color][/color]
>
>
> --
> ------------------------------------------------------------------------
> Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
> My .NET blog: http://weblogs.asp.net/fbouma
> Microsoft MVP (C#)
> ------------------------------------------------------------------------
>[/color]

[Commander]

Re: DataReader

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

[Frans Bouma [C# MVP]]

Re: DataReader

Commander wrote:
[color=blue]
> 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?[/color]

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

FB

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------