15 main Difference between DataSet and DataReader in asp.net

Introduction: In previous articles i explained the Difference between Delete and Truncate in sql server and Difference between Response.Redirect and Server.Transfer  and Difference between DataSet and DataTable and 20 main differences between Stored procedures and Functions in Sql Server. Now in this article i am going to list main differences between DataSet and DataReader. It is also one of the most  important interview questions.

  Asp.net developer uses DataSet and DataReader to fetch data from the data source while developing asp.net application. But most of them don’t know exactly what are the main difference between DataSet and DataReader and what to use and when to use out of these two.

Both DataSet and DataReader are widely used in asp.net applications for the same purpose i.e. to get/fetch the data from the database. But one has to know the best practices in developing fast, reliable and scalable application. So I have tried to list some main differences between the DataSet and DataReader which are as follows:

DataSet Vs DataReader

1.       DataReader is used to retrieve read-only (cannot update/manipulate data back to datasource) and forward-only (cannot read backward/random) data from a database. It provides the ability to expose the data from database while DataSet is a collection of in-memory tables.
 
2.       DataReader fetches the records from database and stores in the network buffer and gives whenever requests. It releases the records as query executes and do not
      wait for the entire query to execute. Hence very fast as compare to the DataSet which releases the data after loading all the data in memory.

3.       DataReader is like a forward only recordset. It fetches one row at a time so very less network cost compare to DataSet which fetches all the rows at a time i.e. it fetches all data from the datasource at a time to its memory area.

4.       As one row at a time is stored in memory in DataReader it increases application performance and reduces system overheads while there is more system overheads in DataSet as it fetches all the data from the datasource at a time in memory.

5.       As DataReader is forward only, we can’t fetch random records as we can’t move back and forth .While in DataSet we can move back and forth and fetch records randomly as per requirement.

6.       DataReader fetches data from a single table while DataSet can fetch data from multiple tables.

7.       As DataReader can have data from a single table so no relationship can be maintained while relationship between multiple tables can be maintained in DataSet.

8.       DataReader is read only so no transaction like insert, update and delete is possible while these transactions are possible in DataSet.

9.       DataSet is a bulky object that requires lot of memory space as compared to DataReader .

10.   DataReader is a connected architecture: The data is available as long as the connection with database exists while DataSet is a disconnected architecture that automatically opens the connection, fetches the data into memory and closes the connection when done.
11.   DataReader requires connection to be open and close manually in code while DataSet automatically handles it.

12.   DataSet can be serialized and represented in XML so easily passed around to other tiers but DataReader can't be serialized.

13.   DataReader will be the best choice where we need to show the data to the user which requires no manipulation while DataSet is best suited where there is possibility of manipulation on the data.

14. Since DataSet can be serialized it, can be used in wcf services  and web service that will return retrieved data. But DataReader can’t be serialized so can’t be used in wcf services and web services.

15. When you need to navigate through the data multiple times then DataSet is better choice e.g.  we can fill data in multiple controls But DataReader can only be read once so it can be bound to a single control and requires data to be retrieved for each control.

Now over to you: 
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned for more technical updates."
Previous
Next Post »

20 comments

Click here for comments
Anonymous
admin
September 10, 2013 ×

Your way describing content is very impressive and gives deep knowledge. please upload more topics

Reply
avatar
September 10, 2013 ×

Thanks for appreciating my articles and alsothanks for the suggestions. Keep reading for more technical updates.

Reply
avatar
Anonymous
admin
September 12, 2013 ×

Good one. Easy to understand and it will be remain in mind.

Reply
avatar
September 12, 2013 ×

thanks for appreciating my work..keep reading

Reply
avatar
Anonymous
admin
September 26, 2013 ×

thanq so much for your information

Reply
avatar
September 27, 2013 ×

your welcome...stay connected for more useful updates like this..:)

Reply
avatar
Anonymous
admin
December 11, 2013 ×

Is there a way to do a read and close the sql connection? DataReader is just a read but needs SQL Connection to be open. If you do not have connection open then you get the message
InvalidOperationException: Invalid attempt to call HasRows when reader is closed.

Reply
avatar
December 29, 2013 ×

some repetitions occurs but anyway it is a good article

Reply
avatar
December 29, 2013 ×

thats because datareader is a connected architecture its require an open connection for data retreival

Reply
avatar
December 29, 2013 ×

Thanks Muhammad for appreciating this article..:)

Reply
avatar
Unknown
admin
December 29, 2013 ×

Very Clear serialization between DataReader & DataSet ... :-)

Reply
avatar
January 01, 2014 ×

Thanks Santhosh M,
i am glad you liked this article..:)

Reply
avatar
January 08, 2014 ×

thank!!!, very good article...

Reply
avatar
January 08, 2014 ×

your welcome Juan Jose Lucero Goicochea..and thanks for appreciating my work..stay connected and keep reading.:)

Reply
avatar
Anonymous
admin
April 13, 2015 ×

Very beautifully explained all the differences between the two...indeed a very helpful article!!

Reply
avatar
April 13, 2015 ×

thanks for your valuable feedback..It is always nice to hear that my articles helped someone..stay connected and keep reading for more useful updates..:)

Reply
avatar
Anonymous
admin
November 06, 2015 ×

Very nice
jotdeep

Reply
avatar
November 20, 2015 ×

Thanks jotdeep for your valuable comment..Stay connected and keep reading for more useful updates.

Reply
avatar
Viplavi
admin
September 03, 2019 ×

Very well explained and elaborated and many points are covered.. Satisfied with the information👍

Reply
avatar
September 09, 2019 ×

thanks for your valuable comment..keep reading

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..