I’d like to find out the best way to handle data query. In ASP.NET, SQL query plays vital roles. But to work with Data Table, which one is better? Dataset or Datareader?
Actually the choice is yours.
According to the situation it changes. Sometimes you may find the first one better, but for a bigger application involving many DataTables, Dataset is a better choice, I think.
You are the best judge, still I’ll quote from a great book
ASP.NET 3.5 SP1 Edition
In C# and VB
Now read what they have to say on this great debate:
Deciding When to Use a DataSet
As revolutionary as a DataSet might be, it is not the best choice in every situation. Often, it may not be
appropriate to use a DataSet; instead it might be better to use a DataReader.
With ADO 2.6, it was possible to perform a command upon a data store and get back a single
collection of data made up of any number of rows. You could then iterate through this collection of
data and use it in some fashion. Now ADO.NET can use the DataSet to return a collection of data
that actually keeps its structure when removed from the data store. In some situations, you benefit
greatly from keeping this copy in its original format. By doing so, you can keep the data disconnected in
an in-memory cache in its separate tables and work with the tables individually or apply relationships
between the tables. You can work with the tables in much the same manner as you do with other relational
data sources — using a parent/child relationship. If it is to your advantage to work with certain
data with all its relationships in place (in order to enforce a parent/child relationship upon the data); in
this case, of course, it is better to use a DataSet as opposed to a DataReader.
Because the DataSet is a disconnected copy of the data, you can work with the same records repeatedly
without having to go back to the data store. This capability can greatly increase performance and lessen
the load upon the server. Having a copy of the data separate from the data store also enables you to
continuously handle and shape the data locally. For instance, you might need to repeatedly filter or sort
through a collection of data. In this case, it would be of great advantage to work with a DataSet rather
than going back and forth to the data store itself.
Probably one of the greatest uses of the DataSet is to work with multiple data stores and come away with
a single collection of data. So for instance, if you have your Customers table within SQL and the orders
information for those particular customers within an Oracle database, you can very easily query each
data store and create a single DataSet with a Customers and an Orders table in place that you can use in
any fashion you choose. The DataSet is just a means of storage for data and doesn’t concern itself with
where the data came from. So, if you are working with data that is coming from multiple data stores, it
is to your benefit to use the DataSet.
Because the DataSet is based upon XML and XML Schemas, it is quite easy to move the DataSet
around — whether you are transporting it across tiers, processes or between disparate systems or
applications. If the application or system to which you are transferring the DataSet doesn’t understand
DataSets, the DataSet represents itself as an XML file. So basically, any system or application that can
interpret and understand XML can work with the DataSet. This makes it a very popular transport
vehicle, and you see an example of it when you transport the DataSet from an XML Web service.
Last but not least, the DataSet enables you to program data with ease. It is much simpler than anything
that has been provided before the .NET Framework came to the scene. Putting the data within a class
object allows you to programmatically access the DataSet. The code example in Listing 8-12 shows you
just how easy it can be.
SqlConnection conn = new SqlConnection
SqlDataAdapter da = new SqlDataAdapter("Select * from Customers", conn);
DataSet ds = new DataSet();
Basically, when you work with data, you have to weigh when to use the DataSet. In some cases, you
get extreme benefits from using this piece of technology that is provided with ADO.NET. Sometimes,
however, you may find it is not in your best interests to use the DataSet. Instead, it is best to use the
The DataSet can be used whenever you choose, but sometimes you would rather use the DataReader
and work directly against the data store. By using the command objects, such as the SqlCommand and the
OleDbCommand objects, you have a little more direct control over what is executed and what you get back
as a result set. In situations where this is vital, it is to your advantage not to use the DataSet.
When you don’t use the DataSet, you don’t incur the cost of extra overhead because you are reading
and writing directly to the data source. Performing operations in this manner means you don’t have to
instantiate any additional objects — avoiding unnecessary steps.
This is especially true in a situation when you work with Web Forms in ASP.NET. If you are
dealing with Web Forms, the Web pages are re-created each and every time. When this happens, not
only is the page re-created by the call to the data source, the DataSet is also re-created unless you are
caching the DataSet in some fashion. This can be an expensive process; so, in situations such as this, you
might find it to your benefit to work directly off the data source using the DataReader. In most situations
when you are working with Web Forms, you want to work with the DataReader instead of creating