In .NET, both DataReader and DataAdapter are used to retrieve data from a database, but they work in very different ways and are suited for different scenarios.
This page explains when to use DataReader and when to use DataAdapter, and pros and cons of using DataReader and DataAdapter in your code.
SQL DataReader
A DataReader is a fast, forward-only, read-only stream of data from the database.
Key idea: Reads data row-by-row directly from the database connection.
Example use
SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader["Name"]);
}
Characteristics
• Fastest way to read data
• Forward-only (cannot go back)
• Read-only
• Requires open connection while reading
• Low memory usage
When to use DataReader
Use it when:
• You need high performance
• You are reading large datasets
• You don’t need to modify data
• You process data sequentially
Limitations
• Cannot edit data
• Cannot cache data
• Connection stays open during reading
SQL DataAdapter
A DataAdapter acts as a bridge between database and in-memory data (DataSet/DataTable).
Key idea: It fetches data into memory, disconnects from DB, and optionally writes changes back.
Example
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Users", conn);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(row["Name"]);
}
Characteristics
• Disconnected architecture
• Works with DataSet/DataTable
• Can read and update data
• Opens/closes connection automatically
• Higher memory usage than DataReader
When to use DataAdapter
Use it when:
• You need offline/disconnected data
• You want to modify data locally
• You are working with DataSet/DataTable
• You need caching-like behavior
Advantages
• Works without constant DB connection
• Supports updates back to database
• Easier for UI binding (WinForms, legacy apps)
Disadvantages
• Slower than DataReader
• Uses more memory
• Overhead of DataSet/DataTable
Real-world usage
DataReader is used in:
• High-performance APIs
• Reporting engines
• Real-time data processing
DataAdapter is used in:
• Desktop applications (WinForms)
• Data-bound UI grids
• Offline editing scenarios
More about DataReader and DataAdapter
DataReader
You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader. Using the DataReader can increase application performance both by retrieving data as soon as it is available, and (by default) storing only one row at a time in memory, reducing system overhead.
DataReader allow you to process each record and throw it away, which is good when you want to process a lot of data records with no relation to each other. For example, you might use DataReader when you want to calculate some complex statistic value from every records in the database, or to save a lot of data records into a local file.
This is best used when you just want to fetch data in readony mode, populate your business entity and close the reader. This is really fast.
Say suppose , you are having a customer class and you want to have fully initilized object with all your customer properties filled like( Name,Address etc..)
You will use DataReader here and just populate the entity and close reader.
You cannot do update with datareader.
DataAdapter
A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET Framework data provider to connect to a data source, and it uses Command objects to retrieve data from and resolve changes to the data source.
DataAdapter is capable to let you have data records in the memory. That allows you to make the GUI to browse data, editing data, etc.. It is more general but will not work well with large data set.
You can Read/Update the data with dataadapters but it is less faster when reading the data then Datareader.
You only want to use DataAdapters when you use DataSets.
An Adapter has the 2 main methods Fill() and Updater() to read a Dataset from and write it to the Database.
Note that Fill() will open a Connnection, use a DataReader to get all records and then close the Connetion.
Without Datasets and DataTables you don't have a use for DataAdapters.
Use an ORM instead: NHipernate (good), Linq2SQL (bad), Entity Framework (bad) or one of the other better abstractions.
