Re: Connection Pooling with Global ADO.Connection Object


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Re: Connection Pooling with Global ADO.Connection Object

  1. #1
    Darta Guest

    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.



  2. #2
    Gonzalo Medina Guest

    Re: Connection Pooling with Global ADO.Connection Object


    >>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 :-)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center