SQL DataReader vs DataAdapter
This page explains when to use DataReader and when to use DataAdapter, and pros and cons of using DataReader and DataAdapter in your code.
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.
