ADO.NET Question: When to use DataReader, DataAdapter

Question

Hi guys, i just wondering, what things i have to consider when using DataReader and DataAdapter in fetching data from the database and whats the difference between this two other the datareader needs open connection and the datadapter does not... In our projects, were using DataReader in ALL our DAL, we never use dataadapter. So I wondering what scenario would it been better to use DataAdapter + Datatable combo than using DataReader. Thanks in advance.

Answer 1

I almost always favor the DataReader when doing ADO.NET stuff as well; the reason being, it does not force you to store the data on the client any longer than you must.

That's also somewhat the answer to when to use a DataAdapter to a DataSet/Table; when you want to store the data on the client, perhaps to work with it somehow - iterating back and forth through it, or operating on it as a set, as opposed to simply outputting the values into a grid, where the Reader, IMO, is a better option.

Answer 2

DataReader : 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 : You can Read/Update the data with dataadapters but it is less faster when reading the data then Datareader.

You can update the data with DataAdapter but with reader you won't

Answer 3

what things i have to consider when using DataReader and DataAdapter

DataReader: Good low level interface. PRetty much the ONLY interface - if you load data into higher up structures, the actual load is always done using a DataReader.

DataAdapter / DataSet: stuff not used by people who like structured p rograms and nice code and do not just happen to write a reporting applcation. Use an ORM instead - NHipernate (good), Linq2SQL (bad), Entity Framework (bad) or one of the other better abstractions.

Answer 4

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.

DataAdapter is something else, it 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.

Answer 5

I never use DataReader.

Since I strongly layer my application, my DAL is responsible for talking to the database and my BLL is responsible for building objects, there's no way for the BLL to close the DataReader when it's done. Instead the BLL requests a DataSet/DataTable from the DAL, which the DAL fulfills. It does this by performing a Fill (to TomTom's point > look at the stack trace and yes, you will see a DataReader in there). The BLL then does what it likes with the result set.