DataSet Vs. DataReader

This is a dedicated thread for discussing the SitePoint article ‘DataSet Vs. DataReader

a very simple written and precise article highlighting the differences of datareader and dataset.

Now I know why my boss insists on the DataReader…a short article but sweet

One of the things I tell people all of the time is that if you need some kind of structured data, use your own populated by a reader instead. You can use a DataTable you define or custom objects.

For example, you can create a class with a bunch of properties, and on each read from your reader, create an instance of that class, fill in its properties, then add it to an arraylist. Then you can bind the arraylist to your control, with your control using the names of the properties in the objects. Works like a champ, and it’s much faster.

A very good article, most interesting, i look forward to more lengthy articles from you :slight_smile:

The only problem with relying only on Datareaders is the fact that during the processing of the reader, the connection to the sql server is left open until the processing completes. Being that with any application, you could have multiple readers open performing various actions at any time AND that this application could be used by multiple users, the number of concurrent open connections could become a problem and cause severe performance issues. I found with one app that the performance actually increased by switching some of my datareader operations to datatables instead.

“However, for the majority of queries employed by Web applications, where data is found, displayed, and then forgotten, the DataReader will increase your performance drastically, with only a little extra work. Sounds like a good deal to me!”

Remember, that for Distributed Applications, you (may) need to access your database with a specific account, meaning you’ll have a different security context, meaning you may use COM+ or remoting, meaning you’ll have cross-process, meaning you may (frequently) need to serialize your result set.

My option would be to build your custom and simple serializable class in which you put your data and deliver to the calling process which may be your page :slight_smile:

Anyhow, nice article!


Your analysis doesn’t make sense to me. In web apps, where data is found, displayed and forgotten, the time to retrieve all the data will be the same regardless of datareader or dataset as the dataset internally uses a datareader. As far as the ability to display a row as soon as you fetch it, on the web, again that makes no difference because the page has to completely generate anyway.

The additional memory required by a dataset shouldn’t affect modern applications.

It is more important to note that when we are using DataReader Object connection remains open with the database and Dataset uses completely disconnected architecture.

So if we are doing some extra work in the loop of Datareader then connection will remain open and if that site is continuously
used by many users then performance will surely decrease in the case of DataReader

So though Dataset class is slightly heavier
then datareader class , I think we should go for dataset so that there should not be much talking between Webserver and Database Server.

Also Dataset class is much more powerful as far as handeling of tables , constraints ,suppoprt to Xml ,serialisation
is considered ,it is really better readonly,forwardonly datareader.

There isn’t more talking with a DataReader, not ever. It may take longer if you’re doing some processing on the data, but it won’t do more talking. If you need to get data out quickly and do something with it, I suggest an ArrayList of some kind of container objects (classes intended just to hold your data structure), then manipulate those objects.

A lot more complex to do that though.

More complex how? It takes no time to make a container class for data.

public class MyContainer
  private string _someString;
  public string SomeString
	get { return _someString; }
	set { _someString = value; }
  private int _someInt;
  public int SomeInt
	get { return _someInt; }
	set { _someInt = value; }

There’s your container. Took a minute even at my typing speed. Then the reader…

ArrayList al = new ArrayList();
while (reader.Read())
  MyContainer container = new MyContainer();
  container.SomeString = reader.GetString(0);
  container.SomeInt = reader.GetInt(1);

Mess with the objects in the ArrayList then bind the ArrayList to your data control when you’re done. Not only is it not any more work than doing something similar with a DataTable, it’s faster because your little container class has far less overhead and internal object creation compared to a DataTable.

how to work with field money??? i want to send to a string variable

After seeing a lot of the information put forward by some of the seeming “experts” who drop in on SitePoint now and then, I’ve come to the conclusion that most of them should hang out at a little more often. They could learn a lot (not referring to jeffyjones, freddy, or Zak).

DataSets DO consume more memory, and it gets exponentially larger the more information you pack in. A DataSet is a disconnected view of the database, it isn’t just a few rows and columns, but all the database relationships that go along with the data contained as well. Anyone who tells you differently either has no experience with them or has no concept of what a DataSet really is.

DataReaders will always be faster, as they simply grab the data from the database and spit it back to the calling application. This argument that “the connection for the data reader stays open” is correct, but misguided. A DataSet keeps the connection open as well, and for longer than the data reader when it initially grabs the data.

