"David Browne" wrote:[color=blue]
> 1 and 2 don't apply to Winforms apps. 3 isn't helped by pooling as you
> still generally need code to detect broken connections.
>[/color]
1 does apply to WinForms, unless you only are using the main UI thread. If
you create multiple threads to process your tasks or even use the threadpool
and they all try to use the same connection you will end up in trouble. I
had to fix some code at work just this week for someones code who had used a
singleton like connection object in one of our multi threaded UI apps - so
this is a problem and I don't recommend you do it.
2 also applies because if you have many win clients hitting a database and
they all hold a connection for the duration of their lifetime then you app
willl not scale. Maybe you will get 50 apps working no problem but the
minute you start getting a significant number of concurrent clients your DB
will run out of connections or slow down significantly. You will have
scaling issues if you hold DB connections for too long - basic software
principle.
3 True there are still times when the conection pool will return you a
connection that is no longer valid, but you are much less likely to see this
happen from the connection pool than if you keep a connection open for long
periods of time. The connection pool will periodically remove bad
connections as it detects them.
[color=blue]
> The problem with 4 is that when you use connections at places in the stack
> you will open multiple connections and consequently cannot enlist the
> changes in a single transaction.
>[/color]
Well - that depends on your software design. If you are calling many
functions then you could pass a single connection throughout the entire call
tree, enabling you to use a single transaction if that is necessary. One the
calls have completed close the connection.
Mark R Dawson
http://www.markdawson.org
[color=blue]
>
> "Mark R. Dawson" <MarkRDawson@discussions.microsoft.com> wrote in message
> news:38B8D8F5-784D-4BF4-BE2E-24D5F263293C@microsoft.com...[color=green]
> > 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.
> >
> >[/color]
>
> 1 and 2 don't apply to Winforms apps. 3 isn't helped by pooling as you
> still generally need code to detect broken connections.
>
> The problem with 4 is that when you use connections at places in the stack
> you will open multiple connections and consequently cannot enlist the
> changes in a single transaction.
>
> David
>
>
>[/color]
