Connection Object and Pooling

[VBCrazyCoder]

Hi all – this should be an easy question:

I have read that when you call a connection objects close() method, the object is not actually closed, but put into the pool (default is for 60 seconds). I have also read that when you call dispose() that close() is called implicitly. Does dispose() put the connection in the pool, or does it totally destroy the object.

Thanks

[Pirate]

MSDN Help

The Close method rolls back any pending transactions. It then releases the connection to the connection pool, or closes the connection if connection pooling is disabled. If Close is called while handling a StateChange event, no additional StateChange events are fired.

What we generally do is : Close the connection , Dispose it without running after these details .

[VBCrazyCoder]

I want to know if I need to open another connection if I call dispose, or if it will still exist.

[Pirate]

Why you don't declare the connection obj as static to use it anywhere while your program is running . I'm doing this right here without any problems so far . One connection object is more better than having multiple ones .

[VBCrazyCoder]

Then you only close() and open() it? You don't call dispose()?

[Pirate]

Sorry missed that part ! This is what I have under my exit button .

MyConnection.Close()
MyConnection.Dispose()
Application.Exit()

[VBCrazyCoder]

My question is a result of an apparent difference between VS.NET 2002 and 2003.

In VS.NET 2002 I have something like:

Public Class DB
 
Private m_CnnConnection As New SqlConnection("myConnectionStringHere")
 
Public Function GetData1(...)...
 
Connection().Open()
'use the connection in here
Connection().Close()
Connection().Dispose()
 
End Function
 
Public Function GetData2(...)...
 
Connection().Open()
'use the connection in here
Connection().Close()
Connection().Dispose()
 
End Function

The calls that I made to are something like:

Dim objDB as New DB(...)
objDB.GetData1
objDB.GetData2

In VS.NET 2002 this code worked fine. I am assuming it was due to Connection pooling, since the call to GetData2 clearly tries to use a disposed connection object.

In VS.NET 2003 this code does not work after the GetData1 calls the dispose().

Any ideas why?

[Pirate]

Well , you could have told me before that you're talking about VS.NET 2003 ! . I dunno about it . But something lik this will work just fine with both versions . I think :

Any ways , I like working with static objects (not always though) . Some other times , I do some inheritance stuff (overriding , deriving etc )
This

Private Shared My_Path As String = Application.StartupPath & "\mydb.mdb"
 
Private Shared My_Password As String = "anything"
 
Private Shared My_Connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyPath & ";Jet OLEDB:Database Password=" & MyPassword)
 
 
Public Shared Property MyPassword() As String
            Get
                Return My_Password
            End Get
            Set(ByVal Value As String)
                My_Password = Value
            End Set
        End Property
 
Public Shared ReadOnly Property MyConnection() As OleDbConnection
            Get
                Return My_Connection
            End Get
        End Property
 
Public Class OpenDB
            Public Shared Sub OpenDB()
                If Not MyConnection.State = ConnectionState.Open Then
                    MyConnection.Open()
                Else
 
                End If
            End Sub
        End Class
 
 
        Public Class CloseDB
            Public Shared Sub CloseDB()
                If MyConnection.State = ConnectionState.Open Then
                    MyConnection.Close()
                Else
 
                End If
            End Sub
        End Class

at the end I dispose my objects using either the Dispose of the form or from anywhere before closing event .

Hope that helps !

[VBCrazyCoder]

Turns out I was thinking about it wrong. The connection pool is handled outside of your code, so anything you do in code - like connection.Dispose() does NOT remove it from the connection pool. You can set parameters to not allow your connection to be pooled, or set the timeout for pooled connections, etc.

So basically, you must call Close() or Dispose() to have the connection put back into the pool. Dispose() does NOT remove it from the pool, it only releases resources on the client side.

[Lunatic3]

The connection pool is handled outside of your code, so anything you do in code - like connection.Dispose() does NOT remove it from the connection pool. You can set parameters......

What you said is a known subject. But you didnt describe why it was different in VS.NET 2003 (Framework 1.1) and VS.NET 2002 (Framework 1.0).

[VBCrazyCoder]

Not really sure why. VS.NET 2002 let me use the code I posted above without error. VS.NET 2003 caught it and burped at me. VS.NET 2002 should have burped too - as I was clearly trying to use a disposed connection object in the second call. Could have been a bug that I exploited in 2002

[Pirate]

It seems we're going to have our VB.NET2002 Code with lots of errors in VB.NET2003 . . BTW , Does it come with any code converter or upgrader ?