DataReader, DataAdapter & DataSet - When to use?

ADO.NET offers two central Data Access Components. In this Article we will discuss about their features and when to use what.

ADO.NET provides two central Data Access Components. The excellent thing is that, they are common across all Databases, be it SQL Server or other competitive databases. Its only the namespace to be used, that differs, while using a Database other than SQL Server.

The two Data Access Compnents are:


1. DataSet

2. The .NET data provider, which is a set of components including the Connection, Command, DataReader, and DataAdapter objects.


Its common that the doubt arises on when to use DataReader and DataAdapter, DataSet.

The thumb rule I would suggest is,


1. If your Data access operations is mainly fetching and displaying the records and doesnt involve insert/update/delete statements and other manipulations (forward only, read only) actions, go for the DataReader.


DataReader offers a forward only read stream of access to the records. It is very useful in cases where we just grab the data from the database to display in DataGrid, Label and other Webcontrols.

However, DataReader requires the connection with the database open until its operation is completed.


2. If your Data access operations extend to as simple as an Insert/Update/Delete statement to as complex as using it as a return type from a web service, go for the DataSet. The DataSet has the following advantages.


i. Complex Data type supporting numerous methods such as ReadXML which reads new XML data to form a dataset, WriteXML which provides an XML format of the existing data, etc.,

ii. Works on the disconnected architecutre i.e. The connection doesnt need to be on for the DataSet to perform the manipulations and other actions and is required only at the time of Updating the records to the Database Table.

iii. Provides an In-memory representation of the Data. Can contain one or more DataTable objects that have primary key, foreign key, and constraints between them and can enforce constraints such as unique or be configured to ignore them
iv. Has Individual elements such as DataTable, DataRow and DataColumn to access specific values.

v. While using DataSet and DataAdapter, you don't need to explicitly open the connection. DataAdapter automatically opens and closes the connection as and when required.

Hope it provided some insight into DataReader, DataSet and DataAdapter.

Thanks.

Print | posted on Monday, April 25, 2005 7:54 AM

Comments on this post

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Information you have provided is fabulous
Left by Suman on Mar 10, 2006 10:27 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Great, thanks for the information.
Left by Jon Brune on Mar 24, 2006 6:53 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Provided info is too good, it is explained in a very efficient and simple manner.
Left by Deepika on Apr 07, 2006 3:41 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Plz Describe more about the Data Set and Data Adapter.This is an ecellent article about the data reader and dataset.
Left by Ali Imran Ahmad on May 03, 2006 7:09 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
doubts cleared by this article in a simple as well as greater manner
Left by subburaj on Jun 22, 2006 2:05 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Its really very useful for the beginnners who are all working in ADO.NET
Left by kesavan on Jun 29, 2006 5:04 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Really explained the use of datareader and dataset. Thanks for the information.
Left by Sujith MS on Aug 09, 2006 1:06 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Provided info is too good, it is explained in a very efficient and simple manner.
Left by Pravin P on Jan 16, 2007 8:13 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
The information provided by u, is simly superb because a person with mere knowledge can understand it easily.
Left by konda reddy on Feb 26, 2007 12:56 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Awesome.... Short and sweet information..
Left by sachin on Jan 16, 2008 10:47 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Its very clear and simple,even the new learners can understand it.
Left by gayathri on Mar 08, 2008 12:26 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
its awesum
Left by rakesh on Apr 02, 2008 4:08 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thaks for the great help. It's so clear and understandable.
Left by Athula on May 04, 2008 8:50 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
thanks for the information bro.. Keep it up.
Left by code0910 on May 26, 2008 12:45 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thanks ,this information is very fabulous and easy .
Left by Rabia on Jun 04, 2008 5:10 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
As i am new to .net,the information you had given is very useful for me to proceed.
thank you
Left by kokilaNaresh on Jun 05, 2008 5:36 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thx for the greate help... :-))
Left by Mahesh Khadse on Jul 06, 2008 6:52 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
the information given above is...MAHA FUUDDDUUUUU
Left by Bond James Bond on Jul 25, 2008 12:21 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
GOOD INFORMATION
Left by JATIN 9855366988 on Jul 25, 2008 6:36 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thank you for the help
Left by digish on Aug 11, 2008 3:35 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Good article..
Left by Aniket on Aug 17, 2008 6:04 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
i got to know the diff between them and their functonality after studying this thank u
Left by navnith on Aug 23, 2008 4:25 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Wonderful explanation boss.
Thanks
coolcoder
Left by coolcoder on Sep 06, 2008 1:29 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
All the operation can perform to the Data reader in Database then what is the use to the DataAdapterin connect to the database
Left by Atul Yadav on Sep 17, 2008 3:52 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
thanks for ur clear explanation......
Left by vamc on Nov 14, 2008 5:59 PM

