There is already an open DataReader associated with this Connection which must be closed first?

Question

I am facing the DataReader open connection issue in ASP.NET c# While Reading the records from MySQL database. I do not know how at first time of execution of datareader it showing me open connection.

The error as below at DAL

MySqlDataReader reader = myCommand.ExecuteReader();

There is already an open DataReader associated with this Connection which must be closed first

Sample Code

// BAL function call the DAL function 

MainFunction()
{
...
...
  using (MySqlDataReader reader = clsDAL.GetALL(SqlQuery, Parms))
            {
                while (reader.Read())
                {
                    clsVitalsDisplay obj = new clsVitalsDisplay();

                    obj.BySessionId = Convert.ToInt32(reader["BySessionId"].ToString());
                    obj.ByDevSessionId_LifeTouchHeartRate = Convert.ToInt32(reader["ByDevSessionId_LifeTouchHeartRate"].ToString());
                    obj.HumanReadableDeviceId_LifeTouch = reader["HumanReadableDeviceId_LifeTouch"].ToString();

                    listLifetouchHeartRate.Add(obj);
                }
            }
}

// Getting the Error while Read the details from database in DAL (Data Access Layer) class at MySqlDataReader reader = myCommand.ExecuteReader();

 public static MySqlDataReader GetALL(String _query, MySqlParameter[] sqlParameter)
    {
        MySqlCommand myCommand = new MySqlCommand();

        try
        {
            myCommand.Connection = OpenConnection();
            myCommand.CommandText = _query;
            myCommand.Parameters.AddRange(sqlParameter);
            myAdapter.SelectCommand = myCommand;
//Face the error at below line
            MySqlDataReader reader = myCommand.ExecuteReader();
            myCommand.Dispose();
            return reader;
        }
        catch (MySqlException ex)
        {
            ErrorLog.ErrorLog.Log_Err("", ex, Global.gUserId.ToString());
            return null;
        }
        finally
        {

        }
    }

ASP.NET and this exception sounds suspiciously: http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren/9707060#9707060 In short: trash `clsDAL` and use the `using`-statement. I guess your connection is `static`, isn't it? — Tim Schmelter, Jun 17 '15 at 12:06
You cannot open more than one reader with same connection. You are not closing the reader anywhere in this code. I suspect you OpenConnection() function is returning the same connection instance. You must close the reader at the end of you method. You might be creating one in some previous function calls. — wonderbell, Jun 17 '15 at 12:10
Yes its a STATIC connection and I retrun type of the function id static MySqlDataReader . public static MySqlDataReader GetALL(String _query, MySqlParameter[] sqlParameter) So I can not use the using - statemnet because of Return type — skt, Jun 17 '15 at 12:11
@aSharma it is inside ``using`` block so no need to explicitly close it — Ehsan Sajjad, Jun 17 '15 at 12:14
@EhsanSajjad yes it is inside using, but the instance is created in GetAll function, so the scope of this reader doesn't end with using. — wonderbell, Jun 17 '15 at 12:16

score 0 · Answer 1 · answered Jun 23 '15 at 09:07

I have fixed this issue by closing the already opening adatabase connection if open as below. Afterthat its working fine.

public static MySqlDataReader GetALL(String _query, MySqlParameter[] sqlParameter)
    {
        MySqlCommand myCommand = new MySqlCommand();

        try
        {
             if (con.State == ConnectionState.Open)
                {
                con.Close();
                }

            myCommand.Connection = OpenConnection();
            myCommand.CommandText = _query;
            myCommand.Parameters.AddRange(sqlParameter);
            myAdapter.SelectCommand = myCommand;

            MySqlDataReader reader = myCommand.ExecuteReader();
            myCommand.Dispose();
            return reader;
        }
        catch (MySqlException ex)
        {
            ErrorLog.ErrorLog.Log_Err("", ex, Global.gUserId.ToString());
            return null;
        }
        finally
        {

        }
    }

There is already an open DataReader associated with this Connection which must be closed first?

1 Answers1