Custom business objects, if created properly (not hard to do if you know what you’re doing), will always be smaller and more efficient then a monolithic dataset that hangs around in memory. They are also more logically implemented in your code, since they are tailored to your application functionality.

Honestly, having seen some fairly scary implementations of .NET in the business world, I’m inclined to assert that DataSets are great for beginners, but once you hit the varsity level, you’re much better off with a custom business layer and an O/R mapper setup.

Well Chris I would agree with you on certian level. Custom business objects are fine if they work into your architecture.

Let me preface with the fact that I use DataSets quite a bit, I do not consider myself a beginner. Easier is not always better, but similarly its not always worse.

I can think of two reasons why I like to use DataSets, and not that I always use them.

  1. I like the built-in pseudo O/R mapping of the SqlDataAdapter, Especially when working with parent/child relationships. I like to be able to create update insert delete procedures for my tables, and I like to be able to call update on my DataTable, I can let the SqlDataAdapter take care of doing that. Done correctly, you can even retrieve inserted keys and propogate them to child records without any additional trips to the database.

  2. I like Typed DataSets, I like intellisense, I like type-ahead. I like design time support for creating parent/child relationships. I like not having to explicitly mark all my classes and their properties as serializable. I like easily sorting data.

Now what I don’t like about DataSets, I don’t like the overhead, especially with large amounts of data. Agreed. But with ArrayLists of objects, you get no sorting capabilities, you get no indexing capabilities, to find a particular object based on a key in ArrayList requires looping all elements casting to native type and checking a key.

I would suggest Strongly typed collection classes, but these can be a bare to setup and even harder to implement sorting on any field. Not that it can’t be done. If time warrants, its all good.

Just a few of my thoughts on the subject. Most are merely my opinions based on my own experience. I do have an open mind though so I enjoy learning from people who have different views than I.

I also hang out at quite a bit.

Wasn’t targetting my ire towards you at all freddy :slight_smile: (notice the “not referring to jeffyjones, freddy, or Zak” comment). If you can’t afford an O/R mapper, then I suppose Typed DataSets would be a quick way to replicate their functionality, if you can afford the performance hit. However, I’m used to working on projects where we have the opportunity to include the $250 for LLBLGen Pro (a fantastic O/R Mapper, Frans and his team have done amazing things). Given the massive productivity gains from using it for our data access layers, and having it automatically build the baseline for our business objects, I tend to turn my nose up at DataSets - if the reasons for using said DataSets are aligned with the functionality of a mapper.

I knew I might sound defensive, didn’t mean to. Funny I just downloaded the trial for LLBLGen, going to have a look at it. Funny my company wastes 15k a month in rent, wonder if we could benefit from this.

So how about the serializing custom collections. Is it as easy as a datatable?

I agree that DataSets have this ridiculous overhead but unless one is familiar with how to set up a collection class for stongly typed data - that can be very tricky.

I have got not help with doing this at all. All the people who know .NET aren’t interested in providing a descent example of how it’s done.

If anyone can find anything on this please write to me at mark4asp [AT] ntlworld DOT com

import javax.servlet.;
import javax.servlet.http.
import java.math.BigInteger;

public class FibonacciServlet extends HttpServlet {

public void doGet(
HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

//read the query string
int numberOfGenerations = 10;
String generations = request.getParameter("generations");
try {
  numberOfGenerations = Integer.parseInt(generations);
catch (Exception e) { // NumberFormat or NullPointerException
  // use default value of 10 
response.setContentType("text/xml; charset=UTF-8");               
PrintWriter out = response.getWriter();

out.println("<?xml version=\\"1.0\\"?>");  
out.print  ("<?xml-stylesheet ");
 "type='text/css' href='/xml/styles/fibonacci.css'?>");  
BigInteger low  = BigInteger.ONE;
BigInteger high = BigInteger.ONE;      
for (int i = 1; i <= numberOfGenerations; i++) {
  out.print("  <fibonacci index=\\"" + i + "\\">");
  BigInteger temp = high;
  high = high.add(low);
  low = temp;


public void doPost(
HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

doGet(request, response);



SqlConnection con = new SqlConnection(“server=sabdi;uid=sa;pwd=sis;database=emp”);
SqlCommand cmd = new SqlCommand(“select * from employee;”, con);
SqlDataReader r =cmd.ExecuteReader();
while (r.Read())
i want to display the records in page but record are not displayed what is wrong with me