[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Closing a connection

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
azrakdragon
Asked:
azrakdragon
  • 4
  • 3
  • 3
  • +1
1 Solution
 
GlomCommented:
Hi,
I could be on the wrong way, but couldn't it be YOUR connection to query analyser that represents the second one... ?
0
 
azrakdragonAuthor Commented:
Nope, there is a third one that is the query analyzer connection. So all in all there are three connections.
0
 
Ignacio Soler GarciaSolution Architech & Technical LeadCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
iboutchkineCommented:
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
 
GlomCommented:
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
 
iboutchkineCommented:
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
 
iboutchkineCommented:
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
 
GlomCommented:
Just what I wanted to say :)
You are just about 1000 times more explicit !
0
 
azrakdragonAuthor Commented:
Ok, lots of useful info but I stll do not understand why two connections are being opened?
0
 
Ignacio Soler GarciaSolution Architech & Technical LeadCommented:
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
 
azrakdragonAuthor Commented:
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
 
Ignacio Soler GarciaSolution Architech & Technical LeadCommented:
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
 
azrakdragonAuthor Commented:
With alot of testing I can see that pooling is the way to go. Thanks again for your help!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now