ADO vs DataSet and DataTables

Question

So I didn't see the answer to my question:

Is there a difference (performance or otherwise) between ADO vs. DataSet? If so, which is more common?

Answer 1

ADO is the way you actually get the data.

I think you're mixing things up here.

A Dataset is basically an in memory representation of the data you just pulled and it is okay for smaller result sets, but for anything large it should probably be avoided.

edit:

Also wanted to add, for larger result sets, Generic Lists populated via Datareaders will help you as far as performance goes.

For an example about datasets, I had to rewrite a report which was taking over 9 hours to run. It was a console application that was generating a CSV. Anyway, the programmer loaded 1.4 million records into a dataset, then did a for each loop through each row. Not only did this take close to 10 hours, but it ate up 4 gigs of memory while running.

After removing the dataset the report now runs in under 5 minutes and takes up about 20 megs of memory.

Just an example.

Answer 2

ADO.NET DataReader is faster than using DataSet because it doesn have the overhead of creating a lot of internal objects that a DataSet needs for operating. For example a DataTable can have a cache of it's Data.

However there are some benefits of using DataSet in the right context. If performance is your primary goal then use ADO, otherwise i recommend to use Typed DataSets.

Answer 3

Your question seems a little strange as you're asking for a comparison between ADO vs DataSet (a class within ADO.NET). It would make more sense if you were asking for a comparison of ADO vs ADO.NET or a comparison between ADO's RecordSet object vs ADO.NET's DataSet object. I'll answer as though you've asked in this manner.

Firstly, see here:

Comparison of ADO and ADO.NET

This is a Wikipedia article detailing the major differences between the two technologies.

The single biggest thing about ADO.NET's DataSet is that is can be thought of as a complete in-memory database. Admittedly, it probably mostly gets used to retrieve a single query or table's worth of data, however, it can contain multiple tables and a complete set of relationships and constraints between those tables (just as would exist in the RDBMS itself!). A DataSet is also a disconnected object. That means that data is retrieved from the RDBMS system, the DataSet is populated and the database connection can then be closed. The complete DataSet can then be passed around in memory between functions, classes and components etc. and even have one or more updates applied to it whilst remaining "in memory". The entire DataSet can then be easily persisted back to the original datastore/RDBMS, effectively, "in one go" after re-establishing a database connection. This allows the data to exist in memory for a long time, without having to "hold open" and expensive (and scarce) resource like a database connection.

By comparison, ADO's RecordSet is mostly a representation of a single table (like looking at only one table in a database, rather than the entire database itself). It is also a "connected" object meaning that whilst you have a populated RecordSet in memory, and you're (for example) iterating through it's records, you are still connected to the back-end database. If the work in your program will take some time, you'll be holding that database connection open for (potentially) quite a while.

If you're looking for performance comparison's between the two technologies (ADO vs ADO.NET), you can find one such comparison here:

PERFORMANCE COMPARISON OF MICROSOFT'S COM ADO AND ADO.NET DATABASE CONNECTORS

Answer 4

ADO / ADO.Net is a way to get data in or out of the database.

A Dataset is a way to carry around the data.

They are not mutualy exclusive

Answer 5

ADO is the mechanism/code that retrieves data from the database. Virtually all database send and retrieval operations use ADO.

DataSets and Datatables are mediums( or classes) to transfer data between the database to the point where it's consumed in the application.

Unless it's a throw away applications I avoid DataSets/DataTables. Explanation why.