ISerializable - Roy Osherove's Blog

Looks like it's taken word for word from this article published a while ago:
http://www.wwwcoder.com/main/parentid/273/site/2114/68/default.aspx

The dataset will use a reader internaly, so why not examine the logic inside the dataset?

Could a dataAdapter do this directly?
(Using the SqlHelper has reduced my awareness of dataccess classes)

Just a quick shot (not tested, typed from my head):

public static DataSet ConvertDataReadertoDataSet(IDataReader reader)
{
DataSet ds=new DataSet();
DataTable dataTable=new DataTable();

DataTable schemaTable=reader.GetSchemaTable();

DataRow row;
string columnName;
DataColumn column;

int count=schemaTable.Rows.Count;

for(int i=0;i<count;i++)
{
row=schemaTable.Rows[i];
columnName=(string)row["ColumnName"];
column=new DataColumn(columnName,(Type)row["DataType"]);
dataTable.Columns.Add(column);
}
ds.Tables.Add(dataTable);

object[] values=new object[count];

try
{
dataTable.BeginLoadData();
while(reader.Read())
{
reader.GetValues(values);
dataTable.LoadDataRow(values,true);
}

}
finally
{
dataTable.EndLoadData();
reader.Close();
}

return ds;
}

Why GetSchemaTable?

Since IDataReader is also IDataRecord, it could be:

static DataSet DataReaderToDataSet(IDataReader reader)
{
DataTable table = new DataTable();
int fieldCount = reader.FieldCount;
for (int i = 0; i < fieldCount; i++)
{
table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
}
table.BeginLoadData();
object[] values = new object[fieldCount];
while (reader.Read())
{
reader.GetValues(values);
table.LoadDataRow(values, true);
}
table.EndLoadData();
DataSet ds = new DataSet();
ds.Tables.Add(table);
return ds;
}

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" );
schema.Columns.Add( "ColumnName" );
schema.Columns.Add( "ColumnOrdinal", typeof(int) );
schema.Columns.Add( "ColumnSize", typeof(int) );
schema.Columns.Add( "NumericPrecision", typeof(int) );
schema.Columns.Add( "NumericScale", typeof(int) );
schema.Columns.Add( "DataType", typeof(Type) );
schema.Columns.Add( "ProviderType", typeof(Type) );
schema.Columns.Add( "IsLong", typeof(bool) );
schema.Columns.Add( "AllowDBNull", typeof(bool) );
schema.Columns.Add( "IsReadOnly", typeof(bool) );
schema.Columns.Add( "IsRowVersion", typeof(bool) );
schema.Columns.Add( "IsUnique", typeof(bool) );
schema.Columns.Add( "IsKeyColumn", typeof(bool) );
schema.Columns.Add( "IsAutoIncrement", typeof(bool) );
schema.Columns.Add( "BaseSchemaName" );
schema.Columns.Add( "BaseCatalogName" );
schema.Columns.Add( "BaseTableName" );
schema.Columns.Add( "BaseColumnName" );

foreach( DataColumn col in _table.Columns ) {
DataRow colRow = schema.NewRow();
colRow["ColumnName"] = col.ColumnName;
colRow["ColumnOrdinal"] = col.Ordinal; //col.Ordinal;
colRow["ColumnSize"] = col.MaxLength; //col.ColumnSize;
colRow["NumericPrecision"] = null; //col.NumericPrecision;
colRow["NumericScale"] = null; //col.NumericScale;
colRow["DataType"] = col.DataType;
colRow["ProviderType"] = col.DataType; //col.ProviderType;
colRow["IsLong"] = null; //col.IsLong;
colRow["AllowDBNull"] = col.AllowDBNull;
colRow["IsReadOnly"] = true; //col.IsReadOnly;
colRow["IsRowVersion"] = false; //col.IsRowVersion;
colRow["IsUnique"] = false; //col.IsUnique;
colRow["IsKeyColumn"] = false; //col.IsKeyColumn;
colRow["IsAutoIncrement"] = false; //col.IsAutoIncrement;
colRow["BaseSchemaName"] = null; //col.BaseSchemaName;
colRow["BaseCatalogName"] = null; //col.BaseCatalogName;
colRow["BaseTableName"] = null; //col.BaseTableName;
colRow["BaseColumnName"] = null; //col.BaseColumnName;
schema.Rows.Add( colRow );
}
return schema;
}

#endregion

#region IDisposable Members

public void Dispose() {
Close();
}

#endregion

#region IDataRecord Members

public int GetInt32(int i) {
return Convert.ToInt32( _row );
}

public object this[string name] {
get {
try {
return _row[name];
} catch( ArgumentException err ) {
throw new IndexOutOfRangeException( err.Message, err );
}
}
}

object System.Data.IDataRecord.this[int i] {
get {
return _row[i];
}
}

