DataReader and DataAdapter

[Guest]

I want to get a better understanding in using DataReader and DataAdapter to
retrieve data from MS SQL Server 2005. I'm using visual basic 2005 and .net
3.0.

Is there any difference in retrieving data in MS SQL Server using DataReader
and DataAdapter? I used to SQL Server Profiler to trace both and it seems
they are the same.

Is there any difference in network traffic? My guess is none.

If I understand correctly, the data retrieved using DataReader will be
stored in client's network buffer and the data retrieved using DataAdapter
will be stored in client's memory. If I'm retrieving a large resultset,
will the size of client's network buffer be a potential problem? How can I
find out the size of the buffer?

Ways in obtaining data from the resultset of DataReader:
1. Use the Read method of the DataReader
2. Load the resultset to a dataset using DataSet.Load method

Ways in obtaining data from the resultset of DataAdapter:
1. Use the Fill method of the DataAdapter to populate a dataset

Are there other ways to populate dataset from resultset of DataReader or
DataAdapter?

 

[Cor Ligthert[MVP]]

Peter,

The dataadapter uses internal the datareader, the big difference as you use
it, is that a datareader is reading row by row, while the dataadapter
stores the rows in a datatable (which can be in a dataset).

Especially when you need any way to update the data than the dataadapter is
very much preferable, however this can be as well if you use WindowForms
complex datacontrols as by instance the combobox and the datagridview.

AFAIK can the datareader only index the resultset by indexing the fields by
their appearance, the datatable can do that also by the given field names or
columns.

Cor

 

[Cor Ligthert[MVP]]

Hi Peter,

Doh,
Sorry, a datareader can of course as well read by item as string name.
The rest of the message stays however the same.

Cor

 

[Guest]

Hi Cor,

Thanks for your replies. One area that I'm still confused is the storage
of the resultset. Before I execute the Fill method of DataAdapter or the
Read method of DataReader, is the resultset stored in network buffer or RAM
or both?

Peter

 

[Guest]

Peter,

Rows are returned from the server to the client in packets, one packet at a
time.

The packet is stored in the client's network buffer.

When your application's datareader's Read method is called, one row of data
from the packet in the network buffer is loaded into the application's memory
and processed.

When Read is called, if a row of data is not available in the packet in the
network buffer, another packet is retrieved from the server.

The same process takes place with a dataadapter, but in this case the
dataadapter loads each row from a datareader into a datatable, which of
course is in the application's memory. This results in all of the returned
rows being in the application's memory, in the datatable.

Kerry Moorman

 

[Guest]

Hi Kerry,

Thanks. This is exactly what I'm trying to find out. So, if I'm retreiving
same amount of data using DataReader or DataAdapter, network traffic (# of
trips to server, packet size, etc) and # of trips to network buffer should be
the same. The difference will be the loading from DataReader to DataTable
and usage of the application's memory.


Peter

 

[Cor Ligthert[MVP]]

Peter,

Exact, however if you need all the data, then it will always be stored in
memory in someplace, whatever it is. By instance if you are using Ajax and
seperate rows in your ASPNET application than the datareader can maybe save
some time. However if you don't use methods as like Ajax then the data has
first to be collected.

Cor

 

[Jay Balapa]

Peter,
Before I execute the Fill method of DataAdapter or the

Read method of DataReader, is the resultset stored in network buffer or
RAM
or both?

When you are using DataReader you are only getting pointer.
When you do a read () you are actually reading the record.

When you do Fill you are reading the record row by row, creating the
datatable and the underlying dataset.

It will be in stored in Asp.Net Worker Processes Virtual Memory and will
not be in NetworkBuffer until you bind to a dataset or try to send it
through the Wire.


--
Jay Balapa
http://jbalapa.blogspot.com