# FREE SİSME BEBEK

Requesting Gravatar...
UU CUCUCUCUCUC
Left by şişme bebek on Nov 20, 2008 1:30 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
i think it is the excellent for New people. in a very easy way.
Left by Adeel Arshad on Jan 26, 2009 2:52 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
It was of a very great help.

Thanks.
Left by Ranganayaki on Feb 06, 2009 12:29 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
thanks very good webpage
Left by şişme bebek on Mar 16, 2009 1:19 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
The explanation is Wow!.
Left by Anup on Mar 21, 2009 12:03 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Extreamlly helpful.. this explanation is so precise and to point, I am blown away.
Left by ITSolutions on May 02, 2009 8:23 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thanks , i am a chinese .
it 's a webpage .
Left by wukai on May 14, 2009 8:06 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Plz Describe more about the Data Set , Data Adapter,DataSet
Left by TOSIF on May 16, 2009 10:44 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Is there any way to fill datadapter by using datareader because if we have more than 1,000,000 records DataAdpter is to low.
Left by Ajn on Jun 01, 2009 1:11 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
much better, but you didnt mention whts use of data adapter, if you can perform everything through datareader and dataset, its mean data adapter only use to connect to database ......
Left by Ily on Jun 03, 2009 6:43 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Awesome piece of information...Thanks
Left by Ajz on Jul 14, 2009 1:42 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
data adapter is used to link dataset & database...
because dataset can not fetch data directly from database...
Left by Devang on Aug 25, 2009 12:32 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thx for this wonderfull information
Left by sarban on Sep 02, 2009 1:17 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thanks Dear Good Artical
Left by Pankaj Pundir on Sep 13, 2009 4:42 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Its good....
Left by Rose on Sep 14, 2009 8:59 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thanks a lot.
Left by Durga on Sep 23, 2009 7:01 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thank u very much to increase my knowledge. Great job
Left by Faheem-Pak on Oct 15, 2009 1:30 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
I´m not sure what´s the different between DataAdapter and Dataset. It´s not clean in the article and I was finding about that. Someone know the difference? Sorry by my english.
Left by wpablow on Nov 22, 2009 3:49 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
great but dataadapter has to be given more precisely
Left by anuraag on Dec 27, 2009 12:47 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
It's is Clear but expect more on wat DataAdapters is.
Left by Tols on Jan 21, 2010 6:15 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Its what I wanted to know in the exact technical words. cool
Left by Kaps on Feb 03, 2010 7:15 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
What is main use of DataReader vs DataAdapter?.... Thanks
Left by Jagadeesh on Feb 09, 2010 9:14 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
That finally helped me understand DataReader, DataSet & DataAdapter a little better. Many thanks.
Left by La Roja on Apr 13, 2010 9:40 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thanks it helped me.....!
Left by Mahesh on May 14, 2010 7:35 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
thnks for the valuable information :)
Left by Janaki on May 18, 2010 12:10 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
good One.. Give a gr8 idea
Left by Saveesh on May 25, 2010 3:39 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
A dataAdaptor will open and close its connection when Fill is executed (as long as the connection was closed when Fill was called). I think this would use the database connection less than looping through the results with datareader Read() and processing the data row. My point is if you need to release your database connection quickly then DataAdaptor may be a better choice.
Left by Darren on Jun 07, 2010 1:04 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Define Very Gud.......But how to use can u give a small program code
Left by Sanket Saxena on Jul 08, 2010 6:20 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thanks a lot. It is a very simple and efficient anser to understand easily..........
Left by Dhiraj singh on Jul 11, 2010 7:13 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Very clear explanation. got some good explanaiton
Left by Tamilarasi on Jul 21, 2010 12:10 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Very brief, simple, clear, and informative. Thanks for sharing those information. More power. Cheers!!!
Left by AL on Aug 06, 2010 3:00 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
thanks for info
Left by amar on Aug 13, 2010 8:35 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Really dam "GOOD".
Left by edwin on Sep 22, 2010 4:20 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
the best topic i have ever seen
Left by rana on Dec 07, 2010 4:56 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Information is good but needs information about data adapter.........
Left by Kavita on Dec 13, 2010 5:02 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Information is tooooooooo good........

