Data Reader Vs Data Adapter

ADO.NET supports two different programming environments: connected and disconnected.

The connected environment provides forward-only, read-only access to data in the data source and the ability to execute commands against the data source. The connected classes provide a common way to work with connected data regardless of the underlying data source. They include Connection, Command, DataReader, Transaction, ParameterCollection, and Parameter classes.

The disconnected environment allows data retrieved from the data source to be manipulated and later reconciled with the data source. The disconnected classes provide a common way to work with disconnected data regardless of the underlying data source. They include the DataSet, DataTable, DataColumn, DataRow, Constraint, DataRelationship, and DataView classes.

Finally, ADO.NET introduces the connected DataAdapter class to bridge the data source and disconnected classes by way of the connected classes. The DataAdapter is an abstraction of the connected classes that simplifies filling the disconnected DataSet or DataTable classes with data from the data source and updating the data source to reflect any changes made to the disconnected data.

When querying data, there are two mechanisms we can use: a DataReader or a DataAdapter.

Even though we are comparing DataReader & DataAdapter, underneath the abstractions, a DataAdapter uses a DataReader to populate the returned DataSet or DataTable. Using a DataReader produces faster results than using a DataAdapter to return the same data. Because the DataAdapter actually uses a DataReader to retrieve data, this should not surprise us. But there are many other reasons as well.

• DataReaders provide multiple asynchronous methods that can be employed. DataAdapters on the other hand, essentially have only synchronous methods. With small-sized record sets, the differences in performance or advantages of using asynchronous methods are trivial, but on large queries that take time, a DataReader, in conjunction with asynchronous methods, can greatly enhance the user experience.
• The Fill method of DataAdapter objects enables us to populate only DataSets and DataTables. To use a custom business object, we have to first retrieve the DataSet or DataTables; then we need to write code to hydrate our business object collection.
• The Fill method of the DataAdapter completes only when all the data has been retrieved and added to the DataSet or DataTable. This enables us to immediately determine the number of records in any given table. By contrast, a DataReader can indicate whether data was returned (via the HasRows property), but the only way to know the exact record count returned from a DataReader is to iterate through it and count it out specifically.
• We can iterate through a DataReader only once and can iterate through it only in a forward-only fashion. But we can iterate through a DataTable any number of times in any manner we wish to.
• After a DataSet or DataTable is populated and returned to the consuming code, no other interaction with the database is necessary unless or until we decide to send the localized changes back to the database. We can think of the dataset as an in-memory copy of the relevant portion of the database.

In a nutshell,

SqlDataReader:

• Holds the connection open until we are finished (don’t forget to close it!).
• Can typically only be iterated over once
• Is not as useful for updating back to the database

On the other hand, it:

• Only has one record in memory at a time rather than an entire result set (this can be huge)
• Is about as fast as we can get for that one iteration
• Allows us to start processing results sooner (once the first record is available)

SqlDataAdapter/DataSet

• Lets us close the connection as soon as it’s done loading data, and may even close it for us automatically
• All of the results are available in memory
• We can iterate over it as many times as we need, or even look up a specific record by index
• Has some built-in faculties for updating back to the database

At the cost of:

• Much higher memory use
• We wait until all the data is loaded before using any of it