Executereader Requires an Open and Available Connection. the Connection'S Current State Is Connecting

ExecuteReader requires an open and available Connection. The connection's current state is Connecting

Sorry for only commenting in the first place, but i'm posting almost every day a similar comment since many people think that it would be smart to encapsulate ADO.NET functionality into a DB-Class(me too 10 years ago). Mostly they decide to use static/shared objects since it seems to be faster than to create a new object for any action.

That is neither a good idea in terms of performance nor in terms of fail-safety.

Don't poach on the Connection-Pool's territory

There's a good reason why ADO.NET internally manages the underlying Connections to the DBMS in the ADO-NET Connection-Pool:

In practice, most applications use only one or a few different
configurations for connections. This means that during application
execution, many identical connections will be repeatedly opened and
closed. To minimize the cost of opening connections, ADO.NET uses an
optimization technique called connection pooling.

Connection pooling reduces the number of times that new connections
must be opened. The pooler maintains ownership of the physical
connection. It manages connections by keeping alive a set of active
connections for each given connection configuration. Whenever a user
calls Open on a connection, the pooler looks for an available
connection in the pool. If a pooled connection is available, it
returns it to the caller instead of opening a new connection. When the
application calls Close on the connection, the pooler returns it to
the pooled set of active connections instead of closing it. Once the
connection is returned to the pool, it is ready to be reused on the
next Open call.

So obviously there's no reason to avoid creating,opening or closing connections since actually they aren't created,opened and closed at all. This is "only" a flag for the connection pool to know when a connection can be reused or not. But it's a very important flag, because if a connection is "in use"(the connection pool assumes), a new physical connection must be opened to the DBMS what is very expensive.

So you're gaining no performance improvement but the opposite. If the maximum pool size specified (100 is the default) is reached, you would even get exceptions(too many open connections ...). So this will not only impact the performance tremendously but also be a source for nasty errors and (without using Transactions) a data-dumping-area.

If you're even using static connections you're creating a lock for every thread trying to access this object. ASP.NET is a multithreading environment by nature. So there's a great chance for these locks which causes performance issues at best. Actually sooner or later you'll get many different exceptions(like your ExecuteReader requires an open and available Connection).

Conclusion:

• Don't reuse connections or any ADO.NET objects at all.
• Don't make them static/shared(in VB.NET)
• Always create, open(in case of Connections), use, close and dispose them where you need them(f.e. in a method)
• use the using-statement to dispose and close(in case of Connections) implicitly

That's true not only for Connections(although most noticeable). Every object implementing IDisposable should be disposed(simplest by using-statement), all the more in the System.Data.SqlClient namespace.

All the above speaks against a custom DB-Class which encapsulates and reuse all objects. That's the reason why I commented to trash it. That's only a problem source.

---

Edit: Here's a possible implementation of your retrievePromotion-method:

public Promotion retrievePromotion(int promotionID)
{
    Promotion promo = null;
    var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        var queryString = "SELECT PromotionID, PromotionTitle, PromotionURL FROM Promotion WHERE PromotionID=@PromotionID";
        using (var da = new SqlDataAdapter(queryString, connection))
        {
            // you could also use a SqlDataReader instead
            // note that a DataTable does not need to be disposed since it does not implement IDisposable
            var tblPromotion = new DataTable();
            // avoid SQL-Injection
            da.SelectCommand.Parameters.Add("@PromotionID", SqlDbType.Int);
            da.SelectCommand.Parameters["@PromotionID"].Value = promotionID;
            try
            {
                connection.Open(); // not necessarily needed in this case because DataAdapter.Fill does it otherwise 
                da.Fill(tblPromotion);
                if (tblPromotion.Rows.Count != 0)
                {
                    var promoRow = tblPromotion.Rows[0];
                    promo = new Promotion()
                    {
                        promotionID    = promotionID,
                        promotionTitle = promoRow.Field<String>("PromotionTitle"),
                        promotionUrl   = promoRow.Field<String>("PromotionURL")
                    };
                }
            }
            catch (Exception ex)
            {
                // log this exception or throw it up the StackTrace
                // we do not need a finally-block to close the connection since it will be closed implicitly in an using-statement
                throw;
            }
        }
    }
    return promo;
}

