SQL Conn: open/close or keep open?

[Matt C.]

I'm writing a WinForms app, and trying to decide whether or not to leave the
connection object open permanently, or call .Open() and .Close() on it for
each db action. My current understanding says that there is no particular
importance in opening and closing the object because ADO.NET is going to
keep a db connection open anyway (so long as I am using the defaults for
conn pooling).

I see a lot of comments from MVPs insisting one should always use .Open()
and .Close() for each db request. I am not sure if these commenters are
unaware of the db connection being kept open anyway, or they are talking
about web apps only, or they have some further good reason for saying "open
late close early".

Does everyone agree that ADO.NET is going to keep a connection open
regardless of whether or not I use .Close() on the conn object? If so, is
there still an argument for explicitly closing the conn object inside a
WinForms app?

Matt

 

[Sean Nolan]

You should Open and Close. All these wise people are fully aware that the
connection stays open anyway because of connection pooling. What happens is,
when you call Open you either open another real connection OR you get a
connection from the pool. If you do not call Close then after you're done
using the connection it is not returned to the pool, so the next time you
create a connection instance and call Open another real connection is
created. That happens every time you create a connection instance and call
Open until you reach the max in the pool (100 by default) or the server runs
out of connections - either way that is a bad thing.

The overhead of calling Close when you are using pooling (the default) is
very low, so rather do it. You might get away with it if you create one
global connection and always use it, but you're almost certainly going to
end up creating a new one somewhere and then you won't get the benefit of
pooling (if you haven't closed your previous connection).

Having said all of that - it is also true that pooling is of much greater
benefit in an environment like ASP.NET rather than WinForms like you're
using - because a web app might be running code for 10s or 100s of web pages
and have lots of connection instances simultaneously, that's unlikely in a
WinForms app.

Sean

 

[Matt C.]

You should Open and Close. All these wise people are fully aware that
the connection stays open anyway because of connection pooling. What
happens is, when you call Open you either open another real connection
OR you get a connection from the pool. If you do not call Close then
after you're done using the connection it is not returned to the pool,
so the next time you create a connection instance and call Open another
real connection is created. That happens every time you create a
connection instance and call Open until you reach the max in the pool
(100 by default) or the server runs out of connections - either way
that is a bad thing.

Er. I was less than clear, sorry. I am contemplating one single
SqlConnection object for the entire app, opening it once on app start, and
then hitting .Close() on app exit. All db activity to use that one conn
obj.

I did not know that repeated .Open() on the same conn object would generate
new connections. That is interesting. But, not what I was planning.

The overhead of calling Close when you are using pooling (the default)
is very low, so rather do it. You might get away with it if you create
one global connection and always use it, but you're almost certainly
going to end up creating a new one somewhere and then you won't get the
benefit of pooling (if you haven't closed your previous connection).

I am indeed talking about one global connection. I am quite confident that
I will not make any unintended calls to .Open() (or extra connection objects
for that matter) if I go this route.

Opening and closing the conn object every time would make my db class quite
a bit more complicated. I'm willing to do it if there is a good reason--but
I'm not seeing one in my case. Still willing to hear arguments for it,
though.

Thanks for your response.

Matt

 

[Sean Nolan]

Er. I was less than clear, sorry. I am contemplating one single
SqlConnection object for the entire app, opening it once on app start, and
then hitting .Close() on app exit. All db activity to use that one conn
obj.

No that was clear - but you had asked why people said that calling Open and
Close was better and you wondered whether they knew that the connection
stayed open anyway. My point is that they do know that and I explained that
the reason that is better comes when you have lots of different pieces of
code that cannot all share a single connection (like all the code in a web
application). In a busy web app if you had one global connection then
performance would be terrible. So connection pooling uses as few as possible
connections to service multiple simultaneous requests. Your app isn't like
that so you can go against the conventional wisdom, not because the
conventional wisdom is wrong, but because your app doesn't need simultaneous
connections.

I did not know that repeated .Open() on the same conn object would generate
new connections. That is interesting. But, not what I was planning.

It won't, once a connection is open you cannot call Open on it again until
after you close it. What will happen however is that if you call Open, then
Close, then a second connection calls Open and gets the connection from the
pool that the first connection originally had, then when you call Open on
the first connection again, it will get a new connection from the pool.

Opening and closing the conn object every time would make my db class
quite
a bit more complicated. I'm willing to do it if there is a good
reason--but
I'm not seeing one in my case. Still willing to hear arguments for it,
though.

There may not be, but here are two possibilities:
1. If you have a DataReader open and you are reading through the records in
the DataReader and you need to run another query, lets say an ExecuteScalar
to get a value based on some data from the DataReader, then you have to use
another connection because a DataReader needs exclusive access to the
connection and you cannot use the connection for other commands while the
DataReader is open. You can write your code to make sure that you never do
this, but it is an example when even a single user WinForms app would need
more than one connection.
2. I haven't gone into this in too much detail, but I'm fairly certain that
a connection that is returned to the pool (with the Close method) will make
better use of the database server resources because - if a user opens your
app and then goes on vacation for a week, the database server will have a
connection in use the whole time because I don't think the app will ever
give up that open connection. However, if your app was calling Open when you
use the connection and Close when done, the connection would be returned to
the pool where it would time out after 20 min (configurable) releasing the
resources on the server. When the user does return the connection is simply
reopened. I suspect you also run a greater risk of runtime errors if you use
one global connection because you're always assuming it stayed open, instead
of opening it whenever you use it.

Sean

 

[J L]

Pardon me for jumping in here...but I was wondering, even though your
app need only one connection, how many instances of the app might be
running at one time (in a networked environment you might have many
users). Then you would have a connection per user and that might be a
problem?

John

 

[Matt C.]

There may not be, but here are two possibilities:
1. If you have a DataReader open and you are reading through the
records in the DataReader and you need to run another query, lets say
an ExecuteScalar to get a value based on some data from the DataReader,
then you have to use another connection because a DataReader needs
exclusive access to the connection and you cannot use the connection
for other commands while the DataReader is open. You can write your
code to make sure that you never do this, but it is an example when
even a single user WinForms app would need more than one connection.

Thanks for the example. Don't envision doing this (or even using the DR at
all), but I'll keep the scenario in mind if I use the DR.

2. I haven't gone into this in too much detail, but I'm fairly certain
that a connection that is returned to the pool (with the Close method)
will make better use of the database server resources because - if a
user opens your app and then goes on vacation for a week, the database
server will have a connection in use the whole time because I don't
think the app will ever give up that open connection. However, if your
app was calling Open when you use the connection and Close when done,
the connection would be returned to the pool where it would time out
after 20 min (configurable) releasing the resources on the server. When
the user does return the connection is simply reopened. I suspect you
also run a greater risk of runtime errors if you use one global
connection because you're always assuming it stayed open, instead of
opening it whenever you use it.

Yes, this makes sense, both points. Not enough to tip the balance, but
valid arguments. I don't expect users to go on vacation for a week. This
app will probably be run from a Terminal Server, so I imagine there will be
policy in place to stop anybody from sitting around idle very long. Having
the connection go down and the app not being able to recover...a small risk
I think. So long as it's very rare a message saying "oops, lost the db,
please restart app" will be OK. (Go ahead, flame on.)

Anyway, I appreciate the feedback. Thanks.

Matt

 

[Matt C.]

Pardon me for jumping in here...but I was wondering, even though your
app need only one connection, how many instances of the app might be
running at one time (in a networked environment you might have many
users). Then you would have a connection per user and that might be a
problem?

Unless I'm missing something, networked environment or (my case) Terminal
Server doesn't change this for my WinForms app. Unless I wrote some kind of
middle tier I would still have one db conn per app instance. I think.

Matt