DataReader to DataTable...

[Guest]

Can anyone tell me how to achieve the following...

I want to convert data from a DataReader into a DataTable...

I was told this should do it but it doesn't seem to work!

Dim Myconn As New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim tbcontactsNonAD As DataTable = New DataTable("contactsNonAD")
Dim cmd As New SqlCommand("NonADUsers", Myconn)
cmd.CommandType = CommandType.StoredProcedure
Myconn.Open()
Dim r As SqlDataReader = cmd.ExecuteReader
ds.Tables("tbcontactsNonAD").load(r)
Myconn.Close()

 

[Karl Seguin]

Tim::..
It's helpful to us trying to help you if you don't spawn multiple threads on
the same topic....

Just loop through the datareader and create new rows...

while dr.read()
Dim row as DataRow = tbContactsNonAD.NewRow()
row(0) = row(0)
row(1) = row(1)
tbContactsNonAD.Rows.Add(row)
end


Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)

 

[Guest]

If you are doing this right after the pull, you might as well just use a
DataAdapter and fill a DataSet. It will certainly reduce the amount of work
you have to do and you can strongly type the DataSet to increase perf. Of
course, this is not 100% true if you are in the 2.0 Framework (Visual Studio
2005), as the ADO.NET model has greatly expanded.

As far as "you should not do this", that is an incorrect statement.
Microsoft uses a DataReader to fill their own DataSets (or rather DataTables
in a DataSet). The DataReader serves as a stream of data (or a firehose
cursor, fast forward cursor, or other ways of naming), while a DataTable is
an endpoint. You can bind directly from a reader to controls, but you lose
the interim step and have to manufacture your own return trip. The Reader is
much faster, but less maintainable for round tripping of data. The only
reason I might agree with "you should not" is it is unlikely you will fill a
DataTable with an algorithm superior to Microsoft's. Make sure you are not
spending a lot of time spinning your wheels to reinvent a mousetrap that is
already on the market; beyond that, there is nothing wrong with moving data
from a "stream" to a "container".

With the DataTable in 1.x, you can load a data row with LoadDataRow(), but
this means you have to build the row. You can also do this in 2.0, but that
is all you have for 1.x. With 2.0, you have a Load() method to make it easier
to load from an object that implements IDataReader. Note, however, that
Microsoft's own documenation shows the new DataTableReader being used rather
than a SqlDataReader:
http://msdn2.microsoft.com/library/7x8ccbsb(en-us,vs.80).aspx

For the SqlDataReader, my next line of attack would be setting it up as its
interface, IDataReader, or casting it as DbDataReader, which is a common
ancestor of both SqlDataReader and the new DataTableReader. I would not
necessarily leave it this way after the code goes gold, but I would consider
this to solve the problem today. I am not sure which version of the 2.0
Framework you are working with (June CTP is the latest), but it is possible a
different version will work with this class.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Guest]

If you are doing this right after the pull, you might as well just use a
DataAdapter and fill a DataSet. It will certainly reduce the amount of work
you have to do and you can strongly type the DataSet to increase perf. Of
course, this is not 100% true if you are in the 2.0 Framework (Visual Studio
2005), as the ADO.NET model has greatly expanded.

As far as "you should not do this", that is an incorrect statement.
Microsoft uses a DataReader to fill their own DataSets (or rather DataTables
in a DataSet). The DataReader serves as a stream of data (or a firehose
cursor, fast forward cursor, or other ways of naming), while a DataTable is
an endpoint. You can bind directly from a reader to controls, but you lose
the interim step and have to manufacture your own return trip. The Reader is
much faster, but less maintainable for round tripping of data. The only
reason I might agree with "you should not" is it is unlikely you will fill a
DataTable with an algorithm superior to Microsoft's. Make sure you are not
spending a lot of time spinning your wheels to reinvent a mousetrap that is
already on the market; beyond that, there is nothing wrong with moving data
from a "stream" to a "container".

With the DataTable in 1.x, you can load a data row with LoadDataRow(), but
this means you have to build the row. You can also do this in 2.0, but that
is all you have for 1.x. With 2.0, you have a Load() method to make it easier
to load from an object that implements IDataReader. Note, however, that
Microsoft's own documenation shows the new DataTableReader being used rather
than a SqlDataReader:
http://msdn2.microsoft.com/library/7x8ccbsb(en-us,vs.80).aspx

For the SqlDataReader, my next line of attack would be setting it up as its
interface, IDataReader, or casting it as DbDataReader, which is a common
ancestor of both SqlDataReader and the new DataTableReader. I would not
necessarily leave it this way after the code goes gold, but I would consider
this to solve the problem today. I am not sure which version of the 2.0
Framework you are working with (June CTP is the latest), but it is possible a
different version will work with this class.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[qvo178]

I find that this is the best way to load it itjungles.com/dotnet/function-to-load-datatable-from-sqldatareader.