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?

azrakdragonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.