none
open DataReader that need to be close. RRS feed

  • Question

  • We are getting the error message:

     

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

     

    However we only create a reader with the 'using' clause like this:

     

    Code Snippet

    using (RobustSqlCommand command = new RobustSqlCommand(get_sections_query, this))

    {

    using (SqlDataReader reader = command.ExecuteReader())

    {

    while (reader.Read())

    {

    byte section_id = reader.GetByte(0);

    string section_name = reader.GetString(1);

    result.Add(new CSSection(section_id, section_name));

    }

    }

    }

     

     

    The RobustSqlCommand looks like this:

     

    The ExecuteReader of RobustSqlCommand looks like this:

     

    Code Snippet

    public SqlDataReader ExecuteReader()

    {

    connector.CheckConnection();

    try

    {

    using (SqlCommand sql_command = new SqlCommand(query, connector.Connection))

    return sql_command.ExecuteReader();

    }

    catch (Exception ex)

    {

    Log.Error("An exception occured while executing: {0}", query);

    Log.Error("Exception was: {0}", ex);

    throw new Exception("External network problem", ex);

    }

    }

     

     

    I already searched the net for the error and some say MARS needs to be activated but that is already the case.

     

    Can anyone shed some light on this problem?  Thanks in advance!!!

    Thursday, June 19, 2008 2:22 PM

All replies

  • Moving to ADO.Net forum.

     

    Thursday, June 19, 2008 7:50 PM
  • Which line does the error occur on?
    Thursday, June 19, 2008 7:53 PM
  • At

     

    return sql_command.ExecuteReader();

     

    Thanks.

    Thursday, June 19, 2008 7:58 PM
  • Can you post any more code? What happens in the ctor for RobustSqlCommand and in the CheckConnection method?
    Thursday, June 19, 2008 8:04 PM
  • Of course!

     

    Code Snippet

    public class RobustSqlCommand: IDisposable

    {

    string query;

    CSConnector connector;

    public RobustSqlCommand(string query, CSConnector connector)

    {

    this.query = query;

    this.connector = connector;

    }

    public SqlDataReader ExecuteReader()

    {

    connector.CheckConnection();

    try

    {

    using (SqlCommand sql_command = new SqlCommand(query, connector.Connection))

    return sql_command.ExecuteReader();

    }

    catch (Exception ex)

    {

    Log.Error("An exception occured while executing: {0}", query);

    Log.Error("Exception was: {0}", ex);

    throw new Exception("External network problem", ex);

    }

    }

    #region IDisposable Members

    public void Dispose()

    {

    }

    #endregion

    }

     

     

    public void CheckConnection()

    {

    if (!Connected)

    {

    TimeSpan TimeSinceLastConnectionAttempt = DateTime.Now - LastConnectionAttempt;

    if (TimeSinceLastConnectionAttempt > ConnectionAttemptsInterval)

    {

    LastConnectionAttempt = DateTime.Now;

    ThreadPool.QueueUserWorkItem(new WaitCallback(delegate(object o)

    {

    try { Connect(); }

    catch (Exception ex) { Log.Error("Could not connect: {0}", ex); }

    }));

    }

    throw new Exception("External network problem");

    }

    }

     

     

    Thursday, June 19, 2008 8:12 PM
  • Can't see anything wrong with the code, have you seen this other post? Seems to be similar to your situation:

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

    When you're stepping through the code, is your connection already open when you get to CheckConnection()?
    Thursday, June 19, 2008 8:33 PM
  • Yes I read that one, that's where it said to make sure to use MARS.

     

    Is there any way in SQL profiler to see if MARS is enabled?

     

    I found this info in profiler:

     

    -- network protocol: TCP/IP
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed

     

    And this is the code to make the connection:

     

    Code Snippet

    SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder();

    cb.InitialCatalog = CSConfig.InitialCatalog;

    cb.DataSource = CSConfig.DataSource;

    cb.UserID = CSConfig.UserID;

    cb.Password = CSConfig.Password;

    cb.IntegratedSecurity = false;

    cb.MultipleActiveResultSets = true;

     

     

    Thursday, June 19, 2008 9:20 PM
  • OK take for granted you are using SQL 2K5? As far as I know MARS is "always on" in SQL2K5, its the connection string that controls whether you are using it or not.

    When you're executing the command, are you doing that via a thread pool method, or is that synchronous code? How much other DB activity is going on? Is it feasible for you to test the code not using a shared connection? (Might be possible for you to change the connector.Connection property to return a new open connection - obviously this would not be a long term solution but it might help identify exactly where the problem is occuring).
    Thursday, June 19, 2008 9:49 PM