When to use dataset & data reader

When we need to work with few tables and most of the time you need to execute queries on these tables, you should go for the dataset.

Disconnected data access is suited most to read only instances and disconnected data access architecture is not designed to be used in the networked environment where multiple users are updating data simultaneously and each of them needs to be aware of current state of database at any time.


DataReader is more useful when you need to work with large number of tables, Database in non-uniform pattern and you need not execute the large no. of queries on few particular table.


It also depends on the nature of application. If multiple users are using the database and the database needs to be updated every time, you must not use the dataset. For this, .Net provides the connection oriented architecture.
But in the scenarios where instant update of database is not required, dataset provides optimal performance by making the changes locally and connecting to database later to update a whole batch of data. This also reduces the network bandwidth if the database is accessed through network.