Your Thoughts On the Use Of SqlConnections

[Galen Henderson]

Hello all. I'm just wanted to gather some suggestions and thoughts on SQL
connection within the VB application. I've done this both ways. The question
is this is it better to open a connection declared as a private form level
variable or class level variable when the form or class is instantiated or
is it better to open and close the connection only within the methods that
require database access. I know there are pros and cons to both of these
approaches such as the overhead involved with opening and closing a connection
and the connection limit on the server in question and the load on the database
server itself. There are also multiuser concerns in some applications. I'm
sure there a lot of other considerations to be considered. Just wanted to
gather some thoughts. Anyone have an opinion on this?

 

[Tom Shelton]

Galen Henderson pretended :

Hello all. I'm just wanted to gather some suggestions and thoughts on SQL
connection within the VB application. I've done this both ways. The question
is this is it better to open a connection declared as a private form level
variable or class level variable when the form or class is instantiated or is
it better to open and close the connection only within the methods that
require database access. I know there are pros and cons to both of these
approaches such as the overhead involved with opening and closing a
connection and the connection limit on the server in question and the load on
the database server itself. There are also multiuser concerns in some
applications. I'm sure there a lot of other considerations to be considered.
Just wanted to gather some thoughts. Anyone have an opinion on this?

IMHO, Open and Close as needed. There usually isn't a lot of overhead
with this, because of connection pooling. In other words, just because
you close a connection - doesn't mean it is really closed right away.

This method generally proves to be more scalable - which is way ADO.NET
is designed more around disconnected recordsets