Solved

Closing a connection

Posted on 2004-10-27
235 Views
Last Modified: 2006-11-17
I got two parts to my problem:

1. When I open a connection to  sql2000 db, query analyzer shows two connections from my machine (using sp_who). Any ideas why? Here is the code (very basic):

Public Class Class1
    Inherits System.ComponentModel.Component
    Public Sub opencon()
        Dim conConnection As New System.Data.SqlClient.SqlConnection
        conConnection.ConnectionString = "(con string here)"
        conConnection.Open()
        conConnection.Close()
        conConnection.Dispose()
        conConnection = Nothing
    End Sub
End Class

On a form:
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim x As New Class1
        x.opencon()
        x.Dispose()
        x = Nothing
    End Sub

2. Even tho dispose is called for both the conn and class, the connection is still alive and well in query analyzer. Should it not be destroyed by the dispose?

0
Question by:azrakdragon
    13 Comments
     
    LVL 4

    Expert Comment

    by:Glom
    Hi,
    I could be on the wrong way, but couldn't it be YOUR connection to query analyser that represents the second one... ?
    0
     

    Author Comment

    by:azrakdragon
    Nope, there is a third one that is the query analyzer connection. So all in all there are three connections.
    0
     
    LVL 5

    Expert Comment

    by:Ignacio Soler Garcia
    Ok, I've tested your code with the same result, but don't worry, code will work as expected, so, your closed connections will be closed really when the app stops.

    Also, to test the connections is better to use the SQL Profiler (under the tools) rather than the sp_who.

    I will test more in the future, I don't have a lot of time now.
    0
     
    LVL 28

    Expert Comment

    by:iboutchkine
    Dispose does not close the connection (COnn.CLose) does. Dispose only markes object to be deleted the next time Garbage collector will fire. GC will fire when you machine will not have enough memory. So don't worry about it
    0
     
    LVL 4

    Expert Comment

    by:Glom
    Hmm I don't totally agree with you iboutchkine.
    You are right about Conn.Close. But the Dispose Method role is to destroy the inner objects that are inside the Conn object. The garbage collector will be aware of Conn = Nothing, which frees the handle on this object.
    0
     
    LVL 28

    Expert Comment

    by:iboutchkine
    Dispose doesn't release the memory used by the object. It is a mechanism by
    which you can tell an object that you no longer need it so it can do any
    cleanup (e.g. Close open files, close database connections or release
    unmanaged resources). After you call dispose, and set your reference to the
    object null (or nothing in VB), the object becomes "Unreachable" from your
    code - the garbage collector can come along at any time and deallocate the
    memory used by the object.

    The garbage collector is not only for VB.net - it is used by any managed
    language (C# etc.) too.

    Memory management in .net is a little bit more complicated than the garbage
    collector deallocating unreachable objects. For example, on a machine with
    lots of free memory, the CLR might allocate a lot of memory to aid
    performance - even though it is not all used. I'm not sure of the exact
    algorithms used, but I know it's sufficiently complicated and well hidden
    that I shouldn't worry about memory leaks.

    One exception to this is if you are using unmanaged resources (e.g. calls to
    COM objects, graphics methods, files, database connections etc.). If you
    don't manually release these objects (By calling dispose or another
    mechanism), you will end up with memory leaks.

    If you want to clean up immediately
    use
    GC.SuppressFinalize(Me)



    --------------------------------
    Public Overloads Sub Close()
      Implements IDisposable.Dispose
       Dispose(True)
       GC.SuppressFinalize(Me)
    End Sub


    you have a powerful garbage collector I won't spend time on
    this and I'll let GC do its work.
    As you know you can force GC to reclaim memory but this will slow down your
    code.
    0
     
    LVL 28

    Expert Comment

    by:iboutchkine
    Here is another example

    Garbage collection is now being handled by the runtime. In VB6, if you set an object
    to Nothing, it was destroyed immediately. This is no longer true in VB.NET. Instead,
    when you set an object to Nothing or it loses all its references, it becomes garbage
    collectable. It’s still in memory, taking up resources. The garbage collector runs on a
    separate thread, and it passes by occasionally looking for objects to clean up
    (destroy). This lag is often less than a second, but it could be longer on a system in
    heavy use. Even though the object has been marked for garbage collection, any
    resources it has opened are still open, including any data or file locks that it might
    have obtained.
    Microsoft calls this “no deterministic finalization” because the developer is no longer
    truly in control of when the object will be destroyed. The Sub Dispose is called
    when the object is truly destroyed by the garbage collector. You can call the garbage
    collector by using the Collect method of the GC class in the System namespace.

    To clean up objects that are pending collection, you can call on the Garbage Collector to collect all of the
    waiting objects immediately. You can force garbage collection with the following two calls:

    GC.Collect
    GC.WaitForPendingFinalizers
     
     The two calls to Collect and to WaitForPendingFinalizers are required in the order shown above.
    The first call to Collect kicks off the garbage collection process asynchronously and immediately returns.
    The call to WaitForPendingFinalizers waits for the collection process to complete.
     
    'Example
    Dim conn As New ADODB.Connection
    conn.Open( ... )
    Dim rs = conn.Execute( ... )
    conn = Nothing  ' The object is not destroyed here and the
                    ' connection is still open

    A proper way to handle this would be as follows:

    Dim conn As New ADODB.Connection
    conn.Open( ... )
    Dim rs = conn.Execute( ... )
    conn.Close()
    conn = Nothing
    The Close method ensures that the database connection is released immediately
    ------------------------------------
    Dispose is just a convential cleanup routine that you can implement with the
    IDisposable interface. In it you can set all internal object references to
    nothing, which will get it ready for cleanup. Built-in classes are no
    different. So calling Dispose does not force the GC to immediately clean it
    up, but it is good practice. You don't need to do this for a controls,
    however, as forms will dispose them for you.

    0
     
    LVL 4

    Expert Comment

    by:Glom
    Just what I wanted to say :)
    You are just about 1000 times more explicit !
    0
     

    Author Comment

    by:azrakdragon
    Ok, lots of useful info but I stll do not understand why two connections are being opened?
    0
     
    LVL 5

    Accepted Solution

    by:
    Here you have the info about this issue:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp

    The SQLClient creates a connection pool to serve you a connection each time you need one.

    To avoid this behaviout just add pooling=false to your connection string.

    It's always good to learn things helping you ...

    Thank a lot mate!
    0
     

    Author Comment

    by:azrakdragon
    Thats great! Thanks for your help SoMoS. Killed 2 birds with one stone. One more tho:
    What will the results be if pooling is turned off when many users are expected? If it is left on will each user that connects leave two connections sitting in the pool. In other words if 500 connect will 1000 connections be sitting in the pool? If so I imagine it will be better not to pool them?
    0
     
    LVL 5

    Expert Comment

    by:Ignacio Soler Garcia
    Nonono, that just the opposite!!! Connection pools are done to improve performance because creatting a new connection is very costly. I don't know exactly how the system works but you can be sure that polling on reduces the load on the server with high connections number.
    0
     

    Author Comment

    by:azrakdragon
    With alot of testing I can see that pooling is the way to go. Thanks again for your help!
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Shellfire Box VPN + Lifetime Subscription

    The Shellfire Box easily connects all of your devices, even those that don't offer the possibility to establish a safe vpn connection. Access blocked content and surf safely, no matter where in the world you are located.

    A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now