DataReader Vs DataSet?

DataSet and DataReader are two common components of ADO.NET that are used to get and store data in a C# application. Let's learn the difference between the two and when to use a DataSet vs a DataReader.

 

DataReader

1. The ADO.NET DataReader is used to retrieve read-only (cannot update data back to a datasource) and forward-only (cannot read backward/random) data from a database.
2. Using of a DataReader increases application performance and reduces system overheads. This is due to one row at a time is stored in memory. 
3. You create a DataReader by calling Command.ExecuteReader after creating an instance of the Command object. 
4. This is a connected architecture: The data is available as long as the connection with database exists.
5. You need to open and close the connecton manually in code.

The following code statement is used to retrieve rows from a data source.

//opening connection is must
conn.open();
string SQLquery = "SELECT CustomerID, CompanyName FROM dbo.Customers";
SqlCommand cmd = new SqlCommand(SQLquery, conn);
// Call ExecuteReader to return a DataReader
SqlDataReader myReader = cmd.ExecuteReader();
//The Read method of the DataReader object is used to obtain a row from the results of the executed query.
while(myReader.Read())
{
    Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
}
//Once you're done with the data reader, call the Close method to close a data reader:
myReader.Close();
//close the connection
conn.close();

Here are detailed tutorials on DataReader: 

• DataReader in ADO.NET 
• DataReader in C#

DataSet

1. The DataSet is a in-memory representation of data. 
2. It can be used with multiple data sources. That is A single DataSet can hold the data from different data sources holdng data from different databases/tables.
3. The DataSet represents a complete set of data including related tables, constraints, and relationships among the tables. 
4. The DataSet can also persist and reload its contents as XML and its schema as XML Schema definition language (XSD) schema.
5. The DataAdapter acts as a bridge between a DataSet and a data source for retrieving and saving data. 
6. The DataAdapter helps mapping the data in the DataSet to match the data in the data source. 
7. Also, Upon an update of dataset, it allows changing the data in the data source to match the data in the DataSet. 
8. No need to manually open and close connection in code.
9. Hence, point (8) says that it is a disconnected architecture. Fill the data in DataSet and that's it. No connection existence required

The following code statement is used to retrieve rows from a data source.

string SQLquery = "SELECT CustomerID, CompanyName FROM dbo.Customers";
// create DataSet that will hold your Tables/data
DataSet ds = new DataSet("CustomerDataSet");
//Create SqlDataAdapter which acts as bridge to put the data in DataSet,(data is table available by executing your SQL query)
SqlDataAdapter myAdapter = new SqlDataAdapter(SQLquery, conn);
//fill the dataset with the data by some name say "CustomersTable"
myAdapter.Fill(ds,"CustomersTable");

Here is a detailed tutorial on DataSet: DataSet in C#

 

Learn more: 

 

Difference between DataReader, DataSet, DataAdapter, and DataTable in C#