Convert Data Reader into DataSet

[Guest]

Environment: VB.Net, SQL Server 2000.

I've used both data readers and also datasets with tables in them. I would
like to use a data reader (because of its speed) to retrieve using a stored
procedure with TWO selects in the stored proc (two result sets), then go thru
the data reader and put both result sets into a DataSet as separate tables
(table names). This will save me having to connect to the database twice and
call two stored procs.

Does anybody have an example of how to put a data reader into a data set as
a table(s)?

 

[Guest]

Chris,

About 10 posts down: How to convert a datareader to datatable? Has at least
8 responses.

Kerry Moorman

 

[W.G. Ryan - MVP]

Chris - if your db supports batch queries, just batch them up, specify Table
and column mappings and let the adapter fill them.

Technically a dataadapter uses DataReaders to fill tables so an Adapter
can't be faster than a reader. However to code the conversion, you need to
check for DB null, infer the schema or know it beforehand , strongly type
the gets and everything else, all of which is a lot of work for a
performance gain that may not even be realized. Yes, depending on how you
code the thing, you could come up with slower code using a reader than you
otherwise would (use nominal lookups for instance and you'll shoot all the
performance benefit right away). Also, you are taking the data from a
reader and storing it locally anyway, so in the scenario you describe, I
doubt you'll realize much performance benefit if any at all.

If you're using the 2.0 Framework you can use dataTable.Load and pass in the
reader. Otherwise you'll need to do
do{
while(dr.Read()){
DataRow dro = myDataTable.NewRow();
dro[0] = dr.Getxxx(0);
dro[1] = dr.Getxxx(1);
}
}while (dr.NextResult());

 

[Guest]

Well let me ask this question. I wite with n-tier components and the reason I
want to get a dataset to a data reader is that I don't want to marshall and
pass the data reader from a component (class that is instantiated) back to
the aspx page. I know in classic asp this would take a performance hit. So I
guess my question is, can datareaders be passed back from a data set with out
a performance hit? I know they are cursors connected to the database until
you close the, so won't I get a big performance hit if I marshall readers in
memory?

--
Chris Davoli

Chris - if your db supports batch queries, just batch them up, specify Table
and column mappings and let the adapter fill them.

Technically a dataadapter uses DataReaders to fill tables so an Adapter
can't be faster than a reader. However to code the conversion, you need to
check for DB null, infer the schema or know it beforehand , strongly type
the gets and everything else, all of which is a lot of work for a
performance gain that may not even be realized. Yes, depending on how you
code the thing, you could come up with slower code using a reader than you
otherwise would (use nominal lookups for instance and you'll shoot all the
performance benefit right away). Also, you are taking the data from a
reader and storing it locally anyway, so in the scenario you describe, I
doubt you'll realize much performance benefit if any at all.

If you're using the 2.0 Framework you can use dataTable.Load and pass in the
reader. Otherwise you'll need to do
do{
while(dr.Read()){
DataRow dro = myDataTable.NewRow();
dro[0] = dr.Getxxx(0);
dro[1] = dr.Getxxx(1);
}
}while (dr.NextResult());

 

[William \(Bill\) Vaughn]

Yes, a Datareader is a connected stream--it can't be serialized or "sent"
anywhere. It would be like sending a firehose to someone in the mail.
Without the (live) connection to the data it's not of much use.
Yes, saving a lot of data in memory can be expensive but saving a reasonable
amount can be faster than refetching it from the database (requery). You
have to figure out a balance that works for your site. Some sites can't
afford to save much of anything (due to volume), but others can save quite a
bit as they don't support more than a few dozen clients at a time. I would
try the DataTable approach (which is far easier to do in the 2.0 Framework)
and see if the performance hit you're worried about really manifests itself.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________

Well let me ask this question. I wite with n-tier components and the
reason I
want to get a dataset to a data reader is that I don't want to marshall
and
pass the data reader from a component (class that is instantiated) back to
the aspx page. I know in classic asp this would take a performance hit. So
I
guess my question is, can datareaders be passed back from a data set with
out
a performance hit? I know they are cursors connected to the database until
you close the, so won't I get a big performance hit if I marshall readers
in
memory?

