Reading/Writing Data with ADO .NET in C#

In .NET coding there are two main methods of handling database data using ADO.NET. The two methods are distinguished by the classes used to access and manipulate the data. The classes are DataReader and DataSet. I will briefly go over each class and major differences between the two in addition to some examples.

DataReader

The DataReader is the core class for retrieving data from a database in ADO.NET. When using ADO.NET to read data from a database, a DataReader is always used although sometimes implicitly. Even in the case of using a DataSet, the DataAdapter that is used to populate the DataSet uses a DataReader internally.
Now a few things about the DataReader. A DataReader object can merely provide read-only access to a database and only in a forward direction. The DataReader is connection based, meaning the connection to database is maintained while data is read by the DataReader. The DataReader class is efficient and has a relatively small memory fingerprint. If no data writing is required, a DataReader is usually the best choice.

Example

using (SqlConnection conn = new SqlConnection(connectionString))
{
   SqlCommand command = new SqlCommand("SELECT * FROM Categories");
   command.Connection = conn;
   conn.Open();
   SqlDataReader dr = command.ExecuteReader();
}

DataSet

As we have seen above, the DataSet aproach also uses a DataReader. However, the difference is that the DataReader is used to read the data into a DataSet object which retains the data in memory. Once in memory, the data can be manipulated as the programmer wishes. After the data is modified inside the DataSet, it can be written back to a database.
The DataSet is a connectionless object. Once the data is read into the DataSet, the connection to the database is halted. Clearly, using a DataSet to hold records has a memory cost. While in memory, the data can be modified multiple times and when finalized written once to the database. If data memory retention or modification is needed, a DataSet is usually the solution.

Example

using (SqlConnection conn = new SqlConnection(connectionString))
{
   SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Categories",conn);
   DataSet ds = new DataSet();
   ad.Fill(ds);
}