Click here to monitor SSC
  • Av rating:
  • Total votes: 207
  • Total comments: 25
Douglas Reilly

Should you use ADO.NET DataReader or DataSet?

10 June 2005

In the beginning, there was the recordset, a central feature of classic ADO. Like a Swiss army knife, it wasn’t the perfect tool for every job, but given the correct configuration, it could do whatever you needed it to do.

The recordset wasn’t always pretty to watch, however, and it came into prominence at a time when client/server applications for which it was optimized were being replaced by web applications. Its dark side included an amazing level of complexity and the number of special cases when it was used with particular providers.

So while the recordset could do almost any task, most code did not use it correctly—in some cases using inappropriate default values, and in others explicitly setting parameters in ways that were not optimal for the application at hand.

Enter ADO.NET

When I was first introduced to ADO.NET, then called ADO+, I was a little concerned: Calling this new technology by the same name as what we now know as classic ADO would cause no end of confusion. I was also troubled by the lack of scrollable cursors and a number of other features not supported in ADO.NET.

My concerns were well founded. Many classic ADO developers new to ADO.NET were befuddled by the lack of anything called a recordset. They expected a 2.0 sort of upgrade to classic ADO. Instead, ADO.NET was a totally new set of components, and it was optimized for the Internet world.

Access through DataReader

When accessing data with ADO.NET, you can use either a DataReader or a DataSet. I’ll start with the DataReader.

There are a number of data providers, such as SqlClient, included with ADO.NET, and each has its own implementation of the DataReader. All of the DataReader classes implement the IDataReader interface. While having different classes for the DataReader in each data provider might seems troublesome, you can usually specify a parameter or return value as an IDataReader and ignore the specific type of the DataReader. Unfortunately, there are a number of occasions when this is not possible. I’ll go into this a bit later.

So, what is a DataReader? It is a one-way, forward-only method of reading data. A common use of the DataReader to read through a result set would look like the following:

