Use DataReader Cleverly

Introduction

ADO.NET DataReader is a read only and forward only cursor that allows you to iterate through the records very fast. In terms of performance DataReader is possibly the fastest way to fetch and iterate the rows. However, if used incorrectly it can cause some troubles. This article throws light on some of such things to watch for while you code and explains general dos and don'ts about DataReader.

Some facts about the DataReader

Before we go ahead to more details it would be nice to highlight some facts about the DataReader.

• DataReader is an ADO.NET class that represents a read only and forward only cursor.
• DataReader classes comes in various flavors based on the underlying data provider. For example, SQL Server data provider has SqlDataReader class that represents DataReader for SQL server.
• DataReader requires an open connection with the database when you are iterating through it.
• This means DataReader do not caches any data unlike classic ADO recordset.
• Since DataReader is very optimized it provides a very fast and efficient way to iterate through the data.

How to use DataReader class?

SqlConnection cnn =
new  SqlConnection
( @" data source=.vsdotnet;initial catalog=northwind;user id=sa " );
SqlCommand cmd =
new  SqlCommand( " select * from employees " ,cnn);
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
... {
Console.WriteLine(dr.GetString(1));
}
dr.Close();
cnn.Close();

 

Above code snippet illustrates the basic use of DataReader class.

• We created an instance of SqlConnection and SqlCommand classes.
• We then called ExecuteReader() method of the command object that returns the DataReader.
• We iterate through the DataReader and print firstname column on the console.
• We then close the DataReader as well as the connection.

In the next sections we will consider certain scenarios in which incorrect use of DataReader can pose problems to your code.

Stored procedures returning records as well as a return value (or output parameters)

In some cases you may have stored procedures that return records as well as a return value. Following is an example,

 
 

  
  

   
   
   
   create
   
    
   
   procedure
   
    GetEmployees

   
   As
   
   

   
   select
   
    
   
   *
   
    
   
   from
   
    employees;

   
   return
   
    
   
   100
   
   ;
  
  
 
 

Here, we are having a SQL Server stored procedure called GetEmployees that returns all the rows from Employees table and also returns some return value (100). Many times such return values are used to indicate some kind of status to the caller.

In order to call above stored procedure you will write code as shown below:

 

SqlConnection cnn =
new  SqlConnection
( @" data source=.vsdotnet;initial catalog=northwind;user id=sa " );
SqlCommand cmd =
new  SqlCommand( " GetEmployees " ,cnn);
cmd.CommandType = CommandType.StoredProcedure;SqlParameter p1 = new  SqlParameter();
p1.ParameterName = " @retval " ;
p1.Direction = ParameterDirection.ReturnValue;cmd.Parameters.Add(p1);
...
...

Here, we added a parameter to the command's Parameters collection. This parameter is for the return value of the stored procedure as indicated by its Direction property. Everything sounds ok till this point. Now, have a look at the code below:

 
 

  
  

   
   
   
   cnn.Open();
SqlDataReader dr
   
   =
   
   cmd.ExecuteReader();

   
   while
   
   (dr.Read())

   
   ...
   
   {
Console.WriteLine(dr.GetString(1));
}
   
   

   
   int
   
    i
   
   =
   
   (
   
   int
   
   )cmd.Parameters[
   
   "
   
   @retval
   
   "
   
   ].Value;
dr.Close();
  
  
 
 

The code looks similar to our previous example. But if you run this code you will get error - "Object reference not set to an instance of object' at the line where we are fetching the return value. The error is caused because we are trying to retrieve the return value before closing the DataReader. I mentioned earlier that DataReader is like an open cursor with the database. Unless the DataReader is closed your stored procedure will not execute further statements and hence the error. Remedy to this is to close DataReader before you retrieve the return value. Note that similar situation will arise in case of output parameters also.

 

...
dr.Close();
int  i = ( int )cmd.Parameters[ " @retval " ].Value;
...

 

Returning DataReader form components

