The difference between ADO DataReader and DataAdapter

SqlDataReader is a forward pointer, which itself does not contain data, and calls a Read () method that moves forward to the next record, a SqlDataReader An open database connection must be occupied separately.

When using SqlDataReader , the associated SqlConnection is busy with the SqlDataReader service and cannot perform any other operations on SqlConnection. unless you call the Close method of SqlDataReader , you are always in this state.

SqlDataAdapter like a bridge, a database table, a dataset or a DataTable, when the data in the database is populated with a dataset or DataTable can be "ladder", Instead of connecting to a database, you can get data directly from a dataset or DataTable.

SqlDataAdapter provides a number of ways to make it easier for us to manipulate specific sets of data such as populating a query result into a DataTable, or a DataSet that is actually similar to: Create a SqlCommand and then execute " SELECT * FROM [Table] "then execute the Excutereader () method to get a IDataReader object and then read the data line by row and put into a collection object (such as a DataTable) tested, if there is a lot of data manipulation is best to write yourself SqlCommand, will be much faster than SqlDataAdapter operations Database

SqlDataReader can only read the database, and the operation of the table must be in a connected state, but to write to the database, only with the help of the SqlCommand class,SqlDataAdapter it based on the SqlCommand object, it has all the functions of the SqlCommand class, the ability to populate the DataSet object with data, and no longer connect to the database, but can get data directly from the dataset or DataTable. (because it uses a non-connected transfer mode)

The SqlDataReader object can get a read-only, forward-only stream of data from the database, and it can improve application performance and reduce overhead, with only one row recorded in memory at a time .

The SqlDataAdapter object can automatically open and close the database connection automatically (without human management), the main workflow of the adapter: SqlConnection object establishes a connection to the data source, SqlDataAdapter The object is returned to SqlDataAdapterthrough the SqlCommand object, and finally the SqlDataAdapter object is added to the DataTables object of the DataSet object.

Summarize:

Performance:SqlDataReader stores only one row in memory at a time, reducing system overhead. Better than SqlDataAdapter.

READ:SqlDataReader is required to iterate through the data to the specified object by calling its own read () method. SqlDataAdapter can populate the dataset one time by calling the fill () method. You can also resolve changes made to the dataset back to the data source.

On operation:SqlDataReader needs to be disconnected by calling its own close () method. SqlDataAdapter can automatically disconnect after the database has been read.

Microsoft extensions read: https://msdn.microsoft.com/zh-cn/zh-ch/library/ms254931.aspx

The difference between ADO DataReader and DataAdapter