<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

When to use a DataSet / DataTable / DataReader / Command

Published on
15,646 Points
4,046 Views
11 Endorsements
Last Modified:
Approved
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.
11
Comment
3 Comments
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.
0

Expert Comment

by:pippen
Thanks. Good to know. I'm putting it into practice right now.
0
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.
0

Featured Post

OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Join & Write a Comment

A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…
Key to your CPU's ability to stay cool is to use the right amount of thermal paste and apply it correctly. In other words you want as much thermal conductivity between CPU and the cooling block. Use a quality thermal paste and apply it in a manner…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month