>>It's just not good practice to keep a connection open for any longer than is neccessary. With connection pooling the connection is only released to the pool - not physically closed. Subsequent calls to Connection.Open() will be virtually instantaneous assuming there are connections available in the pool. If there are not then the worst that can happen is that a new one is created. There may be an argument for maintaining the same connection within a very tight loop. Maintaining a connection even for long enough to service a single request is not a good idea.......
>>
>>There was a thread here recently which led me to test connection open/close timings. Rough results:
>>First Open : 117 milliseconds.
>>Total time for 10,000 subsequent open/close calls : 465 milliseconds.
>>
>>I don't think there's much of a timing issue there ?
>
>Thanks for the benchmark
>
>While you were writing this message, I adjusted my code to this:
>
>

>            ' If the connection is already opened
>            If lConnectionIsAlreadyOpened Then
>
>                ' Get the proper definition as per the current scope
>                If oProcess Is Nothing Then
>                    oCommand.Connection = oApp.aConnection(nConnectionString, 2)
>                Else
>                    oCommand.Connection = oProcess.aConnection(nConnectionString, 2)
>                End If
>
>                ' The following two lines are added here so to close and open the connection back. This is to avoid
>                ' the SQLDataReader related objects to bug once in every hundreds of thousands of SQL requests. When
>                ' that happens, this creates a message like "There is already an open DataReader associated with this
>                ' Command which must be closed first."
>
>                ' Close the connection
>                oCommand.Connection.Close()
>
>                ' Open the connection
>                oCommand.Connection.Open()
>
>            Else
>

>
>This is where all data requests go. So, I added the big comment followed by Close() and Open(). I tested it on the robot and it performs as fast. Then, I uploaded the new version of the framework on a Web site and the numbers were as fast. So, with that change in mind, we will see how it goes. I assume it should resolve that issue. The way I used it so far demonstrated that 2 to 4 times a year, I was getting a situation at SQL Server level which was not fitting well within that infrastructure.

I'm not sure that solution is going to work.
Previously you were probably trying to make use of a connection that was still busy.
Now you might be trying to close a connection that's still busy - which is just as bad...

Bear in mind that there's very little extra overhead in creating a *new* connection providing that the connection string matches an existing pooled connection - testing as above showed that 10,000 'new' connections, by comparison, took 950 ms instead of 450.