Tips on Opening and Closing Connections in ADO.NET

Open connections just before they are needed. Opening them earlier than necessary decreases the number of connections available to other users and can increase the demand for resources.

To keep resources available, explicitly close the connection as soon as it is no longer needed. If you wait for the garbage collector to implicitly clean up connections that go out of scope, the connections are not returned to the connection pool immediately, tieing up resources that are not actually being used.

Close connections inside a finally block. Code in the finally block always runs, regardless of whether an exception occurs. This guarantees explicit closing of connections. For example:

try 
{
    Conn1.Open();
    // Do some other interesting work
}
catch (Exception ex) 
{
    // Handle exceptions
}
finally
{
    //  Close the connection
    if (Conn1 != null)
       Conn1.Close();
}

If you are using connection pooling, opening and closing connections is not an expensive operation. Using the Close() method of the data provider's Connection object adds or returns the connection to the connection pool. Remember, however, that closing a connection automatically closes all DataReader objects associated with the connection.