Issues With IDataReader/DataReader Multiple Results Sets and DataTables

Posted: September 28, 2011 in ASP.Net

One of the issues I recently came across was not getting all the result sets using IDataReader/DataReader with a DataTable. My stored procedure was returning 3 results sets but while carrying out a NextResult() on the reader, it kept returning an error saying the reader was closed!!!.

What I was doing is loading each of the result sets into a DataTable so that I could bind to a GridView control. The problem I found was to be with the command Load which if for the DataTable. What this command does is loads the reader result set into a DataTable. This command will do a command NextResult() for you once the DataTable is loaded!!! This is the old code:

try
{
  Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase DAL = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(MyConnectionString);
  System.Data.Common.DbCommand dbgCommand = DAL.GetStoredProcCommand("MyStoredProcedure");
  IDataReader rdr = DAL.ExecuteReader(dbgCommand);
  try
  {
    // Result set 1
    rdr.Read();
    DataTable dt1 = new DataTable();
    dt1.Load(rdr); // Note this will automatically call the command NextResult() on the reader
    
    // Result set 2
    rdr.NextResult();            
    DataTable dt2 = new DataTable();
    dt2.Load(rdr); // Note this will automatically call the command NextResult() on the reader
    
    // Result set 3
    rdr.NextResult();            
    DataTable dt3 = new DataTable();
    dt3.Load(rdr); // Note this will automatically call the command NextResult() on the reader
  }
  finally
  {
    rdr.Close();
    rdr.Dispose();
  }
}
catch (Exception ex)
{
  ...
}

So the above routine crashes saying the reader is closed and only returns 2 results sets! This is due to the following:

  • The first result set is loaded successfully into a DataTable.
  • Once the DataTable is loaded, the command NextResult() is automatically executed to go to the next result set and since we have NextResult() specified again in the code, the routine will skip the second result set and return the third result set instead.
  • Now that there is no more result sets remaining, when we try and fetch the third result set, an error is returned.

So now to fix the issue we just have to do the following:

try
{
  Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase DAL = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(MyConnectionString);
  System.Data.Common.DbCommand dbgCommand = DAL.GetStoredProcCommand("MyStoredProcedure");
  IDataReader rdr = DAL.ExecuteReader(dbgCommand);
  try
  {
    // Result set 1
    rdr.Read();
    DataTable dt1 = new DataTable();
    dt1.Load(rdr); // Note this will automatically call the command NextResult() on the reader
    
    // Result set 2
    DataTable dt2 = new DataTable();
    dt2.Load(rdr); // Note this will automatically call the command NextResult() on the reader
    
    // Result set 3
    DataTable dt3 = new DataTable();
    dt3.Load(rdr); // Note this will automatically call the command NextResult() on the reader
  }
  finally
  {
    rdr.Close();
    rdr.Dispose();
  }
}
catch (Exception ex)
{
  ...
}

And that is it…you should be able to get all the result sets this way!

Advertisements
Comments
  1. khalid sultani says:

    thank you very much for providing the information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s