Hi ed_p,
there are quite a few reasons not to do this:
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.
Infact I had to spend my time recently fixing someone elses code who just
left where I work. He had made a static connection object on the class in a
multi threaded environment, not very pretty :-)
Mark R Dawson
http://www.markdawson.org
"ed_p" wrote:
Hello,
I have implemented the singleton pattern for a class to hold a
SqlConnection object that
will be used thruout the application to create commands. My application
is a simple Windows Form
Application that connects to a MSDE Database. I was reading the thread
yesterday on this very
same subject, but it only mentioned ASP.NET Applications. Can anyone
tell me if it's a good idea
to have one single SqlConnection Object in the application. Please note
that the Singleton Class
has been configured to be thread-safe. Below is some of the code I am
using to call the instance
of my Singleton class:
SqlCommand command = DB_Connection.I nstance.Connect ion.CreateComma nd();
Once I am done with the connection, I close it down:
command.Connect ion.Open();
//Do stuff here
command.Connect ion.Close();
I also dispose of the command object to make sure I free up resources.
Any information, suggestions, critiques
will be welcomed!
