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

Answer 1

Probably you didn't dispose your SqlConnections try this:

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

this syntax will call dispose automatically for you.

Answer 2

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...

Answer 3

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