Fall 2007

From:

 

 

And I Quote:

 

"1. If you are running in a multithreaded environment then you do not want
multiple threads executing commands on the same connection, since this could
cause exceptions to be thrown if they both try to utilize the connection at
the same time.

2. Connections should not be kept open for too long, by keeping this
connection to yourself you are tying it up so that other resources cannot use
it. You may keep it open even if you are not using it. Usually you want to
get the connection, use it and close it ASAP.

3. By keeping the connection open for long periods of time you are
increasing the likelyhood that the connection gets forecfully closed from the
database side i.e. by the sys admin or a firewall detects the connection has
been idle for a long time and kills the connection etc. You will need
tobuild code into your class to handle these cases i.e. check the connnection
is still open etc. This is an unnecessary hassle for you.

4. .Net handles this kind of scenario behind the scenes using a Connection
Pool. When you open a connection it initially takes some time to connect, but
from there on whenever you call Close, really the connection is not closed
straight away but returned to a connection pool, ready to be used again.
Next time you call Open you will get an open connection from the pool
immediately without any overhead. So there is really no need to have a
singleton object for your connection."