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, is listed below:
Public Function convertDataReaderToDataSet(ByVal reader As SqlDataReader) As DataSet
Dim dataset As New DataSet
If reader.HasRows Then
' Create new data table
Dim schematable As DataTable = reader.GetSchemaTable
Dim datatable As New DataTable
If Not schematable Is System.DBNull.Value Then
' A query returning records was executed
For i As Integer = 0 To schematable.Rows.Count - 1
Dim datarow As DataRow = schematable.Rows(i)
' Create a column name that is unique in the data table
Dim columnName As String = datarow("columnName").ToString
' Add the column definition to the data table
Dim column As DataColumn = New DataColumn(columnName, (datarow("datatype")))
datatable.Columns.Add(column)
Next
dataset.Tables.Add(datatable)
' Fill the data table we just created
While reader.Read
Dim datarow As DataRow = datatable.NewRow
For i As Integer = 0 To reader.FieldCount - 1
datarow(i) = reader.GetValue(i)
Next
datatable.Rows.Add(datarow)
End While
Else
' No records were returned
Dim column As New DataColumn("RowsAffected")
datatable.Columns.Add(column)
dataset.Tables.Add(datatable)
Dim datarow As DataRow = datatable.NewRow
datarow(0) = reader.RecordsAffected
datatable.Rows.Add(datarow)
End If
End If
Return dataset
End Function