Check if there's a open conection to database asp.net/c#

Question

Everytime my application runs a storedprocedure it does something like this:

using (DbBase conn = new DbBase())
{      
//call sproc
}

the DBBase() opens the connection with a DataContext from LINQ.

What I wanted to know, if there's a way to know if a connection has already been open, and use that instead of opening a new one. That verification should be done inside the DbBase() constructor that goes like this:

ClientDB = new ClientDBDataContext([ConnectionString from web.config]);

Thank you

Answer 1

I wouldn't worry about it (unless you've profiled it or something). With connection pooling, opening a new connection can be very cheap. If there is a problem then you might want to look at changing the number of connections in the pool (http://www.15seconds.com/issue/040830.htm)

Answer 2

You look at the State property of any DBConnection object, and it will tell you if it's open, closed, connecting, executing, fetching or broken.

By utilizing the using{ } statement though, you're guaranteed that the connection is being closed when the object goes out of scope.

Answer 3

The nice thing about using using is that this is the type of thing you don't need to worry about.

Answer 4

I don't know about DBase, but the Sql Server provider at least already does this for you. It uses connection pooling in the background to re-use existing connections where possible.

Answer 5

With connection pooling in place (The default - unlkess you have explicitly done something to turn it off) this is not an issue. Let the connection pooling code handle this. Closing the connection then, actually only releases it back to the pool to be reused. Only if there are none in the pool will a new one get created (and opened) for you. Good you are using the using statement. This ensures that the conection will be released back to the pool for reuse (NOT closed) as doon as this code snippet is done with it.