while ( DataReader.Read() )
{
Console.WriteLine("Name: " + DataReader["Name"].ToString();
}

When you get the results into the DataReader, it’s important to note that the "cursor" or logical pointer into the returned data is just before the first row. You need to call .Read() once to read any data. .Read() will return true as long as there is an additional row to be read.

This pattern results in better code than the standard patterns used for classic ADO recordsets. When working with recordsets, the logical pointer is initially placed at the first row of data. To read records you must create a loop that contains a call to .MoveNext at the bottom of the loop. I cannot tell you the number of times I forgot this when working with recordsets in classic ADO. Whenever my application stopped when it should be reading data, I knew I had forgotten or misplaced the .MoveNext call.

A common complaint on the newsgroups related to classic ADO recordsets was the lack of a reliable row count. The availability of a proper row count was dependent on the cursor location and a number of other factors. In the DataReader, even the pretense of a row count is gone. In fact, in the 1.0 implementation, there was no way to know if the DataReader had any data without reading it. And reading the DataReader actually consumed the row. Fortunately, version 1.1 implementations contain a .HasRows property.

Note: There is no IDataReader2 interface that includes the .HasRows property. If you want to use .HasRows, you need to cast it to the implementation class that supports it.

Access through DataSet

Another way to access data with ADO.NET is to use a DataSet. A DataSet is a database-independent, in-memory data store that enables the developer to directly access all rows and columns of one or many tables that a DataSet can contain. The DataSet has a Tables collection of DataTable objects, and the DataTable has a Rows collection that can be indexed to get to a particular row by number. The Rows collection has a .Count property that enables the developer to determine the number of rows in any of the tables in a DataSet.

One thing missing in the classic ADO recordset was the ability to control exactly how an update would take place. The DataSet enables you to update the in-memory copy of the data, and use a DataAdapter specific to a data provider to have those changes persisted back in the database.

If you run a shop where database updates are done using stored procedures, you can use a properly configured DataAdapter to ensure that changes to the database are made using stored procedures. The important thing to remember is that the DataSet is independent of any particular database; the only connection that exists is when the DataSet is used in conjunction with a DataAdapter.

Which to use?

Which of the data access classes should you use in your application?

If you need read-only access to the data, something often done inside an ASP.NET application, using a DataReader makes sense. Unless you are going to persist the data between postbacks, the data that you use to, say, populate a grid, will not be present when someone clicks on one of the rows of that grid.

The ASP.NET application model is that there will be a number of independent requests, and between requests, a client will not maintain a connection to the database. ASP.NET provides session state, a way of maintaining the appearance of a stateful application, but storing a client-specific DataSet in session state is not considered good form and may limit the scalability of your application.

When updates to the data are required, you can either directly execute UPDATE, INSERT or DELETE SQL statements, or you can call a stored procedure. While this seems like more work that just updating data in a DataSet, it gives you more control over exactly how the updates take place, and works very well in a web environment.

One possible disadvantage of using a DataReader is that the connection must be open while you are accessing the data. If you will be doing lengthy processing with each row of returned data, a DataSet might be a better idea. The DataSet can be filled (using a DataReader under the covers, by the way), and then you can use the data in the DataSet at your leisure without consuming a connection.

If you are building a Windows Forms application (or a "Smart Client"), DataSets offer some advantages. Since the application will maintain its state over the entire time it is running, and client context does not tie up server resources, using a DataSet can be very convenient for Windows Forms applications. The model for how it is used is very similar to a classic ADO recordset configured to use client cursor, except that rather than moving a logical cursor, the DataTable’s Rows collection is indexed to reach a particular row.

The dark side of using DataSets is that the data remains in memory for as long as you use it. If you are retrieving thousands or millions of rows, a DataSet is probably not an ideal solution.

Some exceptions…

My predisposition to using DataReaders for ASP.NET applications and DataSets for Windows Forms applications has many exceptions. One of the features of the DataSet that makes it flexible is that it can be easily serialized into XML. It is often convenient to use the .WriteXml method to write out the contents of the DataSet. You cannot write out a single DataTable in a DataSet in the 1.x version of ADO.NET, however.

In addition to writing the DataSet to an XML file, .NET Web Services accepts parameters of type DataSet and return values of type DataSet. While this can be powerful, it does limit your application to .NET consumers of your web service, since the DataSet is not accepted by web services created with any other tools I am aware of. Many people consider it evil to return a DataSet from a web service, but it can be a good idea, especially if you are certain that only .NET consumers will ever need to access your web service.

DataSets can also easily be passed between layers in an application, even if those layers are not in the same process space. For instance, an application server can get the data from a database and then send off the DataSet across process space, or even across a wire to a separate server doing presentation services.

There is one special case where DataSets are essential in an ASP.NET application. When you have data that is accessed by a number of users and can reasonably be cached for at least a short period of time, the cache system (in the System.Web namespace, but actually usable in Windows Forms applications) can be used on a DataSet. Caching can have a dramatic impact on the performance of a heavily used application.

If you are passing results between logical layers inside the same process address space, you can still use DataReaders. How then, you may wonder, do you ensure that the connection is closed? You can’t close it in the method for creating the DataReader, nor can you close it in the method for returning the DataReader since the DataReader will be closed as well.

When you create a DataReader, you call .ExecuteDataReader. This method accepts a parameter that can be CommandBehavior.CloseConnection. This parameter tells the DataReader that when it is closed, the underlying connection should be closed as well. This is an example function that shows how you can return a DataReader and ensure that it is closed by the calling method:

public static IDataReader SelectByRoyalty(int Percentage)
{
SqlDataReader dr=null;
SqlConnection cn=new SqlConnection
("Server=Aron1;Database=pubs;Trusted_Connection=True;");
cn.Open();
try
{
SqlCommand cmd=new SqlCommand("byRoyalty",cn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@Percentage",Percentage);

dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch ( Exception Ex )
{
if ( dr!=null )
{
dr.Close();
cn.Close();
}
throw Ex;
}
return (IDataReader)dr;
}

Then, you could call the method as follows:

IDataReader dr=Coatings.SelectCoating(CoatingID);
try
{
// Use the DataReader..
}
Finally
{
dr.Close();
}

Failure to use a pattern like this will lead to a great deal of difficulty with connections that are not closed in a timely fashion. I believe that Microsoft initially pushed DataReaders as the preferred way to do database access, at least for ASP.NET applications. As time went on, I think the company discovered that many developers misused DataReaders, not properly closing the connection. Recent Microsoft presentations have often emphasized DataSets even for ASP.NET applications.

Conclusion

You should now have a good understanding of the strengths and weaknesses of the two very different ways you can access data using ADO.NET. If you can reliably use a safe code pattern to ensure that the underlying connection will be closed, the DataReader can provide you access to data in a very efficient manner. If maintaining session state is not a problem (like when you are in a Windows Forms application), perhaps a DataSet will be more convenient. As with many developer decisions, there is no clear-cut choice between using a DataSet or a DataReader. Remembering the general rules, as well as the significant exceptions, can help ensure that your application performs and scales well.

Douglas Reilly

Author profile:

The late Douglas Reilly was the owner of Access Microsystems Inc., a small software development company specializing in ASP.NET and mobile development, often using Microsoft SQL Server as a database. He died late in 2006 and is greatly missed by the SQL Server community as one of the industry's personalities.

Search for other articles by Douglas Reilly

Rate this article:   Avg rating: from a total of 207 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: DataSet
Posted by: Anonymous (not signed in)
Posted on: Friday, June 30, 2006 at 12:05 AM
Message: include dataset defferent type of usage

Subject: Really Nice Topic
Posted by: Anonymous (not signed in)
Posted on: Tuesday, July 18, 2006 at 12:52 AM
Message: Really Nice Topic

Subject: How about Excel Interop?
Posted by: Anonymous (not signed in)
Posted on: Saturday, August 12, 2006 at 4:49 PM
Message: When you write datasets in excel s/s cell by sell DataReader is almost as efficient as outsorcing it to india for manual entry. Any thoughts on that?

Subject: datareader
Posted by: Anonymous (not signed in)
Posted on: Monday, August 28, 2006 at 3:02 AM
Message: it is very good. but can u expalin me how execute datareader actually works

Subject: datareader
Posted by: Anonymous (not signed in)
Posted on: Monday, August 28, 2006 at 3:03 AM
Message: can u expalin me how execute datareader actually works.my email is singhparm82@yahoo.co.in

Subject: Closing datareaders
Posted by: Anonymous (not signed in)
Posted on: Thursday, September 21, 2006 at 7:29 AM
Message: Good to see some coverage on a decent way of handling the closing of datareaders. When I started using NET1.1 a few years ago I had great fun with my connection pools filling up due to one missed dr.close() statement!

Subject: Thanks
Posted by: Anonymous (not signed in)
Posted on: Friday, October 06, 2006 at 6:06 AM
Message: good.thanks sir.

Subject: Cool
Posted by: Anonymous (not signed in)
Posted on: Thursday, October 26, 2006 at 6:58 AM
Message: This is Cool Explanation. It really helped me lot,
Thx

Subject: Execute
Posted by: Anonymous (not signed in)
Posted on: Wednesday, November 01, 2006 at 11:51 AM
Message: Nice article indeed, however could you explain
how to use the DataReader with an UPDATE stored Proc.

Can you email your explaination to me please?

vincent.murphy@yahoo.co.uk

Subject: Thank you
Posted by: Anonymous (not signed in)
Posted on: Monday, December 18, 2006 at 11:26 PM
Message: Hi Sir

It's really an excellent explanation.
Thank you very much

Prem
pathuvallil@hotmail.com

Subject: Thanks
Posted by: Anonymous (not signed in)
Posted on: Saturday, January 27, 2007 at 6:20 AM
Message: Sir,
I'm little cleared of ur explanation on DataSet n Datareader. but i need u to say the meaning of both in single words or explain it with the realtime example, so i can understand a better,

Thanking u in anticipation
S.Jayaseelan Vijayan

tryjayaseelan@rediffmail.com

Subject: Goodone
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 15, 2007 at 1:27 PM
Message: Good and useful topic

Subject: important note
Posted by: Anonymous (not signed in)
Posted on: Friday, March 23, 2007 at 5:10 PM
Message: It is important to note that datareader is faster than dataset and this is an important factor in deciding whether to use datareader or dataset.

Subject: a Thing
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 04, 2007 at 2:30 AM
Message: Ok, but how we can get the Count of rows ?!

Subject: good article.
Posted by: bravenewgirl78 (view profile)
Posted on: Saturday, June 09, 2007 at 4:08 PM
Message: Thank you for the good article.

Subject: ?????
Posted by: Anonymous (not signed in)
Posted on: Sunday, July 15, 2007 at 5:28 AM
Message: ????

Subject: not bad
Posted by: ali (not signed in)
Posted on: Sunday, July 15, 2007 at 5:38 AM
Message: what about app.cfg

Subject: good
Posted by: richa (not signed in)
Posted on: Thursday, November 22, 2007 at 11:37 PM
Message: but please explain how exctly it work and what is store in ds.tables(0).row.count(0)

Subject: ok
Posted by: Anonymous (not signed in)
Posted on: Tuesday, February 19, 2008 at 4:27 AM
Message: I didntread c totally.bt please explain clearly how to exicute datareader.

Subject: extraordinary sir.......
Posted by: narayan murthy (not signed in)
Posted on: Friday, March 14, 2008 at 7:04 PM
Message: well organized information

Subject: 88765679
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 23, 2008 at 6:37 AM
Message: 87678

Subject: datareader
Posted by: upen (not signed in)
Posted on: Wednesday, April 23, 2008 at 6:38 AM
Message: using datareader can u explain the custom paging in datagrid

Subject: DataReader or DataSet
Posted by: Ashis Kumar Das (not signed in)
Posted on: Friday, May 30, 2008 at 2:13 PM
Message: but please explain how datareader works in web services.

Subject: Why DataReader is more Fast than DataSet
Posted by: AboOmar (not signed in)
Posted on: Monday, August 04, 2008 at 8:31 AM
Message: Please I need to understand why DataReader has a significant performance improvement than the DataSet ??
How they internally work?

Subject: tv
Posted by: adimaheswara (view profile)
Posted on: Wednesday, December 15, 2010 at 8:07 PM
Message: <%@ Page Language="C#" AutoEventWireup="true" Debug="true" CodeFile="Departments.aspx.cs" Inherits="_Departments" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>List of Departments</title>
</head>
<body>
<form id="frmDepts" runat="server">
<div>


<div style="padding-left:150px;margin-top:50px;">
List Of Departments:
<asp:TreeView ID="tvDept" runat="server" CollapseImageToolTip="Collapse" ExpandImageToolTip="Expand" NodeWrap="true">

</asp:TreeView>
<asp:Label id="lblMsg" runat="server">

</asp:Label>
</div>

<div id="divDetails" runat="server" visible="false" style="padding-left:380px;">
<div>
<b>Employ Details:</b>
<br />
<br />
<asp:Label ID="lblName" runat="server" Text="Employ Name:"></asp:Label>
   
<asp:TextBox ID="txtName" MaxLength="20" runat="server"></asp:TextBox>
</div>
<br />
<div>
<asp:Label ID="lblDesignation" runat="server" Text="Designation:"></asp:Label>
       
<asp:TextBox ID="txtDesignation" MaxLength="20" runat="server"></asp:TextBox>
</div>
<br />
<div>
<asp:Label ID="lblSal" runat="server" Text="Salary:"></asp:Label>
               
<asp:TextBox ID="txtSal" runat="server"></asp:TextBox>
</div>
<br />

<asp:Button ID="btnUpdate" runat="server" Text="UPDATE" OnClick="btnUpdate_Click"/>
<asp:Button ID="btnReset" runat="server" Text="RESET" OnClick="btnReset_Click"/>

</div>
</div>
</form>
</body>
</html>





using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;


public partial class _Departments : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
try
{
if (Request.QueryString["Empid"] != null)
{
divDetails.Visible = true;


}

if (!IsPostBack)
{
GetAllDepartments();
PopulateDetails();
}
}

catch (Exception exp)
{
Response.Write(exp.InnerException);
}
}
public void GetAllDepartments()
{

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());

SqlDataAdapter da ;


DataTable dt = new DataTable();
DataTable dt1 = new DataTable();
conn.Open();
new SqlDataAdapter("select distinct deptnmae,deptid from dept order by deptnmae", conn).Fill(dt);




foreach (DataRow r in dt.Rows)
{


TreeNode treeNode = new TreeNode();
treeNode.Text = r["deptnmae"].ToString();
treeNode.Value = r["deptnmae"].ToString();
treeNode.SelectAction = TreeNodeSelectAction.Expand;
treeNode.ImageUrl = "";

int id =Int32.Parse(r["deptid"].ToString());

new SqlDataAdapter("select empname,empid from emp where deptid="+id+" order by empname", conn).Fill(dt1);

foreach (DataRow r1 in dt1.Rows)
{
StringBuilder sb = new StringBuilder();
TreeNode cn = new TreeNode();
sb.Append("<a href=\"Departments.aspx?" + "Empid=" + r1["empid"].ToString() + "\">" + r1["empname"].ToString() + "</a>");


cn.Text = sb.ToString();
cn.Value = r1["empname"].ToString();
cn.SelectAction = TreeNodeSelectAction.None;
cn.ImageUrl = "";

treeNode.ChildNodes.Add(cn);


cn.CollapseAll();
}

string deptname = r["deptnmae"].ToString();


dt1.Reset();
tvDept.Nodes.Add(treeNode);
tvDept.CollapseAll();
}


}

public void PopulateDetails()
{

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());


DataSet ds = new DataSet();
conn.Open();
string empid = Request.QueryString["Empid"];
SqlDataAdapter da =new SqlDataAdapter("select * from emp where empid=" + empid + "", conn);

da.Fill(ds,"Details");

DataRow r ;
r = ds.Tables["Details"].Rows[0];
txtName.Text =r["empname"].ToString();

txtDesignation.Text = r["designation"].ToString();

txtSal.Text = r["salary"].ToString();

}


