5

Everytime my application runs a stored procedure it does something like this:

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

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

What I wanted to know, if there's a way to know if a connection has already been opened, 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

Improve this question

5 Answers 5

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I Would prefer that answer mentioned that using() does not actually close the connection, but simply releases it back intio ADO.Net Connection pool to be reused...
@Charles - the Close method is specifically called during Dispose() of DbConnection objects. ADO.Net will maintain pooling information, but as far as the client is concerned the connection is closed, which is a good thing.
2

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.

Improve this answer

Comments

2

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

Improve this answer

4 Comments

I DV'd this cause I believe it's insufficient to address OPs lack of knowledge in the topic. Just saying "Don't worry be happy, using will solve all..." does not answer the question or fill in the gaps in the OPs understanding about ADO.Net connection pooling, which lies at the heart of this issue.
@cb: Womp's answer is the best and I upvoted it to indicate that. Had I seen it, I wouldn't have posted my quick and dirty answer.
I've changed the correct answer, don't worry. I actually know about pooling and you're answer was sufficient, as far as i know, but I have to agree that womp's is more complete. thank you.
@Nick, Removed MY DV, due to yr gracious response... @Eduardo, still think that pooling behavior (not using statenment) is the key issue behind the substance of yr question (at least the way you asked it), but this is certainly debatable...
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)

Improve this answer

1 Comment

Thank you for your fast answer. I agree about connection pooling, but I'd like to know if there's a way anyway.
1

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.

Improve this answer

1 Comment

DbBase is a class that I created that manage the datacontexts that we have in the project. thank you

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.