DataReader is connected?

[Ray Alirezaei]

I know that Datareader is connected (despite Dataset and DataView) ,but my
question is that when datareader is reading rows from datasource using read
methid ,I pause the Sqlserver and it still works,why is that working???

 

[DB]

Results are returned as the query executes, and are stored in the network
buffer on the client until you request them using the Read method of the
DataReader

therefore you are reading from a buffer not from the sql server

David

 

[Sahil Malik]

I don't think that's correct. Why do I think so? Try this experiment.

Create datareader.
Read one result.
Pause/Stop Sql Server.
Keep reading from datareader to the end.
When you reach the end, call datareader.close
then without closing the connection, call datareader =
newcmd.ExecuteReader() (which means it must have gone back to Sql Server).
It still works.

.... Maybe Sql Server services any pending connections but disallows any new
ones.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Ray Alirezaei]

I don't think DataReader retrives all the data into Client Memory,because in
that case what would be the differnece between DataReader and Datset in
terms of Memory Consumption?

 

[Sahil Malik]

DataReader stores objects as much lighter System.Data.Common.DbDataRecord,
Datasets store XML pigs instead. (Well they aren't XML in memory, but still
you know what I mean).

But I agree, I don't think there's a prefetch going on, I believe my
insinuation is correct - try this, open a connection - get a datareader -
pause sql server, keep using data reader. close connection - then try
opening it again - see the error message it says.

"Sorry I can't accept new connections. I'm shuttin' down !!" (or something
similar).

I think I'm right, but I have no way to confirm without a Microsoftie
jumping in.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Patrice]

What is the size of the resultset ? IMO the result is buffered. It would
explain why it works for you. I would give this a try with a huge
resultset...

Patrice

 

[Sahil Malik]

I did .. I tried with a resultset of about 32,000 records. Same results.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Miha Markic [MVP C#]]

Hi Sahil,

I think you are right here.
Pause applies to sql server service (thus it won't accept new connections)
and not to active connections IMO.

 

[Sahil Malik]

Miha the funny thing was --- even STOP Sql Server showed the same behavior.

So I ran another quick test - I ran it on my laptop using the Sql Server of
my desktop and instead of politely "Stopping" the sql server, I shut down my
desktop .. LOL !! Then it indeed crapped out .. I think I'm right on this
one

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

Hi Sahil,

I think you are right here.
Pause applies to sql server service (thus it won't accept new connections)
and not to active connections IMO.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

I don't think that's correct. Why do I think so? Try this experiment.

Create datareader.
Read one result.
Pause/Stop Sql Server.
Keep reading from datareader to the end.
When you reach the end, call datareader.close
then without closing the connection, call datareader =
newcmd.ExecuteReader() (which means it must have gone back to Sql Server).
It still works.

... Maybe Sql Server services any pending connections but disallows any
new ones.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Patrice]

I didn't noticed you just paused SQL Server.

I gave this a try :
- the application pause at line 1 and I *stop* SQL Server
- resuming it fails later at row #506 with a "System Error"

It looks like rows are buffered...

Patrice

--

 

[Patrice]

Forgot to mention that given my record size it looks like the buffer size is
around 256 Kb.

Patrice

--

 

[Miha Markic [MVP C#]]

Miha the funny thing was --- even STOP Sql Server showed the same
behavior.

Yes, same as pause, the stop affects only the service.

So I ran another quick test - I ran it on my laptop using the Sql Server
of
my desktop and instead of politely "Stopping" the sql server, I shut down
my
desktop .. LOL !! Then it indeed crapped out .. I think I'm right on this
one

 

[Sahil Malik]

Can I see your table structure? My table structure had 2 columns - int and
int !! And I had around 32,000 rows.

I did stop the sql server too .. so at 32,000 * (int * 2) .. ~~ 256 KB .. so
I might *just* be under that limit.

I could give it a shot again.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Patrice]

I've got 6000 lignes with around 400 bytes per line. It should be easy to
use character fields to have some more data...

Patrice

--

 

[William \(Bill\) Vaughn]

Ah, no. Datasets do not store data as XML. Never have, never will. Data is
transformed to XML on demand.
As to the behavior of the DataReader, I expect that there are cached memory
data structures that return as many rows as possible into memory. Try
pulling the network wire (or disable the LAN connection) and see if that
stops the DataReader mid-read.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Sahil Malik]

You're right Bill, I should have used slightly different words than --
"Datasets store XML pigs instead. (Well they aren't XML in memory, but still
you know what I mean)."

Yes they are serialized to XML on demand - not stored as XML. Yup Yup !!

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[ALI-R]

So,,seems I raised very hot issue ,I didn't get my answer finally ,Is there
somebody who can explain this behaviour?

 

[Patrice]

To summarize :
- when you "pause" this is expected (the server keeps serving existing
connections)
- when you "stop" the server, it fails for me later as if the data reader
was using a 256 kb cache...

Patrice

--

 

[ALI-R]

Thanks for your help.
My question was ,,dose the datareader retrive all the data into
cache(network buffer on client) or only one record at the time??

How did you figue out your daraReader is using 256 kb cache?

Thanks again

 

[Patrice]

It likely uses a cache. As reported in this thread, I've done something
similar to :
- breakpoint at iteration 1
- stop SQL server
- clear breakpoint and continue
- it fails at line 506

The program above uses a DataReader to read a table with more than 6000 rows
with around 490 bytes per record if I remember. The line number it fails
multiplied by the record size gives a number close to 256 kb.

The DataReader looks really connected ;-)

Patrice

--