There are some confusing explanations about data reader and data set. Some people think they are just two exactly same ways of reading data from database but that’s not 100% true.
Lets solve those confusions by understanding the basic needs of both the structures.
Data reader will fetch data from database very fast as compared to data set as it uses forward only structure with one row at a time. It release the record as query executes and does not wait for the entire query to run.
The advantage for data reader is that it stores data in network buffer and gives it when ever requested but for that it always require an active database connection.
Data set is a disconnected mode of data retrieval, It uses data adapter to get data from database and data adapter gets complete data at a time. It waits for the query to execute completely mean while it loads all the data in memory and when query is successful gets data and fill it in data set.
The data set stores all the result data at application level in data tables similar to database table.
The advantage is that there is no need for an active connection once data is filled in data set. All the other operations can be done on the data set.
Data set can be serialized so it can be passed to different tiers easily.
Real Time Example
To be very precise and simple
Data reader is an electricity connection to your home when ever you need it you will switch on and you will start getting electricity but for that you will always require an active electricity connection to your home which will provide you fast access.
On the other hand data set is like a battery which needs to charge once (one time database connection) and then you can use it when ever required even when you don’t have active electricity connection at that time. But carrying battery have a cost and you can not carry battery for every equipment at your home so this is a trade off.