Connection strategies

[Jim]

I would like to hear what other VB Database programmers are doing for reasonable
connection strategies within their client applications?

I have begun to employ a connection with Global scope that is opened in the
form open event of the startup form, and is not closed until the application
is terminated.

This is used as the active connection for all ADODB variables and ADO components
where an active connection is required. The benefits are: 1) only one connection
object is registered on SQL Server; 2) if the connection object requires
changes, the changes only need to be amended in one place in the code.

The biggest down side that I can think of is that this approach requires
diligent error checking each time the global connection object is used -
I can't assume the connection is still open.

Any thoughts on this approach?

Regards, Jim

[Q*bert]

You also therefor have a larger foot print with your application.

I've struggled with this too and ended up using an approach where I had several
functions, one for recordset updates/inserts once for acquiring a recordset
and during each call to the function, the necessary connection was established
and then closed. This added a little to the latency of the application but
only a split second each time. Sure that added up over time, but like you,
all the code was in the same location, errors that are thrown by the connection
or by the SQL are all handled in one location. As a result of this aproach
the foot print was smaller and I didn't have to check for the database connection
each time in the calling function.

Q*bert
!@#($^
"Jim" <james_forrester@urmc.rochester.edu> wrote:
>
>I would like to hear what other VB Database programmers are doing for reasonable
>connection strategies within their client applications?
>
>I have begun to employ a connection with Global scope that is opened in
the
>form open event of the startup form, and is not closed until the application
>is terminated.
>
>This is used as the active connection for all ADODB variables and ADO components
>where an active connection is required. The benefits are: 1) only one connection
>object is registered on SQL Server; 2) if the connection object requires
>changes, the changes only need to be amended in one place in the code.

>
>The biggest down side that I can think of is that this approach requires
>diligent error checking each time the global connection object is used -
>I can't assume the connection is still open.
>
>Any thoughts on this approach?
>
>Regards, Jim

[Paul Clement]

On 1 Oct 2002 17:47:56 -0700, "Jim" <james_forrester@urmc.rochester.edu> wrote:

¤
¤ I would like to hear what other VB Database programmers are doing for reasonable
¤ connection strategies within their client applications?
¤
¤ I have begun to employ a connection with Global scope that is opened in the
¤ form open event of the startup form, and is not closed until the application
¤ is terminated.
¤
¤ This is used as the active connection for all ADODB variables and ADO components
¤ where an active connection is required. The benefits are: 1) only one connection
¤ object is registered on SQL Server; 2) if the connection object requires
¤ changes, the changes only need to be amended in one place in the code.
¤
¤ The biggest down side that I can think of is that this approach requires
¤ diligent error checking each time the global connection object is used -
¤ I can't assume the connection is still open.
¤

I would take a look at the following article. It contains a discussion on connection/resource
pooling and how to implement client side in a VB app (MDAC pooling toolkit). It appears to be an
extension of what you are attempting to implement:

http://msdn.microsoft.com/library/de...l/pooling2.asp


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[chris hylton]

for what it's worth...i use a hybrid of qbert and pauls posts...fully disconnected
data access w/ a single connection...utilizing the client session pooling
capabilities of ADO/OLEDB. Of course, this is strictly a non-MTS 2-tier
design...i.e. no middle layer...just pure client/server. So, it's not exactly
scalable...so this only applies if you aren't trying to build something huge.

I have a rule that I don't use ADO recordsets to directly update the database...I'm
a 100% believer in interrogating the data w/ a stored procedure...I hate
direct rs updates. So, disconnected works great for me...just pull the data
and shut down the connection.

Basically, I have a global connection object and I use the SPTimeout registry
entry for the ado data provider that I'm using...I cut that time out down
to 5 seconds or so...so that connections at the server are dropped as quickly
as possible.

Depending on the app, i've got a standard data class/object that handles
all the communication with the database. When a query or procedure has finished,
I disconnect the recordset and .Close the connection. Within 5-7 second,
ADO session pooling on the client will shut that connection down on the server
and keep minimal info resident (connection string and session information)
so that a call to .Open reopens the connection somewhat quicker than if you
completely destroy it (=nothing).

I haven't found the time hit to reestablish the connection to be too unacceptable...it's
usually a fraction of a second...especially if you don't completely destroy
the object.

Only draw back to this methodolgy is if you need to deal with temp tables,
transactions or the like...you'll have to have control of the connection
to keep it open until you are done...or you'll loose the data in those temp
tables.

Chris

"Jim" <james_forrester@urmc.rochester.edu> wrote:
>
>I would like to hear what other VB Database programmers are doing for reasonable
>connection strategies within their client applications?
>
>I have begun to employ a connection with Global scope that is opened in
the
>form open event of the startup form, and is not closed until the application
>is terminated.
>
>This is used as the active connection for all ADODB variables and ADO components
>where an active connection is required. The benefits are: 1) only one connection
>object is registered on SQL Server; 2) if the connection object requires
>changes, the changes only need to be amended in one place in the code.

>
>The biggest down side that I can think of is that this approach requires
>diligent error checking each time the global connection object is used -
>I can't assume the connection is still open.
>
>Any thoughts on this approach?
>
>Regards, Jim