CODEWITHSUNDEEP
sqlconnectionexecutereader
NKD
NKD

Reputation: 1049

ExecuteReader requires an open connection

I am getting the error: "ExecuteReader requires an open connection" and I know the fix is to add a connection.Open() / connection.Close(). My question pertaining to this error is more for me to understand exactly what happen under the hood.

I am currently using the "USING" statement which I expect it to open and close/dispose the connection for me. So I guess I don't understand why it didn't work as expected and I needed to explicitly code the connection.Open() / connection.Close() myself to fix the issue. I did some research and found people experienced similar issue because they were using static connection. In my case, I am creating a new instance of the connection... hence, it bothers me and hoping to get to the bottom of this instead of just fix it and move on. Thank you in advance.

Here is the code:

        try
        {
            using (SqlConnection connection = new SqlConnection(myConnStr))
            using (SqlCommand command = new SqlCommand("mySPname", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                //add some parameters

                SqlParameter retParam = command.Parameters.Add("@RetVal", SqlDbType.VarChar);
                retParam.Direction = ParameterDirection.ReturnValue;
                /////////////////////////////////////////////////
                // fix - add this line of code: connection.Open();
                ///////////////////////////////////////////////// 
                using(SqlDataReader dr = command.ExecuteReader())
                {
                    int success = (int)retParam.Value;
                    // manually close the connection here if manually open it. Code: connection.Close();
                    return Convert.ToBoolean(success);
                }
            }
        }
        catch (Exception ex)
        {
            throw;            
        }

Upvotes: 1

Views: 245

Answers (2)

JustAPup
JustAPup

Reputation: 1780

For the SqlConnection, you have to explicitly open it inside the using block, you just don't need to close it though.

I also notice that you are missing a set of brackets {} around the using SqlConnection. Maybe that's the issue? It should be like this:

 try
    {
        using (SqlConnection connection = new SqlConnection(myConnStr))
        {
        connection.Open();
        using (SqlCommand command = new SqlCommand("InsertProcessedPnLFile", connection))
        {
            command.CommandType = CommandType.StoredProcedure;

            //add some parameters

            SqlParameter retParam = command.Parameters.Add("@RetVal", SqlDbType.VarChar);
            retParam.Direction = ParameterDirection.ReturnValue;
            /////////////////////////////////////////////////
            // fix - add this line of code: connection.Open();
            ///////////////////////////////////////////////// 
            using(SqlDataReader dr = command.ExecuteReader())
            {
                int success = (int)retParam.Value;
                // manually close the connection here if manually open it. Code: connection.Close();
                return Convert.ToBoolean(success);
            }
        }
        }
    }

Upvotes: 0

Ezi
Ezi

Reputation: 2210

Using does not open any connections, it only disposes of any allocated memory after calling End Using.

Upvotes: 2

Related Questions