C# ExecuteReader requires an open and available Connection. The connection's current state is closed

The immediate error is that the connection is not open, as it told you; so ... open it; however, there are a lot of other serious problems here - most notably "SQL injection", but also non-disposed objects. You can fix SQL injection by using parameters, and the non-disposed problem with lots of using, but: I strongly suggest you make use of tools that will help you get this right by default. For example:

private void comT_SelectedIndexChanged(object sender, EventArgs e)
{
    if (comT.SelectedIndex != -1)
    {
        using (var conn = new SqlConnection(yourConnectionString))
        {
            string max = conn.QuerySingle<string>(@"
SELECT ISNULL(substring(MAX(tCode),3,2),'00')
FROM Teacher
WHERE dCode = @dCode", new { dCode = comT.SelectedValue.ToString() });
            if (max != null)
            {
                // your parse/etc logic
            }
        }
    }
}

This is:

• moving our connection lifetime to be local to this method, which will stop a lot of connection usage problems
• using "Dapper" to provide the QuerySingle<T> code
• which means you don't need to mess with commands or readers
• adding parameter usage via @dCode and the new { dCode = ... } usage

Note it might look like we've still not opened the connection here, but Dapper is happy to do that for us; if it is given a closed connection, it opens the connection for the duration of the query only.

System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is open

Here's the key bit:

    System.Data.Entity.Core.Objects.DataClasses.RelatedEnd.DeferredLoad() 
at  System.Data.Entity.Core.Objects.Internal.LazyLoadBehavior.LoadProperty[TItem]

Your service is returning Entities with Lazy Loading enabled. Lazy Loading entities have a reference to the DbContext that loaded them, and so can't be accessed by multiple threads, or accessed after the DbContext instance is Disposed. So if you're returning the objects from a controller method for serialization, or caching them for use by other requests, Lazy Loading must be disabled.

You need to turn off lazy loading for entities that will get serialized or cached.

ExecuteReader requires an open and available Connection. The connection's current state is open'

In addition to leppie's answer, you should also be Dispose()ing of any IDisposable types:

        try
        {
            Database.Open(); // Custom class that has our connection string hard coded.

            string query = "SELECT * FROM table"; // (dummy query)

            using (SqlCommand command = new SqlCommand(query, Database.Conn))
            using (SqlDataReader reader = command.ExecuteReader(CommandBehaviour.CloseConnection))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        // Do something with the data.
                    }
                }
            }
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }

Error: ExecuteReader requires an open and available Connection. The connection's current state is open

It may be caused that your connection is not really opened, because when this code is called:

if (_conn.State == ConnectionState.Closed)
      _conn.Open();

Connection state could be: Broken, or Connecting or Fetching (See all enum list).

This may happen if you try to share your connection between many threads. I think you need to create a new connection each time this method is called. You can find many examples how to do it, including MSDN.

EDIT:

There is a great answer for such a question: ExecuteReader requires an open and available Connection. The connection's current state is Connecting

But if you really-really need it, try to prevent using the same connection with two or more threads by using lock (it is wrong actually, see the link above):

lock(_conn)
{
    DataTable dt = new DataTable();
    using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
    {
        sqlDataAdapter.Fill(dt);
    }
}

ExecuteReader requires an open and available Connection - current state is closed

You are specifically asking to close your connection with this call:

Dim rschhWriters As SqlDataReader
rschhWriters = chWriters.ExecuteReader(Data.CommandBehavior.CloseConnection)

So try changing it to just

Dim rschhWriters As SqlDataReader = chWriters.ExecuteReader()

Note: Do favor using your disposable objects with Using - End Using blocks. Your empty Try-Catch is problematic, too, because you are hiding problems that will be difficult to debug later. You want to see any exceptions so you can change your code to prevent them from happening.

---

Related Topics