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" <(E-Mail Removed)> schreef in bericht
news:F3D3F95C-129A-4241-BF83-(E-Mail Removed)...
>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]" <(E-Mail Removed)> schreef in bericht
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> schreef in bericht
> news:F3D3F95C-129A-4241-BF83-(E-Mail Removed)...
>>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" <(E-Mail Removed)> schreef in bericht
> news:F3D3F95C-129A-4241-BF83-(E-Mail Removed)...
> >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" <(E-Mail Removed)> schreef in bericht
> > news:F3D3F95C-129A-4241-BF83-(E-Mail Removed)...
> > >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" <(E-Mail Removed)> schreef in bericht
> > > news:F3D3F95C-129A-4241-BF83-(E-Mail Removed)...
> > > >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" <(E-Mail Removed)> schreef in bericht
news:935E3EE6-121B-42D5-8B4C-(E-Mail Removed)...
> 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" <(E-Mail Removed)> schreef in bericht
>> > > news:F3D3F95C-129A-4241-BF83-(E-Mail Removed)...
>> > > >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" <(E-Mail Removed)> wrote in message
news:52B80EB8-0B38-4857-AAC6-(E-Mail Removed)...
> 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" <(E-Mail Removed)> schreef in bericht
>> news:F3D3F95C-129A-4241-BF83-(E-Mail Removed)...
>> >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?
>> >
>> >
>>
>>