Re: Connection Pooling with Global ADO.Connection Object

"Eric Litwin" <eric_litwin@countrywide.com> wrote:

You have to "Open" the connection for the second class. You used it for
the initial class call, then you closed it. So, the next class that is instantiated
and uses your global connection needs to re-OPEN it (it is closed, not set
to NOTHING).

Use something like this when closing a connection:
If Conn.State =adStateOpen then
Conn.Close
end if

When opening it use (put this where global connection is created):
If Conn.State =adStateClosed then
Conn.Open
end if

This will work for you. Again, the connection is not set to nothing, it
is just closed, but hangs in there till you re-open it.

Darta
>
>I have an N-Tier framework that attempts to implement connection pooling
in
>the data layer (if that layer is run on the client).
>
>I do this by having a public global ADO Connection object in a module within
>the data layer. I initialize the object (New) at start-up, Open and then
>immediately Close the connection. I do not set this object to Nothing until
>the program shuts down. (This object is also used for transaction control
>if the layer is not in MTS.)
>
>The problem I have is that the when I run my project group with all the
layers
>in the VB6 IDE, the global connection object remains initialized and works
>properly. However, when I compile the Data Access layer, the object gets
>initialized (New->Open->Close) properly and I can use it once from one of
>the Classes. When another class tries to access the connection object,
it
>has been set to nothing and I am forced to set it to New again before I
try
>to Open it.
>
>I am pretty sure I am losing the connection pooling once the object is set
>to nothing. I cannot figure out where/why it is getting set to nothing.
>I only explicitly set it to nothing on shutdown. My class modules use Command
>objects and recordsets that set their "ActiveConnection" property to this
>connection object, and when they are finished, they Close the Recordset/Command
>object and set it to nothing. I put traces in the code at these points
but
>the Global connection object is still around. It just disappears the second
>time I try to access it when a new Class module is created.
>
>Any help/ideas would be greatly appreciated.

>>I have an N-Tier framework that attempts to implement connection pooling
>in
>>the data layer (if that layer is run on the client).
>>
>>I do this by having a public global ADO Connection object in a module within
>>the data layer. I initialize the object (New) at start-up, Open and then
>>immediately Close the connection. I do not set this object to Nothing
until
>>the program shuts down. (This object is also used for transaction control
>>if the layer is not in MTS.)
>>
>>The problem I have is that the when I run my project group with all the
>layers
>>in the VB6 IDE, the global connection object remains initialized and works
>>properly. However, when I compile the Data Access layer, the object gets
>>initialized (New->Open->Close) properly and I can use it once from one
of
>>the Classes. When another class tries to access the connection object,
>it
>>has been set to nothing and I am forced to set it to New again before I
>try
>>to Open it.
>>
>>I am pretty sure I am losing the connection pooling once the object is
set
>>to nothing. I cannot figure out where/why it is getting set to nothing.
>>I only explicitly set it to nothing on shutdown. My class modules use
Command
>>objects and recordsets that set their "ActiveConnection" property to this
>>connection object, and when they are finished, they Close the Recordset/Command
>>object and set it to nothing. I put traces in the code at these points
>but
>>the Global connection object is still around. It just disappears the second
>>time I try to access it when a new Class module is created.
>>
>>Any help/ideas would be greatly appreciated.
>
Ok.

If your are trying to implement a Connection pooling, it is not necesary
you declare the Connection object in a .BAS module. You have to remember
the Connection pooling occur when "the connection string is the same and
is the SQL Server who take care about."

So if you want to accomplish the Connection pooling in your Data access object,
you have to open and close the connection in every method (Sub or Function)
needs read data from the database.

Becuase you're using MTS (very good) this technique is the want you have
to use, you will get ugly Behavior sharing connection in a MTS environment.

So your code will look something like this:
Public Sub Example()
Dim cnnExample as ADODB.Connection

Set cnnExample = New ADODB.Connection
cnn.Open <Your connection string here>

'Do more stuff here.....

cnn.Close
Set cnn = Nothing

End Sub

When you call this method from your client, more one time you will see how
you achieve your goal (Connection pooling).

Let me know if I'm wrong, we can talk about it

Have fun :-)