Difference between Dataset and Datareader

1) Datareader is forward and read only record set while dataset is both(backward and forward) and editable.

2) Datareader process one row at a time and discard the row if it is already accessed so it is extremely fast in comparison of dataset. And required very less network in comparison of dataset.

3) Datareader is connected and dataset is disconnected architecture.

4) In Datareader you can not get the total number of records using one direct function.

5) In case of large database datareader is best choice in comparison of dataset. Means if you have 2 million row in a table than dataset will not work with it you should use datareader here.

6) If you need result in form of xml than in that case also you can use ExecuteXmlReader rather than using dataset which is extremely slow.

7) We can not fetch the data Randomly with datareader since it is read only and did not store data.

8) Dataset can contain more than one table while datareader can contain only one table.

9) Dataset is just like small database it maintain relationship inside. It is too heavy.It requires a lot of memory space.If data is huge dataset affect the performance of application drastically.

10) To improve the performance of dataset never use command builder to generate the sql statement.

11) Dataset is well suited to communicate remotely as it is disconnected and changes
can be updated whenever it is required.

12) Dataset supports integration with XML whereas Datareader doesn't support.

13) For dataset we use data Adopter for datareader we use Command object.

14) The DataReader has a property named HasRows that return true false based on whether any row exist there in datareader or not. This can be used before Read method.

15) For dataset you have to include System.Data and for datareader you have to include System.Data.SqlClient.

16) Dataset and datareader both can be use with ASP .Net GridView the only difference is datareader does not support paging, you have to write down the code for that. But still datareader is fast than dataset.

17) The DataSet can read and load itself from an XML document as well as export its row set to an XML document. Because the DataSet can be represented in XML, it can be easily transported across processes, over network, or even the Internet via HTTP.

18) Dataset can be serialized while datareader can not thus cannot be passed between physical-tier boundaries where only string (XML) data can go.

19) When you need to do the operation delete,update,insert dataset is best choice.Although Datareader can be use to update the row with the help of separate sql DataAdapter.But it is not as easy as in dataset.

20) When we need operation searching, sorting and filtering dataset is best choice as it can be traverse in any direction.

21) If we have to bound the single server control datareader is best choice if we have to bound more than one control (suppose three) with the same query than dataset is better in case of datareader same query will hit the database three times.

22) Datareader can not be created, filled or traversed with out connection while in other hand dataset can be created manually without a connection to data source.