In doing some research about common interview questions for .Net developers I came across the question of what are the difference between DataReader and DataAdapter; as well as when would each be used.  As it turns out, they are quite easy to figure out which one does what with some distinct difference.

DataReader is a forward only read stream that is useful when you are just selecting data from the database.  DataReader is great for when you are just grabbing information to fill out a DataGrid or WebControls in your page.  However, do note that DataReader requires an open connection to the database until the entire select process is complete.  DataReader is good to use when you only need to grab data once and in a read only context as it offers no way to insert/update/delete data in the database.  With DataReader you also need to make sure that you explicitly open and close the database connection.

DataAdapter is similar in that it can be used to select data but it also allows the other data actions of insert/update/delete.  DataAdapter uses a DataSet to handle all of the data and the manipulations to the data.  This is a nice feature in the respect that it uses a disconnected architecture that allows the database connection to remain close until it is absolutely needed.  A nice difference between DataAdapter and DataReader is that DataAdapter handles the opening and closing of the database connection automatically so there is no manual labor to remember.

Some good points to remember not only for using in your applications but also good to remember going into discussion and interview type of scenes.