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?

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


"Peter"  schreef in bericht 
news:F3D3F95C-129A-4241-BF83-9706062B1D30@microsoft.com...

>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?
>
> 

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

"Cor Ligthert[MVP]"  schreef in bericht 
news:%23iTGVEK2HHA.4880@TK2MSFTNGP03.phx.gbl...

> 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
>
>
> "Peter"  schreef in bericht 
> news:F3D3F95C-129A-4241-BF83-9706062B1D30@microsoft.com...
>>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?
>>
>>
> 

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

"Cor Ligthert[MVP]" wrote:


> 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
> 
> 
> "Peter"  schreef in bericht 
> news:F3D3F95C-129A-4241-BF83-9706062B1D30@microsoft.com...
> >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?
> >
> > 
> 
> 

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


"Peter" wrote:


> 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
> 
> "Cor Ligthert[MVP]" wrote:
> 
> > 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
> > 
> > 
> > "Peter"  schreef in bericht 
> > news:F3D3F95C-129A-4241-BF83-9706062B1D30@microsoft.com...
> > >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?
> > >
> > > 
> > 
> > 

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

"Kerry Moorman" wrote:


> 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
> 
> 
> "Peter" wrote:
> 
> > 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
> > 
> > "Cor Ligthert[MVP]" wrote:
> > 
> > > 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
> > > 
> > > 
> > > "Peter"  schreef in bericht 
> > > news:F3D3F95C-129A-4241-BF83-9706062B1D30@microsoft.com...
> > > >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?
> > > >
> > > > 
> > > 
> > > 

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

"Peter"  schreef in bericht 
news:935E3EE6-121B-42D5-8B4C-BFA4E002CA2B@microsoft.com...

> 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
>
> "Kerry Moorman" wrote:
>
>> 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
>>
>>
>> "Peter" wrote:
>>
>> > 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
>> >
>> > "Cor Ligthert[MVP]" wrote:
>> >
>> > > 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
>> > >
>> > >
>> > > "Peter"  schreef in bericht
>> > > news:F3D3F95C-129A-4241-BF83-9706062B1D30@microsoft.com...
>> > > >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?
>> > > >
>> > > >
>> > >
>> > > 

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




"Peter"  wrote in message 
news:52B80EB8-0B38-4857-AAC6-58BD6E4A0925@microsoft.com...

> 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
>
> "Cor Ligthert[MVP]" wrote:
>
>> 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
>>
>>
>> "Peter"  schreef in bericht
>> news:F3D3F95C-129A-4241-BF83-9706062B1D30@microsoft.com...
>> >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?
>> >
>> >
>>
>>