locked
keep one sqlconnection alive RRS feed

  • Question

  • how can I keep one sql connection alive in my application and use that connection in my all forms? and does it have any disadvantages?
    Jassim Rahma
    Thursday, April 8, 2010 11:31 PM

Answers

  • The SqlConnection object automatically supports connection pooling so that you don't have to worry about keeping the connection around.  The best practice is to construct a new SqlConnection object, perform a unit of work, and then close (dispose) the SqlConnection object.  Keep the SqlConnection object around for as short of a time as possible to do the work at hand.  Closing the SqlConnection object does not disconnect from the database server immediately.  Instead, the actual underlying connection is placed in a pool where it can be reused again (the full delay of opening the transport connection to SQL Server is not incurred while the connection remains in the pool).

    (The main disadvantage of what you stated occurs when there are multiple threads.  If you have only one SqlConnection, it can only be used by one thread at the same time.  By using the technique where you take advantage of the pool, the system automatically takes care of maintaining the proper number of connections regardless of how many threads might need to access the database.)

    Note that the best forum for ADO.NET questions is: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/threads

     

    • Proposed as answer by DeborahKMVP Friday, April 9, 2010 12:38 AM
    • Marked as answer by SamAgain Friday, April 9, 2010 8:26 AM
    Friday, April 9, 2010 12:19 AM