ADO.NET Question: When to use DataReader, DataAdapter

Question

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

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 2

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 3

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 4

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.

So the real question is: What kind of storage classes do you want to use in your DAL? DataSets are viable and simple but it's an aging technology (no longer improved).

Maybe you should look around for an ORM (Object Relational Mapping) library. But that will replace your DataReader/Adapter question with a much more complicated choice.

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.

Answer 6

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 7

I guess this question just to talk about proc and cons ,and being off side the code

*Data Reader is much faster than DataAdapter in fetching Data but you have to know what's exactly Disconnected mode

*DataReader or Connected mode and DataAdapter Disconnected mode are being used in the same scenarios but some times Disconnected mode is better in case of you're a way of your data

*But the Disconnectiod mode is provided with rich APIs like DataAdapter ,DataView ,DataTable and DataSet. The powerful thing is you simply provide your DataAdapter with SELECT,INSERT,UPDATE,DELETE Command ,Attach you Data from single table or multiple tables ,with one line of code Adapter.Fill(DataTable) or Adapter.Fill(DataSet) ,and the same way with Updating Data Adapter.Update(DataTable)

*Updating Hierarchical Data in Disconnected mode is far better than working in connected mode which has to use extra code and extra logic to maintain ,in Disconnected mode you have the the ability to update Only Inserted Rows or Updated Rows Or Deleted Rows beside updating operation is wrapped inside Dot Net Transaction Adapter.Update(DataTable.Select("","",DataViewRowState.Added))

*in disconnected mode you got the ability get the versions of every single row in your data ,beside that you can the Changes to your Data DataTable.GetChanges()

*Disconnected mode provide you with stronglyTypedDataSet ,so you get your data definition schema and the relations ,you can get parent and child rows

*Disconnected mode provide method for Getting Rows by PrimaryKey also getting rows with specific criteria DataTable.Select("FilterExpression","SortOrder",DataRowViewState)

*you can make calculation over DataTable and don't disturb your server with calculations like select productID,ProductName,Price,Quantity,price*quantity as Total, you can easily add column with specific criteria (price*quantity)

*you can make aggregations or your snatched DataTable ,DataTable.Compute("Sum(price)","price>250")

*in Disconnected mode you have CommandBuilder which it creates the sqlcommands for you,but its only working with single table