Use DataReader or DataSet?
ADO.NET exposes two primary classes for retrieving data. Learn to decide which one to use.
by Jonathan Goodyear, MCSD, MCP, CLS

Posted February 28, 2003

I hear this question all the time: "Should I use the DataReader or DataSet class for my ASP.NET Web applications?" The most common misconception I've seen posted in articles and newsgroups states that DataReader (short for either SqlDataReader or OleDbDataReader) is better than DataSet. Sometimes I see it mentioned the other way around. The truth is that Microsoft created these data-access classes because they are both needed. Each has unique strengths and weaknesses, and their usage should be determined by the situation you are facing.

This article sheds some light on the issue by providing you with some guidelines for when to use the DataReader class and when to use the DataSet class, in the context of ASP.NET. The rules might change in the context of a client-based Windows Forms application. I am assuming that you have used, and are familiar with, both the DataReader and DataSet classes.

Use the DataReader Class
These are the ideal situations when you should use the DataReader class:

• The data that you are retrieving must always be fresh, and therefore must be retrieved from the database each time you need it. There is less overhead associated with creating a DataReader class, and performance over DataSet improves rapidly with increasing load (see the Visual Studio Magazine articles in Resources).
• You have simple needs for each row of data. The best example of this would be simply binding DataReader to a Web control, such as DataGrid or DropDownList.
• You simply need forward-only, read-only access to XML data from a database. In this case, you can use the ExecuteXmlReader() method of the SQLCommand object to obtain an XmlReader class (the XML equivalent of DataReader). This requires a SQL Server query using the FOR XML clause, or an ntext field that contains valid XML.
• You plan to make several repeated calls to the database to retrieve small pieces of information. The performance data referred to in the first bullet applies to an even greater scale here.
• It is true that many of the features that make DataSet classes great, such as the ability to set up relations between tables, are better left to client-based Windows Forms applications. There are numerous occasions, however, when DataSet classes are preferable to DataReader classes, and a few where you cannot use DataReader classes at all.

Use the DataSet Class
Here's when you should consider using the DataSet class:

• You are building a Web service that uses the data you are retrieving as the return value. Because DataReader classes maintain a connection to the database, they cannot be serialized into XML and therefore cannot be transmitted over the wire to the caller of a Web service.
• You need to sort or filter data. Before using a DataView object (exposed as the DefaultView property of DataTable classes, which comprise a DataSet class) to sort or filter your data, first try to use SQL query constructs—such as the WHERE and ORDER BY clauses—to do it for you, and use the more lightweight and faster DataReader class. Sometimes, however, this is not possible or you might need to sort or filter the data more than once.
• You need to iterate through the data more than once on the same request. You can only loop through DataReader once. If, for example, you need to bind more than one ServerControl class to the same set of data, DataSet is the more appropriate solution. DataReader cannot be bound to more than one ServerControl class because it is forward-only. The data would have to be retrieved twice from the database in order for DataReader to be used in this situation.
• You need to store data that will be used again by subsequent page requests. If the data is specific to the person who requested it, you can store the DataSet class in a Session variable. If the data can be accessed by anyone, you can store it in an Application variable or in Cache (recommended because it supports expiration and callbacks). Because DataReader classes maintain an open connection to the database, and only hold one data row at a time, they cannot be stored across page requests.
• You need to perform a nontrivial or time-consuming function on each element of a resultset. For instance, if you were retrieving a list of ZIP codes from a database and you wanted to call a Web service to retrieve detailed weather information for each one, DataSet would be the better option. This is because when you use a DataReader class, the connection to the database will not be released back into the connection pool until you close your DataReader class. The latency of even a small extra delay across thousands of page requests can cause a high-traffic Web application to run out of available connections. By contrast, DataSet retrieves all of its data up-front and is able to close the connection to the database immediately, returning it to the connection pool so another page request can use it.
• You need to load and manipulate XML data in a two-dimensional paradigm. DataSet classes are useful for XML because you can use DataView on them to sort and filter the underlying data in the same way that you would use a database resultset. Note, however, that there are many classes in the System.Xml namespace that you can use for more complex XML manipulations.
• Your data source is not a database. Although OleDbDataReader can be used for any OLEDB data provider (which might or might not point to a database), the DataSet object can load data directly from an XML file and interpret its schema dynamically. DataSet classes also possess the ability to write XML data back out to a stream or file.

As you can see, the DataSet class has more features than the DataReader class, which allows you to use it in a wider variety of scenarios. This does not necessarily mean that you will use DataSet classes more often, however. A large percentage of the tasks you need to complete in ASP.NET fall under the purview of DataReader.

Still, DataSet classes undoubtedly play an important role in an ASP.NET Web application of any significant size or complexity. You can reduce the "performance penalty" of DataSet classes by judicious caching to minimize database round-trips. Both DataReader and DataSet are essential pieces of a successful ASP.NET Web application. It's just important to know when and where each is used best.