In many n-tier applications the data access code is isolated in a component (class library). In such cases you get the DataReader as a return value of some method of the component. Following code snippet shows one such example.

 

public  SqlDataReader GetEmployees()
... {
SqlConnection cnn=
new SqlConnection
(@"data source=.vsdotnet;initial catalog=northwind;user id=sa");
SqlCommand cmd=
new SqlCommand("select * from employees",cnn);
cnn.Open();
SqlDataReader dr=cmd.ExecuteReader();
cnn.Close();
return dr;
}

Here, you obtain the DataReader as in previous case by calling ExecuteReader() method and then close the database connection. Finally, you return the DataReader to the caller. Previously I mentioned that DataReader requires a "live" connection with the database. If you close the connection as shown above the caller will not be able to iterate through the DataReader and in fact will get an exception.

To resolve above problem the immediate solution that comes to the mind is - not to close the connection and just return the DataReader to the caller. This way the caller will be able to iterate through the DataRedaer but the underlying connection is kept open! This is certainly a dangerous side effect.

Fortunately, DataReader and Command objects provide a built-in way to tackle this situation. Have a look at the code below:

 

public  SqlDataReader GetEmployees()
... {
SqlConnection cnn=
new SqlConnection
(@"data source=.vsdotnet;initial catalog=northwind;user id=sa");
SqlCommand cmd=
new SqlCommand("select * from employees",cnn);
cnn.Open();
SqlDataReader dr=
cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}

Noticed the difference? Here, we have use some different flavor of ExecuteReader() method. This time we are passing an enumerated value CommandBehavior.CloseConnection. This instructs the DataReader than when the caller calls Close() method of the DataReader the underlying database connection is also to be closed. 

 

SqlDataReader dr = Class1.GetEmployees();
while (dr.Read())
... {
Console.WriteLine(dr.GetString(1));
}
dr.Close();  // closes underlying database connection also.

Note that since calling Close() method on DataReader also closes the underlying connection, you need to open the connection again if you want to execute any more queries.

Using multiple queries in a DataReader

DataReder can also be used to return results of multiple queries. Following example shows how:

 
 

  
  

   
   
   
   SqlConnection cnn
   
   =
   
   

   
   new
   
    SqlConnection
(
   
   @"
   
   data source=.vsdotnet;initial catalog=northwind;user id=sa
   
   "
   
   );
SqlCommand cmd
   
   =
   
   

   
   new
   
    SqlCommand
(
   
   "
   
   select * from employees;select count(*) from employees
   
   "
   
   ,cnn);
cnn.Open();
SqlDataReader dr
   
   =
   
   cmd.ExecuteReader();
  
  
 
 

Here, we created a command object with CommandText containing two queries. Note that semicolon (;) is statement separator in SQL server (T-SQL). When you get the DataRedar back you can iterate through these two result sets like this:

 

while (dr.Read())
... {
//code here
}
// shift to the next result set
dr.NextResult()
// iterate again
while (dr.Read())
... {
//code here
}

One of the use of above technique is to retrieve records as well as aggregate functions such as Count, Max or Min.

General recommendations

Considering above pitfalls and features of DataReader (and also from my experience of working with teams) I recommend these things:

• Use DataReader when you want to quickly scan through the data without much of a processing. DataReader is fastest way when you want to iterate through data.
• If you want to return records and also want to have output parameters or return values in stored procedures design such stored procedures carefully. Think of some possible alternative.
• DataReader is fastest when you want to iterate through data and is ideal for data binding with controls such as DataGrid. I have often seen that developers forget to call the Close() method of the DataRedaer. If you are working with big team with novice members, it is  safe to return DataSet instead of DataReader from your components. If you are having strong quality testing and code review standards in place you can stick with the DataReader approach.
• When you are building components and you don't know at design time how and where exactly they are going to be used it is safe to return DataSet instead of DataReder. Passing DataReader across the system especially through too many layers or classes can cause similar issues discussed above.

Summary

In this article we examined various facets of DataReader class. We saw common pitfalls along with possible solutions.