Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Closing a connection

Posted on 2004-10-27
Medium Priority
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 = 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 = 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?

Question by:azrakdragon
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1

Expert Comment

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

Author Comment

ID: 12431495
Nope, there is a third one that is the query analyzer connection. So all in all there are three connections.

Expert Comment

by:Ignacio Soler Garcia
ID: 12432319
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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 28

Expert Comment

ID: 12433020
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

Expert Comment

ID: 12433092
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.
LVL 28

Expert Comment

ID: 12433143
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

Public Overloads Sub Close()
  Implements IDisposable.Dispose
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
LVL 28

Expert Comment

ID: 12433187
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:

 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.
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 = 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.


Expert Comment

ID: 12433270
Just what I wanted to say :)
You are just about 1000 times more explicit !

Author Comment

ID: 12441601
Ok, lots of useful info but I stll do not understand why two connections are being opened?

Accepted Solution

Ignacio Soler Garcia earned 1200 total points
ID: 12442236
Here you have the info about this issue:


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!

Author Comment

ID: 12442658
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?

Expert Comment

by:Ignacio Soler Garcia
ID: 12443103
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.

Author Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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