Timeout expired. The timeout period elapsed prior to obtaining a connection First connection

Question

I have this problem. I've googled a lot of sites with this issue, but all of them advice to close all the connection, or just using "using" with connection and dataReader. BUT! My probles is in that I can't open the first connection! I set a breakpoint near connection, and saw, that there are no others connections, so, there is a first one. This problem rized when I remake the class which open connection from static to Singleton, here is the code:

public class Storage
{
private static Storage instance;

public static Storage Instance
{
    get
    {
        if (instance == null)
        {
            instance = new Storage();
        }
        return instance;
    }
}

private Storage()
{
    Manager man = new Manager();
    products = man.LoadProducts();
    components = man.LoadComponents();
    man.LoadProductComponents();
}

public Dictionary<int, Product> Products
    {
        get { return products; }
        set { products = value; }
    }

public Dictionary<int, Component> Components
    {
        get { return components; }
        set { components = value; }
    }

private Dictionary<int, Product> products;
private Dictionary<int, Component> components;

}

and here is a Manager constructor

    public Manager()
    {
        connection = new SqlConnection(@"Persist Security Info=False;User ID=user;Password=pass;Initial Catalog=Products;Server=(local)");
        if (connection.State != ConnectionState.Open) connection.Open();
    }

When exception raises, the connection is Closed. Any one have ideas?

UPDATE:

if I turn off pooling - I have "System.StackOverflowException" in System.Data.dll at the same line.

Personally I'd say that anything that involves a connection fixed in a singleton is inherently a bad idea. But: how many other places are there that load managers? in particular, anything that spins up a few managers will have the side effect of locking up connections with no hope of them closing. — Marc Gravell♦
The problem is in first connection, so the Manager() in one. I tried to make Singleton with lock() but it doesn't help. — user1947702
and is this the only connection / manager instance in the entire application? Does the message end with "from the pool"? — Marc Gravell♦
@MarcGravell Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. — user1947702

Marc Gravell Marc Gravell · Accepted Answer · 2013-02-26T08:21:20

Your Manager class creates and opens a connection:

public Manager()
{
    connection = new SqlConnection(@"Persist Security Info=False;User ID=user;Password=pass;Initial Catalog=Products;Server=(local)");
    if (connection.State != ConnectionState.Open) connection.Open();
}

However, if we look at how you are using it, it is clear that nothing is ever closing this connection:

private Storage()
{
    Manager man = new Manager();
    products = man.LoadProducts();
    components = man.LoadComponents();
    man.LoadProductComponents();
}

I would expect Manager to implement IDisposable, and have the Dispose() method close and release the connection:

class Manager : IDisposable
{
    ...
    public void Dispose()
    {
        if(connection != null) connection.Dispose();
        connection = null;
    }
}

which would then be used via using:

private Storage()
{
    using(Manager man = new Manager())
    {
        products = man.LoadProducts();
        components = man.LoadComponents();
        man.LoadProductComponents();
    }
}

My concern is that your manager is just a single example of a wider problem: of not cleaning up connections after yourself. When the Manager is static, this is probably quite invisible, but when switching to Manager instances, it would be easy to be spinning up multiple Manager objects. Each of this ties up a connection until GC.

Timeout expired. The timeout period elapsed prior to obtaining a connection First connection

1 Answers