C# DataSet v DataReader

In C#, a DataSet uses a DataReader to populate itself. By definition, a DataReader is basic and efficient access method to access data and return result as they become available. A DataSet, on the other hand, has to wait for the entire query to process.

The Advantages of the DataReader

Example of code:

 SqlConnection sql= new SqlConnection(connectionString);
 SqlDataAdapter a = new SqlDataAdapter
("select * from mytable;",sql);
 DataSet ds = new DataSet();
 a.Fill(ds);
 foreach (DataRow dr in ds.Tables[0].Rows)
 {
   Console.WriteLine(dr[0].ToString());
 }

The foreach loop does not begin until the DataSet is populated. This means any conditional checks or manipulations must occur in the query or after the DataSet is filled.

A DataReader, on the other hand, looks like the below.

 SqlConnection sql= new SqlConnection(connectionString);
 SqlCommand comm = new SqlCommand("select * from mytable", sql);
 comm.Connection.Open();
 SqlDataReader r =  
     comm.ExecuteReader(CommandBehavior.CloseConnection);
 while(r.Read())  
 {
   Console.WriteLine("Column 1: {0}",r.GetString(0));
 }
 r.Close();
sql.Close();

Here each row is returned into the while loop as it becomes available. while loop until r.Read() returns false. Since the DataReader only stores one row at a time, you can save a significant amount of memory.

When to Use a DataSet

There are a few cases when you must use a DataSet. These include cases where one might serialize the results, pass the result onto another application, or write the results to XML using WriteXML.

Additional Interesting Articles

t-SQL Cursor
PHP All Over Again
Looping Through a SQL Result in C#
SQL Difference Between IS NULL and =NULL
Regex C# HTML
C# Regular Expression Example

©2008 AndrewKimball.com