none
Connection Object reuse

    Question

  • I refer to a connection created with the new (New in vb) keyword declared at the class level (private). It is opened and closed in various methods. I have not had problems but very experienced people have said this is very bad. Reading the docs, it seems the connection that once Close() is called the connection is returned to the pool. If a general reply is not possible, limiting to SQL Server provider is fine. I would like to know why it is bad.

    Mary

    Thursday, March 8, 2018 6:47 AM

All replies

  • You asked this question in the Visual Basic .NET forum and I did provide an answer:

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/fdc1d30f-5c88-4f08-a9c8-939d2b34adfc/whats-wrong-with-reusing-a-connection-object?forum=vbgeneral

    With file based databases such as Microsoft Access you do increase the risk of corruption by maintaining persistent connections, especially in a network environment. With respect to server type databases you're simply maintaining a resource (connection to the database that is managed by the server) that is not being used. If there are many users, persistent connections can impact database performance. An experienced DBA monitoring the database server will probably wonder why all of these connections are persisting over extended periods of time.

    This is why it is recommend that connections only be requested as needed and then closed when finished or not needed.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, March 8, 2018 1:51 PM
  • Thank you Paul, I did read your reply in the VB forum but I thought you meant open connection when you said persistent connection.  I was under the impression (from MS docs) that when I called close on a connection it was returned to the connection pool. I know I cant use it again until I open it. Because the object still exists in my program then it is somehow still connected? I guess I will just have to wait for understanding and concede the point to those who have much more knowledge. One of these days, I will get a big Aha! and the fog will lift. :-)

    Mary

    Friday, March 9, 2018 3:40 AM
  • Persistent means that the app has not closed the connection in order to release it to the pool. The app can either close the connection explicitly, usually by calling the Close method, or the connection can be closed because the variable reference goes out of scope. Once the connection is closed in one of these two ways it is released to the pool. The pooled connection is ultimately destroyed (closed) if there is not another connection (open) request for the pooled connection within a certain timeframe - usually a couple of minutes. If there is another connection request the pooled connection can be reused, without having to make a call to the database for a new connection.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, March 9, 2018 4:38 AM
  • So, if I explicitly .Close(), immediately after my execute call or when my reader is finished and (belt and suspenders) also Check state in a Finally then .Close again if necessary, I am OK? This is my current practise. SQL Server and the pool cares not if my object continues to exist as long as it is most definitely an explicitly closed connection? No waiting to fall out of scope and wondering when GC will run. Close is definitely called.

    Mary

    Friday, March 9, 2018 7:39 AM
  • So, if I explicitly .Close(), immediately after my execute call or when my reader is finished and (belt and suspenders) also Check state in a Finally then .Close again if necessary, I am OK? This is my current practise. SQL Server and the pool cares not if my object continues to exist as long as it is most definitely an explicitly closed connection? No waiting to fall out of scope and wondering when GC will run. Close is definitely called.

    Mary


    Yes, that sounds fine. :-)

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, March 9, 2018 3:06 PM