difference between Dataset and DataReader
Dataset or DataReader?
The DataSet class in ADO.Net operates in an entirely disconnected nature, while DataReader is a connection oriented service.
DataSet is an in-memory representation of a collection of Database objects including related tables, constraints, and relationships among the tables. It provides a consistent relational programming model with multiple data sources from different areas. We can say that the DataSet is a small database because it stores the schema and data in the application memory area. Dataset is used to hold tables with data. You can select data form tables, create views based on table and ask child rows over relations. Also DataSet provides you with rich features like saving data as XML and loading XML data.
DataReader is designed to retrieve a read-only, forward-only stream of data from data sources. DataReader has a connection oriented nature, whenever you want fetch the data from database that you must have a connection. It's usually the most efficient way to deal with records when you don't need any random access. It fetches one row at a time so very less network cost when compare to DataSet. Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader. DataReader is readonly so we can't do any transaction on them. It will be the best choice where we need to show the data to the user which requires no transaction.
The biggest drawbacks of DataSet is speed because it is a high resource consuming process. It carrying considerable overhead because of related tables, constraints, and relationships among the tables. If you need forward only access to query results then DataReader is the best choice because in this scenario it is fastest way to go.