What's better: DataSet or DataReader?

I just saw this topic: https://stackoverflow.com/questions/2250/datatable-vs-dataset but it didn't solve my doubt .. Let me explain better, I was doing connection with database and needed to show the results in a GridView. (I used RecordSet when I worked with VB6 while ago and DataSet is pretty similar to it so was much easier to use DataSet.) Then a guy told me DataSet wasn't the best method to do ..

So, should I 'learn' DataReader or keep using DataSet ? DataTable ? What are the pros/cons ?

That is essentially: "which is better: a bucket or a hose?"

A DataSet is the bucket here; it allows you to carry around a disconnected set of data and work with it - but you will incur the cost of carrying the bucket (so best to keep it to a size you are comfortable with).

A data-reader is the hose: it provides one-way/once-only access to data as it flies past you; you don't have to carry all of the available water at once, but it needs to be connected to the tap/database.

And in the same way that you can fill a bucket with a hose, you can fill the DataSet with the data-reader.

The point I'm trying to make is that they do different things...

I don't personally use DataSet very often - but some people love them. I do, however, make use of data-readers for BLOB access etc.

It depends on your needs. One of the most important differences is that a DataReader will retain an open connection to your database until you're done with it while a DataSet will be an in-memory object. If you bind a control to a DataReader then it's still open. In addition, a DataReader is a forward only approach to reading data that can't be manipulated. With a DataSet you can move back and forth and manipulate the data as you see fit.

Some additional features: DataSets can be serialized and represented in XML and, therefore, easily passed around to other tiers. DataReaders can't be serialized.

On the other hand if you have a large amount of rows to read from the database that you hand off to some process for a business rule a DataReader may make more sense rather than loading a DataSet with all the rows, taking up memory and possibly affecting scalability.

Here's a link that's a little dated but still useful: Contrasting the ADO.NET DataReader and DataSet.

Further to Marc's point: you can use a DataSet with no database at all.

You can fill it from an XML file, or just from a program. Fill it with rows from one database, then turn around and write it out to a different database.

A DataSet is a totally in-memory representation of a relational schema. Whether or not you ever use it with an actual relational database is up to you.

Different needs, different solutions.

As you said, dataset is most similar to VB6 Recordset. That is, pull down the data you need, pass it around, do with it what you will. Oh, and then eventually get rid of it when you're done.

Datareader is more limited, but it gives MUCH better performance when all you need is to read through the data once. For instance, if you're filling a grid yourself - i.e. pull the data, run through it, for each row populate the grid, then throw out the data - datareader is much better than dataset. On the other hand, dont even try using datareader if you have any intention of updating the data...

So, yes, learn it - but only use it when appropriate. Dataset gives you much more flexibility.

DataReader vs Dataset

1. • DataReader is designed in the connection-oriented architecture
     
   • DataSet is designed in the disconnected architecture
2. • DataReader gives forward-only access to the data
     
   • DataSet gives scrollable navigation to the data
3. • DataReader is read-only we can’t make changes to the data present under it
     
   • DataSet is updatable we can make changes to the data present under it and send those changes back to the data source
4. • DataReader does not provide options like searching and sorting of data
     
   • DataSet provides options like searching and sorting of data

To answer your second question - Yes, you should learn about DataReaders. If anything, so you understand how to use them.

I think you're better of in this situation using DataSets - since you're doing data binding and all (I'm thinking CPU cycles vs Human effort).

As to which one will give a better performance. It very much depends on your situation. For example, if you're editing the data you're binding and batching up the changes then you will be better off with DataSets

DataReader is used to retrieve read-only and forward-only data from a database. It read only one row at a time and read only forward, cannot read backward/random. DataReader cannot update/manipulate data back to database. It retrieve data from single table. As it is connected architecture, data is available as long as the connection exists. DataSet is in-memory tables. It is disconnected architecture, automatically opens the connection and retrieve the data into memory, closes the connection when done. It fetches all the data at a time from the datasource to its memory. DataSet helps to fetch data from multiple tables and it can fetch back/forth/randomly.DataSet can update/insert/manipulate data.