When to use a DataSet / DataTable / DataReader / Command

There is a very standard "mistake" that I see everywhere. I think I do not exagerate if I say that more than half of sample code, questions and answers we see about ADO.NET everywhere do the same mistake. Too many programmers think that you need a DataSet to work with ADO.NET.

This is not True (my VB showing here :-)).

That misconception probably comes from the very first version of the Framework, version 1.0, where the DataSet offered methods that were not available in other classes, such as WriteXml. Very useful one, but you needed a DataSet to be able to store DataTables in an XML file easily. Programmers used DataSet everywhere only to have that extra work done for free.

The framework was refined, and methods such as that one were later added to the DataTable, making it possible to do more with a standalone DataTable. Unfortunately, a lot of articles and books were written that were always using a DataSet as the basis for anything having to do with database, and everybody repeated these same ideas even after they became obsolete.

There are 4 main ways of dealing with data in ADO.NET. Let's make the necessary nuances.

DataSet

A DataSet is a collection of tables linked together through relations. It is useful only when you need relations. When you work with only one table, or with many tables that are not linked together, a DataSet adds extra useless overhead. A DataTable is sufficient.

Simply put, if there is not a DataRelation object in the code, there should not be a DataSet.

DataTable

A DataTable is useful only when you need to get many rows. It requires a DataColumn object for each column and a DataRow object for each row. If you need only one row, once again, this is usually useless overhead. A DataReader is sufficient.

DataReader

A DataReader reads lines one by one. The object containing the line is a DataRecord, an object that is a lot simpler and requires less resources dans the DataRow used by the DataTable.

Since it holds only one line at a time in memory, and since that line is readonly, it offers less features than a DataTable. But by the same token, it requires a lot less resources. So, when you do not need those extra features, why use a DataTable. The DataReader is an ideal tool to read data into controls that are used only for display, such as a ComboBox. It is also useful to populate collections or single objects when you work with your own classes as a data layer.

But a DataReader is useful only when you need at least a complete row. If you need to retrieve only one value, say a count or the ID for a given row, once again, it brings useless overhead. A Command object would then be a better choice.

Command

A Command object, usually through a DataAdapter is used to fill a DataSet and a DataTable, and to post back the data to the database if it has been modified by the application.

But it can also be used alone. Its ExecuteScalar method is the most efficient way retrieve only one value from the database.

It also has a lot of different Execute and BeginExecute methods that can be used in different ways to handle trafic to and from the database, without having to ever use a DataReader, a DataTable or a DataSet.

Conclusion

Do you put your car in a van and the van in a plane when you have to move only 60 miles?

Do yourself, a favor and stop using DataSets and DataTables all the time. Keep them for when they are needed.

Do everybody a favor. Flag any useless use of a DataSet when you see one in a forum question or answer. Then maybe, one day, we will be seeing more efficient code everywhere.