Before some time a reader vamsi asked me in my increase asp.net application performance post that why should i prefer data reader over dataset. Both data reader and dataset are usually use to fetch data from the database but both are having different mechanism for fetching data from database. First we looked into that and then we decide in which condition we need to use dataset and in which condition we need to use data reader.
DataReader:
Datareader fetch data one by one from the database. You can have one row(record) at a time in datareader. Once you read next row with read method of the datareader the older record is destroyed from memory and new record will take place instead of it. You can do that only forward only and you can not modify the data. It also require a database connection to stayed open while fetching data. You can use CommandBehavior.CloseConnection property to close connection after data reader finish reading data from database.
Dataset:
You can call dataset a mini database in your memory. Dataset support disconnected architecture as once you fill dataset with data then no connection is required. A database can contain multiple tables also its supports relationship between them. You can insert,update and delete records from dataset and you can update them also in database.
Where i should use dataset over database?
When you are required to display only data then use datareader.Dataset will fetch data once a time. So if you are having millions of records then it will consume your server memory while datareader will have just one record at a time in database. And you need not to display millions of records at time.
Following are the link from which you can find which is best suited for your need.
http://www.simple-talk.com/dotnet/.net-framework/should-you-use-ado.net-datareader-or-dataset/
http://aspnet.4guysfromrolla.com/articles/050405-1.aspx
http://www.netnewsgroups.net/group/microsoft.public.dotnet.framework.aspnet/topic740.aspx
