|
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;
}
| | csharpindex.com/colorCode |
|
|
