Comparing the Options
You have the following options when you retrieve multiple rows from a data source:
- Use a SqlDataAdapter object to generate a DataSet or DataTable.
- Use a SqlDataReader to provide a read-only, forward-only data stream.
- Use an XmlReader to provide a read-only, forward-only data stream of XML data.
The choice between SqlDataReader and DataSet/DataTable is essentially one of performance versus functionality. The SqlDataReader offers optimum performance; the DataSet provides additional functionality and flexibility.
All three of these objects can act as data sources for data-bound controls, although the DataSet and DataTable can act as data sources for a wider variety of controls than the SqlDataReader. This is because the DataSet and DataTable implement IListSource (yielding IList), whereas the SqlDataReader implements IEnumerable. A number of WinForm controls capable of data binding require a data source that implements IList.
This difference is due to the type of scenario for which each object type is designed. The DataSet (which includes the DataTable) is a rich, disconnected structure suited to both Web and desktop (WinForm) scenarios. The data reader, on the other hand, is optimized for Web applications that require optimized forward-only data access.
Check the data source requirements for the particular control type that you want to bind to.