There is already an open DataReader…

Sometimes, when I try to get data from the SQL Server the following exception is loaded:

“There is already an open DataReader associated with this Command which must be closed first.”

This occurs when you have multiple DataReaders open concurrently on the same connection, ie you call SqlCommand.ExecuteReader but don’t close the SqlDataReader returned by this method before calling it again (either on the same command or another command on the same connection).

This is due to a change in the default setting for MARs (Multiple Active Result Sets).  By default, It used to be set as True but it was changed and was set to False by default post RC1.

So to remove this exception, you just need to change the following:

  1. Open your web.config
  2. Find the <connectionStrings part
  3. In your connection string add the following at the end: MultipleActiveResultSets=True;

The final connection string should look like this:

<add name=ConnectionName connectionString=Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True;MultipleActiveResultSets=True providerName=System.Data.SqlClient/>

You shouldn’t have any more problems with MARs now!

Happy Programming 🙂

Resources: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=123691&SiteID=1

Categories

No Responses

Leave a Reply

Your email address will not be published. Required fields are marked *