connection state is closed when using a property

Question

I'm trying to use a Using() statement with a SqlConnection.

I've made the SqlConnection a page property like so...

public SqlConnection baseConnection
{
    get { return new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); }
}

then created the using() statement like so...

using (baseConnection)
            {
                SqlCommand select = new SqlCommand("SELECT * FROM TABLE", baseConnection);
                baseConnection.Open();
                SqlDataReader reader = select.ExecuteReader();

                //... other irrelevant code
            }

But when the code runs I get an exception

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

I understand the exception, what I dont understand, is why is the connection not open when I've opened it with baseConnection.Open() ?

I use this connection all over the site, and I'd like to put it in a custom base class as a page property so I don't have to keep typing it. Is this not allowed?

Answer 1

Problem I find in your example

You get new SqlConnection object each time you request baseConnection property

Problematic code:

public SqlConnection baseConnection
{
    get { return new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); }
}

Corrected Code

private SqlConnection _baseConnection;
public SqlConnection BaseConnection
{
    get { return _baseConnection = _baseConnection ?? new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); }
}

Here return _baseConnection = _baseConnection ?? new SqlConnection() is using Null Coaleasing operator from C# ?? i.e. It will create new instance if _baseConnection variable is null.

My Assumptions
BaseConnection property is defined in some BaseClass and derived class is not extended further in sub-derived class. And in this way you would have one SqlConnection in use per derived class types.