I don't remember all the specifics, but enough to get a
working version.
Basically, the DbDataAdapter class (which all provider
specific DataAdapters derive from) already contains a
protected Fill overload that takes an IDataReader as a
parameter.
So, create a custom class that derives from
DbDataAdapter, and expose that functionality with a
public method, as follows:
public class DataReaderAdapter : DbDataAdapter {
public int FillFromReader(DataTable dataTable,
IDataReader dataReader){
return this.Fill(dataTable, dataReader);
}
}
(You are also required to override 4 events. I left them
out of this example for brevity. My implementation didnt
include any code in the events, just the base
implementation provided by VS.NET 2003.)
Now, in your code, it is as easy as:
SqlDataReader dr = cmd.ExecuteReader();
DataSet ds = new DataSet();
DataSet dt = new DataTable();
DataReaderAdapter da = new DataReaderAdapter();
da.FillFromReader(dt, dr);
ds.Tables.Add(dt)
Hurray! You will find your dataset now contains a table
with all of the data from the datareader. No messing
with schemas, loops, etc. Use the functionality already
built into the Framework.
I find that I often want to go the other way (i.e.,
convert a DataSet into a DataReader). We all know that
it's best to use DataReaders when possible for
performance reasons, but it's really hard to create a
mock data layer with DataReaders (it's very hard to stub
them out).
One of the guys on my team came up with a
DataSetDataReader, which was an implementation of
IDataReader whose underlying stream was a DataSet. This
solved our problem -- for unit tests, we could create a
DataSet of "dummy" data to represent
our mock data layer, wrap a DataSetDataReader around and
return it to the business logic layer with noone being
the wiser. It worked quite well, and helped us unit test
our BL without introducing a dependancy on the database.
I wonder where the code for that got to...
Diego,
ah yes, how obvious, thanks. :-) I though that it was
still too complicated.
And for the others, damn that I love reading these
blogs, so much good solutions so quickly. :-)
I didn't even realize that DBDataAdapter has that
overload for Fill method (can't just remeber everything
and yes it was in docs, but can't just read everything
:-D ).
Steve: hey, if you posted that code somewhere that would
be great! Please find it :)
Here's some code I recently put together to expose an
IDataReader interface from a DataTable. The
GetSchemaTable method is pretty incomplete, but it has
all the functionality that I've needed from it.
#region references
using System;
using System.Data;
using System.Diagnostics;
using System.Text;
#endregion
namespace Common.SQL
{
public class DataTableReader : IDataReader
{
private const int NotSet = -1;
private DataTable _table;
private int _currentRow = NotSet;
private DataRow _row;
private bool _closed = false;
public DataTableReader( DataTable table ) {
_table = table;
}
#region IDataReader Members
public int RecordsAffected {
get { return -1; }
}
public bool IsClosed {
get { return _closed; }
}
public bool NextResult() {
Close();
return false;
}
public void Close() {
_closed = true;
_currentRow = NotSet;
_row = null;
_table.Dispose();
}
public bool Read() {
_currentRow++;
if( _currentRow < _table.Rows.Count ) {
_row = _table.Rows[_currentRow];
return true;
}
//passed the end, clean up
Close();
return false;
}
public int Depth {
get {
//we don't have any depth
return 0;
}
}
/// <summary>
/// Note: this makes no real attempt to get all the
functionality in there
/// </summary>
/// <returns></returns>
public DataTable GetSchemaTable() {
DataTable schema = new DataTable(
"Schema" );
dc = New DataColumn(columnName,
CType(dr("DataType"), Type))
data.Columns.Add(dc)
Next
ds.Tables.Add(data)
While reader.Read()
dr = data.NewRow()
For i = 0 To reader.FieldCount - 1
dr(i) = reader.GetValue(i)
Next
data.Rows.Add(dr)
End While
Else
dc = New DataColumn("RowsAffected")
data.Columns.Add(dc)
ds.Tables.Add(data)
dr = data.NewRow()
dr(0) = reader.RecordsAffected
data.Rows.Add(dr)
End If
Loop While reader.NextResult()
reader.Close()
Return ds
End Function
You all are completely out of date.
1) look at ado.net 2.0:
...
DataTable orders = new
DataTable("Orders");
conn.Open();
SqlCommand cmd = new SqlCommand("select * from
orders");
SqlDataReader r = cmd.ExecuteReader();
orders.Load(r);
...
2) there were similar solutions for this available long
time ago
Please can anybody tell abt performance issue on Convert
a dataReader into a dataset or going old method which is
provided by microsoft, like retrive data from database
and using data adapter fill the dataset.
Which is faster and more reliable..? please explain.
thanks.
I create XSD in which I create Datatable. when I want
assined normal datatable it give error
Wow Pete McKinney, your DataReader works! :)
Just one small bug in the GetInt32 method where you want
to convert the entire row into an integer. Use row[i]
and you're done!
I'm using this to stub out a DataReader that I've copied
from CSLA. I've made my own implementation from it, but
wanted to test it. As it requires another IDataReader as
parameter in its constructor, I had to stub that one
out. So I used your implementation and now I can test my
own datareader implementation!
Things like these make programming fun! ;)
Wow Pete McKinney, your DataReader works! :)
Just one small bug in the GetInt32 method where you want
to convert the entire row into an integer. Use row[i]
and you're done!
I'm using this to stub out a DataReader that I've copied
from CSLA. I've made my own implementation from it, but
wanted to test it. As it requires another IDataReader as
parameter in its constructor, I had to stub that one
out. So I used your implementation and now I can test my
own datareader implementation!
Things like these make programming fun! ;)
I have 65 lack records to be read.When I loop through
the records it is taking almost 1 minutes for 30,000
records.
How i can minimize this time.To read the data into data
reader it is taking only 30 seconds.