Thank u..........
Left by payal on Feb 03, 2011 1:43 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Very nice one. You discussed all points very clear and in simple manner. A candidate who have little knowledge of ado can learn this easily.
Left by Ritesh on Mar 19, 2011 12:24 PM

# re: DataAdapter & DataSet - Insert and other OPeration

Requesting Gravatar...
string sql="insert into tableName values(Variable1,varizble2)";
sqlDataAdapter adp=new sqlDataAdapter(sql,YourConnection);
Dataset ds=new Dataset();
adp.Fill(ds);
Left by Girija(S/w Developer) on May 02, 2011 4:35 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
string sql="insert into tableName values('"+Variable1+"','"+varizble2+"')";
sqlDataAdapter adp=new sqlDataAdapter(sql,YourConnection);
Dataset ds=new Dataset();
adp.Fill(ds);
Left by Girija(S/w Developer) on May 02, 2011 4:40 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
thanx for explaining in such a easier way.....
Left by manoj rathore on Jun 22, 2011 3:36 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thanks, u did a wonderful job..!!
Now got a clear idea of dataset and datareader
Left by Ankit Baria on Jul 28, 2011 12:26 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
it is Excellent....
Left by Megha on Aug 25, 2011 6:39 AM

# DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
good describe friend.
bt i have a problem , how to pass datatable or dataset in sql dataadapter with out pass any sqlquery , i have already value in dataset or datatable.


Left by Avdhesh kumar on Dec 02, 2011 4:12 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Before this i was using these components based on right hand thumb rule but today i came to know the basic difference between these thanks alot
Left by Ritesh on Dec 13, 2011 3:30 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Good article....n very useful..
Left by Vinayak rahate on Jan 06, 2012 5:13 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
information provided is quite good
Left by yamini on Jan 13, 2012 2:19 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
thanks for the information..its was really helpful...
Left by uttam on Jan 17, 2012 1:29 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Not a good article
Left by ded on Feb 23, 2012 5:26 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thanks,Very nice one. You discussed all points very clear and in simple manner. A candidate who have little knowledge of ado can learn this easily.
Left by Draj on Apr 18, 2012 8:09 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
Thanks Very nice one dear.You discussed all points very clear and in simple manner. A candidate who have little knowledge of ado can learn this easily.
Left by Draj on Apr 18, 2012 8:10 AM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
how explaination is nice thank u
give some disadvatage of dataset,data adpater which is useful for everyone...
Left by Manimaran on Jan 01, 2013 6:53 PM

# re: DataReader, DataAdapter & DataSet - When to use?

Requesting Gravatar...
vry nice.easy to understand.Thanks a lot.
Left by Malathy on Oct 10, 2014 2:43 PM

Your comment:

 (will show your gravatar)