--
Chris Davoli

Chris - if your db supports batch queries, just batch them up, specify
Table
and column mappings and let the adapter fill them.

Technically a dataadapter uses DataReaders to fill tables so an Adapter
can't be faster than a reader. However to code the conversion, you need
to
check for DB null, infer the schema or know it beforehand , strongly type
the gets and everything else, all of which is a lot of work for a
performance gain that may not even be realized. Yes, depending on how
you
code the thing, you could come up with slower code using a reader than
you
otherwise would (use nominal lookups for instance and you'll shoot all
the
performance benefit right away). Also, you are taking the data from a
reader and storing it locally anyway, so in the scenario you describe, I
doubt you'll realize much performance benefit if any at all.

If you're using the 2.0 Framework you can use dataTable.Load and pass in
the
reader. Otherwise you'll need to do
do{
while(dr.Read()){
DataRow dro = myDataTable.NewRow();
dro[0] = dr.Getxxx(0);
dro[1] = dr.Getxxx(1);
}
}while (dr.NextResult());

Environment: VB.Net, SQL Server 2000.

I've used both data readers and also datasets with tables in them. I
would
like to use a data reader (because of its speed) to retrieve using a
stored
procedure with TWO selects in the stored proc (two result sets), then
go
thru
the data reader and put both result sets into a DataSet as separate
tables
(table names). This will save me having to connect to the database
twice
and
call two stored procs.

Does anybody have an example of how to put a data reader into a data
set
as
a table(s)?

 

[W.G. Ryan - MVP]

You don't want to Marshall a reader back and forth between teirs mainly b/c
there's no way to necessarily do it. In order to Remote an object you need
to serialize it and that can't be done with DataReaders. In the early days,
I used to be a 'performance' nut and only used dataReaders. But I found out
the hard way that you can easily write code using a dataReader that's slower
than using a DataAdapter. And when you factor in all the stuff you have to
do to maintain the performance edge, coupled with maintainability, there's
not a lot of reason to use readers if performance is your primary objective.
within the context you describe, I'd recommend using a datatable/Dataset and
just use an adapter to fill them - all in all I think it's your best bet.

Well let me ask this question. I wite with n-tier components and the
reason I
want to get a dataset to a data reader is that I don't want to marshall
and
pass the data reader from a component (class that is instantiated) back to
the aspx page. I know in classic asp this would take a performance hit. So
I
guess my question is, can datareaders be passed back from a data set with
out
a performance hit? I know they are cursors connected to the database until
you close the, so won't I get a big performance hit if I marshall readers
in
memory?

--
Chris Davoli

Chris - if your db supports batch queries, just batch them up, specify
Table
and column mappings and let the adapter fill them.

Technically a dataadapter uses DataReaders to fill tables so an Adapter
can't be faster than a reader. However to code the conversion, you need
to
check for DB null, infer the schema or know it beforehand , strongly type
the gets and everything else, all of which is a lot of work for a
performance gain that may not even be realized. Yes, depending on how
you
code the thing, you could come up with slower code using a reader than
you
otherwise would (use nominal lookups for instance and you'll shoot all
the
performance benefit right away). Also, you are taking the data from a
reader and storing it locally anyway, so in the scenario you describe, I
doubt you'll realize much performance benefit if any at all.

If you're using the 2.0 Framework you can use dataTable.Load and pass in
the
reader. Otherwise you'll need to do
do{
while(dr.Read()){
DataRow dro = myDataTable.NewRow();
dro[0] = dr.Getxxx(0);
dro[1] = dr.Getxxx(1);
}
}while (dr.NextResult());

Environment: VB.Net, SQL Server 2000.

I've used both data readers and also datasets with tables in them. I
would
like to use a data reader (because of its speed) to retrieve using a
stored
procedure with TWO selects in the stored proc (two result sets), then
go
thru
the data reader and put both result sets into a DataSet as separate
tables
(table names). This will save me having to connect to the database
twice
and
call two stored procs.

Does anybody have an example of how to put a data reader into a data
set
as
a table(s)?