protected void btnUpdate_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());


conn.Open();
string empid = Request.QueryString["Empid"];
SqlCommand cmd = new SqlCommand("update emp set empname='" + txtName.Text + "' , designation='" + txtDesignation.Text + "', salary=" + Convert.ToInt32(txtSal.Text) + " where empid=" + Convert.ToInt32(empid) + "", conn);

int i = cmd.ExecuteNonQuery();



}
protected void btnReset_Click(object sender, EventArgs e)
{
txtName.Text = "";
txtDesignation.Text = "";
txtSal.Text = "";

}
}







 

Top Rated

Acceptance Testing with FitNesse: Multiplicities and Comparisons
 FitNesse is one of the most popular tools for unit testing since it is designed with a Wiki-style... Read more...

Acceptance Testing with FitNesse: Symbols, Variables and Code-behind Styles
 Although FitNesse can be used as a generic automated testing tool for both applications and databases,... Read more...

Acceptance Testing with FitNesse: Documentation and Infrastructure
 FitNesse is a popular general-purpose wiki-based framework for writing acceptance tests for software... Read more...

TortoiseSVN and Subversion Cookbook Part 11: Subversion and Oracle
 It is only recently that the tools have existed to make source-control easy for database developers.... Read more...

TortoiseSVN and Subversion Cookbook Part 10: Extending the reach of Subversion
 Subversion provides a good way of source-controlling a database, but many operations are best done from... Read more...

Most Viewed

A Complete URL Rewriting Solution for ASP.NET 2.0
 Ever wondered whether it's possible to create neater URLS, free of bulky Query String parameters?... Read more...

Visual Studio Setup - projects and custom actions
 This article describes the kinds of custom actions that can be used in your Visual Studio setup project. Read more...

.NET Application Architecture: the Data Access Layer
 Find out how to design a robust data access layer for your .NET applications. Read more...

Calling Cross Domain Web Services in AJAX
 The latest craze for mashups involves making cross-domain calls to Web Services from APIs made publicly... Read more...

Web Parts in ASP.NET 2.0
 Most Web Parts implementations allow users to create a single portal page where they can personalize... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.