SqlDataReader and SqlDataAdapter

SqlDataReader efficient, low-performance, read-only access
SqlDataAdapter is strong and requires a little more resources

SqlDataReader can only be read if it is kept in a database connection ...

SqlDataAdapter in most cases, a single table is read one at a time, then populated into a dataset, and then the connection to the database can be disconnected.

The difference between the two is mainly online and offline .....

One:
SqlDataReader Rd;
Rd=cmd. ExecuteReader ();

More efficient, if only to display the data, of course, use this

Two:
SqlDataAdapter Adapter=new SqlDataAdapter (CMD);
Ado. NET has two categories, connected offline
Offline is the dataset and so on, you can take the data out to put in, and then disconnect, save server resources
When you make changes locally, connect to the database to update
The job of fetching data and updating data is done by SqlDataAdapter, an "extended wire"-a metaphor that appears to be from the inside of the technology insider.

SqlDataReader//connection-based, read-only access suitable for smaller data volumes
SqlDataAdapter//Based on non-connected, suitable for large data volume, can be modified, and finally return the results of the changes to the database. Ask for a bigger resource, too.

SqlDataReader like a linked list, you can read data in one Direction only
SqlDataAdapter's function is as powerful as they say.

1.SqlDataReader returned is a data reader, only one of the reading, operation is not flexible, generally in the read-only time to use.
2.SqlDataAdapter returns a dataset or table that can manipulate the data in it

The SqlDataReader is a data reader that cannot forward backwards and reads only one bar at a time. It is fast and uses it most in modern projects.
The dataset is a memory database, which is fundamentally different from the other two.
SqlDataAdapter is a data adapter that works between datasets and databases and is responsible for reading the data from the data source into the dataset or updating the changes in the dataset back to the database.

So the above can be combined into two modes: 1 is SqlDataReader a read (usually this will be related to the entity conversion), 2 is the use of SqlDataAdapter and datasets.

In both of these scenarios, the comparison is as follows:
1. Performance. The data in the dataset is completely stored in memory, so it consumes memory. But typically, the data that DataReader reads is also in memory after it is instantiated, so it doesn't make much difference. However, when you use a dataset with a large amount of data, whether you are paging or not paging, it is less efficient than SqlDataReader, especially if you use DataReader to read only the data you need in the case of paging, and network traffic can be very small , but DataAdapter still have to read all of it, network traffic is hard to optimize, unless you go deep into your own control.
2. Convenient. DataSet data is stored in memory, can be filled with adapter disposable, at first glance convenient, not actually. Because the data in the dataset is weak, you have to use a lot of indexes, coercion type conversions, type conversions to complete the read task, which is cumbersome and error-prone. From this convenience, it is not advisable to have a bit of convenience when reading data.
3. Offline. The dataset is offline, and the database connection can be closed after the data is read, but DataReader must always connect to the database when using the data. Most of the current projects use entities to save data read from DataReader, but also for offline purposes, although a bit cumbersome, but later very convenient to use.

Ado. NET provides the following two objects for retrieving relational data and storing it in memory: Datasets and DataReader.
The dataset provides a relational representation of the data in memory, a complete set of data including tables that contain data, sort and constrain data, and relationships between tables.
DataReader provides a fast, forward-only, read-only stream of data from a database. When working with datasets, it is often possible to interact with the data source using DataAdapter (or perhaps CommandBuilder). When working with datasets, you can also use DataView to apply sorting and filtering to data in a dataset. You can also inherit from a dataset, creating a strongly typed dataset that exposes tables, rows, and columns as strongly typed object properties. When designing an application, consider the level of functionality required by the application to determine whether to use a dataset or a DataReader. To do the following from an application, use a dataset:
L Navigate between multiple discrete tables of results.
L manipulate data from multiple data sources (for example, mixed data from multiple databases, an XML file, and a spreadsheet).
L Exchange data between tiers or use XML Web services. Unlike DataReader, a dataset can be passed to a remote client.
L REUSE the same collection of records to get performance improvements through caching (such as sorting, searching, or filtering data).
• A large amount of processing is required for each record. Scaling processing for each row returned with DataReader will prolong the time required to service the DataReader connection, which affects performance.
L manipulate data using XML operations, such as Extensible Stylesheet Language Transformations (XSLT transformations) or XPath queries.
Use DataReader in your application for the following scenarios:
L do not need to cache data.
L The result set to be processed is too large to fit in memory.
• Once you need to quickly access data in a forward-only, read-only manner.
Note: When populating a DataSet, DataAdapter uses DataReader. Therefore, the performance of using DataAdapter instead of DataSet elevation is to save the loop that the dataset needs to consume memory and populate the dataset. In general, this performance boost is symbolic, so design decisions should be based on the required functionality.
In large projects, DataReader is almost the first choice and datasets are hard to trace.

SqlDataReader and SqlDataAdapter