Choosing between DataReader and DataSet

The benefit of using a DataSet is that it gives you a disconnected view of the database. For long-running applications, this is often the best method. However, for Web applications developers usually perform short, simple operations with each request, such as displaying data. For such brief operations, it is usually not effecient to maintain a DataSet object. In such cases, you can use a DataReader.

Use a Dataset when you need to do any of the following

• Transmit data between tiers or from an XML web service
• Interact dynamically with data, as when binding to a Windows Forms control or combing and relating data from multiple sources.
• Cache data locally in your application
• Provide a hierarchical XML view of rlational data, and use tools like an XSL Transformation or an XML Path Language (XPATH) Query on your data.
• Perform extensive processing on data without requiring an open connection to the datasource, leaving the connection available to other clients.

If you do not require the functionality that is provided by the Dataset class, you can improve the performance of you application by using the DataReader class to return your data in a read-only /forward only fashion. Although, the DataAdapter class uses a DataReader to fill the contents of a DataSet, by using the DataReader class instead you can secure the following performance benefits:

• The memory that would be consumed by the DataSet would instead be available.
• The processing that would be required to create and fill the contents of the DataSet will not be needed.