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

Advertisements

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