When to use a DataSet / DataTable / DataReader / Command

Published on
15,451 Points
11 Endorsements
Last Modified:
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.


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.


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.


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.


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.


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.
LVL 40

Author Comment

by:Jacques Bourgeois (James Burger)
Thanks for the good words.

I put code samples when they help understand the discussion. This article is more about the whys of using those classes than the hows, so I do not think that adding code would have helped the reader to understand the concepts better.

Expert Comment

Thanks. Good to know. I'm putting it into practice right now.
LVL 86
Excellent advice, very clear and easy to read. Wish I would have seen this when I was trying to understand how to work with data and such when I was transitioning to .NET.

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Join & Write a Comment

Wrapper-1-Query. Use an Excel function to calculate a column for an Access query. Part 1. Shows a query in Access that has a calculated column with the results of an Excel worksheet function. See how to call a wrapper function from a query, and …
Discover the basics of using Outlook 2016 from office 365.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month