public object GetValue(int i) {
return _row[i];
}

public bool IsDBNull(int i) {
return _row[i] == DBNull.Value;
}

public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length) {
throw new ApplicationException( "Not implemented" );
}

public byte GetByte(int i) {
return Convert.ToByte( _row[i] );
}

public Type GetFieldType(int i) {
return _row[i].GetType();
}

public decimal GetDecimal(int i) {
return Convert.ToDecimal( _row[i] );
}

public int GetValues(object[] values) {
int count = Math.Min( values.Length, _row.ItemArray.Length );
for( int i = 0; i < count; i++ )
values[i] = _row[i];

return _row.ItemArray.Length;
}

public string GetName(int i) {
return _table.Columns[i].ColumnName;
}

public int FieldCount {
get {
return _row.ItemArray.Length;
}
}

public long GetInt64(int i) {
return Convert.ToInt64( _row[i] );
}

public double GetDouble(int i) {
return Convert.ToDouble( _row[i] );
}

public bool GetBoolean(int i) {
return Convert.ToBoolean( _row[i] );
}

public Guid GetGuid(int i) {
return (Guid)_row[i];
}

public DateTime GetDateTime(int i) {
return Convert.ToDateTime( _row[i] );
}

public int GetOrdinal(string name) {
return _table.Columns[name].Ordinal;
}

public string GetDataTypeName(int i) {
return _table.Columns[i].DataType.Name;
}

public float GetFloat(int i) {
return (float)Convert.ToDouble( _row[i] );
}

public IDataReader GetData(int i) {
return null;
}

public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length) {
char[] chars = _row[i].ToString().ToCharArray( Convert.ToInt32( fieldoffset ), length );
length = Math.Min( chars.Length, length );
for( int j = 0; j < length; j++ )
buffer[j+bufferoffset] = chars[j];
return length;
}

public string GetString(int i) {
return _row[i].ToString();
}

public char GetChar(int i) {
return Convert.ToChar( _row[i] );
}

public short GetInt16(int i) {
return Convert.ToInt16( _row[i] );
}

#endregion
}
}

'''' PA-CA-USA, 28 January 2004, Include this attribution line if you re-use some
'''' or all of this code, please.
'''' This code has been working well for me for some time now. It handles
'''' multiple result sets from a single reader.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Shared Function convertSqlDataReaderToDataSet(ByVal reader As SqlDataReader) As DataSet
Dim ds As DataSet = New DataSet()
Dim schema As DataTable
Dim data As DataTable
Dim i As Integer
Dim dr As DataRow
Dim dc As DataColumn
Dim columnName As String

Do
schema = reader.GetSchemaTable()
data = New DataTable()
If Not schema Is Nothing Then
For i = 0 To schema.Rows.Count - 1
dr = schema.Rows(i)
columnName = dr("ColumnName")
If data.Columns.Contains(columnName) Then
columnName = columnName + "_" + i.ToString()
End If
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

DataReader to DataTable Happy Ending

DataReader to DataTable sample code

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

public class DataReaderAdapter : DbDataAdapter { public int FillFromReader(DataTable dataTable, IDataReader dataReader) { return this.Fill(dataTable, dataReader); } protected override void OnRowUpdated(System.Data.Common.RowUpdatedEventArgs oValue) { //protected abstract new void OnRowUpdated ( System.Data.Common.RowUpdatedEventArgs value ) //Member of System.Data.Common.DbDataAdapter } protected override void OnRowUpdating(System.Data.Common.RowUpdatingEventArgs oValue) { //protected abstract new void OnRowUpdating ( System.Data.Common.RowUpdatingEventArgs value ) //Member of System.Data.Common.DbDataAdapter } protected override System.Data.Common.RowUpdatingEventArgs CreateRowUpdatingEvent(System.Data.DataRow oDataRow , System.Data.IDbCommand oCommand , System.Data.StatementType oStatementType , System.Data.Common.DataTableMapping oTableMapping) { return null; //protected abstract new System.Data.Common.RowUpdatingEventArgs CreateRowUpdatingEvent ( System.Data.DataRow dataRow , System.Data.IDbCommand command , System.Data.StatementType statementType , System.Data.Common.DataTableMapping tableMapping ) //Member of System.Data.Common.DbDataAdapter } protected override System.Data.Common.RowUpdatedEventArgs CreateRowUpdatedEvent(System.Data.DataRow oDataRow , System.Data.IDbCommand oCommand , System.Data.StatementType oStatementType , System.Data.Common.DataTableMapping oTableMapping) { return null; //protected abstract new System.Data.Common.RowUpdatedEventArgs CreateRowUpdatedEvent ( System.Data.DataRow dataRow , System.Data.IDbCommand command , System.Data.StatementType statementType , System.Data.Common.DataTableMapping tableMapping ) //Member of System.Data.Common.DbDataAdapter } }

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.