>>>Hi,
>>>That would work but then you'd have to spin your wheels waiting for the current operation to finish.
>>>Essentially you would be queuing all requests to wait for the same connection to become available.
>>>To my mind it's far better to remove the risks inherent in accessing the same connection from multiple threads altogether.....
>>>
>>
>>This seems to be an extremely rare event from what I see in the thread - if what he has works, a minor safety seems to be a simple solution.
>
>So a 'rare' exception is OK ?
>And the rarity is only because of the workload. More work - more exceptions.....
>Plus, what code would you actually use if the connection reported busy?

Loop until not busy.

>
>>
>>
>>>>I think you may just need to check your connection.State to see if it is busy (Executing or Fetching) .
>>>>
>>>>>>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.