Invalid attempt to call FieldCount when reader is closed

Question

The error above occurs when I try to do a dataReader.Read on the data recieved from the database. I know there are two rows in there so it isnt because no data actually exists.

Could it be the CommandBehavior.CloseConnection, causing the problem? I was told you had to do this right after a ExecuteReader? Is this correct?

        try
        {
            _connection.Open();
            using (_connection)
            {
                SqlCommand command = new SqlCommand("SELECT * FROM Structure", _connection);
                SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

                if (dataReader == null) return null;

                var newData = new List<Structure>();
                while (dataReader.Read())
                {
                    var entity = new Structure
                    {
                        Id = (int)dataReader["StructureID"],
                        Path = (string)dataReader["Path"],
                        PathLevel = (string)dataReader["PathLevel"],
                        Description = (string)dataReader["Description"]
                    };

                    newData.Add(entity);
                }
                dataReader.Close();

                return newData;
            }
        }
        catch (SqlException ex)
        {
            AddError(new ErrorModel("An SqlException error has occured whilst trying to return descendants", ErrorHelper.ErrorTypes.Critical, ex));
            return null;
        }
        catch (Exception ex)
        {
            AddError(new ErrorModel("An error has occured whilst trying to return descendants", ErrorHelper.ErrorTypes.Critical, ex));
            return null;
        }
        finally
        {
            _connection.Close();
        }
    }

Thanks in advance for any help.

Clare

Answer 1

When you use the Using in C#, after the last } from the using, the Connection automatically close, thats why you get the fieldcount to be closed when u try to read him, as that is impossible, because u want those datas, read then before close the using, or u can open and close manually the connection, by not using the (using)

Answer 2

Your code, as displayed is fine. I've taken it into a test project, and it works. It's not immediately clear why you get this message with the code shown above. Here are some debugging tips/suggestions. I hope they're valuable for you.

• Create a breakpoint on the while (dataReader.Read()). Before it enters its codeblock, enter this in your Immediate or Watch Window: dataReader.HasRows. That should evaluate to true.

• While stopped on that Read(), open your Locals window to inspect all the properties of dataReader. Ensure that the FieldCount is what you expect from your SELECT statement.

• When stepping into this Read() iteration, does a student object get created at all? What's the value of dataReader["StructureID"] and all others in the Immediate Window?

It's not the CommandBehavior.CloseConnection causing the problem. That simply tells the connection to also close itself when you close the datareader.