Differences between SqlDataAdapter and SqlDataReader

When I went for an interview last few weeks, the interviewers threw me some technical questions about .NET. Luckily I still can get some correct ideas on those questions. Here I will share with you one of the interview questions "What is the differences between SqlDataAdapter and SqlDataReader?"

For SqlDataReader, you need to call the ExecuteReader method of the SqlCommand object.

You can use SqlDataReader when your data access operations is mainly for fetching and displaying all the records in the database, which does not involve insert, update or delete actions, and other manipulations actions e.g. forward-only and read-only.

If the data from database is mainly for displaying in data grid, labels or other web controls, you can go for SqlDataReader.

However, when the SqlDataReader is in use, it requires the connection with the database remains open until its operation is completed.

In short, SqlDataReader can be used when:
- To deal with large volumes of data
- To optimize data access
- To display data in web controls (no update/insert/delete action)


SqlDataAdapter acts as a bridge between a dataset and database server for fetching and saving data. If your data access operations involve actions other than only displaying data, you can use SqlDataAdapter to generate a dataset or datatable.

By using SqlDataAdapter, you don’t need to explicitly open the connection. It will open and close the connection automatically when required.

One of the advantages is that the dataset works on a disconnected architecture. The connection does not need to be remained for it to perform manipulations or other actions. The connection is only required when there is an update records to the database server.

Hope you can get some ideas about SqlDataReader and SqlDataAdapter.