SQLClient Exception - Retrying

  • Thread starter Thread starter Shawn Berg
  • Start date Start date
S

Shawn Berg

Upon executing a stored procedure using the Data Access application block's
SQLHelper class, I would like to continually retry to execute it every 1
second up to 3 times if an exception occurs. For example, if for some reason
the database server is down, a timeout occurs, etc. I would like to
automatically retry a certain number of times before giving up. What is the
best way to accomplish this? Below is what I envision (pseudo-code):

Dim Retries As Integer = 0
Dim

Try
'// execute database command here
SQLHelper.ExecuteNonQuery(...)
Catch ex AS SQLException
If Retries <= 3 Then
Sleep(1000)
SQLHelper.ExecuteNonQuery(...)
End If
End Try

The problem I have with the code above is that I have duplicate code
(SQLHelper.ExecuteNonQuery(...)). The other problem I have, is what happens
when an exception occurs in the Catch block?

Any help would be greatly appreciated.

Thanks,

Shawn
 
Ah, if the server goes down the pooled connection will be corrupted and
cannot be reused. You'll also have to wait a lot longer than 3 seconds for
the server to restart. If you lose the network you have most of the same
issues. Putting an ADO operation in the Catch block is problematic--it needs
its own Try/Catch.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
You are correct, but I do feel it is important to at least try a few more
times before completely giving up. It won't hurt, right? And as far as the
time goes, sometimes we get intermittent timeouts with our DB (very rare,
but it does happen) and these I would definitely like to retry and 99% of
the time they go through the second time around.

Can you give me any suggestions as to how this can be accomplished?

Shawn
 
With timeouts, yes it makes sense to retry. With the other failures, it's a
waste of time. I would add code to test the viability of the LAN and SS
connection and retry once they are back up. In the meantime I would inform
the user that there are problems with the server and/or back off to a
mirrored or backup server. This will be easy with Whidbey/Yukon and the 2.0
Framework. It can be done now, but it's a bit harder.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
This app I am programming is not operated by a live user, but more of a
service app which needs to continually run uninterrupted no matter what
network problems may be occurring. When there are network problems,
timeouts, sql server is down, etc. I need the app to handle this gracefully
and process all remaining transactions when the problems are resolved. This
has to be done without any user intervention. This is why I want to retry no
matter what the error may be, and continue to do so until the problem is
resolved.

What is the best way to code this?
 
Anyone have any suggestions?

Shawn Berg said:
This app I am programming is not operated by a live user, but more of a
service app which needs to continually run uninterrupted no matter what
network problems may be occurring. When there are network problems,
timeouts, sql server is down, etc. I need the app to handle this gracefully
and process all remaining transactions when the problems are resolved. This
has to be done without any user intervention. This is why I want to retry no
matter what the error may be, and continue to do so until the problem is
resolved.

What is the best way to code this?


it's What
Click to expand...
 
How about this? (just taking your pseudo-code and adding more pseudo-code :)

Dim Retries As Integer = 0
Do While Retries < 3
Try
'// execute database command here
SQLHelper.ExecuteNonQuery(...)
Exit Do // if we made it here then there was no exception, just leave
the loop
Catch ex AS SQLException
Retries = Retries + 1
if Retries > 2 Then
throw // don't know VB syntax for rethrow :), otherwise just do
"throw ex" and loose the stack-trace
else
Sleep(1000)
end if
Loop

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top