ASP.NET SqlDataReader throwing error: Invalid attempt to call Read when reader is closed.

Question

This one has me stumped. Here are the relative bits of code:

    public AgencyDetails(Guid AgencyId)
    {
        try
        {
            evgStoredProcedure Procedure = new evgStoredProcedure();
            Hashtable commandParameters = new Hashtable();
            commandParameters.Add("@AgencyId", AgencyId);
            SqlDataReader AppReader = Procedure.ExecuteReaderProcedure("evg_getAgencyDetails", commandParameters);
            commandParameters.Clear();

            //The following line is where the error is thrown. Errormessage: Invalid attempt to call Read when reader is closed.
            while (AppReader.Read())
            {
                AgencyName = AppReader.GetOrdinal("AgencyName").ToString();
                AgencyAddress = AppReader.GetOrdinal("AgencyAddress").ToString();
                AgencyCity = AppReader.GetOrdinal("AgencyCity").ToString();
                AgencyState = AppReader.GetOrdinal("AgencyState").ToString();
                AgencyZip = AppReader.GetOrdinal("AgencyZip").ToString();
                AgencyPhone = AppReader.GetOrdinal("AgencyPhone").ToString();
                AgencyFax = AppReader.GetOrdinal("AgencyFax").ToString();
            }
            AppReader.Close();
            AppReader.Dispose();
        }
        catch (Exception ex)
        {
            throw new Exception("AgencyDetails Constructor: " + ex.Message.ToString());
        }
    }

And the implementation of ExecuteReaderProcedure:

    public SqlDataReader ExecuteReaderProcedure(string ProcedureName, Hashtable Parameters)
    {
        SqlDataReader returnReader;

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            try
            {
                SqlCommand cmd = new SqlCommand(ProcedureName, conn);
                SqlParameter param = new SqlParameter();
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                foreach (DictionaryEntry keyValue in Parameters)
                {
                    cmd.Parameters.AddWithValue(keyValue.Key.ToString(), keyValue.Value);
                }

                conn.Open();
                returnReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message.ToString());
            }
        }
        return returnReader;
    }

The connection string is working as other stored procedures in the same class run fine. The only problem seems to be when returning SqlDataReaders from this method! They throw the error message in the title. Any ideas are greatly appreciated! Thanks in advance!

Answer 1

A DataReader is generally connected directly to the database. In this case, when you return from the method, you're returning from inside the using statement that created the SqlConnetion object. That will call Dispose on the SqlConnection, and render the SqlDataReader useless.

Try this:

public SqlDataReader ExecuteReaderProcedure(string ProcedureName, Hashtable Parameters)
{
    SqlConnection conn = new SqlConnection(connectionString);
    using(SqlCommand cmd = new SqlCommand(ProcedureName, conn))
    {
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        foreach(DictionaryEntry keyValue in Parameters)
        {
            cmd.Parameters.AddWithValue(keyValue.Key.ToString(), keyValue.Value);
        }

        conn.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
}

Call it like this:

public AgencyDetails(Guid AgencyId)
{
    evgStoredProcedure Procedure = new evgStoredProcedure();
    Hashtable commandParameters = new Hashtable();
    commandParameters.Add("@AgencyId", AgencyId);
    using(SqlDataReader AppReader = 
        Procedure.ExecuteReaderProcedure("evg_getAgencyDetails", 
                                         commandParameters))
    {
        commandParameters.Clear();

        while(AppReader.Read())
        {
            AgencyName = AppReader.GetOrdinal("AgencyName").ToString();
            AgencyAddress = AppReader.GetOrdinal("AgencyAddress").ToString();
            AgencyCity = AppReader.GetOrdinal("AgencyCity").ToString();
            AgencyState = AppReader.GetOrdinal("AgencyState").ToString();
            AgencyZip = AppReader.GetOrdinal("AgencyZip").ToString();
            AgencyPhone = AppReader.GetOrdinal("AgencyPhone").ToString();
            AgencyFax = AppReader.GetOrdinal("AgencyFax").ToString();
        }
    }
}

At the end of the using statement for AppReader, AppReader.Dispose will be called. Since you called ExecuteReader with CommandBehavior.CloseConnection, Disposing of the reader will also close the connection.

Note that I got rid of your bad exception handling as well. Never use ex.Message except possibly for displaying to end-users. Everyone else will want the full exception. Also, no need to print the method name as part of the exception message if you're allowing the full exception to propagate. The method name will be in the stack trace.