Connection strategies


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Connection strategies

  1. #1
    Jim Guest

    Connection strategies


    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

  2. #2
    Q*bert Guest

    Re: Connection strategies


    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



  3. #3
    Paul Clement Guest

    Re: Connection strategies

    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)

  4. #4
    chris hylton Guest

    Re: Connection strategies


    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center