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.