The difference between a DataReader and a DataSet

Excerpt from: http://www.cnblogs.com/zhjjNo1/archive/2009/08/26/1554420.html

The first kind of explanation

The biggest difference between the DataReader and the DataSet is that when DataReader is used, it always occupies SqlConnection (commonly known as: non-disconnected), and when you operate the database online, Any operation on SqlConnection throws an DataReader exception. Because DataReader only loads one piece of data in memory at a time, the memory consumed is very small. Because of the particularity of the DataReader and high performance, so DataReader is only in, you read the first article can not read the first one.
A DataSet is a one-time load of data in memory, discarding the database connection (commonly known as: disconnected). The database connection is discarded because the dataset loads all the data in memory, so it consumes more memory. However, it is more flexible than DataReader, and can dynamically add rows, columns, data, callbacks to the database, update operations, etc.

The second explanation

Data can be read from the data source using both DataReader and datasets. The DataReader itself is built by means of the Idbcommand.executereader () method, while a dataset is populated by the Dbdataadapter.fill () method. In addition, the way they work is significantly different: the execution of DataReader cannot be separated from the database connection, that is, when DataReader reads the data, it cannot use the Idbconnection.close () method to close the database connection , and when you use the dataset to get data, you can disconnect the database because DbDataAdapter is now responsible for getting the data to the application server.
Because of this distinction, special attention needs to be paid to developing database-related programs. For example, after you use DataReader to obtain data, you should actively close the database connection, or you might get an exception to the database connection pool overflow.

The third explanation

You can use the object of the DataReader class or the object of the dataset class to read data from the database, but they are different, summed up roughly the following:

1. DataReader is a data management provider class, whereas a DataSet is a generic data class.

2. A dataset acquires data that needs to be filled by a bridge DataAdapter, and since DataReader itself is the management provider, it can obtain data through the command's ExecuteReader () method.

3. DataReader is online processing, the data cannot be read when the connection is closed, the dataset can process the data offline, it is copying data from the database to the local storage, in the case of closing the connection can still process the data in the dataset, even in the locally stored tables to increase the restrictions.

4. DataReader can only read data directly, but cannot modify the data; the dataset can read rows in any order, can search, sort, and filter these rows in a flexible way, and can even change those rows, and then synchronize those changes to the database.

5. Reading data from DataReader is faster than the dataset.

6. Because the dataset is processed offline, the dataset cannot be used when the database is locked in a transaction. Because when the dataset is populated, the connection to the database is automatically disconnected, and the database cannot be locked at this time.

It can be seen that, in general, datasets and DataReader may be substituted for each other, and when there are specific requirements, they need to be analyzed carefully, whichever is more appropriate.

The most fundamental difference between DataReader and datasets is that one is online and the other is offline. When online, it is the real data of the current database, but always online, increase the network traffic burden. Offline after the data copy in the local, can reduce the burden of the network, the process of processing data more convenient, if the offline time is too long, see the data is not necessarily true data.

Supplemental experience: Using Datasets and DataReader on large data volumes, using a dataset is a very unwise choice when the amount of single-table data is large, because the dataset stores data in DataTable memory and puts large data into memory at once. This is very memory-eating. It's a lot more elegant than dataset,datareader, which is to read only one piece of data at a time and then loop through the mechanism. But it also has the disadvantage that it is relatively non-disconnected, but it is advantageous for memory consumption. Datasets also have their advantages in most applications, such as full decoupling, one-time operation, and domain model manipulation.

The difference between a DataReader and a DataSet