Results 1 to 4 of 4

Thread: Data Connection - Best Practice

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2004
    Location
    Manchester
    Posts
    266

    Data Connection - Best Practice

    Hello All,

    I was having a debate with a colleague today re: best practice when it comes to handling connections to online databases.
    Imagine a situation like this:

    • We connect to the database using one connection object
    • We make a query
    • As a result of that query, we need to make another


    My colleague argues that it would be better to initiate a new connection object to make the new query; I argue that one should close the current connection object, wait for it to close and then re-connect using the same connection object to perform the new query.

    Which do you prefer?

    Jord

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Data Connection - Best Practice

    If I use a strict reading of the scenario, the answer is "Neither - use the same connection"... but, if the situation has logic between the the last two steps, then the answer is "who cares?" An object is just that, an object... doesn't matter if it's a new one or a recycled one, because the underlying connection is what really matters, and since ADO.NET uses connection pooling when it can, the object is irrelevant.

    Generally speaking the generally accepted best practice is to have the connection open for only as long as you need to in order to perform the action desired. That action could consist of one query, or a series of queries, but once the action is done, you close the connection and move on. To that end, most people create the connection object, open it, use it, close it and dispose of it as quickly as possible... there's no need to keep the object around if you're not going to need it.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Data Connection - Best Practice

    Quote Originally Posted by intraman View Post
    ... wait for it to close...
    Why do you say this?

    Do you think that the "closing" of the connection is hand-in-hand with the committing of the last query run? Flushing of some buffer??

    It is not related.

    If you are holding a TRANSACTION open - then that's a different question.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Data Connection - Best Practice

    Personally I use one connection for a solution that resides in a class project removed from the main project. Depending on the operations dicates if the connection stays open or is closed after operations.

    MSDN VS2010 sample solution, refer to the data access project for MS_AccessConnection.vb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width