DataReaders do require an open connection. That's why a connection cannot be used for any other purpose while a DataReader is open. It will read one record at a time from the result set, which is stored on the database itself. Also, it can only move forward one record at a time. Note also that a DataReader can support multiple result sets from multiple queries, although they'd rarely be used like that.
A DataSet is something else. It is a data store itself, not something used to read a data store. It is used to store one or more result sets in DataTables. It supports relationships between these tables and it allows you to navigate that data in any way you like.
Here are some scenarios and what data access modes you should employ:
1. You need a single value from the database:
Call the ExecuteScalar method of a command.
2. You want to read one or more rows and process each one in turn, discarding it immediately thereafter:
Call the ExecuteReader method of a command to get a DataReader. You then use a loop to read each row and process it in turn.
3. You need to display the contents of a result set to the user but not save any changes that may be made:
Call the ExecuteReader method of a Command to get a DataReader. You then call the Load method of a DataTable to populate it with the data from the DataReader. The reader can then be closed and the DataTable bound to a control or controls.
4. You need to make changes to make changes to a result set and save those changes back to the database:
Call the Fill method of a DataAdapter or TableAdapter to populate a DataTable with a result set, then call the Update method of the same adapter to save any changes.
Note that you cannot use a DataSet without DataTables. If there's only one DataTable then a DataSet is usually pointless, like an array to store a single string. The DataSet helps if you have multiple related tables or require design time support.
