I like to do as little work as I can when I code, so I used to like the DataSet. It can be filled and ready to go in just 3 lines of code, and then iterated using a nice, simple foreach loop (it’s even easier if you use typed DataSets!). It’s a nice collection to work with. But often, performance is required at the expense of elegance -- especially on a performance-critical Web application. The DataSet actually uses a DataReader to populate itself. A DataReader is a lean, mean access method that returns results as soon as they’re available, rather than waiting for the whole of the query to be populated into a DataSet. This can boost your application performance quite dramatically, and, once you get used to the methodology, can be quite elegant in itself. The Advantages of DataReader in Action To highlight the advantages of using a DataReader over the DataSet, here’s an example of using a DataSet. The following fills a DataSet with the results from a table, and outputs the first field in each row:

SqlConnection conn = new SqlConnection(connectionString); SqlDataAdapter a = new SqlDataAdapter ("select * from mytable;",conn); DataSet s = new DataSet(); a.Fill(s); foreach (DataRow dr in s.Tables[0].Rows) { Console.WriteLine(dr[0].ToString()); } As you can see, we don’t actually start the actual inspection of data (the foreach loop), until the whole DataSet has been filled. There may be occasions where we may not use all our results, or we might execute other code while inspecting (a progress bar’s progress is a trivial example). Using a DataSet, this can only take place after the complete results are fetched and passed into the various collections within the DataSet. In contrast, here’s code that achieves the same results using a DataReader in place of a DataSet: SqlConnection conn = new SqlConnection(connectionString); SqlCommand comm = new SqlCommand("select * from mytable", conn); comm.Connection.Open(); SqlDataReader r = comm.ExecuteReader(CommandBehavior.CloseConnection); while(r.Read()) { Console.WriteLine(r.GetString(0)); } r.Close(); conn.Close(); Here, the inspection is made as soon as data is available by employing the while loop, where r.Read() returns false if no more results are found. Not only can we therefore inspect as we go, but the DataReader only stores one result at a time on the client. This results in a significant reduction in memory usage and system resources when compared to the DataSet, where the whole query is stored.

I like to do as little work as I can when I code, so I used to like the DataSet. It can be filled and ready to go in just 3 lines of code, and then iterated using a nice, simple foreach loop (it’s even easier if you use typed DataSets!). It’s a nice collection to work with. But often, performance is required at the expense of elegance -- especially on a performance-critical Web application. The DataSet actually uses a DataReader to populate itself. A DataReader is a lean, mean access method that returns results as soon as they’re available, rather than waiting for the whole of the query to be populated into a DataSet. This can boost your application performance quite dramatically, and, once you get used to the methodology, can be quite elegant in itself. The Advantages of DataReader in Action To highlight the advantages of using a DataReader over the DataSet, here’s an example of using a DataSet. The following fills a DataSet with the results from a table, and outputs the first field in each row: SqlConnection conn = new SqlConnection(connectionString); SqlDataAdapter a = new SqlDataAdapter ("select * from mytable;",conn); DataSet s = new DataSet(); a.Fill(s); foreach (DataRow dr in s.Tables[0].Rows) { Console.WriteLine(dr[0].ToString()); } As you can see, we don’t actually start the actual inspection of data (the foreach loop), until the whole DataSet has been filled. There may be occasions where we may not use all our results, or we might execute other code while inspecting (a progress bar’s progress is a trivial example). Using a DataSet, this can only take place after the complete results are fetched and passed into the various collections within the DataSet. In contrast, here’s code that achieves the same results using a DataReader in place of a DataSet: SqlConnection conn = new SqlConnection(connectionString); SqlCommand comm = new SqlCommand("select * from mytable", conn); comm.Connection.Open(); SqlDataReader r = comm.ExecuteReader(CommandBehavior.CloseConnection); while(r.Read()) { Console.WriteLine(r.GetString(0)); } r.Close(); conn.Close(); Here, the inspection is made as soon as data is available by employing the while loop, where r.Read() returns false if no more results are found. Not only can we therefore inspect as we go, but the DataReader only stores one result at a time on the client. This results in a significant reduction in memory usage and system resources when compared to the DataSet, where the whole query is stored.

It returns a read-only, forward-only rowset from the data source.  A DataReader provides fast access when a forward-only sequential read is needed.