Reading data in to a DataSet

[Sheila Jones]

Following on from my previous post, I have another quick question (sorry!):

I want to retrieve the records into a dataset. Is it possible to use a
DataReader for this, or can it only be done with a DataAdapter? The reason
for asking is that I only need to read the data into the dataset, not update
it, so a DataAdapter might be more heavyweight than I need...

 

[Cor]

Hi Sheila,

It can, count for every column extra an avarages with the read of 1.4 rows
of extra code.

Internaly it will not change much because the dataadapter.fill will do the
same as your code.

However when you do that, than you can better read it in a datatable.

Just my thougth,

Cor

 

[Chuck]

Shelia,
This may turn out to be a red hearing, because bad advice is always the
easiest to find. But you can pop a Dataset Table from an ADO recordset. A
quick and simple snapshot population. The code below:

Dim MyAdapter As New Data.OleDb.OleDbDataAdapter

Dim MyRec As New ADODB.Recordset

Dim MyCon As New ADODB.Connection

Dim MyDataSet As New DataSet

MyCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data
Source=YourSrver"

If MyCon.State = 0 Then MyCon.Open()

MyRec.Open("Orders", MyCon)

MyAdapter.Fill(MyDataSet.Tables.Add, MyRec)

'Proof

DataGrid1.DataSource = MyDataSet.Tables(0)

 

[Sheila Jones]

Hi,

That's quite interesting, thanks. But I was wondering if it was possible to
fill the dataset without using a DataAdapter at all? I really only need a
'fetching' capability, not the additional updating capability that a
DataAdapter offers.

 

[William Ryan eMVP]

Yes, you can but whether or not you want to is another issue. What you'll
have to do is build and populate a datatable as you iterate through the
reader...behind the scenes, a datareader is used to fill a datatable anyway
when youuse dataadapter.fill.

Assuming you have a datatable built already

while (rdr.Read()){
dt.Rows.[0] = rdr.Getxxxx;
dt.Rows.[1] = rdr.Getxxx;
i++;
}

Do this for each column in the datareader, so if 6 fields were in your
select query, you'd have a dt.Rows[Value] for 0,1,2,3,4,5. Also, you can
do a Select COUNT(*) from the onset so you know how many rows to add and
then just populate them, but this is another query. The other option is to
include
DataRow dro = dt.NewRow();
//make sure you use new ..then just add to the row

 

[Sheila Jones]

No, I don't fancy doing that! I just thought there might be a quck/easy way
to fill a DataSet/DataTable straight from a DataReader - similar to binding
a DataReader to a Listbox, for example.

Yes, you can but whether or not you want to is another issue. What you'll
have to do is build and populate a datatable as you iterate through the
reader...behind the scenes, a datareader is used to fill a datatable anyway
when youuse dataadapter.fill.

Assuming you have a datatable built already

while (rdr.Read()){
dt.Rows.[0] = rdr.Getxxxx;
dt.Rows.[1] = rdr.Getxxx;
i++;
}

Do this for each column in the datareader, so if 6 fields were in your
select query, you'd have a dt.Rows[Value] for 0,1,2,3,4,5. Also, you can
do a Select COUNT(*) from the onset so you know how many rows to add and
then just populate them, but this is another query. The other option is to
include
DataRow dro = dt.NewRow();
//make sure you use new ..then just add to the row

Following on from my previous post, I have another quick question (sorry!):

I want to retrieve the records into a dataset. Is it possible to use a
DataReader for this, or can it only be done with a DataAdapter? The reason
for asking is that I only need to read the data into the dataset, not update
it, so a DataAdapter might be more heavyweight than I need...

 

[William Ryan eMVP]

There's no native method for converting a DataReader to a Datatable as they
are fundamentally different objects....however, taking a reader and creating
a DataTable may look complex, but it's something that you can write a
method for one time in a utility class and use over and over since you can
retrieve the schema information from the reader or Information_Schema in SQL
Server and the Connection object in OleDb. It sounds a lot more complex
than it really is.

No, I don't fancy doing that! I just thought there might be a quck/easy way
to fill a DataSet/DataTable straight from a DataReader - similar to binding
a DataReader to a Listbox, for example.

Yes, you can but whether or not you want to is another issue. What you'll
have to do is build and populate a datatable as you iterate through the
reader...behind the scenes, a datareader is used to fill a datatable anyway
when youuse dataadapter.fill.

Assuming you have a datatable built already

while (rdr.Read()){
dt.Rows.[0] = rdr.Getxxxx;
dt.Rows.[1] = rdr.Getxxx;
i++;
}

Do this for each column in the datareader, so if 6 fields were in your
select query, you'd have a dt.Rows[Value] for 0,1,2,3,4,5. Also, you can
do a Select COUNT(*) from the onset so you know how many rows to add and
then just populate them, but this is another query. The other option

is

to
include
DataRow dro = dt.NewRow();
//make sure you use new ..then just add to the row

 

[Sheila Jones]

but it's something that you can write a method for one time in a utility
class

....or alternatively, derive my own data reader class from OleDbDataReader
and add Fill methods that take a DataSet or DataTable parameter?

There's no native method for converting a DataReader to a Datatable as they
are fundamentally different objects....however, taking a reader and creating
a DataTable may look complex, but it's something that you can write a
method for one time in a utility class and use over and over since you can
retrieve the schema information from the reader or Information_Schema in SQL
Server and the Connection object in OleDb. It sounds a lot more complex
than it really is.

No, I don't fancy doing that! I just thought there might be a quck/easy way
to fill a DataSet/DataTable straight from a DataReader - similar to binding
a DataReader to a Listbox, for example.

Yes, you can but whether or not you want to is another issue. What you'll
have to do is build and populate a datatable as you iterate through the
reader...behind the scenes, a datareader is used to fill a datatable anyway
when youuse dataadapter.fill.

Assuming you have a datatable built already

while (rdr.Read()){
dt.Rows.[0] = rdr.Getxxxx;
dt.Rows.[1] = rdr.Getxxx;
i++;
}

Do this for each column in the datareader, so if 6 fields were in your
select query, you'd have a dt.Rows[Value] for 0,1,2,3,4,5. Also,

you
can

do a Select COUNT(*) from the onset so you know how many rows to add and
then just populate them, but this is another query. The other option

is

to
include
DataRow dro = dt.NewRow();
//make sure you use new ..then just add to the row
Following on from my previous post, I have another quick question
(sorry!):

I want to retrieve the records into a dataset. Is it possible to use a
DataReader for this, or can it only be done with a DataAdapter? The reason
for asking is that I only need to read the data into the dataset, not
update
it, so a DataAdapter might be more heavyweight than I need...