Understand & Reproduce ExecuteReader InvalidOperationException ("The connection's current state is open")

Question

I have received a report from a user that they got this exception while using my application:

ExecuteReader requires an open and available Connection. The connection's current state is open.
                     |  System.InvalidOperationException
                     |     at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)
                     |     at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
                     |     at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
                     |     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
                     |     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
                     |     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
                     |     at System.Data.SqlClient.SqlCommand.ExecuteReader()
                     |     at MyProject.Database.GetData(DataType data, SqlConnection& _conn)

However, I can't seem to reproduce this exception, in fact I don't even know what it means: i.e. If the connection's current state is open, then what's the problem? Why the exception?

I've tried stepping through the code, with the connection's state being open, but it just reads the data correctly. Indeed, the Microsoft Docs seem to suggest this would happen if the connection is closed, but as you can see from the exception message, it's open.

Here is the relevant code:

    private int GetData(DataType data, ref SqlConnection _conn)
    {
        string sqlStr = string.Format("select top 1 * from dbo.DataTable where [Id] = N'{0}'", data.Id);

        using (SqlCommand cmd = new SqlCommand(sqlStr, _conn))
        {
            SqlDataReader rs = null;
            try
            {
                if (_trans != null) cmd.Transaction = _trans;
                rs = cmd.ExecuteReader();

                if (rs == null)
                    return DB_ERROR;

                int attI = rs.GetOrdinal("FileData");
                int fileNameI = rs.GetOrdinal("FileName");

                while (rs.Read())
                {
                    dataFile.FileData = (byte[]) rs.GetValue(attI);
                    dataFile.FileName = DBCommon.ColStr(rs, fileNameI);
                }

                rs.Close();
                rs = null;
                return DB_OK;
            }
            catch (Exception e)
            {
                LogException(e);
                return DB_ERROR;
            }
            finally
            {
                if (rs != null)
                    rs.Close();
                rs = null;
            }
        }
    }

While debugging, I examine the _conn.State property, check that it's Open, then step through the rs = cmd.ExecuteReader(); line, but it processes fine.

It should be noted that this is a very large application with many tables, each with their own method similar to the one above, which all refer to that _conn object. Restructuring it would not be feasible.

Any other questions have answers on how to prevent it, but I'm trying to reproduce it, and understand why exactly it's happening.

A method that gets a `SqlConnection` passed in, rather than creating one itself, is always suspect. Any chance you're getting a connection object that's used from multiple threads? Few operations on ADO.NET operations are thread-safe. This code has other warning signals (like not using `using` for the reader, catching `Exception`, using error return values and vague "helper methods that make things easier"), but let's ignore those for the moment. — Jeroen Mostert, Nov 27 '19 at 14:35
Even more worrisome is that the parameter is declared `ref`. This method doesn't replace the value, but are there any that do? That makes tracking what connection is actually getting used even more perilous. If the `ref`-ness isn't used anywhere it may just be a quirk of the author not understanding how reference types work, or someone used to classic VB (where the pass by value/reference distinction was much more explicit). — Jeroen Mostert, Nov 27 '19 at 14:43

Understand & Reproduce ExecuteReader InvalidOperationException ("The connection's current state is open")

0 Answers0