How does the SQL connection in an application should be called?

Back to basics.

I have an application written in c# and I am using the sqlC开发者_C百科lient to connect to database.

I have several methods and I usually open the connection in a try catch block

try{
    **open connection**
   //Mehod1()
   //Method2()
   ........
 }catch(exception){
   //Do something
 }finally{
  **close connection**
 }

The Problem is that there are a lot connections in pool.

I am using master page and in master page I am loading the menu from database (different menu for each user).

Then in main pages I open again a connection to get the rest data.

In the middle of the page it may be a method that need again to connect to database.

My Question is

Is this a good practise?

Am I doing something wrong?

Is there a better practise to avoid multiple connections? What about singleton pattern?

Thanks in advance

SOLUTION

I found the reason!!!

I had forgot to close a connection.

I was sure that I had close it, but sometimes you can't be so sure.

Thanks everyone for your responses

Since the connection is pooled you don't need to "reuse" it in different methods.

I use the following code:

using(SqlConnection connection = new SqlConnection("your-connectionstring"))
{
  // Do your stuff here...
}

Using is just a short hand way of writting try-catch-finally. It is used for disposable objects.

And this can go into each method.

EDIT: Using the connection from the pool is not hurting performance either. All connection information are cached anyway. So just use the SqlConnection on an atomic level.

It's a good thing though to have the ConenctionString handling in a more generic way...

Using() as said above is a good way to new up a new object of a class that implements IDisposable. But with that being said , you cannot leave you connection open once you done. You have finite number of connection in the pool and leaving a connection unclosed can starve other SPIDs which are waiting for active connection which will finally timeout. So you should

1. Always have atomic and small transactions .
2. Close when done.

There is DAAB (data access application block) from Microsoft Enterprise Library which can be used as helper to open and close connections + do many other DB related tasks easily. Here it is http://msdn.microsoft.com/en-us/library/cc511547.aspx

Probably you didn't dispose your SqlConnections try this:

using (SqlConnection connection = new SqlConnection(connectionString))
{ }

this syntax will call method Dispose() automatically for you. Using statement details here

UPDATE:

A bit more info about this methods you may find here: Close, Dispose

Basically the difference is that method Dispose() called method Close(), but before it is cleaning some resources and removing connection from the pool details here.

As you see Dispose() doing a bit more than Close(). So if you going to reuse connection later use method Close() if not destroy that completely using method Dispose() which is automatically getting called if you using the syntax above.