Code: Convert DataReader to DataSet

Code: Convert DataReader to DataSet

By Steven Smith

The DataReader and the DataSet are two very different ways to access data using ADO.NET. The DataReader provides a direct one-way connection to the data and is the fastest way to read data from a database. The DataSet is essentially a collection, and has a lot of functionality to support working with relational data. A DataSet will always perform worse than a DataReader (albeit perhaps unnoticeably so), due to its size and the simple fact that it uses a DataReader to populate itself. For most data access, Microsoft recommends and I prefer to use the DataReader. However, there are times when a DataReader won't do the job, such as when data must be serialized for caching or sending back from a web service. Also, there is no standard method to convert between a DataReader and a DataSet in the .NET Framework. Thus, one option when building your application is to build everything using DataReaders (per MS's guidance), and then duplicate those data access functions you expect to need to serialize (Web Services, for instance) as DataGrid methods. This is not ideal, and results in a rather ugly data access layer, full of methods like GetProductsReader and GetProductsDataSet, which is just silly.

Since I don't want to duplicate code to return a DataReader vs. a DataSet from the same database stored procedure, I use a utility function to convert my DataReaders to DataSets as needed. This lets me use one consistent return type for all of my Data Access Layer (DAL) methods: the DataReader. This method, which is adapted from the FMStocks 7 application, is a great utility function to keep in your tool library. It converts a DataReader into a DataSet, which is then ready to be cached or sent over the wire as part of a web service. The complete method call, in C#, is listed below:

ConvertDataReaderToDataSet:

///    <summary>         ///    Converts a SqlDataReader to a DataSet         ///    <param name='reader'>         /// SqlDataReader to convert.</param>         ///    <returns>         /// DataSet filled with the contents of the reader.</returns>         ///    </summary>         public static DataSet convertDataReaderToDataSet(SqlDataReader reader)         {             DataSet dataSet = new DataSet();             do             {                 // Create new data table                 DataTable schemaTable = reader.GetSchemaTable();                 DataTable dataTable = new DataTable();                 if ( schemaTable != null )                 {                     // A query returning records was executed                     for ( int i = 0; i < schemaTable.Rows.Count; i++ )                     {                         DataRow dataRow = schemaTable.Rows[ i ];                         // Create a column name that is unique in the data table                         string columnName = ( string )dataRow[ "ColumnName" ]; //+ "<C" + i + "/>";                         // Add the column definition to the data table                         DataColumn column = new DataColumn( columnName, ( Type )dataRow[ "DataType" ] );                         dataTable.Columns.Add( column );                     }                     dataSet.Tables.Add( dataTable );                     // Fill the data table we just created                     while ( reader.Read() )                     {                         DataRow dataRow = dataTable.NewRow();                         for ( int i = 0; i < reader.FieldCount; i++ )                             dataRow[ i ] = reader.GetValue( i );                         dataTable.Rows.Add( dataRow );                     }                 }                 else                 {                     // No records were returned                     DataColumn column = new DataColumn("RowsAffected");                     dataTable.Columns.Add(column);                     dataSet.Tables.Add( dataTable );                     DataRow dataRow = dataTable.NewRow();                     dataRow[0] = reader.RecordsAffected;                     dataTable.Rows.Add( dataRow );                 }             }             while ( reader.NextResult() );             return dataSet;         }