Is this piece of code dangerous?

I'm building a site, and while stress testing it I received a few exceptions when the SQL Server was under relatively high load. Originally I was opening the connection when required in a particular Sub as follows (and then closing it when I was finished with it):

If Not MyConnection.State = ConnectionState.Open Then MyConnection.Open()

The probelm however was that from time to time the connection state was Opening instead of Closed or Open. So I am considering using the following piece of code instead:

If MyConnection.State = ConnectionState.Connecting Then
Do Until MyConnection.State = ConnectionState.Open

Loop
ElseIf MyConnection.State = ConnectionState.Broken Or MyConnection.State = ConnectionState.Closed Then
MyConnection.Open()
End If

I'm a little worried about the Do...Loop in there, but I don't see how it should be a problem. Any thoughts?

plenderj:

If MyConnection.State = ConnectionState.Connecting Then
Do Until MyConnection.State = ConnectionState.Open

Loop

I'm a little worried about the Do...Loop in there, but I don't see how it should be a problem. Any thoughts?

Yes, it is a problem. While you're waiting, doing nothing, your thread is chewing up CPU, which could be better used by other threads and processes, including the SQL client. A much better solution would be to stick a Thread.Sleep(10) in your loop. It's a small enough interval that it won't hurt, but it won't peg your CPU at 100%.

A better solution yet would be to wait on the StateChange event of the connection, because then you wouldn't use ANY CPU while waiting on the connection, but it would require more code, and if this doesn't happen often, wouldn't buy you much.

|||

mpswaim:

A much better solution would be to stick a Thread.Sleep(10) in your loop. It's a small enough interval that it won't hurt, but it won't peg your CPU at 100%.

DOH! How did I forget about that lol. Thanks for spotting it :D